Oracle Scripts & Tricks

--Script to monitor the Historic growth of the tablespaces --

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname  NOT IN ('SYSTEM','SYSAUX')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;


 --Script to find the LR(Logical reads) & DR(Disk Reads) of an SQL stmt --
select
sql_id "Sqlid",
--sql_fulltext,
--last_active_time "LastActive",
--first_load_time "FirstLoaded",
--parsing_schema_name "ParsingUser",
executions "Executions",
--round(fetches/executions,2) "FetchesPerExec",
--round(fetches,2) "TotalFetches",
--round(rows_processed /executions,2) "RowsPerExec",
--rows_processed "TotalRowsProcessed",
round(buffer_gets/executions,2) "LRsPerExec",
--round(buffer_gets,2) "TotalLRs",
round(disk_reads/executions,2) "DRsPerExec",
--round(disk_reads,2) "TotalDRs", 
round((elapsed_time/1000000)/executions,3) "ElapsedPerExec",
round((elapsed_time/1000000),2) "TotalElapsedc",
cpu_time
--sql_fulltext "Sqltext"
from v$sql
where
parsing_schema_name like 'CMPS%'
and sql_fulltext NOT LIKE '%v$sql%'
and sql_fulltext NOT LIKE '%QRTZ%'
and sql_fulltext NOT LIKE '%session%'
and lower(sql_fulltext)  LIKE '%<enter the sql text here>%'
and executions > 0
--and TO_date(last_active_time, 'DD-MON-YYYY') = TO_date(sysdate, 'DD-MON-YYYY')
and last_active_time > to_date('24-11-2011 08:30:00', 'DD-MM-YYYY HH24:MI:SS')
--and (sql_id in ( 'xxxxxxxxx,'xxxxxxxxxx ) )
--and upper(sql_text) like '%con.cust_stat_cd = %'
order by last_active_time desc
--order by sql_id