@@header /* * * Author : Vishal Gupta * Purpose : Display top segment from AWR * Version : 10.1 and above * Parameters : 1 - Instance Number (Default is '%' ie ALL) * 2 - From Timestamp (YYYY-MM-DD HH24:MI:SS) * 3 - To Timestamp (YYYY-MM-DD HH24:MI:SS) * 4 - Top record Count * 5 - ORDER BY column name/number * 6 - Where Clause * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 13-JAN-14 Vishal Gupta Created * */ UNDEFINE INST_ID UNDEFINE FROM_TIMESTAMP UNDEFINE TO_TIMESTAMP UNDEFINE TOP_RECORDCOUNT UNDEFINE ORDER_BY_COLUMN UNDEFINE WHERECLAUSE DEFINE INST_ID="&&1" DEFINE FROM_TIMESTAMP="&&2" DEFINE TO_TIMESTAMP="&3" DEFINE TOP_RECORDCOUNT="&4" DEFINE ORDER_BY_COLUMN="&5" DEFINE WHERECLAUSE="&&6" DEFINE COUNT_SMALL_FORMAT=999,999 DEFINE COUNT_SMALL_DIVIDER="1" DEFINE COUNT_SMALL_HEADING="#" --DEFINE COUNT_SMALL_DIVIDER="1000" --DEFINE COUNT_SMALL_HEADING="#1000" --DEFINE COUNT_SMALL_DIVIDER="1000000" --DEFINE COUNT_SMALL_HEADING="#million" DEFINE COUNT_FORMAT=999,999 --DEFINE COUNT_DIVIDER="1" --DEFINE COUNT_HEADING="#" DEFINE COUNT_DIVIDER="1000" DEFINE COUNT_HEADING="#1000" DEFINE BYTES_FORMAT="999,999" --DEFINE BYTES_DIVIDER="1024" --DEFINE BYTES_HEADING="KB" DEFINE BYTES_DIVIDER="1024/1024" DEFINE BYTES_HEADING="MB" --DEFINE BYTES_DIVIDER="1024/1024/1024" --DEFINE BYTES_HEADING="GB" DEFINE TIME_FORMAT=99,999 DEFINE TIME_DIVIDER="60" DEFINE TIME_HEADING="min" COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN object_name HEADING "Object|Name" FORMAT a50 COLUMN table_scans HEADING "Table|Scans|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT COLUMN itl_waits HEADING "ITL|Waits|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT COLUMN row_lock_waits HEADING "Row|Lock|Waits|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT COLUMN chain_row_excess HEADING "Chain|Row|Excess|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT COLUMN buffer_busy_waits HEADING "Buffer|Busy|Waits|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT COLUMN gc_buffer_busy HEADING "GC|Buffer|Busy|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT COLUMN gc_cr_blocks_served HEADING "Global|Cache|Consistent|Read|Served|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN gc_cu_blocks_served HEADING "Global|Cache|Current|Blocks|Served|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN gc_cr_blocks_received HEADING "Global|Cache|Consistent|Read|Recv|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN gc_cu_blocks_received HEADING "Global|Cache|Current|Blocks|Recv|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN logical_reads HEADING "Logical|Reads|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_reads HEADING "Phy|Reads|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_writes HEADING "Phy|Write|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN db_block_changes HEADING "DB|Block|Changes|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_writes_direct HEADING "Phy|Write|Direct|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_reads_direct HEADING "Phy|Read|Direct|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN space_used HEADING "Space|Used|Delta|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN space_allocated HEADING "Space|Alloc|Delta|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT Prompt Prompt *************************************************************************************************** Prompt * A W R T O P &&TOP_RECORDCOUNT S E G M E N T S Prompt * (By &&ORDER_BY_COLUMN) Prompt * Prompt * Input Parameters Prompt * - INST_ID - "&&INST_ID" Prompt * - From Timestamp - "&&from_timestamp" (YYYY-MM-DD HH24:MI:SS) Prompt * - To Timestamp - "&&to_timestamp" (YYYY-MM-DD HH24:MI:SS) Prompt * - Top RecordCount - "&&TOP_RECORDCOUNT" Prompt * - Order By - "&&ORDER_BY_COLUMN" Prompt * - WhereClause - "&&WHERECLAUSE" Prompt *************************************************************************************************** SELECT * FROM ( SELECT /*+ LEADING(d s) USE_NL (d s ss) PARALLEL(s,8) PARALLEL(ss,8)*/ (SELECT o.owner || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name,'') FROM dba_hist_seg_stat_obj o WHERE o.dbid = ss.dbid AND o.ts# = ss.ts# AND o.obj# = ss.obj# AND o.dataobj# = ss.dataobj# ) object_name --/* , ROUND(SUM(ss.table_scans_delta) /&COUNT_SMALL_DIVIDER) table_scans , ROUND(SUM(ss.itl_waits_delta) /&COUNT_SMALL_DIVIDER) itl_waits , ROUND(SUM(ss.row_lock_waits_delta) /&COUNT_SMALL_DIVIDER) row_lock_waits -- As per Bug 13621258 , chain_row_excess needs to be divided by 100 , ROUND(SUM(ss.chain_row_excess_delta/100) /&COUNT_DIVIDER) chain_row_excess , ROUND(SUM(ss.buffer_busy_waits_delta) /&COUNT_DIVIDER) buffer_busy_waits , ROUND(SUM(ss.gc_buffer_busy_delta) /&COUNT_DIVIDER) gc_buffer_busy , ROUND(SUM(ss.gc_cr_blocks_served_delta * p.value) /&BYTES_DIVIDER) gc_cr_blocks_served , ROUND(SUM(ss.gc_cu_blocks_served_delta * p.value) /&BYTES_DIVIDER) gc_cu_blocks_served , ROUND(SUM(ss.gc_cr_blocks_received_delta * p.value) /&BYTES_DIVIDER) gc_cr_blocks_received , ROUND(SUM(ss.gc_cu_blocks_received_delta * p.value) /&BYTES_DIVIDER) gc_cu_blocks_received , ROUND(SUM(ss.logical_reads_delta * p.value) /&BYTES_DIVIDER) logical_reads , ROUND(SUM(ss.physical_reads_delta * p.value) /&BYTES_DIVIDER) physical_reads , ROUND(SUM(ss.physical_writes_delta * p.value) /&BYTES_DIVIDER) physical_writes , ROUND(SUM(ss.db_block_changes_delta) /&BYTES_DIVIDER) db_block_changes , ROUND(SUM(ss.physical_writes_direct_delta * p.value) /&BYTES_DIVIDER) physical_writes_direct , ROUND(SUM(ss.physical_reads_direct_delta * p.value) /&BYTES_DIVIDER) physical_reads_direct , ROUND(SUM(ss.space_used_delta) /&BYTES_DIVIDER) space_used , ROUND(SUM(ss.space_allocated_delta) /&BYTES_DIVIDER) space_allocated --*/ from v$database d , dba_hist_snapshot s , dba_hist_seg_stat ss , (select value from v$system_parameter where name = 'db_block_size') p where d.dbid = s.dbid AND ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss.instance_number LIKE '&&INST_ID' AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS') &&WHERECLAUSE GROUP BY ss.dbid , ss.ts# , ss.obj# , ss.dataobj# order by &&ORDER_BY_COLUMN DESC NULLS LAST ) WHERE ROWNUM <= &&TOP_RECORDCOUNT ; @@footer