148 lines
8.2 KiB
MySQL
148 lines
8.2 KiB
MySQL
@@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
|