oracle 10g performance: chapter 04 new features

#.1Copyright 2006 Kyle Hailey

10g New Performance Features10g New Performance Features

1. Alerts

2. Metrics

3. AWR ( 7 days of history DBA_HIST_ )

4. Time Model

5. Wait Classes

6. Misc

#.2

Alerts SetAlerts Set

Copyright 2006 Kyle Hailey

select select metrics_name alert, metrics_name alert, warning_operator op, warning_operator op, warning_value warn ,warning_value warn , object_name obj object_name obj from from dba_thresholds;dba_thresholds;

ALERT OP WARN OBJALERT OP WARN OBJ----------------------------------- ---- ------ ------------------------------------------ ---- ------ -------Average Users Waiting Counts GT 10 Average Users Waiting Counts GT 10 ConcurrencyConcurrencyBlocked User Session Count GT 0Blocked User Session Count GT 0CPU Time Per User Call GE 8000 db3CPU Time Per User Call GE 8000 db3Logons Per Sec GE 100Logons Per Sec GE 100Session Limit % GT 90Session Limit % GT 90Tablespace Space Usage GE 85Tablespace Space Usage GE 85Tablespace Space Usage GE 90 SYSTEMTablespace Space Usage GE 90 SYSTEM

In 10g, alerts are In 10g, alerts are managed in the managed in the databasedatabase

#.3

Alerts OutstandingAlerts Outstanding


select select

reason, reason,

object_type type,object_type type,

object_name name object_name name

from from

dba_outstanding_alerts;dba_outstanding_alerts;

REASON TYPE NAMEREASON TYPE NAME

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

Tablespace [PERFSTAT] is [97 percent] full TABLESPACE PERFSTATablespace [PERFSTAT] is [97 percent] full TABLESPACE PERFSTA

#.4

Alerts SettingAlerts Setting


DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.SET_THRESHOLD(

METRICS_ID => DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, METRICS_ID => DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,

WARNING_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE, WARNING_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE,

WARNING_VALUE => '8000',WARNING_VALUE => '8000',

CRITICAL_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE, CRITICAL_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE,

CRITICAL_VALUE => '10000', CRITICAL_VALUE => '10000',

OBSERVATION_PERIOD => 1, OBSERVATION_PERIOD => 1,

CONSECUTIVE_OCCURRENCES => 2, CONSECUTIVE_OCCURRENCES => 2,

INSTANCE_NAME => 'SID'INSTANCE_NAME => 'SID',,

OBJECT_TYPE => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, OBJECT_TYPE => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,

OBJECT_NAME => 'SID')OBJECT_NAME => 'SID')

dbms_server_alert.set_threshold(dbms_server_alert.set_threshold(

METRICS_ID => dbms_server_alert.tablespace_pct_full,METRICS_ID => dbms_server_alert.tablespace_pct_full,

WARNING_OPERATOR => dbms_server_alert.operator_ge, WARNING_OPERATOR => dbms_server_alert.operator_ge,

WARNING_VALUE => 90,WARNING_VALUE => 90,

CRITICAL_OPERATOR => dbms_server_alert.operator_ge, CRITICAL_OPERATOR => dbms_server_alert.operator_ge,

CRITICAL_VALUE => 99,CRITICAL_VALUE => 99,

OBSERVATION_PERIOD => 1, OBSERVATION_PERIOD => 1,

CONSECUTIVE_OCCURRENCES => 1, CONSECUTIVE_OCCURRENCES => 1,

INSTANCE_NAME => null, INSTANCE_NAME => null,

OBJECT_TYPE => dbms_server_alert.object_type_tablespace,OBJECT_TYPE => dbms_server_alert.object_type_tablespace,

OBJECT_NAME => 'SYSTEM');OBJECT_NAME => 'SYSTEM');

http://www.psoug.org/reference/dbms_serv_alert.htmlhttp://www.psoug.org/reference/dbms_serv_alert.html


9i9i

v$sysstat

v$system_eventv$sql

v$session

V$session_wait

V$session_event

V$sesstat

v$systat

Waits v$sql

v$session

v$system_eventv$system_event

Metrics

Stats

Waits SQL

Sessions V$sessmetric

V$sysmetric

DBA_HIST_SQLSTAT

V$eventmetric

V$waitclassmetric

10g Metrics10g Metrics


MetricsMetricsWhat inspired Metrics?

Alerting

But … metrics make our lives easierOld statistics were cumulativeOld statistics lacked history

Question:

What is the IO on the system right now?


First Tedious Step First Tedious Step

Not much help …Why? Let see …

Select value from v$sysstat

where name=‘physical reads’;

VALUE

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

1,533,787


This tells you … NothingThis tells you … Nothing

1,533,787

IO’s

time

30 minutes

30 minutes

time

GOOD BAD


Need to do Need to do MATHMATH to find Out to find Out

Take value at time A Take value at time BDelta = (B-A)

orRate = (B-A)/elapsed time


MethodsMethods 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)

Oracle 10 introduces Metrics (and AWR & OEM)

Overkill to run statspack for one or two statistics, Overkill to run statspack for one or two statistics, now 10g gives us metric tablesnow 10g gives us metric tables


Performance Metric DeltasPerformance Metric Deltas Kinds of Metric Tables

1. Metric – current

2. Metric History – last hour

V$EVENTMETRIC

V$FILEMETRIC

V$SESSMETRIC

V$SERVICEMETRIC

V$SYSMETRIC

V$SYSMETRIC_SUMMARY

V$WAITCLASSMETRIC

V$FILEMETRIC_HISTORY

V$SYSMETRIC_HISTORY

V$SERVICEMETRIC_HISTORY

V$WAITCLASSMETRIC_HISTORY

Current DeltasCurrent Deltas History of Values over last hourHistory of Values over last hour


MetricsMetrics Intervals

15 second60 second10 minutes - file IO30 minutes - sql

CalculationsDeltasRates

per second per transaction

Ratios Percentages


Metric TablesMetric Tables

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 DBA_HIST_SQLSTAT


Answer at your fingertipsAnswer at your fingertips

3 IO/sec

IO’s

time

30 minutes

30 minutes

time

GOOD BAD

513 IO/sec

1,533,787X


Now What’s the IO?Now What’s the IO?

VALUE METRIC_UNIT INTSIZE_CSECVALUE METRIC_UNIT INTSIZE_CSEC

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

654.6736 Reads Per Second 5959654.6736 Reads Per Second 5959 134.9835 Reads Per Second 1515134.9835 Reads Per Second 1515

Select VALUE , METRIC_UNIT

from v$sysmetric

where metric_name='Physical Reads Per Sec‘;

Avg IO per sec for the last 15 and 60 secsAvg IO per sec for the last 15 and 60 secs


v$sysmetricv$sysmetric

desc v$sysmetric BEGIN_TIME END_TIME INTSIZE_CSEC – interval value in 1/100sec GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT

Attention: metric_names are different from v$sysstat


v$sysmetric 15 Secsv$sysmetric 15 Secs

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 Secv$sysmetric 60 Sec

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 Secv$sysmetric 60 Sec

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

desc 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

• Only 15 second Delta


Desc v$eventmetricDesc 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


Historical MetricsHistorical Metrics

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?


Metric History TablesMetric 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


Metric History TablesMetric History Tables

Last hour of statistics at your fingertips :

Statistics V$SYSMETRIC_HISTORY

60 seconds for an hour 3 minutes of 15 second

File IO V$FILEMETRIC_HISTORY

10 minutes for an hour

Waits V$WAITCLASSMETRIC_HISTORY

60 seconds for an hour

60 minutes of 1 minute deltas

3 minutes of 15 second deltas


What was IO 30 minutes ago?What was IO 30 minutes ago?

Select

to_char(end_time,'DD-MON-YY HH24:MI'),

VALUE ,

METRIC_UNIT

from

v$sysmetric_history

where

METRIC_NAME = 'Physical Reads Per Sec' and

END_TIME < ( sysdate - (30/(24*60))) and

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

order by end_time;TO_CHAR(END_TIM VALUE METRIC_UNITTO_CHAR(END_TIM VALUE METRIC_UNIT

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

08-JAN-08 11:38 118.65 Reads Per Second08-JAN-08 11:38 118.65 Reads Per Second08-JAN-08 11:39 76.6166667 Reads Per Second08-JAN-08 11:39 76.6166667 Reads Per Second08-JAN-08 11:40 3.44770153 Reads Per Second08-JAN-08 11:40 3.44770153 Reads Per Second08-JAN-08 11:41 28.7 Reads Per Second08-JAN-08 11:41 28.7 Reads Per Second08-JAN-08 11:42 19.6166667 Reads Per Second08-JAN-08 11:42 19.6166667 Reads Per Second


V$FILEMETRIC_HISTORYV$FILEMETRIC_HISTORY

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…

Physical Reads Physical Reads

Broken down byBroken down by

filefile


Metric AlertsMetric Alerts

If Alerts fire on Metrics, then the deltas are kept in historical table for 7 days by default

Statistics DBA_HIST_SYSMETRIC_HISTORY DBA_HIST_SESSMETRIC_HISTORY

Waits WAITCLASSMETRIC_HISTORY

File IO DBA_HIST_FILEMETRIC_HISTORY

Not to be confused Not to be confused with the other metric with the other metric tables, these only tables, these only have values when have values when alerts firealerts fire


Metrics Family of TablesMetrics Family of TablesStats Waits Files

Raw V$sysstat v$system_event

v$event_histogram

v$system_wait_class

v$fileio

Now V$sysmetric

V$SYSMETRIC

V$SYSMETRIC_SUMMARY

V$SESSMETRIC

Session : v$sessmetric

v$eventmetric

v$waitclassmetric

v$filemetric

1 Hour

V$sysmetric_history V$sysmetric_summary

v$waitclassmetric_history v$filemetric_history

7 days

Alerts

only

DBA_HIST_SYSMETRIC_HISTORY DBA_HIST_SESSMETRIC_HISTORY

DBA_HIST_

SYSMETRIC_SUMMARY

DBA_HIST_WAITCLASSMETRIC_HISTORY

DBA_HIST_SYSTEM_EVENT

DBA_HIST_FILEMETRIC_HISTORY

DBA_HIST_SQLSTAT


AWRAWRAutomatic Workload Repository Statspack on Steroids More efficient than Statspack

More in memoryLess latching

Keeps stats every hour Stores by default the last 7 days


SnapshotingSnapshoting

Done automatically out of the box, but can be run by hand:

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();END;

/

dbms_workload_repository.drop_snapshot_rangedbms_workload_repository.drop_snapshot_range


DBA_HISTDBA_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_SQLBIND_META - DBA_HIST_SQLBIND_META - WRH$_SQL_BIND_METADATAWRH$_SQL_BIND_METADATA-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 – object name-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


Retention and IntervalRetention and IntervalDefaults: Hourly snapshots 7 days saved

SELECT retention, snap_interval FROM wrm$_wr_control;SELECT retention, snap_interval FROM wrm$_wr_control;

RETENTION SNAP_INTERVALRETENTION SNAP_INTERVAL

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

+00007 00:00:00.0 +00000 01:00:00.0+00007 00:00:00.0 +00000 01:00:00.0

exec dbms_workload_repository.modify_snapshot_settings(14*24*60,30);exec dbms_workload_repository.modify_snapshot_settings(14*24*60,30);

RETENTION SNAP_INTERVALRETENTION SNAP_INTERVAL

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

+00014 00:00:00.0 +00000 00:30:00.0+00014 00:00:00.0 +00000 00:30:00.0

MinutesMinutes


AWR RPTAWR RPT

Creates Report File Similar to STATSPACK report More efficient and more data HTML or TEXT

@?/rdbms/admin/awrrpt.sql

SELECT * FROM TABLE(SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(dbms_workload_repository.awr_report_text(

(select dbid from v$database),(select dbid from v$database),1,1,124, -- begin id124, -- begin id

125 -- end id ))125 -- end id ))


AWR ReportAWR Report

1) General info

2) Load Profile

3) Wait Events

4) OS Stats

5) Service Stats

6) Top SQL

7) SQL Text

8) Instance Statistic

9) I/O Stat

10) Buffer Pool Stats

1) General info

2) Load Profile

3) Wait Events

4) OS Stats

5) Service Stats

6) Top SQL

7) SQL Text

8) Instance Statistic

9) I/O Stat


11) Advisories

1) Buffer pool

2) PGA

3) Shared Pool

4) SGA

5) Streams

6) Java

11) Advisories

1) Buffer pool

2) PGA

3) Shared Pool

4) SGA

5) Streams

6) Java


13) Wait Stats

1) Buffer Busy

2) Enqueues

14) Undo Stats

15) Latch Stats

16) Segment Stats

17) Dictionary Stats

18) Library Cache

19) Memory SGA/PGA

20) Streams

21) Init.ora


13) Wait Stats

1) Buffer Busy

2) Enqueues

14) Undo Stats

15) Latch Stats

16) Segment Stats

17) Dictionary Stats

18) Library Cache

19) Memory SGA/PGA

20) Streams

21) Init.ora


Awr 1-13Awr 1-13


AWR 14-26AWR 14-26


AWR DiffAWR Diff

SELECT * FROM TABLE(SELECT * FROM TABLE( dbms_workload_repository.awr_diff_report_text(dbms_workload_repository.awr_diff_report_text( (select dbid from v$database),(select dbid from v$database), 1,1, 120,120, 121,121, (select dbid from v$database),(select dbid from v$database), 1 ,1 , 122,122, 123)123)););

Also : AWR_DIFF_REPORT_HTML


AWR Diff Report in OEMAWR Diff Report in OEM

NOTE: Package

AWR_DIFF_REPORT_HTML

Is different from OEM

Its just a tabular output


Also, ADDM report by HandAlso, ADDM report by Hand

select dbms_advisor.get_task_report(task_name)

from dba_advisor_taskswhere task_id = ( select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where t.task_id = l.task_id and t.advisor_name = 'ADDM' and l.status = 'COMPLETED');

Set long 100000


Important AWR TablesImportant AWR Tables

DBA_HIST_SQLSTAT SQL deltas

DBA_HIST_SEG_STAT Segment deltas

DBA_HIST_SYSMETRIC_SUMMARY Stats, max, min, avg Trending

DBA_HIST_ACTIVE_SESS_HISTORY ASHASH

DBA_HIST_SYSTEM_EVENT DBA_HIST_SYSTEM_EVENT WaitsWaits

dba_hist_sqltext – get sql text from AWR dba_hist_sqltext – get sql text from AWR


DBA_HIST_SNAPSHOTDBA_HIST_SNAPSHOT

SQL> desc SQL> desc DBA_HIST_SNAPSHOTDBA_HIST_SNAPSHOTName TypeName Type

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

SNAP_ID NUMBERSNAP_ID NUMBER

DBID NUMBERDBID NUMBER

INSTANCE_NUMBER NUMBERINSTANCE_NUMBER NUMBER

STARTUP_TIME TIMESTAMP(3)STARTUP_TIME TIMESTAMP(3)

BEGIN_INTERVAL_TIME TIMESTAMP(3)BEGIN_INTERVAL_TIME TIMESTAMP(3)

END_INTERVAL_TIME TIMESTAMP(3)END_INTERVAL_TIME TIMESTAMP(3)

FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)

SNAP_LEVEL NUMBERSNAP_LEVEL NUMBER

ERROR_COUNT NUMBERERROR_COUNT NUMBER


DBA_HIST_SQLSTATDBA_HIST_SQLSTAT

SNAP_ID

DBIDINSTANCE_NUMBERSQL_IDPLAN_HASH_VALUEOPTIMIZER_COSTOPTIMIZER_MODEOPTIMIZER_ENV_HASH_VALUESHARABLE_MEMLOADED_VERSIONSVERSION_COUNTMODULEACTIONSQL_PROFILEFORCE_MATCHING_SIGNATUREPARSING_SCHEMA_IDPARSING_SCHEMA_NAMEBIND_DATA

FETCHES_DELTAFETCHES_DELTAEND_OF_FETCH_COUNT_DELTAEND_OF_FETCH_COUNT_DELTASORTS_DELTASORTS_DELTAEXECUTIONS_DELTAEXECUTIONS_DELTAPX_SERVERS_EXECS_DELTAPX_SERVERS_EXECS_DELTALOADS_DELTALOADS_DELTAINVALIDATIONS_DELTAINVALIDATIONS_DELTAPARSE_CALLS_DELTAPARSE_CALLS_DELTADISK_READS_DELTADISK_READS_DELTABUFFER_GETS_DELTABUFFER_GETS_DELTAROWS_PROCESSED_DELTAROWS_PROCESSED_DELTACPU_TIME_DELTACPU_TIME_DELTAELAPSED_TIME_DELTAELAPSED_TIME_DELTAIOWAIT_DELTAIOWAIT_DELTACLWAIT_DELTACLWAIT_DELTAAPWAIT_DELTAAPWAIT_DELTACCWAIT_DELTACCWAIT_DELTADIRECT_WRITES_DELTADIRECT_WRITES_DELTAPLSEXEC_TIME_DELTAPLSEXEC_TIME_DELTAJAVEXEC_TIME_DELTAJAVEXEC_TIME_DELTA


DBA_HIST_SEG_STATDBA_HIST_SEG_STAT

SQL> desc DBA_HIST_SEG_STAT SQL> desc DBA_HIST_SEG_STAT

Name Name

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

SNAP_ID SNAP_ID

DBID DBID

INSTANCE_NUMBER INSTANCE_NUMBER

TS# TS#

OBJ# OBJ#

DATAOBJ# DATAOBJ#

LOGICAL_READS_TOTAL LOGICAL_READS_TOTAL

LOGICAL_READS_DELTA LOGICAL_READS_DELTA

BUFFER_BUSY_WAITS_TOTAL BUFFER_BUSY_WAITS_TOTAL

BUFFER_BUSY_WAITS_DELTA BUFFER_BUSY_WAITS_DELTA

DB_BLOCK_CHANGES_TOTAL DB_BLOCK_CHANGES_TOTAL

DB_BLOCK_CHANGES_DELTA DB_BLOCK_CHANGES_DELTA

PHYSICAL_READS_TOTAL PHYSICAL_READS_TOTAL

PHYSICAL_READS_DELTA PHYSICAL_READS_DELTA

PHYSICAL_WRITES_TOTAL PHYSICAL_WRITES_TOTAL

PHYSICAL_WRITES_DELTA PHYSICAL_WRITES_DELTA

PHYSICAL_READS_DIRECT_TOTALPHYSICAL_READS_DIRECT_TOTAL

PHYSICAL_READS_DIRECT_DELTA PHYSICAL_READS_DIRECT_DELTA

PHYSICAL_WRITES_DIRECT_TOTALPHYSICAL_WRITES_DIRECT_TOTAL

PHYSICAL_WRITES_DIRECT_DELTA PHYSICAL_WRITES_DIRECT_DELTA

ITL_WAITS_TOTAL ITL_WAITS_TOTAL

ITL_WAITS_DELTA ITL_WAITS_DELTA

ROW_LOCK_WAITS_TOTAL ROW_LOCK_WAITS_TOTAL

ROW_LOCK_WAITS_DELTA ROW_LOCK_WAITS_DELTA

SPACE_USED_TOTAL SPACE_USED_TOTAL

SPACE_USED_DELTA SPACE_USED_DELTA

SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_TOTAL

SPACE_ALLOCATED_DELTA SPACE_ALLOCATED_DELTA

TABLE_SCANS_TOTAL TABLE_SCANS_TOTAL

TABLE_SCANS_DELTA TABLE_SCANS_DELTA

Gets reset every DB bounceGets reset every DB bounce


DBA_HIST_SYSTEM_EVENTDBA_HIST_SYSTEM_EVENT

Cumulative Values – can use LAG function

SQL> desc DBA_HIST_SYSTEM_EVENTSQL> desc DBA_HIST_SYSTEM_EVENT Name TypeName Type -------------------------------------------------------------------- SNAP_ID NUMBERSNAP_ID NUMBER DBID NUMBERDBID NUMBER INSTANCE_NUMBER NUMBERINSTANCE_NUMBER NUMBER EVENT_ID NUMBEREVENT_ID NUMBER EVENT_NAME VARCHAR2(64)EVENT_NAME VARCHAR2(64) WAIT_CLASS_ID NUMBERWAIT_CLASS_ID NUMBER WAIT_CLASS VARCHAR2(64)WAIT_CLASS VARCHAR2(64) TOTAL_WAITS NUMBERTOTAL_WAITS NUMBER TOTAL_TIMEOUTS NUMBERTOTAL_TIMEOUTS NUMBER TIME_WAITED_MICRO NUMBERTIME_WAITED_MICRO NUMBER


Average Wait TimesAverage Wait Times

select

btime,

(time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0) avg_ms

from (

select

to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI') btime,

total_waits count_end,

time_waited_micro/1000 time_ms_end,

LagLag ( (e.time_waited_micro/1000)

OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,

LagLag ( (e.total_waits)

OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg

from

DBA_HIST_SYSTEM_EVENT e,

DBA_HIST_SNAPSHOT s

where

s.snap_id=e.snap_id and e.event_name= '&1'

order by begin_interval_time

)

order by btime;

BTIME AVG_MSBTIME AVG_MS-------------------- -------------------------------- ------------08-JAN-08 01:00 1.01708-JAN-08 01:00 1.017

08-JAN-08 02:00 .72008-JAN-08 02:00 .720

08-JAN-08 03:00 .62108-JAN-08 03:00 .621

08-JAN-08 04:00 1.74708-JAN-08 04:00 1.747

08-JAN-08 05:00 1.04608-JAN-08 05:00 1.046

08-JAN-08 06:00 1.44408-JAN-08 06:00 1.444


ASH RPTASH RPT

@?/rdbms/admin/ashrpt Detailed report on top SQL, Sessions, Objects etc Run over SQL*Net with

Also ASH_REPORT_HTML

select output select output

from table(dbms_workload_repository.ash_report_text( from table(dbms_workload_repository.ash_report_text( (select dbid from v$database),(select dbid from v$database),

1,1, sysdate – 2/24, sysdate – 2/24, sysdate – 1/24,sysdate – 1/24, 0)) ;0)) ;


ASH RPTASH RPT

1) General info

2) Top User Events ***

3) Top Background Events

4) Top Event P1/P2/P3 Values

5) Top Service/Module

6) Top Client IDs

7) Top SQL Command Types

8) Top SQL Statements ***

1) General info

2) Top User Events ***

3) Top Background Events

4) Top Event P1/P2/P3 Values

5) Top Service/Module

6) Top Client IDs

7) Top SQL Command Types

8) Top SQL Statements ***

9) Top SQL using literals

10) Top Sessions ***

11) Top Blocking Sessions

12) Top Sessions running PQs

13) Top DB Objects

14) Top DB Files

15) Top Latches

16) Activity Over Time ***

9) Top SQL using literals

10) Top Sessions ***

11) Top Blocking Sessions

12) Top Sessions running PQs

13) Top DB Objects

14) Top DB Files

15) Top Latches

16) Activity Over Time ***


ASH RPT HTMLASH RPT HTML


TimeTime ModelModelNew concept• DB Time

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

Metrics

Time Model

Stats

Wait SQL

Sessions


Time Model areasTime Model areas Total Time CPU

Updated every 5 seconds Elapsed Time SQL execution Plsql execution (sql execute subtracted out) Java execution Connection time

Probably the most important as it points out problems with too many logons/sec


Time Model ComponentsTime 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 TimeTotal Time

Total Database Time

DB CPU Time

~ DB Wait Time

DB CPU Time

DB Wait

Time

Parse Elapsed

Time


Hard Parse Bind MismatchHard Parse Bind Mismatch

Parse

time

elapsed

hard parse elapsed time

hard parse

(sharing criteria) elapsed

time

hard parse (bind

mismatch)

elapsed time


Time Model TablesTime Model Tables

Current cumulative values V$SYS_TIME_MODEL V$SESS_TIME_MODEL

AWR Snapshots of cumulative values DBA_HIST_SYS_TIME_MODEL

Tables show cumulative values. To find deltas, then need Tables show cumulative values. To find deltas, then need to run AWR report (no time model metric tables to run AWR report (no time model metric tables ) )


Wait Classes 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 Class TablesWait Class Tables

v$system_wait_classcumulative

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


MiscellaneousMiscellaneous

V$sqlstats Field naming conventions Wait Improvements

Wait histogramsWaits in v$session and v$sqlWaits show the blockerWait history of last 10 waits

Services Client Id


v$sqlstatsv$sqlstats

More efficient than v$sql or v$sqlarea Uses less CPU Faster Skips many of the latch gets that v$sql does Uses Mutexes instead of some latches


Event and Stat Fields NamesEvent and Stat Fields Names Names

Event Statistic Waitclass

Ids Event# Statistic# Waitclass#

Name Hash Event_id Statistic_id Waitclass_id

SQL

SQL_ID new hash

HASH_VALUE still there


Wait HistogramsWait 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$sessionWaits in v$session

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$session exposes all the fields from v$session_wait


Waits in V$SQLWaits in V$SQL

SQL_FULLTEXT SQL_ID FETCHES END_OF_FETCH_COUNT DIRECT_WRITES CONCURRENCY_WAIT_TIME APPLICATION_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME CPU_TIME ELAPSED_TIME


Waits show Blocking SessionWaits show Blocking Session

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


ServicesServices

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.

DBMS_SERVICE.CREATE_SERVICE


Setting up ServicesSetting up Services

tnsA =tnsA = (DESCRIPTION =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(ADDRESS = (PROTOCOL = TCP) (HOST = kylehpd)(PORT = 1521)) )(HOST = kylehpd)(PORT = 1521)) ) (CONNECT_DATA =(CONNECT_DATA = (SERVER = DEDICATED)(SERVER = DEDICATED) (SERVICE_NAME = A)(SERVICE_NAME = A) ) )) )

dbms_service.CREATE_SERVICE('ora10a',‘A');dbms_service.CREATE_SERVICE('ora10a',‘A');dbms_service.START_SERVICE ('ora10a');dbms_service.START_SERVICE ('ora10a');-- dbms_service.STOP_SERVICE ('ora10a');-- dbms_service.STOP_SERVICE ('ora10a');-- dbms_service.DELETE_SERVICE('ora10a');-- dbms_service.DELETE_SERVICE('ora10a');

Service "A" has 1 instance(s).Service "A" has 1 instance(s). Instance "v10g", status READY, has 1 handler(s) for this service...Instance "v10g", status READY, has 1 handler(s) for this service... Handler(s):Handler(s): "DEDICATED" established:0 refused:0 state:ready"DEDICATED" established:0 refused:0 state:ready

tnsnames.oratnsnames.ora

lsnrctl serviceslsnrctl services

Sqlplus un/pw@tnsASqlplus un/pw@tnsA

Name Name tracked tracked in OEMin OEM

jdbc:oracle:thin:@//server:1521/SERVICE_NAMEjdbc:oracle:thin:@//server:1521/SERVICE_NAME


Services in OEMServices in OEM

Enable Trace Enable Trace

By ServiceBy Service

View doesn’t workView doesn’t work


Client IdClient Id

Trcsess output=<name> client_id=<name> *

Setting Client ID

Enabling trace for a client ID

Enabling statistics aggregation by client id

Script to Extract Client Trace

dbms_session.set_identifier(client_id)

dbms_monitor.client_id_trace_enable (client_id, TRUE, FALSE)

dbms_monitor.client_id_stat_enable(client_id)


Session DedicatedSession Dedicated

Oracle Database Host

Oracle

SessionsS1 S2 S3 S4 S4 S5 S6

Scott John Sue Mike Randy Tim Mary


Session Dedicated traceSession Dedicated trace


Oracle

SessionsS1 S2 S3 S4 S4 S5 S6


SQL_TRACE=TRUE


Session Pooling traceSession Pooling trace


APP Server

1

Oracle

SessionsS1 S2 S3 S4 S4 S5 S6 S7


S1, sql_trace=true


Session PoolingSession 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


DB_FILE_MULTI_BLOCK_READ_COUNT DB_FILE_MULTI_BLOCK_READ_COUNT Auto tuned in 10gR2 Recommended to leave unset in init.ora, ex, when unset on my PC

db_file_multiblock_read_count 55 _db_file_exec_read_count 55 (IO reads) _db_file_optimizer_read_count 8 (optimization)

When running Dbms_stats.gather_system_stats(gathering_mode => ‘interval’

interval => 30 )

db_file_multiblock_read_count is ignored

Alter system reset db_file_multiblock_read_count scope=spfile sid=‘*’;

Troubleshooting Oracle PerformanceTroubleshooting Oracle Performance

Christian Antognini


UNDO Retention Auto TunedUNDO Retention Auto Tuned

Oracle10G records the times of the longest-running queries and the amount of undo generated to automatically tune the UNDO_RETENTION parameter.

If Autoextend on then undo retention set slightly longer than the longest running query

If autoextend off then depends on free space available Bug 5387030 – undo retention miscalculated and UNDO can

grow, fixed 10.2.0.4 10.2.0.3 or below can set alter system set "_smu_debug_mode" = 33554432

#.75

SQL Trace ImprovementSQL Trace Improvement

dbms_monitor


dbms_monitor.session_trace_enable(session_id => 127, serial_num => 29, waits => TRUE, binds => FALSE)

10gR2 v$session columns sql_tracesql_trace_waitssql_trace_binds

are set when the proceduresession_trace_enable is used and at least one SQL statement has been executed by the sessionvtraced

New 10gNew 10g

Instead of

alter session set events ‘10046 trace name context forever, level 12';

#.76

Q1Q1


An ASH report can be run on data covering what time An ASH report can be run on data covering what time periods (by default)periods (by default)

a. any 60 seconds in the past weeka. any 60 seconds in the past week

b. any hour period in the past dayb. any hour period in the past day

c. any day in the past weekc. any day in the past week

d. any 60 second period in the past hourd. any 60 second period in the past hour

answersanswers

all the aboveall the above

#.77

Q2Q2


AWR ( automatic workload repository ) reports on data AWR ( automatic workload repository ) reports on data covering covering

what time periods (by default)what time periods (by default)

a. any 60 seconds in the past weeka. any 60 seconds in the past week

b. any hour period in the past dayb. any hour period in the past day

c. any day in the past weekc. any day in the past week

d. any 60 second period in the past hourd. any 60 second period in the past hour

answersanswers

b and c - AWR only takes data every hour for a week b and c - AWR only takes data every hour for a week by defaultby default

#.78

Q3Q3


Connecting to Oracle is a CPU intensive operation. A high Connecting to Oracle is a CPU intensive operation. A high logon ratelogon rate

may indicate an application design issue that unnecessarily may indicate an application design issue that unnecessarily burns CPU.burns CPU.

What performance table(s) indicates the time spent What performance table(s) indicates the time spent connecting to the database ?connecting to the database ?

a. v$active_session_historya. v$active_session_history

b. v$sys_time_modelb. v$sys_time_model

c. v$waitclassmetricc. v$waitclassmetric

d. v$sysstatd. v$sysstat

b - onlyb - only

othersothers

a,c,d - don't report a,c,d - don't report connection time or connect connection time or connect CPU usageCPU usage

#.79

Q4Q4


What is the least resource consuming view for getting the What is the least resource consuming view for getting the numbernumber

of executions of an sql statementof executions of an sql statement

a. v$sqllitea. v$sqllite

b. v$sqlstatsb. v$sqlstats

c. v$sqlc. v$sql

d. v$sqlaread. v$sqlarea

b - new in 10g, less latch usage than the other b - new in 10g, less latch usage than the other tablestables

othersothers

a doesn't exita doesn't exit

c reading uses many of the latches as users c reading uses many of the latches as users executing sqlexecuting sql

d the worst, like v$sql, but also does an d the worst, like v$sql, but also does an expensive group byexpensive group by

#.80

Q5Q5


How can you immediately find the top CPU consuming How can you immediately find the top CPU consuming session in the past 15 secondssession in the past 15 seconds

a. v$sessmetrica. v$sessmetric

b. v$active_session_historyb. v$active_session_history

c. v$sesstatc. v$sesstat

d. v$top_sessionsd. v$top_sessions

answeranswer

a, b a, b

others:others:

c - only has cumulative data, need to take c - only has cumulative data, need to take deltas of values to see what is happeningdeltas of values to see what is happening

d - doesn't existd - doesn't exist

#.81

Q6Q6


What script runs 16 different queries against What script runs 16 different queries against v$active_session_history to v$active_session_history to

produce a detailed report over any time interval in the AWR produce a detailed report over any time interval in the AWR repositoryrepository

a. ?/rdbms/admin/spreport.sqla. ?/rdbms/admin/spreport.sql

b. ?/rdbms/admin/awrrpt.sqlb. ?/rdbms/admin/awrrpt.sql

c. ?/rdbms/admin/ashrpt.sqlc. ?/rdbms/admin/ashrpt.sql

d. ?/rdbms/admin/ashreport.sqld. ?/rdbms/admin/ashreport.sql

answeranswer

cc

otherother

a and b don't use ASHa and b don't use ASH

d doesn't exitd doesn't exit

#.82

Q7Q7


In 10g, it is recommended to set In 10g, it is recommended to set db_file_multiblock_read_count todb_file_multiblock_read_count to

a. unseta. unset

b. 8b. 8

c. 16c. 16

d. 32d. 32

e. 128e. 128

answeranswer

a. Oracle will set it automatically to the optimal a. Oracle will set it automatically to the optimal valuevalue

(this won't affect SQL optimization as Oracle (this won't affect SQL optimization as Oracle will calculatewill calculate

sql optimization with sql optimization with _db_file_optimizer_read_count )_db_file_optimizer_read_count )

#.83

Q8Q8


Oracle can track different applications using the "services' Oracle can track different applications using the "services' identifier.identifier.

After the service identifier gets created and started, how After the service identifier gets created and started, how does thedoes the

services identifier get set for a connection.services identifier get set for a connection.

a. call to dbms_servicesa. call to dbms_services

b. via the tnsnames.ora connection b. via the tnsnames.ora connection

c. in the init.orac. in the init.ora

d. via a middle tier commandd. via a middle tier commandansweranswer

bb

#.84

Q9Q9


The identifier "client_id" allows a DBA to monitor what The identifier "client_id" allows a DBA to monitor what special situation:special situation:

a. track different applications resource usage on a databasea. track different applications resource usage on a database

b. track application users in session poolsb. track application users in session pools

c. track dedicated sessions c. track dedicated sessions

d. is the same as the database "username"d. is the same as the database "username"answeranswer

b - set up specifically to track application users b - set up specifically to track application users that connect withoutthat connect without

a dedicated connection vi a session pool a dedicated connection vi a session pool

oracle 10g performance: chapter 04 new features

Documents