Storage Operations Management Work Plan
Download
Report
Transcript Storage Operations Management Work Plan
Time Model
SQL
Metrics
Stats
Waits
Sessions
Performance Tuning in Oracle
10g
Kyle Hailey
9i
Waits
v$system_event
v$sql
V$session_wait
V$sql –
includes stats
V$session_event
V$sesstat
v$sysstat
v$session
V$session
V$sysstat
V$system_event
10g
Waits
SQL
V$sql
Includes some
waits now
V$session includes waits
Stats
Sessions
Metrics – rates and
calculations
X$ -> no v$ -> WRH$_SQLSTAT
SQL
Waits
V$eventmetric
V$waitclassmetric
Metrics
Stats
Sessions
V$sysmetric
V$sessmetric
Time Model
Time Model
SQL
Wait
Metrics
Stats
Sessions
In Memory History
1 hour in Memory
15 and 60 seconds
Time
1-128M in Memory
Every second
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 View
V$
Cumulative
values now
Stats
V$stat_name
Waits
V$event_name
waitclasses
Time Model
sql
V$sysstat
V$system_event
v$system_wait_class *
V$sys_time_model *
V$sql
v$waitclassmetric *
----------Some goes into
sysmetric
ASH
V$event_histogram
*
Metrics
v$metricgrou
p
V$metricnam
e
deltas
V$sysmetric *
Metrics
V$sysmetric_
history *
V$sysmetric_
summary *
-----------
v$waitclassmetric_histo
ry *
-----------
-----------
wrh$_sysmetr
ic_history
*(alert only)
wrh$_sysstat
WRH$_SYSM
ETRIC_SUMM
ARY *
wrh$_system_even
t
wrh$_waitclassmetric_hi
story (alert only) *
WRH$_SYS_TIME
_MODEL *
WRH$_SQLSTAT
History
hour of Deltas
AWR
Last 7 days
V$eventmetric *
x$kewrtsqlstat *
Cumulative,
updated every 10
seconds?)
*
Session Statistics View
stats
waits
waitclasses
Time Model
sql
V$
V$sessstat
V$session_event
----------------
V$sess_time_model
*
----------------(some stats
possible
from ASH)
Metrics
V$sessmetric
-----------------
-----------------
-----------------
-----------------
Metrics
-----------------
-----------------
-----------------
-----------------
-----------------
----------------(some stats
possible from
ASH)
----------------
-----------------
----------------(some stats
possible
from ASH)
history
AWR
WRH$_SESSMET
RIC_HISTORY *
(alert only)
Session Polling View
Active Session History : ASH
stats
V$
V$session_wait
Metrics
Metrics
V$active_session_history *
history
Polling at 1 second
AWR
Wrh$_active_session_history
(V$session_wait_history)
(1 in 10 values from “”) *
Waits
Time Model
SQL
Waits
Metrics
Stats
Sessions
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) Configuration (20)
–
–
Application (11)
–
–
enqueues
sqlnet break/reset
Cluster (113)
Commit (1)
–
Log file Sync
Concurrency (12)
–
–
–
–
switch logfile
rebuild index
Latches: cbc, lbc,
Lib cache locks
Bbw
–
–
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)
Waitclasses
SQL> 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
Waits Metrics (Events and
Classes)
Current
cumulative
Current deltas
Recent deltas
AWR on disk 7
days
Wait Classes
v$system_wait_class
v$waitclassmetric
v$waitclassmetric_history
WAIT_CLASS_ID
BEGIN_TIME
BEGIN_TIME
WAIT_CLASS#
END_TIME
END_TIME
WAIT_CLASS
INTSIZE_CSEC
INTSIZE_CSEC
TOTAL_WAITS
WAIT_CLASS_ID
WAIT_CLASS_ID
TIME_WAITED
WAIT_CLASS#
WAIT_CLASS#
v$event_name
NUM_SESS_WAITING
NUM_SESS_WAITING
EVENT#
TIME_WAITED
TIME_WAITED
EVENT_ID
WAIT_COUNT
WAIT_COUNT
wrh$_waitclassmetric_history
Only gets populated with alerts
NAME
PARAMETER1
PARAMETER2
PARAMETER3
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
Wait Events
V$system_event
EVENT
TOTAL_WAITS
TOTAL_TIMEOUTS
TIME_WAITED
AVERAGE_WAIT
TIME_WAITED_MICRO
EVENT_ID
V$session_event
v$eventmetric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
EVENT_ID
NUM_SESS_WAITING
TIME_WAITED
WAIT_COUNT
WRH$_SYSTEM_EVENT
No in memory history
Waits – Session Sampling ER
current
7 days (disk)
Half hour
10 samples
v$session_wait v$session_wait_history v$active_session_history
SID
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
SEQ#
EVENT#
EVENT
SAMPLE_ID
SNAP_D
SAMPLE_TIME
DBID
INSTANCE_NUMBER
SESSION_ID
P1TEXT
SESSION_SERIAL#
P1
USER_ID
P2TEXT
SQL_ID
P2
SQL_CHILD_NUMBER
P3TEXT
P3
WAIT_TIME
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
v$event_name
wrh$active_session_history
EVENT#
P1
EVENT_ID
P2
EVENT#
P3
EVENT_ID
WAIT_TIME
NAME
TIME_WAITED
PARAMETER1
CURRENT_OBJ#
PARAMETER2
CURRENT_FILE#
PARAMETER3
CURRENT_BLOCK#
WAIT_CLASS_ID
PROGRAM
WAIT_CLASS#
MODULE
WAIT_CLASS
ACTION
CLIENT_ID
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
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
Time Model
SQL
Metrics
Stats
Waits
Sessions
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, stddev
Snapshots:
* 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
Per Sec and Per Transaction
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
Per Sec
User Transaction Per Sec
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
Executions
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
Per Second and Transaction
Per Sec
User Commits
User Rollbacks
User Transaction
DBWR Checkpoints
Background Checkpoints
Network Traffic Volume
Per Transaction
Response Time
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
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%
.
Metrics Visually
v$sysmetric_history
3 minutes of 15 second deltas
60 minutes of 1 minute deltas
Not saved to disk but summary is
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)
Stats – ER
v$sysmetric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
v$sysmetric_history
Statistics Metrics
v$metricgroup
GROUP_ID
NAME
v$sysstat
INTERVAL_SIZE
STATISTIC#
NAME
CLASS
VALUE
HASH
MAX_INTERVAL
BEGIN_TIME
BEGIN_TIME
END_TIME
END_TIME
INTSIZE_CSEC
INTSIZE_CSEC
GROUP_ID
SESSION_ID
METRIC_ID
SESSION_SERIAL_NUM
METRIC_NAME
CPU
VALUE
PHYSICAL_READS
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$sessmetric
PGA_MEMORY
v$sessstat
HARD_PARSES
STATISTIC#
NAME
CLASS
VALUE
HASH
SOFT_PARSES
PHYSICAL_READ_PCT
v$metricname
GROUP_ID
GROUP_NAME
METRIC_ID
METRIC_NAME
METRIC_UNIT
LOGICAL_READ_PCT
Time Model
Time Model
SQL
Waits
Metrics
Stats
Sessions
Time Model
*V$SYS_TIME_MODEL
*V$SESS_TIME_MODEL
WRH$_SYS_TIME_MODEL
WRH$_SYS_TIME_MODEL_BL
DBA_HIST_SYS_TIME_MODEL
Time Model
V$SYS_TIME_MODEL
V$SESS_TIME_MODEL
STAT_ID
STAT_ID
STAT_NAME
STAT_NAME
VALUE
VALUE
Time Model Details
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 Detail Session
SQL> select STAT_NAME, value from V$SESS_TIME_MODEL where sid=1;
DB time
DB CPU
background cpu time
sequence load elapsed time
parse time elapsed
hard parse elapsed time
sql execute elapsed time
global cache cr block receive time
global cache current block receive time
global cache get time
connection management call elapsed time
failed parse elapsed time
hard parse (sharing criteria) elapsed time
hard parse (bind mismatch) elapsed time
PL/SQL execution elapsed time
inbound PL/SQL rpc elapsed time
PL/SQL compilation elapsed time
Java execution elapsed time
bind/define call elapsed time
cluster wait time
concurrency wait time
application wait time
user I/O wait time
143
290000
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
SQL
Time Model
SQL
Waits
Metrics
Stats
Sessions
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
Time Model
SQL
Waits
Metrics
Stats
Sessions
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$METRIC
V$METRIC_HISTORY
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
Combined view onto the other
metric tables
Metric groups and names
v$metricgroup
GROUP_ID
NAME
INTERVAL_SIZE
MAX_INTERVAL
v$metricname
GROUP_ID
GROUP_NAME
METRIC_ID
METRIC_NAME
METRIC_UNIT
Metric Group
2 is a superset of 3
4 is a superset of 5
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
60000
6
7
6
7 File Metrics Long Duration
Max Interval
Ie, v$sysmetric_history keeps the last hour for
long duration deltas, 60s, and the last 4
minutes for short duration, 15 second deltas
V$metric
v$metric
BEGIN_TIME
END_TIME
INTSIZE_CSEC
GROUP_ID
ENTITY_ID
ENTITY_SEQUENCE
METRIC_ID
METRIC_NAME
VALUE
METRIC_UNIT
Session
Time Model
SQL
Waits
Metrics
Stats
Sessions
Session
Added ( exposed ) v$session_wait in v$session
EVENT#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
WAIT_TIME
SECONDS_IN_WAIT
NUMBER
VARCHAR2(64)
VARCHAR2(64)
NUMBER
RAW(4)
VARCHAR2(64)
NUMBER
RAW(4)
VARCHAR2(64)
NUMBER
RAW(4)
NUMBER
NUMBER
VARCHAR2(64)
NUMBER
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_HIST
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_SNAPSHOT
- DBA_HIST_SNAP_ERROR
- DBA_HIST_BASELINE
- DBA_HIST_WR_CONTROL ? (work load repository ?)
- DBA_HIST_DATAFILE
DBA_HIST_FILESTATXS – only file number, no name
- DBA_HIST_TEMPFILE
DBA_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 backgrounds
DBA_HIST_WAITSTAT
DBA_HIST_ENQUEUE_STAT
-DBA_HIST_LATCH_NAME
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_PARENT
DBA_HIST_LATCH_MISSES_SUMMARY – summed over parent latch
DBA_HIST_LIBRARYCACHE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_ROWCACHE_SUMMARY – summed over rowcache entries
DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_PGASTAT
DBA_HIST_RESOURCE_LIMIT
DBA_HIST_SHARED_POOL_ADVICE
?DBA_HIST_SQL_WORKAREA_HSTGRM
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_INSTANCE_RECOVERY
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_THREAD - logswitches
-DBA_HIST_STAT_NAME
*DBA_HIST_SYSSTAT
*-DBA_HIST_SYS_TIME_MODEL
-DBA_HIST_OSSTAT_NAME
-DBA_HIST_OSSTAT
DBA_HIST_PARAMETER_NAME
DBA_HIST_PARAMETER
DBA_HIST_UNDOSTAT
DBA_HIST_ROLLSTAT
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
-DBA_HIST_METRIC_NAME
*-DBA_HIST_SYSMETRIC_HISTORY alert
*-DBA_HIST_SYSMETRIC_SUMMARY – max, min, avg standard devia
*-DBA_HIST_SESSMETRIC_HISTORY alert
-DBA_HIST_FILEMETRIC_HISTORY alert
*DBA_HIST_WAITCLASSMET_HISTORY alert
DBA_HIST_DLM_MISC
-DBA_HIST_RCVRY_FILE_DEST_STAT
-DBA_HIST_RMAN_PERFORMANCE
*-DBA_HIST_ACTIVE_SESS_HISTORY – every 10th point from v$active_
-DBA_HIST_TABLESPACE_STAT
-DBA_HIST_LOG
DBA_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 Tables
WRI$_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$_SQLSET_REFERENCES
WRI$_ADV_SQLW_TABLES
WRI$_SQLSET_STATEMENTS
WRI$_ADV_SQLW_TABVOL
WRI$_TRACING_ENABLE
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
V$
SQL
GO$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
Event, Waits, Stats and Metrics
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
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$ash
SQL> desc x$kewash
ADDR
INDX
INST_ID
SAMPLE_ID
SAMPLE_TIME
SAMPLE_ADDR
SAMPLE_LENGTH
ROW_COUNT
desc x$ash
RAW(4)
NUMBER
NUMBER
NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
ADDR
INDX
INST_ID
SAMPLE_ADDR
SAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SERVICE_HASH
SESSION_TYPE
SQL_OPCODE
QC_SESSION_ID
QC_INSTANCE_ID
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
SEQ#
EVENT#
P1
P2
P3
WAIT_TIME
TIME_WAITED
PROGRAM
MODULE
ACTION
CLIENT_ID
RAW(4)
NUMBER
NUMBER
NUMBER
NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(48)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
Sql metrics: x$kewrtsqlstat
x$kewrtsqlstat
Schema URL
http://svrman.us.oracle.com/SchemaView/view_al
l.html