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

3 thoughts on “Scripts

    • Jon,
      Thanks for the feedback. I intentionally did not include index in this post as I plan to discuss index thing in the future post related to HCC. The script above actually covers the scenario for index. I did a quick test as follows:

      WZHOU@dbm1> alter table testcp add constraint testcp_pk primary key(id);
      Table altered.
      WZHOU@dbm1> create index testcp_objid_idx on testcp(object_id);
      Index created.
      
      WZHOU@dbm1> @table_size
      Enter value for owner: WZHOU
      Enter value for table_name: TESTCP%
      Enter value for type: 
      
      OWNER  SEGMENT_NAME           TYPE  TOTALSIZE_MEGS TABLESPACE_NAME
      ------ ------------------ --------- -------------- ------------------------------
      WZHOU  TESTCP_ARCHIVE_LOW    TABLE       59.0      USERS
      WZHOU  TESTCP_ARCHIVE_HIGH   TABLE       59.0      USERS
      WZHOU  TESTCP_QUERY_HIGH     TABLE       59.0      USERS
      WZHOU  TESTCP_QUERY_LOW      TABLE      161.0      USERS
      WZHOU  TESTCP_OBJID_IDX      INDEX      176.0      USERS
      WZHOU  TESTCP_PK             INDEX      176.0      USERS
      WZHOU  TESTCP_BASIC          TABLE      384.0      USERS
      WZHOU  TESTCP_OLTP           TABLE      424.0      USERS
      WZHOU  TESTCP                TABLE    1,152.0      USERS
      				     --------------
      sum                                   2,650.0
      
      9 rows selected.
      

      Anyway, still a good suggestion. Thanks.

  1. Pingback: SQL Tuning beyond the Execution Plan | My Big Data World

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s