Segment Statistics dba_hist_seg_stat
The AWR repository also stores a history for a set of
segment related statistics such as logical reads, physical
reads and writes, buffer busy waits, row lock waits, etc.
Also note this related
segment growth script. The kernel AWR view for segment statistics is
dba_hist_seg_stat.
SQL> desc DBA_HIST_SEG_STAT
Name Null? Type
----------------------------------------- --------
----------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
TS# NUMBER
OBJ# NUMBER
DATAOBJ# NUMBER
LOGICAL_READS_TOTAL NUMBER
LOGICAL_READS_DELTA NUMBER
BUFFER_BUSY_WAITS_TOTAL NUMBER
BUFFER_BUSY_WAITS_DELTA NUMBER
DB_BLOCK_CHANGES_TOTAL NUMBER
DB_BLOCK_CHANGES_DELTA NUMBER
PHYSICAL_READS_TOTAL NUMBER
PHYSICAL_READS_DELTA NUMBER
PHYSICAL_WRITES_TOTAL NUMBER
PHYSICAL_WRITES_DELTA NUMBER
PHYSICAL_READS_DIRECT_TOTAL NUMBER
PHYSICAL_READS_DIRECT_DELTA NUMBER
PHYSICAL_WRITES_DIRECT_TOTAL NUMBER
PHYSICAL_WRITES_DIRECT_DELTA NUMBER
ITL_WAITS_TOTAL NUMBER
ITL_WAITS_DELTA NUMBER
ROW_LOCK_WAITS_TOTAL NUMBER
ROW_LOCK_WAITS_DELTA NUMBER
GC_CR_BLOCKS_SERVED_TOTAL NUMBER
GC_CR_BLOCKS_SERVED_DELTA NUMBER
GC_CU_BLOCKS_SERVED_TOTAL NUMBER
GC_CU_BLOCKS_SERVED_DELTA NUMBER
SPACE_USED_TOTAL NUMBER
SPACE_USED_DELTA NUMBER
SPACE_ALLOCATED_TOTAL NUMBER
SPACE_ALLOCATED_DELTA NUMBER
TABLE_SCANS_TOTAL NUMBER
TABLE_SCANS_DELTA NUMBER
This view contains historical snapshots for the
v$segstat dynamic performance view. Oracle10g also has a more user
friendly dynamic view called
v$segment_statistics
which shows the same statistics
along with additional owner and segment names, tablespace
name, etc. Available segment-level statistics can be
selected from the v$segstat_name
view:
SQL> select name from V$SEGSTAT_NAME;
NAME
-------------------------------------
logical reads
buffer busy waits
gc buffer busy
db block changes
physical reads
physical writes
physical reads direct
physical writes direct
gc cr blocks received
gc current blocks received
ITL waits
row lock waits
space used
space allocated
segment scans
Reviewing the segment-level statistics history helps us to
identify hot segments in the database such as tables and
indexes, which possibly play a significant role in
performance problems. For example, if the database has a
high value of TX enqueue waits, the
dba_hist_seg_stat
view can be queried to find actual
segments experiencing high row lock activity.
Users can query the
dba_hist_seg_stat
view using various criteria to
identify hot segments. For example, the
seg_top_logreads_10g.sql
script retrieves top segments that have high logical reads
activity:
<
seg_top_logreads_10g.sql
select
object_name "Object Name"
, tablespace_name "Tablespace Name"
, object_type "Object Type"
, logical_reads_total "Logical Reads"
, ratio "%Total"
from(
select n.owner||'.'||n.object_name||decode(n.subobject_name,null,null,'.'||n.subobject_name)
object_name
, n.tablespace_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_total
, round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n
, (select *
from (select e.dataobj#
, e.obj#
, e.dbid
, e.logical_reads_total -
nvl(b.logical_reads_total, 0) logical_reads_total
,
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 = 2694
and e.snap_id = 2707
and b.dbid = 37933856
and e.dbid = 37933856
and b.instance_number = 1
and e.instance_number = 1
and e.obj# = b.obj#
and e.dataobj# = b.dataobj#
and e.logical_reads_total -
nvl(b.logical_reads_total, 0) > 0
order by logical_reads_total desc) d
where rownum <= 100) r
where n.dataobj# = r.dataobj#
SEE CODE DEPOT FOR FULL SCRIPTS
)
order by logical_reads_total desc;
This script allows the identification of hot segments which
experience high logical reads activity. This information may
help with the selection of tuning actions such as the
optimization of corresponding queries that access these
segments, re-distribute segments across different disks,
etc.
SQL> @seg_top_logreads.sql
Object
Name Tablespace Object Type Logical Reads
%Total
------------------------------ ---------- -----------
------------- ------
SYSMAN.MGMT_METRICS_RAW_PK
SYSAUX INDEX 46,272 8.68
SYS.SMON_SCN_TIME
SYSTEM TABLE 43,840 8.23
SYS.JOB$
SYSTEM TABLE 30,640 5.75
SYS.I_SYSAUTH1 SYSTEM INDEX
27,120 5.09
PERFSTAT.STATS$EVENT_HISTOGRAM SYSAUX INDEX
26,912 5.05
The Ion tool also has several reports for the retrieval of
hot segments using the following criteria:
§
Top logical reads.
§
Top physical reads.
§
Top physical writes.
§
Top buffer busy waits.
§
Top row lock waits.
§
Top block changes.
The
dba_hist_seg_stat
view has two columns for each
statistic: total and delta. The total column shows the
cumulative value of the statistic and the delta column shows
change in the statistic value between
begin_interval_time and
end_interval_time
in the
dba_hist_snapshot
view for the corresponding
snap_id in the
dba_hist_seg_stat
view.
