I use the following query to display the top 20 hot segments which have the biggest logical reads today.
query:
with hotsegmentvw as
(
select * from
(
SELECT obj# obj_id ,dataobj# data_obj_id,
sum(logical_reads_delta) AS total_logical_reads
FROM dba_hist_seg_stat a
WHERE a.snap_id in (select snap_id from dba_hist_snapshot where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate))
GROUP BY obj#,dataobj#
order by sum(logical_reads_delta) desc
)
where rownum <= 20
)
select owner,object_name, total_logical_reads from hotsegmentvw a,dba_objects b
where a.obj_id = b.object_id
AND a.data_obj_id = b.data_object_id
order by total_logical_reads
/
sample result:
OWNER OBJECT_NAME TOTAL_LOGICAL_READS
--------------- ------------------------------ -------------------
ODYSSEY SPOT_CHANNEL_I4 334938608
ODYSSEY SCHEDULE_AVAIL 369947968
BSS VERSION_U1 380481744
BSS TABLE_LINE_PK 420110048
BSS SCHEDULE_ITEM_I11 424232128
ODYSSEY SPOT_CHANNEL 434493104
IBMSTASK TASK_SERVER_LOG_I1 440787200
ODYSSEY IMPRESSION_COPY_PK 467589344
ODYSSEY SPOT_CHANNEL_U3 561695824
BSS TABLE_LINE 618886448
ODYSSEY SCHEDULE_SECTION_PK 630677792
ODYSSEY SCHEDULE_AVAIL_PK 633438400
ODYSSEY SALES_PRODUCT_DAYPART 650545904
BSS SCHEDULE_ITEM_PK 671033824
ODYSSEY SPOT 709647104
ODYSSEY SCHEDULE_EVENT 841863248
ODYSSEY SALES_PRODUCT_DAYPART_PK 993979440
ODYSSEY SCHEDULE_EVENT_PK 1319646848
BISTAGE TMP_SPOT_GOUPING_I1 3438150208
ODYSSEY SPOT_PK 4701989600
query:
with hotsegmentvw as
(
select * from
(
SELECT obj# obj_id ,dataobj# data_obj_id,
sum(logical_reads_delta) AS total_logical_reads
FROM dba_hist_seg_stat a
WHERE a.snap_id in (select snap_id from dba_hist_snapshot where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate))
GROUP BY obj#,dataobj#
order by sum(logical_reads_delta) desc
)
where rownum <= 20
)
select owner,object_name, total_logical_reads from hotsegmentvw a,dba_objects b
where a.obj_id = b.object_id
AND a.data_obj_id = b.data_object_id
order by total_logical_reads
/
sample result:
OWNER OBJECT_NAME TOTAL_LOGICAL_READS
--------------- ------------------------------ -------------------
ODYSSEY SPOT_CHANNEL_I4 334938608
ODYSSEY SCHEDULE_AVAIL 369947968
BSS VERSION_U1 380481744
BSS TABLE_LINE_PK 420110048
BSS SCHEDULE_ITEM_I11 424232128
ODYSSEY SPOT_CHANNEL 434493104
IBMSTASK TASK_SERVER_LOG_I1 440787200
ODYSSEY IMPRESSION_COPY_PK 467589344
ODYSSEY SPOT_CHANNEL_U3 561695824
BSS TABLE_LINE 618886448
ODYSSEY SCHEDULE_SECTION_PK 630677792
ODYSSEY SCHEDULE_AVAIL_PK 633438400
ODYSSEY SALES_PRODUCT_DAYPART 650545904
BSS SCHEDULE_ITEM_PK 671033824
ODYSSEY SPOT 709647104
ODYSSEY SCHEDULE_EVENT 841863248
ODYSSEY SALES_PRODUCT_DAYPART_PK 993979440
ODYSSEY SCHEDULE_EVENT_PK 1319646848
BISTAGE TMP_SPOT_GOUPING_I1 3438150208
ODYSSEY SPOT_PK 4701989600
Comments
Post a Comment