Scripts

table_size.sql

set lines 155
compute sum of totalsize_megs on report
break on report
col owner for a20
col segment_name for a30
col segment_type for a10
col totalsize_megs for 999,999.9
select * from (
select owner, segment_name, segment_type type,
sum(bytes/1024/1024) as totalsize_megs,
tablespace_name
from dba_segments
where owner like nvl('&owner',owner)
and segment_name like nvl('&table_name',segment_name)
and segment_type like nvl('&type',segment_type)
group by owner, segment_name, tablespace_name, segment_type
order by 4 desc
)
where rownum < 30
order by 4;

part_size.sql

set lines 155
compute sum of totalsize_megs on report
break on report
col owner for a20
col segment_name for a30
col segment_type for a10
col totalsize_megs for 999,999.9
select owner, segment_name, partition_name, segment_type type,
sum(bytes/1024/1024) as totalsize_megs,
tablespace_name
from dba_segments
where owner like nvl('&owner',owner)
and segment_name like nvl('&table_name',segment_name)
and partition_name like nvl('&partition_name',partition_name)
and segment_type like nvl('&type',segment_type)
group by owner, segment_name, partition_name, tablespace_name, segment_type
order by 4;

dplan_awr.sql

set lines 180
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id','a96b61z6vp3un'),nvl('&plan_hash_value',null),null,'typical +peeked_binds'))
/

dba_tables.sql

select owner, table_name, status, last_analyzed, num_rows, blocks, degree
from dba_tables
where owner like nvl('&owner',owner)
and table_name like nvl('&table_name',table_name)
/

awr_plan_change.sql

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and executions_delta > 0
order by 1, 2, 3
/

sql_text_info.sql

accept sql_text prompt "Please enter sql text: "

set pagesize 999
set lines 200
col sql_text format a36 trunc
col inst for 99
col total_row for 99999999
col execs for 9,999,999
col avg_etime for 99,999.999
col avg_lio for 999,999,999.9
col avg_pio for 999,999,999.9
col begin_interval_time for a30
col hash_value for 9999999999
col "interval time" for a20
break on hash_value on startup_time skip 1

select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') "interval time", ss.instance_number inst, s.sql_id, plan_hash_value hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
trim(replace(dbms_lob.substr(sql_text,36, 1),chr(9),'')) sql_text
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, DBA_HIST_SQLTEXT ST
where
dbms_lob.substr(sql_text,3999,1) like  '&sql_text'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.sql_id = st.sql_id
and executions_delta > 0
order by begin_interval_time desc
;
undef sql_text

Advertisements