performance tuning in oracle 10g l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Performance Tuning in Oracle 10g PowerPoint Presentation
Download Presentation
Performance Tuning in Oracle 10g

Loading in 2 Seconds...

play fullscreen
1 / 56

Performance Tuning in Oracle 10g - PowerPoint PPT Presentation


  • 922 Views
  • Uploaded on

Time Model. Waits. Performance Tuning in Oracle 10g. SQL. Metrics. Sessions. Stats . Kyle Hailey. 9i. Waits v$system_event. v$sql. v$session. V$system_event. v$sysstat . V$session_wait V$session_event. V$sql –includes stats. V$sesstat. V$session.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Performance Tuning in Oracle 10g


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
performance tuning in oracle 10g

Time Model

Waits

Performance Tuning in Oracle 10g

SQL

Metrics

Sessions

Stats

Kyle Hailey

slide2
9i

Waits

v$system_event

v$sql

v$session

V$system_event

v$sysstat

V$session_wait

V$session_event

V$sql –includes stats

V$sesstat

V$session

V$sysstat

slide3
10g

Waits

SQL

V$sql

Includes some waits now

Sessions

Stats

V$session includes waits

metrics rates and calculations
Metrics – rates and calculations

X$ -> no v$ -> WRH$_SQLSTAT

Waits

SQL

V$eventmetric

V$waitclassmetric

Metrics

Sessions

Stats

V$sessmetric

V$sysmetric

time model
Time Model

Time Model

Wait

SQL

Metrics

Sessions

Stats

in memory history

Session Wait

Metrics

In Memory History

1 hour in Memory

15 and 60 seconds

Time

1-128M in Memory

Every second

10g constructs
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
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
10g Generic Fields Names Conventions V$
  • Names
    • Event
    • Statistic
    • Waitclass
  • Ids
    • Event#
    • Statistic#
    • Waitclass#
  • Name Hash
    • Event_id
    • Statistic_id
    • Waitclass_id
waits
Waits

Time Model

Waits

SQL

Metrics

Sessions

Stats

waits v
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
Waits New
  • 800 waits
  • Latches broken out
    • Ex) Latch: library cache latch
  • Enqueues broken out
    • Ex) Enq: HW - contention
waitclasses
Waitclasses
  • 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)
  • 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
waitclasses17
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
Waits Metrics (Events and Classes)

v$eventmetric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

EVENT_ID

NUM_SESS_WAITING

TIME_WAITED

WAIT_COUNT

AWR on disk 7 days

Current cumulative

Recent deltas

Current deltas

Wait Classes

wrh$_waitclassmetric_history

Only gets populated with alerts

v$system_wait_class

WAIT_CLASS_ID

WAIT_CLASS#

WAIT_CLASS

TOTAL_WAITS

TIME_WAITED

v$waitclassmetric_history

BEGIN_TIME

END_TIME

INTSIZE_CSEC

WAIT_CLASS_ID

WAIT_CLASS#

NUM_SESS_WAITING

TIME_WAITED

WAIT_COUNT

v$waitclassmetric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

WAIT_CLASS_ID

WAIT_CLASS#

NUM_SESS_WAITING

TIME_WAITED

WAIT_COUNT

v$event_name

EVENT#

EVENT_ID

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

No in memory history

WRH$_SYSTEM_EVENT

V$session_event

waits session sampling er
Waits – Session Sampling ER
  • v$active_session_history

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#

P1

P2

P3

WAIT_TIME

TIME_WAITED

CURRENT_OBJ#

CURRENT_FILE#

CURRENT_BLOCK#

PROGRAM

MODULE

ACTION

CLIENT_ID

v$session_wait_history

SID

SEQ#

EVENT#

EVENT

P1TEXT

P1

P2TEXT

P2

P3TEXT

P3

WAIT_TIME

7 days (disk)

Half hour

current

10 samples

  • wrh$active_session_history

SNAP_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

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$event_name

EVENT_ID

EVENT#

EVENT_ID

NAME

PARAMETER1

PARAMETER2

PARAMETER3

WAIT_CLASS_ID

WAIT_CLASS#

WAIT_CLASS

waits in workload repository awr
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
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_BLWRH$_SYSTEM_EVENT
    • DBA_HIST_BG_EVENT_SUMMARY
stats
Stats

Time Model

Waits

SQL

Metrics

Sessions

Stats

stats23
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
v$sysmetric
  • Short Duration – 15 secs
  • Long Duration – 60 secs
  • Per Transaction
  • Per Sec
  • Ratios
metric short duration
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

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

Per Sec

User Transaction Per Sec

metric long duration
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
Metric long duration per sec/txn

Per Second and Transaction

Per Sec

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%

.

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

Per Transaction

Response Time

metrics visually
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
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
Stats – ER

Statistics Metrics

v$sysmetric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

GROUP_ID

METRIC_ID

METRIC_NAME

VALUE

METRIC_UNIT

v$metricgroup

GROUP_ID

NAME

INTERVAL_SIZE

MAX_INTERVAL

v$sysstat

STATISTIC#

NAME

CLASS

VALUE

HASH

v$sysmetric_history

BEGIN_TIME

END_TIME

INTSIZE_CSEC

GROUP_ID

METRIC_ID

METRIC_NAME

VALUE

METRIC_UNIT

v$sessmetric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

SESSION_ID

SESSION_SERIAL_NUM

CPU

PHYSICAL_READS

PGA_MEMORY

HARD_PARSES

SOFT_PARSES

PHYSICAL_READ_PCT

LOGICAL_READ_PCT

v$sessstat

STATISTIC#

NAME

CLASS

VALUE

HASH

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$metricname

GROUP_ID

GROUP_NAME

METRIC_ID

METRIC_NAME

METRIC_UNIT

time model31
Time Model

Time Model

Waits

SQL

Metrics

Sessions

Stats

time model32
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 model33
Time Model

V$SESS_TIME_MODEL

STAT_ID

STAT_NAME

VALUE

V$SYS_TIME_MODEL

STAT_ID

STAT_NAME

VALUE

time model details

SQL> select STAT_NAME, value from V$SYS_TIME_MODEL

2 ;

STAT_NAME VALUE

---------------------------------------------------------------- ----------

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

STAT_NAME VALUE

---------------------------------------------------------------- ----------

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

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
Time Model Detail Session

SQL> select STAT_NAME, value from V$SESS_TIME_MODEL where sid=1;

DB time 143

DB CPU 290000

background cpu time 0

sequence load elapsed time 0

parse time elapsed 0

hard parse elapsed time 0

sql execute elapsed time 0

global cache cr block receive time 0

global cache current block receive time 0

global cache get time 0

connection management call elapsed time 0

failed parse elapsed time 0

hard parse (sharing criteria) elapsed time 0

hard parse (bind mismatch) elapsed time 0

PL/SQL execution elapsed time 0

inbound PL/SQL rpc elapsed time 0

PL/SQL compilation elapsed time 0

Java execution elapsed time 0

bind/define call elapsed time 0

cluster wait time 0

concurrency wait time 0

application wait time 0

user I/O wait time 0

slide36
SQL

Time Model

Waits

SQL

Metrics

Sessions

Stats

v 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
AWR SQL
  • WRH$_SQLSTAT
  • WRH$_SQLSTAT_BL
  • WRH$_SQLTEXT
metrics
Metrics

Time Model

Waits

SQL

Metrics

Sessions

Stats

metrics40
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
metrics41
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$METRIC
  • V$METRIC_HISTORY
  • Combined view onto the other
  • metric tables
metric groups and names
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
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

7 File Metrics Long Duration 60000 6 7 6

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

v$metric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

GROUP_ID

ENTITY_ID

ENTITY_SEQUENCE

METRIC_ID

METRIC_NAME

VALUE

METRIC_UNIT

session
Session

Time Model

Waits

SQL

Metrics

Sessions

Stats

session46
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

slide47
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
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_HISTORYalert

*-DBA_HIST_SYSMETRIC_SUMMARY– max, min, avg standard deviation

*-DBA_HIST_SESSMETRIC_HISTORYalert

-DBA_HIST_FILEMETRIC_HISTORYalert

*DBA_HIST_WAITCLASSMET_HISTORYalert

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_session_history

-DBA_HIST_TABLESPACE_STAT

-DBA_HIST_LOG

DBA_HIST_MTTR_TARGET_ADVICE

-DBA_HIST_TBSPC_SPACE_USAGE - ?

awr data tables wrh
AWR Data Tables - WRH

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

*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

awr metadata tables
AWR Metadata Tables

SWRF METADATA TABLES

WRM$_BASELINE

WRM$_DATABASE_INSTANCE

WRM$_SNAPSHOT

WRM$_SNAP_ERROR

WRM$_WR_CONTROL

awr internal tables
AWR Internal Tables

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$_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$_SQLSET_REFERENCES

WRI$_SQLSET_STATEMENTS

WRI$_TRACING_ENABLE

slide52
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

slide53
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
X$ Desc x$ash

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> desc x$kewash

ADDR RAW(4)

INDX NUMBER

INST_ID NUMBER

SAMPLE_ID NUMBER

SAMPLE_TIME TIMESTAMP(3)

SAMPLE_ADDR NUMBER

SAMPLE_LENGTH NUMBER

ROW_COUNT NUMBER

schema url
Schema URL

http://svrman.us.oracle.com/SchemaView/view_all.html