155 lines
8.7 KiB
SQL
155 lines
8.7 KiB
SQL
@@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
|