Oracle Tutorial: Learn about v $ segment_statistics

I sent a SR on MOS and asked how v $ segment_statistics was updated?

Oracle: v $ segment_statisitcs has been used for internal research since 9IR2 was introduced]

(I) first look at the table structure:

Sys @ ORCL> desc v $ segment_statistics
Name Null? Type
-------------------------------------------------------------------------------------------------
OWNER VARCHAR2 (30) # object OWNER
OBJECT_NAME VARCHAR2 (30) # Object Name
SUBOBJECT_NAME VARCHAR2 (30) # subobject name
TABLESPACE_NAME VARCHAR2 (30) # table space where the object is located
TS # NUMBER # tablespace ID
OBJ # NUMBER # dictionary Object ID
DATAOBJ # NUMBER # Data Object ID
OBJECT_TYPE VARCHAR2 (18) # object type
STATISTIC_NAME VARCHAR2 (64) # statistical item name
STATISTIC # NUMBER # STATISTIC identifier
Value number # The VALUE and unit of measurement are the NUMBER of visits.

(Ii) subsequent application scenarios

It can be used to monitor segment-level (segment_level) statistics, identify performance problems from tables or Indexes

Based on this, we can analyze whether some objects in the database are too concentrated and wait too frequently.

For example, you can consider adding more transaction slots to an ITL-waiting object.

(3) Let's look at the example.

Use v $ segment_statistics to find the most frequently accessed table

Sys @ ORCL> ed
Wrote file afiedt. buf

1 SELECT t. owner, t. table_name, lr. value + pr. value AS total_reads
2 FROM
3 (SELECT owner, object_name, value FROM v $ segment_statistics WHERE statistic_name = 'logical reads') lr,
4 (SELECT owner, object_name, value FROM v $ segment_statistics WHERE statistic_name = 'logical reads') pr, dba_tables t
5 WHERE lr. owner = pr. owner AND
6 lr. object_name = pr. object_name AND
7 lr. owner = t. owner AND
8 lr. object_name = t. table_name
9 * order by 3 desc
Sys @ ORCL>/

OWNER TABLE_NAME TOTAL_READS
------------------------------------------------------------------------
Sys wri $ _ OPTSTAT_HISTGRM_HISTORY 14080
Sys wri $ _ OPTSTAT_HISTHEAD_HISTORY 4736
SYSMAN MGMT_METRIC_COLLECTIONS 4000
SYS HIST_HEAD $3936
SYS COL_USAGE $3104
SYS FIXED_OBJ $2912
SYSMAN MGMT_SYSTEM_PERFORMANCE_LOG 2688