performance tuning in oracle 10g
TRANSCRIPT
Performance Tuning in Oracle 10g
Kyle Hailey
Stats
WaitsMetrics
Time Model
SQL
Sessions
9i
v$sysstat
Waits
v$system_eventv$sql
v$session
V$session_wait
V$session_event
V$system_event
V$session
V$sysstat
V$sesstat
V$sql –includes stats
10g
Stats
Waits SQL
Sessions
V$sql
Includes some waits now
V$session includes waits
Metrics – rates and calculations
Stats
Waits SQL
Sessions
Metrics
V$sessmetric
V$sysmetric
X$ -> no v$ -> WRH$_SQLSTAT
V$eventmetric
V$waitclassmetric
Time Model
Stats
Wait SQL
Sessions
Metrics
Time Model
In Memory History
1 hour in Memory15 and 60 secondsM
etri
cs
1-128M in MemoryEvery second
Time
Ses
sio
n
Wai
t
10g Constructs
OS Statistics (Kodi Uma…) – Depends on platform
Metrics– Deltas and derived stats over 15 and 60 second periods
Metric History– Up to an hour
Time Model– groups time like waits & cpu into domains , eg Logon on/off , Parse
Active Session History AWR History
– Like statspack++, history in “wrh$” tables for 7 day
Services– (not discussed in this presentation)
ADDM– Automatic analysis of performance data
10g Performance Data Statistics
– New stats, DB Time (?)
Metrics*– Deltas of Stats and Events over 15 and 60 seconds– Max, min, average, standard deviation over 30 minutes
Wait Model– Wait events times and counts– Wait classes– ASH* – history of session waits
Time Model*– Database time– Aggregation of time by operational area such as log on/off, parsing etc
SQL– * Added some wait data – ( internally track the cursor statistic deltas)
Session– * exposed wait info
* New in 10g
10g Generic Fields Names Conventions V$
Names– Event– Statistic– Waitclass
Ids – Event#– Statistic#– Waitclass#
Name Hash– Event_id– Statistic_id– Waitclass_id
System Statistics ViewStats
V$stat_name
Waits
V$event_name
waitclasses Time Model sql
V$
Cumulative values now
V$sysstat V$system_event
V$event_histogram *
v$system_wait_class * V$sys_time_model * V$sql
Metrics
v$metricgroupV$metricnamedeltas
V$sysmetric * V$eventmetric * v$waitclassmetric * -----------
Some goes into sysmetric
ASH
x$kewrtsqlstat *Cumulative, updated every 10 seconds?)
Metrics
History
hour of Deltas
V$sysmetric_history * V$sysmetric_summary *
----------- v$waitclassmetric_history *
----------- -----------
AWR
Last 7 days
wrh$_sysmetric_history *(alert only)
wrh$_sysstat
WRH$_SYSMETRIC_SUMMARY *
wrh$_system_event
wrh$_waitclassmetric_history (alert only) *
WRH$_SYS_TIME_MODEL *
WRH$_SQLSTAT *
Session Statistics Viewstats waits waitclasses Time Model sql
V$ V$sessstat V$session_event ---------------- V$sess_time_model *
-----------------
(some stats possible from ASH)
Metrics V$sessmetric ----------------- ----------------- ----------------- -----------------
Metrics
history
----------------- ----------------- ----------------- ----------------- -----------------
AWR WRH$_SESSMETRIC_HISTORY *(alert only)
-----------------
(some stats possible from ASH)
---------------- ----------------- -----------------
(some stats possible from ASH)
Session Polling View Active Session History : ASH
stats
V$ V$session_wait
Metrics
Metrics
history
V$active_session_history *Polling at 1 second
(V$session_wait_history)
AWR Wrh$_active_session_history
(1 in 10 values from “”) *
Waits
Stats
Metrics
Time Model
SQL
Sessions
Waits
Waits – v$ Names and Classes
– v$event_name Cumulative
– v$system_event – by event– v$session_event – by session and event– * v$system_wait_class – by wait class– * v$event_histogram – by event and wait time bucket
Metrics - Deltas– * v$eventmetric – 60 second deltas for all events (799) – * v$waitclassmetric – 60 seconds deltas for 12 waitclasses– * v$waitclassmetric_history – last 60 minutes of 1 minute deltas
Details - polling– v$session_wait – current wait– * v$session_wait_history – last 10 waits – a bit superfluous – * v$active_session_history - last 30 minutes polled every second
* new in 10g
Waits New
800 waits Latches broken out
– Ex) Latch: library cache latch
Enqueues broken out– Ex) Enq: HW - contention
Waitclasses Administrative (39)
– switch logfile– rebuild index
Application (11)– enqueues – sqlnet break/reset
Cluster (113)
Commit (1)– Log file Sync
Concurrency (12)– Latches: cbc, lbc,– Lib cache locks– Bbw
Configuration (20)– log file size– Enqueues: ST, HW, ITL– Latch: redo copy,shared pool
Idle (56)
Network (25)
System I/O (19)
Scheduler (6)
User I/O (12)
Other (485)
WaitclassesSQL> select * from v$system_wait_class;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ----------- -------------------- ----------- -----------
1893977003 0 Other 11695 1873612
4217450380 1 Application 9316 850799
3290255840 2 Configuration 1949 2379
4166625743 3 Administrative 3 475
3875070507 4 Concurrency 184 634
3386400367 5 Commit 6260 1423
2723168908 6 Idle 1531734 530987091
2000153315 7 Network 127231 1709
1740759767 8 User I/O 1037623 16561
4108307767 9 System I/O 268085 150502
v$waitclassmetricBEGIN_TIME
END_TIME
INTSIZE_CSEC
WAIT_CLASS_ID
WAIT_CLASS#
NUM_SESS_WAITING
TIME_WAITED
WAIT_COUNT
Waits Metrics (Events and Classes)
v$waitclassmetric_history BEGIN_TIME
END_TIME
INTSIZE_CSEC
WAIT_CLASS_ID
WAIT_CLASS#
NUM_SESS_WAITING
TIME_WAITED
WAIT_COUNT
v$eventmetric BEGIN_TIME END_TIME INTSIZE_CSEC EVENT_ID NUM_SESS_WAITING TIME_WAITED WAIT_COUNT
v$event_name EVENT#
EVENT_ID
NAME
PARAMETER1
PARAMETER2
PARAMETER3
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
v$system_wait_classWAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
TOTAL_WAITS
TIME_WAITED
V$system_eventEVENT
TOTAL_WAITS
TOTAL_TIMEOUTS
TIME_WAITED
AVERAGE_WAIT
TIME_WAITED_MICRO
EVENT_ID
No in memory history
WRH$_SYSTEM_EVENT
wrh$_waitclassmetric_history
Only gets populated with alerts
V$session_event
Wait Classes
Wait Events
Current cumulative Current deltas Recent deltas
AWR on disk 7 days
Waits – Session Sampling ER
v$session_wait SID
SEQ#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
WAIT_TIME
SECONDS_IN_WAIT
STATE
v$session_wait_history SID
SEQ#
EVENT#
EVENT
P1TEXT
P1
P2TEXT
P2
P3TEXT
P3
WAIT_TIME
v$event_nameEVENT_ID
EVENT#
EVENT_ID
NAME
PARAMETER1
PARAMETER2
PARAMETER3
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
v$active_session_historySAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
EVENT#
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
ACTION
CLIENT_ID
current 10 samples Half hour
wrh$active_session_historySNAP_D
DBIDINSTANCE_NUMBER
SAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
EVENT_ID
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
ACTION
CLIENT_ID
7 days (disk)
Waits in Workload Repository – AWR
WRH$ – WRH$_EVENT_NAME– WRH$_SYSTEM_EVENT ( + BL* )– WRH$_BG_EVENT_SUMMARY– WRH$_WAITCLASSMETRIC_HISTORY alerts only– WRH$ACTIVE_SESSION_HISTORY
*( BL : base line)
Waits – DBA view on AWR
DBA_HIST – views onto WRH– DBA_HIST_WAITCLASSMET_HISTORY
Alerts only– DBA_HIST_SYSTEM_EVENT
Queries both WRH$_SYSTEM_EVENT_BL WRH$_SYSTEM_EVENT
– DBA_HIST_BG_EVENT_SUMMARY
Stats
Metrics
Time Model
SQL
Sessions
Waits
Stats
Stats
V$statname V$systat V$sesstat *v$sysmetric – derived and deltas, 15 and
60 second *v$sysmetric_history – last hour *v$sysmetric_summary – max, min, avg, stddevSnapshots: * wrh$_sysmetric_history (alert only) * wrh$systat * wrh$_ sysmetric_summary
v$sysmetric
Short Duration – 15 secs Long Duration – 60 secs Per Transaction Per Sec Ratios
Metric short duration
Buffer Cache Hit Ratio
Memory Sorts Ratio
Execute Without Parse Ratio
Soft Parse Ratio
Database CPU Time Ratio
Library Cache Hit Ratio
Shared Pool Free %
Txns Per Logon
Physical Reads
Physical Writes
Physical Reads Direct
Redo Generated
Logons
User Calls
Logical Reads
Redo Writes
Total Table Scans
Full Index Scans
DB Block Gets
Consistent Read Gets
DB Block Changes
Consistent Read Changes
ExecutionsUser Transaction Per Sec
Per Sec and Per Transaction
Per Sec
Metric long duration
Buffer Cache Hit Ratio
Memory Sorts Ratio
Redo Allocation Hit Ratio
User Commits Percentage
User Rollbacks Percentage
Cursor Cache Hit Ratio
Rows Per Sort
Execute Without Parse Ratio
Soft Parse Ratio
User Calls Ratio
Global Cache Average CR Get Time
Global Cache Average Current Get Time
Global Cache Blocks Corrupted
Global Cache Blocks Lost
Current Logons Count
Current Open Cursors Count
User Limit %
SQL Service Response Time
Database Wait Time Ratio
Database CPU Time Ratio
Row Cache Hit Ratio
Row Cache Miss Ratio
Library Cache Hit Ratio
Library Cache Miss Ratio
Shared Pool Free %
PGA Cache Hit %
Process Limit %
Session Limit %
Txns Per Logon
Metric long duration per sec/txn
Physical Reads
Physical Writes
Physical Reads Direct
Physical Writes Direct
Physical Reads Direct Lobs
Physical Writes Direct Lobs
Redo Generated
Logons
Open Cursors
User Calls
Recursive Calls
Logical Reads
Redo Writes
Long Table Scans
Total Table Scans
Full Index Scans
Total Index Scans
Total Parse Count
Hard Parse Count
Parse Failure Count
User Commits
User Rollbacks
User Transaction
DBWR Checkpoints
Background Checkpoints
Network Traffic Volume
Response Time
Disk Sort
Enqueue Timeouts
Enqueue Waits
Enqueue Deadlocks
Enqueue Requests
DB Block Gets
Consistent Read Gets
DB Block Changes
Consistent Read Changes
CPU Usage
CR Blocks Created
CR Undo Records Applied
User Rollback Undo Records Applied
Leaf Node Splits
Branch Node Splits
PX downgraded 1 to 25%
PX downgraded 25 to 50%
PX downgraded 50 to 75%
PX downgraded 75 to 99%
.
Per Second and Transaction
Per Sec
Per Transaction
Metrics Visually
v$sysmetric_history
Not saved to disk but summary is
60 minutes of 1 minute deltas
3 minutes of 15 second deltas
Stat Metrics Summary
*v$sysmetric_summary – max, min, avg, stddev– Last hour summary
* wrh$_ sysmetric_summary (all long duration )
– Half hour summaries (or when AWR runs)
Statistics Metrics
v$sessmetricBEGIN_TIME
END_TIME
INTSIZE_CSEC
SESSION_ID
SESSION_SERIAL_NUM
CPU
PHYSICAL_READS
PGA_MEMORY
HARD_PARSES
SOFT_PARSES
PHYSICAL_READ_PCT
LOGICAL_READ_PCTv$metricname GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT
v$sysmetric_summary BEGIN_TIME END_TIME INTSIZE_CSEC -> intsize GROUP_ID METRIC_ID METRIC_NAME NUM_INTERVAL MAXVAL MINVAL AVERAGE STANDARD_DEVIATION METRIC_UNIT
v$sysmetric_historyBEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
v$sysmetric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT v$sysstat
STATISTIC# NAME CLASS VALUE HASH
Stats – ER
v$metricgroup GROUP_ID
NAME
INTERVAL_SIZE
MAX_INTERVAL
v$sessstat STATISTIC# NAME CLASS VALUE HASH
Time Model
Stats
Metrics
SQL
Sessions
Waits
Time Model
Time Model
*V$SYS_TIME_MODEL *V$SESS_TIME_MODEL WRH$_SYS_TIME_MODEL WRH$_SYS_TIME_MODEL_BL DBA_HIST_SYS_TIME_MODEL
V$SYS_TIME_MODEL
STAT_ID
STAT_NAME
VALUE
Time Model
V$SESS_TIME_MODEL
STAT_ID
STAT_NAME
VALUE
SQL> select STAT_NAME, value from V$SYS_TIME_MODEL;
DB time 3.9837E+10
DB CPU 2.4055E+10
background cpu time 2.1808E+10
sequence load elapsed time 15939410
parse time elapsed 451760577
hard parse elapsed time 178851736
sql execute elapsed time 3.6900E+10
connection management call elapsed time 1045589383
failed parse elapsed time 848439
hard parse (sharing criteria) elapsed time 3347865
hard parse (bind mismatch) elapsed time 1515268
PL/SQL execution elapsed time 418089534
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 37666077
Java execution elapsed time 0
bind/define call elapsed time 0
Time Model Details
Time Model Detail SessionSQL> select STAT_NAME, value from V$SESS_TIME_MODEL where sid=1;DB time 143DB CPU 290000background cpu time 0sequence load elapsed time 0parse time elapsed 0hard parse elapsed time 0sql execute elapsed time 0global cache cr block receive time 0global cache current block receive time 0global cache get time 0connection management call elapsed time 0failed parse elapsed time 0hard parse (sharing criteria) elapsed time 0hard parse (bind mismatch) elapsed time 0PL/SQL execution elapsed time 0inbound PL/SQL rpc elapsed time 0PL/SQL compilation elapsed time 0Java execution elapsed time 0bind/define call elapsed time 0cluster wait time 0concurrency wait time 0application wait time 0user I/O wait time 0
SQL
Stats
Time Model
Sessions
Waits
Metrics
SQL
V$SQL SQL_FULLTEXT SQL_ID FETCHES END_OF_FETCH_COUNT DIRECT_WRITES APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME CPU_TIME ELAPSED_TIME
AWR SQL
WRH$_SQLSTAT WRH$_SQLSTAT_BL WRH$_SQLTEXT
Metrics
Stats
Time Model
SQL
Sessions
Waits
Metrics
Metrics
Performance Advantage– Less CPU than v$sysstat, v$system_event– (not scanning the big strucs #sessions x
#stats or #events, no conversions )
Precomputed deltas– 15, 60 seconds
Precomputed derived values– Per tnx, per sec, ratios
Metrics V$METRICGROUP V$METRICNAME
V$SYSMETRIC V$SESSMETRIC V$FILEMETRIC V$EVENTMETRIC V$WAITCLASSMETRIC V$SVCMETRIC
V$SYSMETRIC_HISTORY V$FILEMETRIC_HISTORY V$WAITCLASSMETRIC_HISTORY V$SVCMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$METRICV$METRIC_HISTORY
Combined view onto the other metric tables
Metric groups and names
v$metricname GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT
v$metricgroup GROUP_ID NAME INTERVAL_SIZE MAX_INTERVAL
Metric Group 1* select * from v$metricgroup
GID NAME INTS M_INT GROUP_ID COUNT(*)
--- ----------------------------------- ------ ----- -------- ----------
0 Event Metrics 6000 1 0 3
1 Event Class Metrics 6000 60 1 4
2 System Metrics Long Duration 6000 60 2 114
3 System Metrics Short Duration 1500 12 3 40
4 Session Metrics Long Duration 6000 60 4 1
5 Session Metrics Short Duration 1500 1 5 8
6 Service Metrics 6000 60 6 2
7 File Metrics Long Duration 60000 6 7 6
2 is a superset of 3
4 is a superset of 5
Ie, v$sysmetric_history keeps the last hour for long duration deltas, 60s, and the last 4 minutes for short duration, 15 second deltas
Max Interval
V$metric
v$metric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
ENTITY_ID
ENTITY_SEQUENCE
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
Session
Stats
Metrics
Time Model
SQL
Waits
Sessions
Session
Added ( exposed ) v$session_wait in v$session EVENT# NUMBER EVENT VARCHAR2(64) P1TEXT VARCHAR2(64) P1 NUMBER P1RAW RAW(4) P2TEXT VARCHAR2(64) P2 NUMBER P2RAW RAW(4) P3TEXT VARCHAR2(64) P3 NUMBER P3RAW RAW(4) WAIT_CLASS_ID NUMBER WAIT_CLASS# NUMBER WAIT_CLASS VARCHAR2(64) WAIT_TIME NUMBER SECONDS_IN_WAIT NUMBER
ADDM
dba_advisor_findings TASK_NAME, TASK_ID is indexed and
dba_advisor_recommendations TASK_ID,FINDING_ID
dba_advisor_actions TASK_ID and REC_ID
dba_advisor_rationale TASK_ID and REC_ID
dba_advisor_objects TASK_ID , OBJECT_ID from actions or rationale.
AWR Views: DBA_HISTDBA_HIST_DATABASE_INSTANCEDBA_HIST_SNAPSHOT- DBA_HIST_SNAP_ERROR- DBA_HIST_BASELINE- DBA_HIST_WR_CONTROL ? (work load repository ?)- DBA_HIST_DATAFILEDBA_HIST_FILESTATXS – only file number, no name- DBA_HIST_TEMPFILEDBA_HIST_TEMPSTATXS – onl;y file number+DBA_HIST_SQLSTAT group by parent cursor plus DELTAs was (stats$sql_summary)DBA_HIST_SQLTEXT*+DBA_HIST_SQL_SUMMARY – identify litterals (was stat$sql_statistics)DBA_HIST_SQL_PLAN-DBA_HIST_SQLBIND-DBA_HIST_OPTIMIZER_ENV*-DBA_HIST_EVENT_NAME*DBA_HIST_SYSTEM_EVENT*DBA_HIST_BG_EVENT_SUMMARY – sum of backgroundsDBA_HIST_WAITSTATDBA_HIST_ENQUEUE_STAT-DBA_HIST_LATCH_NAMEDBA_HIST_LATCHDBA_HIST_LATCH_CHILDRENDBA_HIST_LATCH_PARENTDBA_HIST_LATCH_MISSES_SUMMARY – summed over parent latchDBA_HIST_LIBRARYCACHEDBA_HIST_DB_CACHE_ADVICEDBA_HIST_BUFFER_POOL_STATDBA_HIST_ROWCACHE_SUMMARY – summed over rowcache entriesDBA_HIST_SGADBA_HIST_SGASTATDBA_HIST_PGASTAT
DBA_HIST_RESOURCE_LIMITDBA_HIST_SHARED_POOL_ADVICE?DBA_HIST_SQL_WORKAREA_HSTGRMDBA_HIST_PGA_TARGET_ADVICEDBA_HIST_INSTANCE_RECOVERYDBA_HIST_JAVA_POOL_ADVICEDBA_HIST_THREAD - logswitches-DBA_HIST_STAT_NAME*DBA_HIST_SYSSTAT*-DBA_HIST_SYS_TIME_MODEL-DBA_HIST_OSSTAT_NAME-DBA_HIST_OSSTATDBA_HIST_PARAMETER_NAMEDBA_HIST_PARAMETERDBA_HIST_UNDOSTATDBA_HIST_ROLLSTATDBA_HIST_SEG_STATDBA_HIST_SEG_STAT_OBJ-DBA_HIST_METRIC_NAME*-DBA_HIST_SYSMETRIC_HISTORY alert*-DBA_HIST_SYSMETRIC_SUMMARY – max, min, avg standard deviation*-DBA_HIST_SESSMETRIC_HISTORY alert-DBA_HIST_FILEMETRIC_HISTORY alert*DBA_HIST_WAITCLASSMET_HISTORY alertDBA_HIST_DLM_MISC-DBA_HIST_RCVRY_FILE_DEST_STAT-DBA_HIST_RMAN_PERFORMANCE*-DBA_HIST_ACTIVE_SESS_HISTORY – every 10th point from v$active_session_history-DBA_HIST_TABLESPACE_STAT-DBA_HIST_LOGDBA_HIST_MTTR_TARGET_ADVICE-DBA_HIST_TBSPC_SPACE_USAGE - ?
AWR Data Tables - WRH*WRH$_ACTIVE_SESSION_HISTORY
*WRH$_ACTIVE_SESSION_HISTORY_BL
*WRH$_BG_EVENT_SUMMARY
WRH$_BUFFER_POOL_STATISTICS
WRH$_DATAFILE
WRH$_DB_CACHE_ADVICE
WRH$_DB_CACHE_ADVICE_BL
WRH$_DLM_MISC
WRH$_ENQUEUE_STAT
WRH$_ENQUEUE_STAT_BL
* WRH$_WAITCLASSMETRIC_HISTORY
*WRH$_EVENT_NAME
WRH$_FILEMETRIC_HISTORY
WRH$_FILESTATXS
WRH$_FILESTATXS_BL
WRH$_INSTANCE_RECOVERY
WRH$_JAVA_POOL_ADVICE
WRH$_LATCH
WRH$_LATCH_BL
WRH$_LATCH_CHILDREN
WRH$_LATCH_CHILDREN_BL
WRH$_LATCH_MISSES_SUMMARY
WRH$_LATCH_MISSES_SUMMARY_BL
WRH$_LATCH_NAME
WRH$_LATCH_PARENT
WRH$_LATCH_PARENT_BL
WRH$_LIBRARYCACHE
WRH$_LOG
*WRH$_METRIC_NAME
WRH$_MTTR_TARGET_ADVICE
WRH$_OPTIMIZER_ENV
WRH$_OSSTAT
WRH$_PARAMETER
WRH$_PARAMETER_BL
WRH$_PARAMETER_NAME
WRH$_PGA_TARGET_ADVICE
WRH$_PGA_TARGET_ADVICE_BL
WRH$_PGASTAT
WRH$_PGASTAT_BL
WRH$_RECOVERY_FILE_DEST_STAT
WRH$_RESOURCE_LIMIT
WRH$_RMAN_PERFORMANCE
WRH$_ROLLSTAT
WRH$_ROWCACHE_SUMMARY
WRH$_ROWCACHE_SUMMARY_BL
WRH$_SEG_STAT
WRH$_SEG_STAT_BL
WRH$_SEG_STAT_OBJ
*WRH$_SESSMETRIC_HISTORY
WRH$_SGA
WRH$_SGASTAT
WRH$_SGASTAT_BL
WRH$_SHARED_POOL_ADVICE
WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_SQLBIND
WRH$_SQLBIND_BL
WRH$_SQLSTAT
WRH$_SQLSTAT_BL
WRH$_SQLTEXT
WRH$_STAT_NAME
*WRH$_SYS_TIME_MODEL
*WRH$_SYS_TIME_MODEL_BL
*WRH$_SYSMETRIC_HISTORY
*WRH$_SYSMETRIC_SUMMARY
*WRH$_SYSSTAT
*WRH$_SYSSTAT_BL
*WRH$_SYSTEM_EVENT
*WRH$_SYSTEM_EVENT_BL
WRH$_TABLESPACE_SPACE_USAGE
WRH$_TABLESPACE_STAT
WRH$_TABLESPACE_STAT_BL
WRH$_TEMPFILE
WRH$_TEMPSTATXS
WRH$_THREAD
WRH$_UNDOSTAT
WRH$_WAITSTAT
WRH$_WAITSTAT_BL
AWR Metadata Tables
SWRF METADATA TABLES WRM$_BASELINE
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAP_ERROR WRM$_WR_CONTROL
AWR Internal TablesWRI$_ADV_ACTIONS
WRI$_ADV_DEFINITIONS
WRI$_ADV_DEF_PARAMETERS
WRI$_ADV_DIRECTIVES
WRI$_ADV_FINDINGS
WRI$_ADV_JOURNAL
WRI$_ADV_LOG
WRI$_ADV_MESSAGE_GROUPS
WRI$_ADV_OBJECTS
WRI$_ADV_PARAMETERS
WRI$_ADV_RATIONALE
WRI$_ADV_RECOMMENDATIONS
WRI$_ADV_REC_ACTIONS
WRI$_ADV_SQLA_FAKE_REG
WRI$_ADV_SQLA_MAP
WRI$_ADV_SQLA_STMTS
WRI$_ADV_SQLA_TMP
WRI$_ADV_SQLT_BINDS
WRI$_ADV_SQLT_PLANS
WRI$_ADV_SQLT_RTN_PLAN
WRI$_ADV_SQLT_STATISTICS
WRI$_ADV_SQLW_COLVOL
WRI$_ADV_SQLW_STMTS
WRI$_ADV_SQLW_SUM
WRI$_ADV_SQLW_TABLES
WRI$_ADV_SQLW_TABVOL
WRI$_ADV_TASKS
WRI$_ADV_USAGE
WRI$_AGGREGATION_ENABLED
WRI$_ALERT_HISTORY
WRI$_ALERT_OUTSTANDING
WRI$_ALERT_THRESHOLD
WRI$_ALERT_THRESHOLD_LOG
WRI$_DBU_FEATURE_METADATA
WRI$_DBU_FEATURE_USAGE
WRI$_DBU_HIGH_WATER_MARK
WRI$_DBU_HWM_METADATA
WRI$_DBU_USAGE_SAMPLE
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_OPR
WRI$_OPTSTAT_TAB_HISTORY
WRI$_SQLSET_BINDS
WRI$_SQLSET_DEFINITIONS
WRI$_SQLSET_REFERENCES
WRI$_SQLSET_STATEMENTS
WRI$_TRACING_ENABLE
V$ SQLGO$SQL_BIND_CAPTURE
O$SQL_BIND_CAPTURE
V$SQL
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$CLIENT_STATS
V$EVENT_HISTOGRAM
V$EVENT_NAME
V$EVENTMETRIC
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$FILESTAT
V$METRICGROUP
V$METRICNAME
V$MYSTAT
V$OSSTAT
V$SERV_MOD_ACT_STATS
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SESSION_EVENT
V$SESSION_WAIT_CLASS
V$SESSMETRIC
V$SESSTAT
V$STATISTICS_LEVEL
V$STATNAME
V$SVCMETRIC
V$SVCMETRIC_HISTORY
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$SYSSTAT
V$SYSTEM_EVENT
V$SYSTEM_WAIT_CLASS
Event, Waits, Stats and Metrics
ASH
ASH provides two x$ fixed tables
x$kewash - returns a row for every ASH sample taken
x$ash- returns a row for every active session in every ASH sample
SELECT /*+ no_merge ordered use_nl(s,a) */ a.inst_id, a.sample_id, a.sample_tim
e, a.session_id, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number,
a.sql_plan_hash_value, a.sql_opcode, a.service_hash, decode(a.session_type, 1,'FORE
GROUND', 2,'BACKGROUND', 'UNKNOWN'), decode(a.wait_time, 0, 'WAITING', 'ON CPU')
, a.qc_session_id, a.qc_instance_id, a.seq#, a.event#, a.p1, a.p2, a.p3, a.wait_
time, a.time_waited, a.current_obj#, a.current_file#, a.current_block#, a.progra
m, a.module, a.action, a.client_id FROM x$kewash s, x$ash a WHERE s.sample_addr
= a.sample_addr and s.sample_id = a.sample_id
No range scans on x$ tables, so full scan x$kewash and then use equality in query on x$ash
X$ Desc x$ashSQL> desc x$kewashADDR RAW(4) INDX NUMBER INST_ID NUMBER SAMPLE_ID NUMBER SAMPLE_TIME TIMESTAMP(3) SAMPLE_ADDR NUMBER SAMPLE_LENGTH NUMBER ROW_COUNT NUMBER
desc x$ash ADDR RAW(4) INDX NUMBER INST_ID NUMBER SAMPLE_ADDR NUMBER SAMPLE_ID NUMBER SAMPLE_TIME TIMESTAMP(3) SESSION_ID NUMBER SESSION_SERIAL# NUMBER USER_ID NUMBER SQL_ID VARCHAR2(13) SQL_CHILD_NUMBER NUMBER SQL_PLAN_HASH_VALUE NUMBER SERVICE_HASH NUMBER SESSION_TYPE NUMBER SQL_OPCODE NUMBER QC_SESSION_ID NUMBER QC_INSTANCE_ID NUMBER CURRENT_OBJ# NUMBER CURRENT_FILE# NUMBER CURRENT_BLOCK# NUMBER SEQ# NUMBER EVENT# NUMBER P1 NUMBER P2 NUMBER P3 NUMBER WAIT_TIME NUMBER TIME_WAITED NUMBER PROGRAM VARCHAR2(48) MODULE VARCHAR2(48) ACTION VARCHAR2(32) CLIENT_ID VARCHAR2(64)
Sql metrics: x$kewrtsqlstat
x$kewrtsqlstat
Schema URL
http://svrman.us.oracle.com/SchemaView/view_all.html