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
You should include indexes when looking at table sizes. You can union on segments where segment name is equal to index name in dba_indexes
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:
Anyway, still a good suggestion. Thanks.
Pingback: SQL Tuning beyond the Execution Plan | My Big Data World