@@header /* * * Author : Vishal Gupta * Purpose : Display segment statistics from AWR * Parameters : 1 - Number of Days * 2 - Owner * 3 - Object Name * 4 - SubObject Name * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 14-Sep-12 Vishal Gupta Created * * */ /************************************ * INPUT PARAMETERS ************************************/ DEFINE no_of_days="&&1" DEFINE owner="&&2" DEFINE object_name="&&3" DEFINE subobject_name="&&4" COLUMN _no_of_days NEW_VALUE no_of_days NOPRINT COLUMN _owner NEW_VALUE owner NOPRINT COLUMN _object_name NEW_VALUE object_name NOPRINT COLUMN _subobject_name NEW_VALUE subobject_name NOPRINT COLUMN _object_type NEW_VALUE object_type NOPRINT set term off SELECT DECODE('&&no_of_days','','30','&&no_of_days') "_no_of_days" , DECODE('&&owner','','%',UPPER('&&owner')) "_owner" , DECODE('&&object_name','','%',UPPER('&&object_name')) "_object_name" , DECODE('&&subobject_name','','%',UPPER('&&subobject_name')) "_subobject_name" FROM DUAL ; set term on PROMPT ************************************************************************ PROMPT * AWR Segment Statistics PROMPT * PROMPT * Input Parameters PROMPT * - NoOfDays - "&&no_of_days" PROMPT * - Owner - "&&owner" PROMPT * - ObjectName - "&&object_name" PROMPT * - SubObjectName - "&&subobject_name" PROMPT ************************************************************************ COLUMN end_interval_time HEADING "SnapTime" FORMAT a15 COLUMN object HEADING "Object" FORMAT a40 COLUMN subobject_name HEADING "SubObject" FORMAT a25 COLUMN object_type HEADING "ObjectType" FORMAT a20 COLUMN tablespace_name HEADING "Tablespace|Name" FORMAT a15 COLUMN partition_type HEADING "Partition|Type" FORMAT a10 COLUMN changes_delta HEADING "Changes|Delta|(MB)" FORMAT 999,999,999 COLUMN used_delta HEADING "Used|Delta|(MB)" FORMAT 999,999,999 COLUMN allocated_delta HEADING "Alloc|Delta|(MB)" FORMAT 999,999,999 COLUMN itl_waits_delta HEADING "ITL|Waits|Delta" FORMAT 999,999,999 COLUMN row_lock_waits_delta HEADING "Row|Lock|Waits|Delta" FORMAT 999,999,999 COLUMN physical_reads_delta HEADING "Phy|Reads|Delta" FORMAT 999,999,999 COLUMN physical_writes_delta HEADING "Phy|Write|Delta" FORMAT 999,999,999 COLUMN physical_reads_direct_delta HEADING "Phy|Reads|Direct|Delta" FORMAT 999,999,999 COLUMN physical_writes_direct_delta HEADING "Phy|Writes|Direct|Delta" FORMAT 999,999,999 COLUMN gc_cr_blocks_served_delta HEADING "GC CR|Blocks|Served|Delta" FORMAT 999,999,999 COLUMN gc_cu_blocks_served_delta HEADING "GC CU|Blocks|Served|Delta" FORMAT 999,999,999 COLUMN gc_buffer_busy_delta HEADING "GC|Buffer|Busy|Delta" FORMAT 999,999,999 COLUMN gc_cr_blocks_received_delta HEADING "GC CR|Blocks|Recvd|Delta" FORMAT 999,999,999 COLUMN gc_cu_blocks_received_delta HEADING "GC CU|Blocks|Recvd|Delta" FORMAT 999,999,999 COLUMN table_scans_delta HEADING "Table|Scans|Delta" FORMAT 999,999,999 COLUMN chain_row_excess_delta HEADING "Chain|Row|Excess|Delta" FORMAT 999,999,999 COLUMN physical_read_requests_delta HEADING "Phy|Read|Reqs|Delta" FORMAT 999,999,999 COLUMN physical_write_requests_delta HEADING "Phy|Write|Reqs|Delta" FORMAT 999,999,999 COLUMN optimized_physical_reads_delta HEADING "Opt'z|Phy|Reads||Delta" FORMAT 999,999,999 COLUMN changes_total HEADING "Changes|Total|(MB)" FORMAT 999,999,999 COLUMN used_total HEADING "Used|Total|(MB)" FORMAT 999,999,999 COLUMN allocated_total HEADING "Alloc|Total|(MB)" FORMAT 999,999,999 COLUMN itl_waits_total HEADING "ITL|Waits|Total" FORMAT 999,999,999,999 set lines 4000 SELECT /*+ parallel(8) */ to_char(TRUNC(s.end_interval_time,'MI'),'DD-MON-YY HH24:MI') end_interval_time --, o.owner || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name,'') object , o.owner || '.' || o.object_name object --, o.subobject_name --, o.object_type --, o.tablespace_name --, o.partition_type , ROUND(SUM(ss.db_block_changes_delta * p.value)/1024/1024) changes_delta , ROUND(SUM(ss.space_used_delta )/1024/1024) used_delta , ROUND(SUM(ss.space_allocated_delta )/1024/1024) allocated_delta , ROUND(SUM(ss.itl_waits_delta )) itl_waits_delta , ROUND(SUM(ss.row_lock_waits_delta )) row_lock_waits_delta , ROUND(SUM(ss.physical_reads_delta )) physical_reads_delta , ROUND(SUM(ss.physical_writes_delta )) physical_writes_delta , ROUND(SUM(ss.physical_reads_direct_delta )) physical_reads_direct_delta , ROUND(SUM(ss.physical_writes_direct_delta )) physical_writes_direct_delta -- , ROUND(SUM(ss.gc_cr_blocks_served_delta )) gc_cr_blocks_served_delta -- , ROUND(SUM(ss.gc_cu_blocks_served_delta )) gc_cu_blocks_served_delta , ROUND(SUM(ss.gc_buffer_busy_delta )) gc_buffer_busy_delta , ROUND(SUM(ss.gc_cr_blocks_received_delta )) gc_cr_blocks_received_delta , ROUND(SUM(ss.gc_cu_blocks_received_delta )) gc_cu_blocks_received_delta , ROUND(SUM(ss.table_scans_delta )) table_scans_delta , ROUND(SUM(ss.chain_row_excess_delta )) chain_row_excess_delta , ROUND(SUM(ss.physical_read_requests_delta )) physical_read_requests_delta , ROUND(SUM(ss.physical_write_requests_delta )) physical_write_requests_delta , ROUND(SUM(ss.optimized_physical_reads_delta )) optimized_physical_reads_delta -- , ROUND(MAX(ss.db_block_changes_total * p.value)/1024/1024) changes_total -- , ROUND(MAX(ss.space_used_total )/1024/1024) used_total -- , ROUND(MAX(ss.space_allocated_total )/1024/1024) allocated_total -- , ROUND(SUM(ss.itl_waits_total )) itl_waits_total FROM v$database d JOIN dba_hist_snapshot s ON d.dbid = s.dbid JOIN sys.WRH$_SEG_STAT ss ON s.dbid = ss.dbid AND s.instance_number = ss.instance_number AND s.snap_id = ss.snap_id JOIN v$system_parameter p ON p.name = 'db_block_size' JOIN dba_hist_seg_stat_obj o ON o.dbid = ss.dbid AND o.ts# = ss.ts# AND o.obj# = ss.obj# AND o.dataobj# = ss.dataobj# WHERE s.end_interval_time > systimestamp - INTERVAL '&&no_of_days' DAY AND o.owner LIKE '&&owner' ESCAPE '\' AND o.object_name LIKE '&&object_name' ESCAPE '\' AND NVL(o.subobject_name,'x') LIKE '&&subobject_name' ESCAPE '\' GROUP BY ss.dbid , TRUNC(s.end_interval_time,'MI') --, o.owner || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name,'') , o.owner || '.' || o.object_name --, o.subobject_name --, o.object_type --, o.tablespace_name --, o.partition_type ORDER BY TRUNC(s.end_interval_time,'MI') ; @@footer