Performance Tuning


Determining Session-Level CPU Usage:

SQL> SELECT sid, s.value "Total CPU Used by this Session"
FROM V$SESSTAT S
WHERE S..statistic# = 12
ORDER BY S,value DESC;
SID Total CPU Used by this Session
----- ------------------------------
496 27623
542 21325
111 20814
731 17089
424 15228

What Is the CPU Time Used For?
It would be a mistake to treat all CPU time as equal. CPU time is generally understood as the
processor time taken to perform various tasks, such as the following:
• Loading SQL statements into the library cache
• Searching the shared pool for parsed versions of SQL statements
• Parsing the SQL statements
• Querying the data dictionary
• Reading data from the buffer cache
• Traversing index trees to fetch index keys

The total CPU time used by an instance (or a session) can be viewed as the sum of the following
components:
total CPU time = parsing CPU usage + recursive CPU usage + other CPU usage

Decomposition of Total CPU Usage:

SQL> SELECT name,value FROM V$SYSSTAT
WHERE NAME IN ('CPU used by this session',
'recursive cpu usage',
'parse time cpu');

NAME VALUE
-------------------------------------
recursive cpu usage 4713085
CPU used by this session 98196187
parse time cpu 132947
3 rows selected.

Parse Time CPU Usage:
The first query tells you that the total CPU usage in your instance is 49159124:

SQL> SELECT name, value FROM V$SYSSTAT
2* WHERE name LIKE '%CPU%';
NAME VALUE
--------------------------------------------
CPU used when call started 13220745
CPU used by this session 49159124
2 rows selected.
SQL>
The next query shows that the parse time CPU usage is 96431, which is an insignificant proportion
of total CPU usage in your database:

SQL> SELECT name, value FROM V$SYSSTAT
WHERE name LIKE '%parse%';
NAME VALUE
-------------------------------------
parse time cpu 96431
parse time elapsed 295451
parse count (total) 3147900
parse count (hard) 29139
4 rows selected.
SQL>

Determining Parse Time CPU Usage

SQL> SELECT a.value " Tot_CPU_Used_This_Session",
b.value "Total_Parse_Count",
 c.value "Hard_Parse_Count",
d.value "Parse_Time_CPU"
FROM v$sysstat a,
v$sysstat b,
v$sysstat c,
v$sysstat d
WHERE a.name = 'CPU used by this session'
AND b.name = 'parse count (total)'
AND c.name = 'parse count (hard)'
AND d.name = 'parse time cpu';

Tot_CPU_Used Total_Parse_Count Hard_Parse_Count Parse_Time_CPU
This_Session
------------------------------ -----------------------------------
2240 53286 281 1486

Recursive CPU Usage

Recursive CPU usage is mostly for data dictionary lookups and for executing PL/SQL programs.
Thus, if your application uses a high number of packages and procedures, you’ll see a significant
amount of recursive CPU usage.
In the following example, there’s no need for alarm, because the percentage of recursive CPU
usage is only about 5 percent of total CPU usage:
SQL> SELECT name, value FROM V$SYSSTAT
WHERE name IN ('CPU used by this session',
'recursive cpu usage');

NAME VALUE
------------------------------------------------ -------
recursive cpu usage 4286925
CPU used by this session 84219625
2 rows selected.
SQL>
  
$ sar -d 10 5
HP-UX finance1 B.11.00 A 9000/800 07/03/05
17:27:13 device %busy avque r+w/s blks/s avwait avserv
17:27:23 c2t6d0 100 61.40 37 245 4.71 10.43
c5t6d0 20.38 0.50 28 208 4.92 9.54
c2t6d0 100 61.40 38 273 4.55 9.49
c5t6d0 18.28 0.50 27 233 4.46 7.93
c0t1d0 0.10 0.50 4 33 4.99 0.81

Determining I/O Distribution in the Database

SQL> SELECT d.name,
 f.phyrds reads,
f.phywrts wrts,
(f.readtim / decode(f.phyrds,0,-1,f.phyrds)) readtime,
 (f.writetim / decode(f.phywrts,0,-1,phywrts)) writetime
FROM
v$datafile d,
v$filestat f
WHERE
d.file# = f.file#
ORDER BY
d.name;
NAME READS WRTS READTIME WRITETIME
----------------------------- ----- ---- ---------- ----------
/pa01/oradata/pa/lol_i_17.dbf 23 9 .608695652 .222222222
/pa01/oradata/pa/lol_i_18.dbf 18 7 .277777778 0

The V$SYSMETRIC view displays the system metric values for the
most current time interval. The following query using the V$SYSMETRIC view reveals a database
instance where waits are taking more time than the instance CPU usage time:

SQL> SELECT METRIC_NAME, VALUE
FROM V$SYSMETRIC
WHERE METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio') AND
INTSIZE_CSEC =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);

METRIC_NAME VALUE
------------------------------------------------
Database Wait Time Ratio 72
Database CPU Time Ratio 28

Determining Total Waits and Percentage Waits by Wait Class

SQL> SELECT WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOT_WAITS / SUM_WAITS),2) PCT_TOTWAITS,
ROUND((TIME_WAITED / 100),2) TOT_TIME_WAITED,
round(100 * (TOT_TIME_WAITED / SUM_TIME),2) PCT_TIME
FROM
(select WAIT_CLASS,
TOT_WAITS,
TOT_TIME_WAITED
FROM V$SYSTEM_WAIT_CLASS
WHERE WAIT_CLASS != 'Idle'),
(select sum(TOT_WAITS) SUM_WAITS,
sum(TOT_TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle')
ORDER BY PCT_TIME DESC;

WAIT_CLASS TOTAL_WAITS PCT_TOT_WAITS TOT_TIME_WAITED PCT_TIME
------------- ----------- ------------- --------------- --------
User I/O 6649535191 45.07 46305770.5 84.42
Other 394490128 2.67 5375324.17 9.8
Concurrency 78768788 .53 1626254.9 2.96
Network 7546925506 51.15 547128.66 1
Application 2012092 .01 449945.5 .82
Commit 15526036 .11 351043.3 .64
Configuration 12898465 .09 116029.85 .21
System I/O 53005529 .36 78783.64 .14
Administrative 25 0 7.6 0
Scheduler 1925 0 .15 0
10 rows selected.
SQL>

Using the V$SYSTEM_EVENT View to View Wait Events

SQL> SELECT event, time_waited, average_wait
FROM V$SYSTEM_EVENT
GROUP BY event, time_waited, average_wait
ORDER BY time_waited DESC;

EVENT TIME_WAITED AVERAGE_WAIT
------------------------------------------------------------------
rdbms ipc message 24483121 216.71465
SQL*Net message from client 18622096 106.19049
PX Idle Wait 12485418 205.01844
pmon timer 3120909 306.93440
smon timer 3093214 29459.18100
PL/SQL lock timer 3024203 1536.68852
db file sequential read 831831 .25480
db file scattered read 107253 .90554
free buffer waits 52955 43.08787
log file parallel write 19958 2.02639
latch free 5884 1.47505
...
58 rows selected

Instance-Wide Waits Sorted by Total Wait Time

SQL> SELECT event, total_waits,time_waited
FROM V$SYSTEM_EVENT
WHERE event NOT IN
 ('pmon timer','smon timer','rdbms ipc reply','parallel deque
wait’,'virtual circuit','%SQL*Net%','client message','NULL event')
ORDER BY time_waited DESC;

EVENT TOTAL_WAITS TIME_WAITED
------------------------------------------------------------------
db file sequential read 35051309 15965640
latch free 1373973 1913357
db file scattered read 2958367 1840810
enqueue 2837 370871
buffer busy waits 444743 252664
log file parallel write 146221 123435

SQL> SELECT sid, blocking_session, username,
event, seconds_in_wait siw
FROM V$SESSION
WHERE blocking_session_status = 'VALID';

SID BLOCKING_SESS USERNAME EVENT SIW
---- ------------- -------- ----------------------------- -----
1218 1527 UCR_USER enq: TX - row lock contention 23
1400 1400 APPOWNER latch free 0

The V$SESSION_WAIT_HISTORY view holds information about the last ten wait events for each
active session. The other wait-related views, such as the V$SESSION and the V$SESSION_WAIT,
show you only the wait information for the most recent wait. This may be a short wait, thus escaping
your scrutiny. Here’s a sample query using the V$SESSION_WAIT_HISTORY view:

SQL> SELECT seq#, event, wait_time, p1, p2, p3
FROM V$SESSION_WAIT_HISTORY
WHERE sid = 988
ORDER BY seq#;

SEQ# EVENT WAIT_TIME P1 P2 P3
---- ----------------------- --------- ----- ----- ------___
1 db file sequential read 0 52 21944
2 db file sequential read 0 50 19262
3 latch: shared pool 0 1.3835E+19 198 0
4 db file sequential read 0 205 21605
5 db file sequential read 4 52 13924
6 db file sequential read 1 49 29222
7 db file sequential read 2 52 14591
8 db file sequential read 2 52 12723
9 db file sequential read 0 205 11883
10 db file sequential read 0 205 21604
10 rows selected.
SQL>

Objects with the Highest Waits

The following query identifies the objects causing the most waits and the type of events the objects
waited for during the last 15 minutes:

SQL> SELECT o.object_name, o.object_type, a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
dba_objects o
WHERE a.sample_time between sysdate - 30/2880 and sysdate
AND a.current_obj# = o.object_id
GROUP BY o.object_name, o.object_type, a.event
ORDER BY total_wait_time;
OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAIT_TIME
------------------- --------------------------------------------------
UC_ADDRESS TABLE SQL*Net message to client 2
PERS_PHONES TABLE db file sequential read 8836
PAY_FK_I INDEX db file sequential read 9587
UC_STAGING TABLE log file sync 23633
PERSONNEL TABLE db file sequential read 43612

Most Important Wait Events

The following query lists the most important wait events in your database in the last 15 minutes:
SQL> SELECT a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time between
sysdate - 30/2880 and sysdate
GROUP BY a.event
ORDER BY total_wait_time DESC;

EVENT TOTAL_WAIT_TIME
---------------------------------------------------------
wait for SGA component shrink 878774247
smon timer 300006992
PL/SQL lock timer 210117722
SQL*Net message from client 21588571
db file scattered read 1062608
db file sequential read 105271
log file sync 13019
latch free 274
SQL*Net more data to client 35
null event 6
17 rows selected.
SQL>

Users with the Most Waits

The following query lists the users with the highest wait times within the last 15 minutes:
SQL> SELECT s.sid, s.username,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
v$session s
WHERE a.sample_time between sysdate - 30/2880 and sysdate
AND a.session_id=s.sid
GROUP BY s.sid, s.username
ORDER BY total_wait_time DESC;

SID USERNAME TOTAL_WAIT_TIME
---------- ------------------------------ -----------------
1696 SYSOWNER 165104515
885 SYSOWNER 21575902
1087 BLONDI 5019123
1318 UCRSL 569723
1334 REBLOOM 376354
1489 FRAME 395
15 rows selected.

Identifying SQL with the Highest Waits

Using the following query, you can identify the SQL that’s waiting the most in your instance.
The sample time covers the last 15 minutes.

SQL> SELECT a.user_id,d.username,s.sql_text,
SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a,
v$sqlarea s,
dba_users d
WHERE a.sample_time between sysdate - 30/2880 and sysdate
AND a.sql_id = s.sql_id
AND a.user_id = d.user_id
GROUP BY a.user_id,s.sql_text, d.username;

USER_ID USERNAME SQL_TEXT TOTAL_WAIT_TIME
---------- -------- ------------------------------ ----------------
0 SYS BEGIN dbms_stats...; END; 9024233

Wait Classes and the Wait-Related Views

The V$SESSION_WAIT view shows the events and resources that active sessions are waiting for.
Using the V$SESSION_WAIT view, you can also see what types of wait classes your session waits
belong to. Here’s an example:
SQL> SELECT wait_class, event, sid, state, wait_time, seconds_in_wait
FROM v$session_wait
ORDER BY wait_class, event, sid;
WAIT_CLASS EVENT SID STATE WAIT_TIM SEC_IN_WAIT
---------- -------------------- ---------- ----------------------- --
Application enq: TX - 269 WAITING 0 73
row lock contention
Idle Queue Monitor Wait 270 WAITING 0 40
Idle SQL*Net message from client 265 WAITING 0 73
Idle jobq slave wait 259 WAITING 0 8485
Idle pmon timer 280 WAITING 0 73
Idle rdbms ipc message 267 WAITING 0 184770
Idle wakeup time manager 268 WAITING 0 40
Network SQL*Net message to client 272 WAITED SHORT TIME 1
SQL>

The V$SYSTEM_WAIT_CLASS view gives you a breakdown of waits by wait classes, as
shown here:

SQL> SELECT wait_class, time_waited
FROM v$system_wait_class
ORDER BY time_waited DESC;
WAIT_CLASS TIME_WAITED
-----------------------------------------------
Idle 1.0770E+11
User I/O 4728148400
Other 548221433
Concurrency 167154949
Network 56271499
Application 46336445
Commit 35742104
Configuration 11667683
System I/O 8045920
Administrative 760
Scheduler 16
11 rows selected.
SQL>
The V$SESSION_WAIT_CLASS view shows the total time spent in each type of wait class by an
individual session. Here’s an example:

SQL> SELECT wait_class, time_waited
FROM v$session_wait_class
WHERE sid = 1053
ORDER BY time_waited DESC;

WAIT_CLASS TIME_WAITED
------------------------------------------
Idle 21190
User I/O 8487
Other 70
Concurrency 13
Application 0
Network 0
6 rows selected.
SQL>

The V$WAITCLASSMETRIC view shows metric values of wait classes for the most recent
60-second interval. The view keeps information for up to one hour. Here’s an example of using
the query:

SQL> SELECT WAIT_CLASS#, WAIT_CLASS_ID
dbtime_in_wait,time_waited,wait_count
FROM v$waitclassmetric
ORDER BY time_waited DESC;

WAIT_CLASS# DBTIME_IN_WAIT TIME_WAITED WAIT_COUNT
----------- -------------- ----------- ----------
6 2723168908 170497 51249
0 1893977003 5832 58
8 1740759767 717 1351
5 3386400367 11 68
7 2000153315 8 52906
9 4108307767 6 99
1 4217450380 0 4
2 3290255840 0 0
3 4166625743 0 0
11 3871361733 0 0
10 2396326234 0 0
4 3875070507 0 0
12 rows selected.
SQL>

Collecting Detailed Wait Event Information

Selecting data from V$ dynamic performance views and interpreting them meaningfully isn’t
always so easy to do. Because the views are dynamic, the information that they contain is constantly
changing as Oracle updates the underlying tables for each wait event. Also, the wait-related
dynamic performance views you just examined don’t provide crucial data such as bind variable
information. For a more detailed level of wait information, you can use one of the methods
described in the following sections.

Method 1:Using the Oracle Event 10046 to Trace SQL Code

You can get all kinds of bind variable information by using a special trace called the 10046 Trace,
which is much more advanced than the SQL Trace.. The use of this trace
causes an output file to be written to the trace directory. You can set the 10046 Trace in many ways
by specifying various levels, and each higher level provides you with more detailed information.
(Level 12 is used in the following case as an example only—it may give you much more information
than necessary. Level 4 gives you detailed bind value information, and Level 8 gives you wait
information.)

ou can use the ALTER SESSION statement as follows:
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever level 12';
Session altered.
SQL>
You can also incorporate the following line in your init.ora file:
event = 10046 trace name context forever, level 12

Method 2:Using the Oradebug Utility to Perform the Trace

You can use the oradebug utility as shown in the following example:
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER LEVEL 8;
Statement processed.
SQL>
In this example, SETMYPID indicates that you want to trace the current session. If you want a
different session to be traced, you replace this with SETOSPID <Process Id>.

Method 3:Using the DBMS_SYSTEM Package to Set the Trace

Use the SET_EV procedure of the DBMS_SYSTEM package so you can set tracing on in any session,
as shown in the following example:
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV (9,271,10046,12,'');
PL/SQL procedure successfully completed.
SQL>

Method 4:Using the DBMS_MONITOR Package

The DBMS_MONITOR package provides you with an easy way to collect extended session trace
information. You enable tracing of a user’s session using the DBMS_MONITOR.SESSION_
TRACE_ENABLE package. Here’s the structure of the procedure:
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE)
If you set the waits parameter to TRUE, the trace will contain wait information. Similarly, setting
the binds parameter to TRUE will provide bind information for the session being traced.
If you don’t set the SESSION_ID parameter or set it to NULL, your own session will be traced.
Here’s how you trace your session using the DBMS_MONITOR package:
SQL> EXECUTE dbms_monitor.session_trace_enable (waits=>TRUE, binds=>TRUE);


Determining the Latch Hit Ratio

SQL> SELECT a.name "Latch Name",
a.gets "Gets (Wait)",
a.misses "Misses (Wait)",
(1 - (misses / gets)) * 100 "Latch Hit Ratio %"
FROM V$LATCH a
WHERE a.gets != 0
UNION
SELECT a.name "Latch Name",
a.gets "Gets (Wait)",
a.misses "Misses (Wait)",
100 "Latch Hit Ratio"
FROM V$LATCH a
WHERE a.gets = 0
ORDER BY 1;
Subscribe to: Posts (Atom)
Powered by Blogger.