Time Model SQL Metrics Waits Stats Sessions Performance

Time Model SQL Metrics Waits Stats Sessions Performance Tuning in Oracle 10 g Kyle Hailey

9 i Waits v$system_event v$sql V$session_wait V$sql –includes stats v$sysstat V$session_event V$sesstat v$session V$sysstat V$system_event

10 g SQL Waits V$sql Includes some waits now V$session includes waits Stats Sessions

Metrics – rates and calculations X$ -> no v$ -> WRH$_SQLSTAT SQL Metrics Stats V$sysmetric Waits V$eventmetric V$waitclassmetric Sessions V$sessmetric

Time Model SQL Wait Metrics Stats Sessions

Metrics In Memory History 1 hour in Memory 15 and 60 seconds Session Wait Time 1 -128 M in Memory Every second

10 g 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

10 g 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 10 g –

10 g 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$metricname 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 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 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 Metrics Stats Waits 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 10 g

Waits New Ÿ 800 waits Ÿ Latches broken out – Ex) Latch: library cache latch Ÿ Enqueues broken out – Ex) Enq: HW - contention

Waitclasses Ÿ Administrative (39) Ÿ Configuration (20) – – 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 – – – 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 v$system_wait_class Current deltas Recent deltas Wait Classes v$waitclassmetric_history WAIT_CLASS_ID BEGIN_TIME WAIT_CLASS# END_TIME WAIT_CLASS INTSIZE_CSEC TOTAL_WAITS WAIT_CLASS_ID TIME_WAITED WAIT_CLASS# NUM_SESS_WAITING EVENT# TIME_WAITED EVENT_ID WAIT_COUNT v$event_name AWR on disk 7 days wrh$_waitclassmetric_history Only gets populated with alerts NAME PARAMETER 1 PARAMETER 2 PARAMETER 3 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_history v$active_session_history SID SEQ# EVENT P 1 TEXT P 1 RAW P 2 TEXT P 2 RAW P 3 TEXT P 3 RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SEQ# EVENT SAMPLE_ID SNAP_D SAMPLE_TIME DBID INSTANCE_NUMBER SESSION_ID P 1 TEXT SESSION_SERIAL# P 1 USER_ID P 2 TEXT SQL_ID P 2 SQL_CHILD_NUMBER P 3 TEXT P 3 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# P 1 EVENT_ID P 2 EVENT# P 3 EVENT_ID WAIT_TIME NAME TIME_WAITED PARAMETER 1 CURRENT_OBJ# PARAMETER 2 CURRENT_FILE# PARAMETER 3 CURRENT_BLOCK# WAIT_CLASS_ID PROGRAM WAIT_CLASS# MODULE WAIT_CLASS ACTION CLIENT_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# USER_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 P 1 P 2 P 3 WAIT_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 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_NAME VALUE METRIC_UNIT Statistics Metrics v$metricgroup GROUP_ID v$sysstat NAME INTERVAL_SIZE v$sysmetric_history MAX_INTERVAL BEGIN_TIME v$sessmetric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID SESSION_ID METRIC_ID SESSION_SERIAL_NUM METRIC_NAME CPU VALUE PHYSICAL_READS METRIC_UNIT PGA_MEMORY HARD_PARSES v$sysmetric_summary BEGIN_TIME END_TIME INTSIZE_CSEC -> intsize GROUP_ID METRIC_NAME NUM_INTERVAL MAXVAL MINVAL AVERAGE STANDARD_DEVIATION METRIC_UNIT STATISTIC# NAME CLASS VALUE HASH SOFT_PARSES v$metricname GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT PHYSICAL_READ_PCT LOGICAL_READ_PCT v$sessstat STATISTIC# NAME CLASS VALUE HASH

Time Model Time Model SQL Metrics Stats Waits Sessions

Time Model Ÿ Ÿ Ÿ *V$SYS_TIME_MODEL *V$SESS_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_NAME VALUE

Time Model Details SQL> select STAT_NAME, value from V$SYS_TIME_MODEL; DB time 3. 9837 E+10 DB CPU 2. 4055 E+10 background cpu time 2. 1808 E+10 sequence load elapsed time 15939410 parse time elapsed 451760577 hard parse elapsed time 178851736 sql execute elapsed time 3. 6900 E+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

SQL Time Model SQL Metrics Stats Waits 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_BL Ÿ WRH$_SQLTEXT

Metrics Time Model SQL Metrics Stats Waits 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_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 File Metrics Long Duration Max Interval Ie, v$sysmetric_history keeps the last hour for long duration deltas, 60 s, and the last 4 minutes for short duration, 15 second deltas

V$metric v$metric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID ENTITY_SEQUENCE METRIC_ID METRIC_NAME VALUE METRIC_UNIT

Session Time Model SQL Metrics Stats Waits Sessions

Session Added ( exposed ) v$session_wait in v$session EVENT# EVENT P 1 TEXT P 1 RAW P 2 TEXT P 2 RAW P 3 TEXT P 3 RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT NUMBER VARCHAR 2(64) NUMBER RAW(4) NUMBER VARCHAR 2(64) 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_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_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_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_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 10 th 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_BL *WRH$_BG_EVENT_SUMMARY WRH$_BUFFER_POOL_STATISTICS WRH$_DATAFILE WRH$_DB_CACHE_ADVICE_BL WRH$_DLM_MISC WRH$_ENQUEUE_STAT_BL * WRH$_WAITCLASSMETRIC_HISTORY *WRH$_EVENT_NAME WRH$_FILEMETRIC_HISTORY WRH$_FILESTATXS_BL WRH$_INSTANCE_RECOVERY WRH$_JAVA_POOL_ADVICE WRH$_LATCH_BL WRH$_LATCH_CHILDREN_BL WRH$_LATCH_MISSES_SUMMARY_BL WRH$_LATCH_NAME WRH$_LATCH_PARENT_BL WRH$_LIBRARYCACHE WRH$_LOG *WRH$_METRIC_NAME WRH$_MTTR_TARGET_ADVICE WRH$_OPTIMIZER_ENV WRH$_OSSTAT WRH$_PARAMETER_BL WRH$_PARAMETER_NAME WRH$_PGA_TARGET_ADVICE_BL WRH$_PGASTAT_BL WRH$_RECOVERY_FILE_DEST_STAT WRH$_RESOURCE_LIMIT WRH$_RMAN_PERFORMANCE WRH$_ROLLSTAT WRH$_ROWCACHE_SUMMARY_BL WRH$_SEG_STAT_BL WRH$_SEG_STAT_OBJ *WRH$_SESSMETRIC_HISTORY WRH$_SGASTAT_BL WRH$_SHARED_POOL_ADVICE WRH$_SQL_PLAN WRH$_SQL_SUMMARY WRH$_SQL_WORKAREA_HISTOGRAM WRH$_SQLBIND_BL WRH$_SQLSTAT_BL WRH$_SQLTEXT WRH$_STAT_NAME *WRH$_SYS_TIME_MODEL_BL *WRH$_SYSMETRIC_HISTORY *WRH$_SYSMETRIC_SUMMARY *WRH$_SYSSTAT_BL *WRH$_SYSTEM_EVENT_BL WRH$_TABLESPACE_USAGE WRH$_TABLESPACE_STAT_BL WRH$_TEMPFILE WRH$_TEMPSTATXS WRH$_THREAD WRH$_UNDOSTAT 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_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 V$SQL_BIND_DATA V$SQL_BIND_METADATA V$SQL_CURSOR V$SQL_OPTIMIZER_ENV V$SQL_PLAN_STATISTICS_ALL V$SQL_REDIRECTION V$SQL_SHARED_CURSOR V$SQL_SHARED_MEMORY V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA_HISTOGRAM V$SQLAREA V$SQLTEXT_WITH_NEWLINES Event, Waits, Stats and Metrics V$CLIENT_STATS V$EVENT_HISTOGRAM V$EVENT_NAME V$EVENTMETRIC 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_HISTORY V$SYSMETRIC_HISTORY V$SYSMETRIC_SUMMARY V$SYSSTAT V$SYSTEM_EVENT V$SYSTEM_WAIT_CLASS

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_tim e, a. session_id, a. session_serial#, a. user_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. p 1, a. p 2, a. p 3, 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_TIME SAMPLE_ADDR SAMPLE_LENGTH ROW_COUNT desc x$ash RAW(4) NUMBER TIMESTAMP(3) NUMBER ADDR INDX INST_ID SAMPLE_ADDR SAMPLE_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# USER_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# P 1 P 2 P 3 WAIT_TIME_WAITED PROGRAM MODULE ACTION CLIENT_ID RAW(4) NUMBER TIMESTAMP(3) NUMBER VARCHAR 2(13) NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR 2(48) VARCHAR 2(32) VARCHAR 2(64)

Sql metrics: x$kewrtsqlstat Ÿ x$kewrtsqlstat

Schema URL http: //svrman. us. oracle. com/Schema. View/view_al l. html