基於AWR實現STATSPACK報告(7-TOPSEGMENT)
Segments by Logical Reads
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.logical_reads,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.logical_reads_total - nvl(b.logical_reads_total, 0) logical_reads,
ratio_to_report(e.logical_reads_total -
nvl(b.logical_reads_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.logical_reads_total - nvl(b.logical_reads_total, 0) > 0
ORDER BY logical_reads DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY logical_reads DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by Physical Reads
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.physical_reads,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.physical_reads_total -
nvl(b.physical_reads_total, 0) physical_reads,
ratio_to_report(e.physical_reads_total -
nvl(b.physical_reads_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.physical_reads_total -
nvl(b.physical_reads_total, 0) > 0
ORDER BY physical_reads DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY physical_reads DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by Row Lock Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.row_lock_waits,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.row_lock_waits_total -
nvl(b.row_lock_waits_total, 0) row_lock_waits,
ratio_to_report(e.row_lock_waits_total -
nvl(b.row_lock_waits_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.row_lock_waits_total -
nvl(b.row_lock_waits_total, 0) > 0
ORDER BY row_lock_waits DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY row_lock_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by ITL Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
n.subobject_name,
n.object_type,
r.itl_waits,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.itl_waits_total - nvl(b.itl_waits_total, 0) itl_waits,
ratio_to_report(e.itl_waits_total -
nvl(b.itl_waits_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.itl_waits_total - nvl(b.itl_waits_total, 0) > 0
ORDER BY itl_waits DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY itl_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by Buffer Busy Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.buffer_busy_waits,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.buffer_busy_waits_total -
nvl(b.buffer_busy_waits_total, 0) buffer_busy_waits,
ratio_to_report(e.buffer_busy_waits_total -
nvl(b.buffer_busy_waits_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.buffer_busy_waits_total -
nvl(b.buffer_busy_waits_total, 0) > 0
ORDER BY buffer_busy_waits DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY buffer_busy_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by Global Cache Buffer Busy Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.gc_buffer_busy,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.gc_buffer_busy_total -
nvl(b.gc_buffer_busy_total, 0) gc_buffer_busy,
ratio_to_report(e.gc_buffer_busy_total -
nvl(b.gc_buffer_busy_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.gc_buffer_busy_total -
nvl(b.gc_buffer_busy_total, 0) > 0
ORDER BY gc_buffer_busy DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY gc_buffer_busy DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments by CR Blocks Received(was Served in versions prior to 10g)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.cr_blocks_received,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.gc_cr_blocks_received_total -
nvl(b.gc_cr_blocks_received_total, 0) cr_blocks_received,
ratio_to_report(e.gc_cr_blocks_received_total -
nvl(b.gc_cr_blocks_received_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.gc_cr_blocks_received_total -
nvl(b.gc_cr_blocks_received_total, 0) > 0
ORDER BY cr_blocks_received DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY cr_blocks_received DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Segments By Current Blocks Received(was Served in versions prior to 10g)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
n.tablespace_name,
n.object_name,
CASE
WHEN length(n.subobject_name) < 11 THEN
n.subobject_name
ELSE
substr(n.subobject_name, length(n.subobject_name) - 9)
END subobject_name,
n.object_type,
r.cu_blocks_received,
substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
FROM dba_hist_seg_stat_obj n,
(SELECT *
FROM (SELECT e.dataobj#,
e.obj#,
e.ts#,
e.dbid,
e.gc_cu_blocks_received_total -
nvl(b.gc_cu_blocks_received_total, 0) cu_blocks_received,
ratio_to_report(e.gc_cu_blocks_received_total -
nvl(b.gc_cu_blocks_received_total, 0)) over() ratio
FROM dba_hist_seg_stat e, dba_hist_seg_stat b
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND b.ts#(+) = e.ts#
AND b.obj#(+) = e.obj#
AND b.dataobj#(+) = e.dataobj#
AND e.gc_cu_blocks_received_total -
nvl(b.gc_cu_blocks_received_total, 0) > 0
ORDER BY cu_blocks_received DESC) d
WHERE rownum <= 10) r
WHERE n.dataobj# = r.dataobj#
AND n.obj# = r.obj#
AND n.ts# = r.ts#
AND n.dbid = r.dbid
ORDER BY cu_blocks_received DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-701153/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於AWR實現STATSPACK報告(4-等待事件)事件
- 基於AWR實現STATSPACK報告(5-TOPSQL)SQL
- 基於AWR實現STATSPACK報告(2-系統效率)
- 基於AWR實現STATSPACK報告(3-RAC統計)
- 基於AWR實現STATSPACK報告(6-例項元件)元件
- 基於AWR實現STATSPACK報告(1-系統負載)負載
- 基於AWR實現STATSPACK報告(8-例項元件二)元件
- AWR報告基礎操作
- statspack 報告分析
- shell指令碼實現自動生成awr報告指令碼
- 詳解statspack 報告
- 關於類似於awr的效能分析報告
- statspack報告分析摘錄
- Statspack分析報告說明
- Statspack分析報告詳解
- 對於AWR報告的幾個片段分析。
- Oracle生成awr報告Oracle
- AWR解析報告分析
- mysql-awr報告MySql
- Oracle 生成awr報告Oracle
- oracle效能awr報告Oracle
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- Statspack分析報告詳解 (zt)
- Statspack分析報告詳解(1)
- Statspack分析報告詳解(2)
- Statspack分析報告詳解(3)
- Statspack分析報告詳解(4)
- Statspack分析報告詳解(轉)
- 手工生成AWR分析報告
- Oracle AWR報告大綱Oracle
- oracle 產生awr 報告Oracle
- oracle AWR報告提取分析Oracle
- 為Active DataGuard的備庫生成statspack報告並實現定時傳送
- statspack、awr、addm,ash影片分享
- statspack中報告中的等待事件事件
- Statspack之六-生成分析報告
- 【AWR】自動生成AWR報告指令碼以及用法指令碼
- 生成awr報告的指令碼指令碼