performance tuning in oracle 10g feel the power ! kyle hailey [email protected]

Performance Tuning in Oracle 10g

Feel the Power !

Kyle [email protected]

http://oraperf.sourceforge.net

mailto:[email protected]

http://oraperf.sourceforge.net/

New Features

1.Metrics2.Wait Classes3.Time Model4.ASH5.AWR ( DBA_HIST_ )

6.ADDM7.Misc

Part I

Eat your Spinach(Hold on to the seat of your

pants … )

Part II

Wow – the main course

Part III

Dessert – this is cool(And that’s not all)

Metrics … a new 10g feature to make our lives easier


6.ADDM7.Misc

Part I

Part II

Part III

Motivation

What inspired Metrics?Why should you care ?

Eating your Spinach

How do we find Performance Problems

– With StatisticsStatistics have always been a

Pain– How do YOU find bottlenecks with

statistics?YOU DO THE MATH This is TEDIOUS

– (and your boss probably doesn’t even appreciate you for it )

First Tedious Step

SQL> Select value from v$sysstat where name=‘physical reads’;

VALUE --------------- 1,533,787

Not much help …Why? Let see …

This tells you … Nothing

1,533,787

IO’s

time

30 minutes

30 minutes

time

GOOD BAD

How do You find the Delta?

Where’s the Beef ?

YOU Need to do MATH to find Out

Take value at time A Take value at time BDelta = (B-A) orRate = (B-A)/elapsed

time

Current Methods Oracle 6 Utlbstat.sql/Utlestat.sql

– Creates tables, inserts, deletes

Oracle 8 introduced Statspack– Improvement, but needs to be set up and

administered (by guess who)

Another Option Write your own Scripts

– Take time to write, no standards

In Summary These options take time … whose time?

YOUR TIME

Solution ! 10g Metrics

Available Immediately at your fingertips … for your enjoyment and relaxation,

Introducing Metrics– Automated– Immediate– Always there– Time saving (whose time? … YOUR time)

Metrics dependability at your fingertips

Pre-Set intervals– 15 second– 60 second– 10 minutes– 30 minutes

Current Value for – Deltas– Rates

per second per transaction

– Ratios – Percentages

Serious Geek Stuff :Our Metric Family

Wait Events– V$EVENTMETRIC (60 secs )– V$WAITCLASSMETRIC (60 secs)

Statistics– V$SESSMETRIC (15 secs Deltas)– V$SYSMETRIC (15 and 60 secs deltas)

Files– V$FILEMETRIC ( 10 minutes)

SQL (the secret is out)– x$kewrtsqlstat (30 Minutes) “not a metric” cumulates

values up to 30 minutes, then snapshots it to dba_hist_sqlstat

The Solution Table v$sysmetric

SQL> desc v$sysmetric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT

Now What’s the IO?

No Calculations, just a simple select :

SQL> Select VALUE , METRIC_UNIT

from v$sysmetric

where name=‘Physical Reads’

/

(Gives per second and per transaction)

Answer at your fingertips

3 IO/sec

IO’s

time

30 minutes

30 minutes

time

GOOD BAD

513 IO/sec

1,533,787X

We solved what’s Happening now… but

What if your problem happened 10 minutes ago?

How do we get History? What happened in the past?

More work, Time and calculations by YOU

That was now. What was then?

High rate

Lots of IO

Low rate

Little IO

v$sysstat

physical reads

Solution! Metric History Tables

Last 60 minutes of history, in memory

v$sysstat

physical reads

60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs

delta

delta

delta

delta

deltadeltadelta

Family of Metric History Tables

At your fingertips :

Statistics– V$SYSMETRIC_HISTORY (60 seconds) (including 3 minutes of 15 second history

as a bonus!)

File IO– V$FILEMETRIC_HISTORY (10 minutes)

Waits– V$WAITCLASSMETRIC_HISTORY ( 60 seconds)

1 hour of 60 second deltas

v$sysstat

physical reads

60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs

delta

delta

delta

delta

deltadeltadelta

That’s not all Folks, as a bonus:

3 Minutes 15 second deltas

v$sysstat

physical reads

3 minutes of 15 second deltas

Both Stored in Same Table v$sysmetric_history

Not saved to disk but summary is

60 minutes of 1 minute deltas

3 minutes of 15 second deltas

What was IO 30 minutes ago?

SQL> Select VALUE , METRIC_UNIT

from v$sysmetric_history

where METRIC_NAME = ‘Physical Reads’ and END_TIME < ( sysdate - (30/(24*60)))

and END_TIME > ( sysdate - (35/(24*60)))

/

Once again the answer is at your fingertips

How about a 2 days ago?Long Term History, 7 days

Statistics– DBA_HIST_SYSMETRIC_SUMMARY– DBA_HIST_SYSMETRIC_HISTORY (alerts)– DBA_HIST_SYSSTAT (cumulative)

Waits– WAITCLASSMETRIC_HISTORY (alerts) – DBA_HIST_SYSTEM_EVENT (cumulative)

File IO– DBA_HIST_FILEMETRIC_HISTORY (alerts)– DBA_HIST_FILESTATXS (cumulative)

SQL– DBA_HIST_SQLSTAT

EM Exposing Metrics

That was the Introduction to Metrics

We saw– Current deltas : Metrics Tables– Hour History : Metric History Tables– Week of History : DBA_HIST Tables

Now lets Look at the Groupings– Statistics– Waits– File I/O

Statistics

• Raw : v$sysstat• Current Rates: v$sysmetric 15 & 60 seconds

• 15 Second • 60 Second• Session Stats

• 1 Hour : v$sysmetric_history (in memory)

• 7 Days : dba_hist_sysmetric_summary (with AWR)

v$sysmetric 15 Secs for 3 minutes

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

v$sysmetric 60 Sec for an hour

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

v$sysmetric 60 Sec rates sec/txn for an hour

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%




.

Per Second and Transaction

Per Sec

Per Transaction

v$sessmetric: Session Metric

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_PCT

• No History

• Only a one 15 second Delta

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 Family of Tables

v$metricgroup GROUP_ID

NAME

INTERVAL_SIZE

MAX_INTERVAL

v$sessstat STATISTIC# NAME CLASS VALUE HASH

Waits

• Raw : v$system_event• Current Deltas : v$eventmetric (60 seconds)• 1 Hour : n/a• 7 Days : dba_hist_system_event (cumulative)

Desc v$eventmetric

SQL> desc v$eventmetric

Name Type

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

BEGIN_TIME DATE

END_TIME DATE

INTSIZE_CSEC NUMBER

EVENT# NUMBER

EVENT_ID NUMBER

NUM_SESS_WAITING NUMBER

TIME_WAITED NUMBER

WAIT_COUNT NUMBER

Select from v$eventmetric

SQL> select en.name name, num_sess_waiting WAITERS, time_waited, wait_count from v$eventmetric em, v$event_name en where wait_count > 0 and en.event# = em.event# /

v$eventmetric results

NAME WAITERS TIME_WAITED WAIT_COUNT

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

pmon timer 1 5875 20

process startup 0 13 1

rdbms ipc message 7 41104 168

control file sequential read 0 0 10

control file parallel write 0 2 20

log file parallel write 0 0 1

SQL*Net message to client 0 0 47

SQL*Net more data to client 0 1 4

SQL*Net message from client 4 14721 47

SQL*Net more data from client 0 0 1

queue messages 2 12012 24

Queue Monitor Wait 1 3000 1

Queue Monitor Task Wait 0 0 1

Files

• Raw : v$fileio• Current Delta : v$filemetric ( 10 Minutes)• 1 Hour : v$filemetric_history ( 1 hour, 7

points)• 7 Days : dba_hist_filemetric_history (alerts

only)

V$FILEMETRIC_HISTORY

SQL> select BEGIN_TIME,FILE_ID, PHYSICAL_READS

from V$FILEMETRIC_HISTORY;

BEGIN_TI FILE_ID PHYSICAL_READS-------- ---------- --------------04:12:16 1 20804:12:16 2 600

04:02:18 1 60004:02:18 2 189

03:52:15 1 192203:52:15 2 2082

... For the last hour

Metrics – in summary

Current rates automatically calculated

History of Rates for an hour in memory

History kept for a week on disk

Metrics V$METRICGROUP V$METRICNAME

Current Values V$SYSMETRIC V$SESSMETRIC V$FILEMETRIC V$EVENTMETRIC V$WAITCLASSMETRIC V$SVCMETRIC

Last Hour V$SYSMETRIC_HISTORY V$FILEMETRIC_HISTORY V$WAITCLASSMETRIC_HISTORY V$SVCMETRIC_HISTORY

V$SYSMETRIC_SUMMARY – avg, std dev, max, min

V$METRICV$METRIC_HISTORY

Combined view onto the other metric tables

Week of History dba_hist_sysmetric_summary dba_hist_system_event (cumulative) dba_hist_filemetric_history (alerts)

Metrics Family of TablesStats ( V$stat_name) Waits ( V$event_name ) Files

Raw V$sysstat v$system_event

v$event_histogram

v$system_wait_class

v$fileio

Now

v$metricgroupv$metricname

V$sysmetric v$eventmetric

v$waitclassmetric

v$filemetric

1 Hour

V$sysmetric_history V$sysmetric_summary

v$waitclassmetric_history v$filemetric_history

7 days

DBA_HIST_*

SYSMETRIC_SUMMARY

SYSSTAT (cumulative)

SYSMETRIC_HISTORY (alerts)

SYSTEM_EVENT(cumulative)

WAITCLASSMETRIC_HISTORY (alert) FILESTATXS (cumulative)

TEMPSTATXS (cumulative)

FILEMETRIC_HISTORY (alert)

Wait Classes


6.ADDM7.Misc

Part I

Part II

Part III

Wait Classes 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– Buffer busy wait

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)

Wait Classes in EM

Wait Class Tables

v$system_wait_class– cumulative

v$waitclassmetric – Current rates

v$waitclassmetric_history

– Hour of rates (60 second intervals)

v$waitclassmetricBEGIN_TIME

END_TIME

INTSIZE_CSEC

WAIT_CLASS_ID

WAIT_CLASS#

NUM_SESS_WAITING

TIME_WAITED

WAIT_COUNT

Waits Metrics

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

dba_hist_system_event

(Cumulative)

dba_hist_waitclassmetric_history

Only gets populated with alerts

Wait Classes

Wait Events

Cumulative Current deltas Last Hour Last 7 days

Time Model


6.ADDM7.Misc

Part I

Part II

Part III

Time Model

New concept• DB Time

Total time for all database calls• cpu time • wait time

Time Model areas Total Time CPU Elapsed Time SQL execution Plsql execution (sql execute subtracted

out) Java execution Connection time

Time Model Components1) background elapsed time 2) background cpu time

1) DB time 2) DB CPU 2) connection management call elapsed time 2) sequence load elapsed time 2) sql execute elapsed time 2) parse time elapsed 3) hard parse elapsed time 4) hard parse (sharing criteria) elapsed time 5) hard parse (bind mismatch) elapsed time 3) failed parse elapsed time 4) failed parse (out of shared memory) elapsed time 2) PL/SQL execution elapsed time 2) inbound PL/SQL rpc elapsed time 2) PL/SQL compilation elapsed time 2) Java execution elapsed time

Total Time

Total Database Time

Total and CPU

DB CPU Time

=~ DB Wait Time

Total and CPU and Parse Time

DB CPU Time

DB Wait

Time

Parse Elapsed

Time

Parse Time

Parse Elapsed

Time

Hard Parse

Parse

time

elapsed

hard parse elapsed time

Hard Parse Sharing Criteria

Parse

time

elapsed


hard parse

(sharing criteria) elapsed

time

Hard Parse Bind Mismatch

Parse

time

elapsed


hard parse

(sharing criteria) elapsed

time

hard parse (bind

mismatch)

elapsed time

Time Model TablesCurrent cumulative values V$SYS_TIME_MODEL V$SESS_TIME_MODEL

AWR Snapshots of cumulative values DBA_HIST_SYS_TIME_MODEL

ASH


6.ADDM7.Misc

Part I

Part II

Part III

ASH A Revolution in Monitoring

Active Session History New 10g Every Second it collects data 1 hour of history in Memory for

immediate access at your fingertips

This hour of data could change your life

It’s a Revolution and it’s an Evolution

Oracle 6 … ie the dark ages … there was Cache Buffer Hit Ratio

Oracle 7 … turned the lights on … Wait Events … hallelujah I can see the light

Oracle 10g … ASH has landed

ASH – Intelligence for the new

Millennium

Selectively Collects Data– More active, more data collected – Less active, less data collected– It self adjusts for your needs

Old methods collect everything – Costly– Limits fine Granularity

ASH – In Memory

Collects active session data only History v$session_wait + v$session +

extras• Circular Buffer - 1M to 128M (~2% of SGA)

• Flushed every hour to disk or when buffer 2/3 full

ASH Sizing … Bigger isn’t always Better

Avg row around 150bytes 3600 secs in an hour ~ ½ Meg per Active Session per hour That’s generally over an hour of ASH

v$active_session_history

SESSION_ID NUMBER SESSION_SERIAL# NUMBER USER_ID NUMBER SERVICE_HASH NUMBER SESSION_TYPE VARCHAR2(10) PROGRAM VARCHAR2(64) MODULE VARCHAR2(48) ACTION VARCHAR2(32) CLIENT_ID VARCHAR2(64)

EVENT VARCHAR2(64) EVENT_ID NUMBER EVENT# NUMBER SEQ# NUMBER P1 NUMBER P2 NUMBER P3 NUMBER WAIT_TIME NUMBER TIME_WAITED NUMBER CURRENT_OBJ# NUMBER CURRENT_FILE# NUMBER CURRENT_BLOCK# NUMBER0

SQL_ID VARCHAR2(13) SQL_CHILD_NUMBER NUMBER SQL_PLAN_HASH_VALUE NUMBER SQL_OPCODE NUMBER QC_SESSION_ID NUMBER QC_INSTANCE_ID NUMBER

SAMPLE_ID NUMBER SAMPLE_TIME TIMESTAMP(3) When

Session

SQL

Wait

SESSION_STATE VARCHAR2(7) WAIT_TIME NUMBER State

TIME_WAITED NUMBER

Duration

Consumers Top Session Top User Top SQL Top Object Top Module.Action Top Program Top Service Top Client Top Wait

X

• CPU• Waits

• Event• I/O

• File• Block

• Time

Top CPU Session Top CPU Session in last 5 minutes

Select session_id, count(*)

from v$active_session_history

where session_state= ‘ON CPU‘ and SAMPLE_TIME > sysdate – (5/(24*60))

group by session_id

order by count(*) desc;

Results Top CPU Session

SESSION_ID COUNT(*) ---------- ---------- 265 236 264 115 257 52 271 22 276 1

ASH in OEM 10g

Family of ASH Tables

v$session_wait

v$active_session_history

wrh$active_session_history

V$session_wait_history

ASH Tables

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 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)1 in 10

AWR


6.ADDM7.Misc

Part I

Part II

Part III

Automatic Workload Repository Statspack on Steroids More efficient than Statspack Keeps stats every hour Stores by default the last 7 days

Snapshoting

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();END;

/

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_NAMEDBA_HIST_SYSTEM_EVENTDBA_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_NAMEDBA_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 - ?

New in 10g

ADDM


6.ADDM7.Misc

Part I

Part II

Part III

Automatic Database Diagnostic Monitor

Every Hour with AWR Analyze Identify problems Proposes solutions

ADDM Page

ADDM Details

ADDM Advice

ADDM tables

dba_advisor_findings TASK_NAME, TASK_ID (is indexed)

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.

ADDM tables

Actions

Findings

Recommendations

objects

Rational

Add Free lists or move to ASSM

SQL

BBW:

Read & write contention

on a block

Segment Tuningobjects Tables

Advisory Framework tables

Actions

Findings

Recommendations

objects

Rational

Could be just a message w/o command or rational

Like investigate application logic

22 types of which ADDM uses 7:SQLDATABASE OBJECT (Tables, Indexes, ...)TABLESPACEDATABASE BLOCKDATABASE FILEDATABASE LATCHDATABASE ENQ

ADDM does not use this connection

(maybe SQL Tuning Advisor )

Types:

PROBLEM

SYMPTOM

INFORMATION

Types:

Application Analysis

DB Configuration

Host Configuration

SQL Tuning

Segment Tuning

Schema

Doesn’t use type

Miscellaneous


6.ADDM7.Misc

Part I

Part II

Part III

Miscellaneous

Field naming conventions Wait Improvements

– Wait histograms– Waits in v$session and v$sql– Waits broken out for latches and locks– Waits show the blocker– Wait history of last 10 waits

Services Client Id

Fields Names

Names– Event– Statistic– Waitclass

Ids – Event#– Statistic#– Waitclass#

Name Hash– Event_id– Statistic_id– Waitclass_id

Wait Histograms

V$event_histogram 1ms to 1 hour buckets 23 buckets < 1 ms, < 2 ms, < 4 ms, < 8 ms, ..., < 2^22 ms

Waits in v$session and v$sql V$session exposes all the fields from

v$session_wait SEQ# NUMBER 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 STATE VARCHAR2(19)

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

Wait Types Broken Out

800 waits Latches broken out

– Ex) Latch: library cache latch

Enqueues broken out– Ex) Enq: HW - contention

Waits show Blocking Session

V$session. BLOCKING_SESSION -> can build a wait tree:

Waits History of last 10 waits

select sid, event, p1,p2,p3 from v$session_wait_history

SID EVENT P1 P2 P3---------- --------------------------- ---------- ---------- ---------- 36 db file sequential read 1 953 1 36 SQL*Net message from client 1413697536 1 0 36 SQL*Net message from client 1413697536 1 0 36 SQL*Net message to client 1413697536 1 0 36 db file sequential read 1 658 1 36 db file sequential read 1 828 1 36 db file sequential read 1 569 1 36 db file sequential read 1 827 1 36 db file sequential read 1 19199 1 36 db file sequential read 1 29 1

Services

Services is a new way to measure resource usage and statistics. A session is associated with a services when the session connects to the database via the listener.

Client Id

Setting Client ID dbms_session.set_identifier (client_id); Enabling trace for a client ID dbms_monitor.client_id_trace_enable (client_id, TRUE, FALSE); Enabling statistics aggregation by client id dbms_monitor.client_id_stat_enable (client_id);• Script to Extract Client Trace

trcsess

Session Dedicated

Oracle Database Host

Oracle

SessionsS1 S2 S3 S4 S4 S5 S6

Scott John Sue Mike Randy Tim Mary

Session Dedicated trace


Oracle

SessionsS1 S2 S3 S4 S4 S5 S6


SQL_TRACE=TRUE

Session Pooling trace


APP Server

1

Oracle

SessionsS1 S2 S3 S4 S4 S5 S6 S7


S1, sql_trace=true

Session Pooling


APP Server

1

Oracle

SessionsS1 S2 S3 S4 S4 S5 S6 S7


Set client_id = SCOTT

dbms_monitor.client_id_trace_enable (client_id, TRUE, FALSE); waits, binds

------ Reference -------------

System Statistics ViewStats ( V$stat_name) Waits ( V$event_name ) Time Model sql

Raw V$sysstat v$system_event

v$event_histogram

v$system_wait_class

v$sys_time_model V$sql

Now V$sysmetric v$eventmetric

v$waitclassmetric Some goes into sysmetric

ASH

x$kewrtsqlstat

1 Hour

V$sysmetric_history V$sysmetric_summary

v$waitclassmetric_history ASH

7 days

DBA_HIST_*

SYSMETRIC_SUMMARY

SYSSTAT

(cumulative)

SYSMETRIC_HISTORY (alerts)

SYSTEM_EVENT

(cumulative)

WAITCLASSMETRIC_HISTORY (alert)

SYS_TIME_MODEL

(cumulative)

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)

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


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


V$metric

v$metric

BEGIN_TIME

END_TIME

INTSIZE_CSEC

GROUP_ID

ENTITY_ID – session id, wait_class_id, etc

ENTITY_SEQUENCE

METRIC_ID

METRIC_NAME

VALUE

METRIC_UNIT

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

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

INIT.ORA ASH _ash_enable = false; [ A dynamic parameter will turn off

ASH sampling, flushing and the V$ views on ASH ] ADDM _addm_auto_enable = false; [ A dynamic parameter to

turn off automatic ADDM runs after every AWR snapshot ] AWR "_swrf_mmon_flush" = FALSE ; AWR metrics "_swrf_mmon_metrics" = FALSE ; METRICS DB Feature Usage "_swrf_mmon_dbfus" = FALSE ; DB Feature Usage "_swrf_on_disk_enabled" = FALSE ; disable all (on disk,

including manual) AWR operations:

EM Product Layout for Performance

Database Home Page

Database Performance Page

Drilldowns

SQL Session

EM Pages Layout

Home Page

Perf Page

Top Session Wait Detail Top SQL

SQL Detail Session Detail

ADDM

ADDM Details

Buffer Busy Waits Use Case

Home Page

Perf Page



ADDM

ADDM Details

Three Paths

ADDM Path

Home Page

Perf Page



ADDM

ADDM Details

Database Home Page

ADDM Home

Home Page

Perf Page



ADDM

ADDM Details

ADDM Home

ADDM Details

Home Page

Perf Page



ADDM

ADDM Details

ADDM Details

Home Page

Perf Page



ADDM

ADDM Details

Manual Path

Database Home Page

Performance Page

Home Page

Perf Page

Top Session Top SQL Wait Detail


ADDM

ADDM Details

Database Performance Page

Database Performance Page highlight

Wait Drill Down

Home Page

Perf Page



ADDM

ADDM Details

Wait Drill Down

Wait Drill Down highlight

Wait Drill Down – Top SQL

SQL Details

Home Page

Perf Page



ADDM

ADDM Details

SQL Details

Combined

Home Page

Perf Page



ADDM

ADDM Details

Comparison

performance tuning in oracle 10g feel the power ! kyle hailey [email protected]

Documents