Oracle Database

Session Level Tuning

Bjørn Engsig
bjorn.engsig@oracle.com

Overview
Introduction Tuning possibilities
How is the time spent? – CPU
– wait events
Time based tuning – latches
Wait events – I/O
Using SQL_TRACE Programming
Using ”event 10046” practices
– Cursor handling
– Bind variables

1
Some typical performance
questions
Why is database performance ALWAYS a hot topic?
Why does my application not scale?
Where does my performance problem really come
from?
Can I set a magic init.ora parameter?
– There are in fact some, although not magical ones!

This presentation focuses on SQL processing -

rather than on data processing

A famous picture
Tuning cost increases in time
Tuning benefit decreases in time

Benefit Cost
Taking a look at tuning cost and
benefit over time from application
design till full production use

Time
Design Development Implementation Production

2
Sources of performance
problems
Using too many resources, such as CPU or disk I/O
– Potential cause of poor response time
(my SQL statement takes too long to execute)
Waiting for others holding a single resource, such
as a latch
– Potential cause of poor scalability
(adding more CPU doesn’t allow me to run more
concurrent users)
– Causes contention for the resource

How is the time spent?

Various steps takes place when the user asks for

some processing
– SQL statements sent to the server
– Data blocks read from disk
– Blocks processed in the cache
– Waiting for locks
– … much more

3
How is the time is spent?

Block processed in cache

Block read from disk
Waiting for a lock

time

You need to half the time - how would you tune?

How is the time is spent?

time

Buffer cache hit ratio is only 86%

- let me increase it to 95% - that should help!
time

Not even 100% is good enough!

4
How is the time is spent?
time

Would decreasing lock waiting time help?

– No!
Would getting faster disks help?
– No!

You need to reduce the number of blocks processed

How is the time is spent?

Block processed in cache
Block read from disk
Waiting for a lock

time

You need to half the time - how would you tune?

5
How is the time is spent?
time

Reduce lock wait time

time

How is the time is spent?

Block processed in cache
Block read from disk
Waiting for a lock
Waiting for a library cache latch
time

You need to half the time - how would you tune?

6
How is the time spent?
time

Remove the latch wait time

time

Time based tuning

YAPP formula:
response time = service time + wait time
What is really processing time and wait time?
Σ formula:
Modified

response time =
Σ time componenti

7
Getting tuning data from your
application
Prepare your application to produce these data
Measure time spent calling Oracle inside your
application
Make Oracle produce timing data with an Oracle
perspective

Think of the complete application as a single-

threaded sequence of operations

Measuring time
Time

Application
call Oracle return call
Oracle CPU
an Oracle wait event
Oracle Wait
The more places you can measure time, the better
Oracle can precisely do it with its perspective
– Really done in the server process

8
Oracle CPU time and wait events

Oracle time reporting

– Oracle measures the CPU time spent and the time
spent in various wait events.
CPU time
– Processing data in blocks, evaluating expressions
– Executing PL/SQL such as stored procedures
Wait time
– Reading data from disk
– Waiting for a lock

SQL_TRACE

SQL_TRACE is used to trace SQL execution

It will show CPU and elapsed time for all individual
steps
– Parse, execute, fetch
It will show number of blocks processed
It will show the execution plan

9
 SQL_TRACE

Turned on/off with

alter session set sql_trace=true/false
Executed like any other SQL statement
Output is generated in trace files found on the
database server
CERN has a system to send these via email to the
user

SQL_TRACE sample output

PARSING IN CURSOR #3 len=33 dep=0 uid=21 oct=6 lid=21
hv=1693389691 ad='388bfaf4'
update rac1 set b=:b1 where a=:b2
PARSE #3:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,
EXEC #3:c=0,e=727,p=0,cr=2,cu=2,mis=0,r=1,dep=0,og=1,
EXEC #3:c=0,e=120,p=0,cr=2,cu=1,mis=0,r=1,dep=0,og=1,
XCTEND rlbk=0, rd_only=0

PARSING IN … - shows the SQL statement

PARSE #n: - shows that a parse took place
EXEC #n: - shows that an execute took place
The handling of cursors, with parse, execute, etc will be
explained later

10
 SQL_TRACE data
PARSE #3:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,

c=0 – CPU time in µs

e=0 – Elapsed time in µs
p=0 – Number of blocks physically read
cr=0 – Number of consistent read blocks
cu=0 – Number of current read blocks
mis=0 – explanation to follow….
r=0 – Number of rows
dep=0 – Recursive depth (e.g. 1 for SQL in PL/SQL)

Event 10046

SQL_TRACE is the simple for of the famous ”event 10046”

Search on metalink or for ’oracle 10046’ or google
Set using the syntax:
alter session set events
‘10046 trace name context forever, level NN’
NN=1: like setting sql_trace to true
NN=4: Trace all events
NN=8: Trace bind variable contents
NN=12: Trace both
NN=0: turn off, like setting sql_trace to false

11
 Event 10046 example

PARSING IN CURSOR #3 len=33 dep=0 uid=21 oct=6 lid=21

update rac1 set b=:b1 where a=:b2
PARSE #3:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,
BINDS #3:
Bind#0
value=1177835187
Bind#1
value=23
WAIT #3: nam='enq: TX - row lock contention' ela= 2776415
EXEC #3:c=0,e=2777517,p=0,cr=2,cu=3,mis=0,r=1,dep=0,og=1,

Event 10046 example

BINDS #n: – Show values (plus more) of bind

variables
WAIT #n: – Show a wait event including elapsed
time

Note in the example how there is a wait for a row

lock of around 2.7s, and that elapsed time for the
execute is also around 2.7s

12
Let’s combine three slides!

Application

10046
call Oracle return call
Σ
Oracle CPU
an Oracle wait event
Oracle Wait

Σ
response time = time componenti

Too much data?

Want aggregation?
The tkprof utility does exactly that
Basic usage:
tkprof <tracefile> <outputfile>
Makes aggregates per SQL statement
Shows times, including wait times from 10046
level 8 for each
Shows other statistics like number of buffers

13
 tkprof output example
update rac1 set b=:b1
where
a=:b2

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 2.82 0 28 17 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 2.82 0 28 17 10

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS
Parsing user id: 21

tkprof output example, cont.

Rows Row Source Operation
------- ---------------------------------------------------
10 UPDATE RAC1 (cr=28 pr=0 pw=0 time=2797538 us)
10 INDEX UNIQUE SCAN SYS_C002813 (cr=20 pr=0 pw=0 time=269 us)(object
id
9680)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enq: TX - row lock contention 2 2.77 2.77
SQL*Net message to client 10 0.00 0.00
SQL*Net message from client 10 0.00 0.00
buffer busy waits 6 0.00 0.01

14
What have we learned so far?

Tuning is about finding how you spend the time

If you use too much CPU, that’s what you should
reduce.
– This is not a matter of setting some parameters
– This really is looking at the application
If you spend too much time waiting for various
events, this is what you should reduce
– Occasionally, setting parameters may help
– Often, modifying the application is needed

Tuning possibilities for CPU

Order the SQL statements by CPU usage

Logical I/O (buffer gets) is a primary CPU
consumer
Tune SQL statements from the top of this
– Modify SQL statement, i.e. SQL tuning
– Reduce number of calls to SQL statement
SQL statement tuning is primarily about reducing
the number of logical I/O’s
– Outside the scope of this presentation

15
Tuning possibilities for CPU

High CPU may be a PL/SQL block or stored

procedure call
– Ignore if PL/SQL mostly does SQL
– Reduce PL/SQL if it mostly does procedural
processing

If parsing has a high CPU usage, reduce parsing,

in particular hard parsing

Elapsed time vs. CPU time

Oracle does not see processes waiting in the CPU
run queue
If cpu+wait>elapsed, you are probably waiting for
the CPU to be available

??
Oracle CPU
an Oracle wait event
Oracle Wait

16
Tuning possibilities for wait
events
If your largest time component is a wait event
Buffer management events
I/O events
Lock and latching events
SQL*Net events

Buffer management events

Event name Description Possible tuning

free buffer waits Waiting for a free DBWR not able to keep up.
buffer to be available − Use asynchronous I/O
− Redistribute files
− Too small buffer cache

buffer busy waits Waiting for a specific Details in v$waitstat - typically:

buffer to become − Frequent updates to rows in same block
available − Not using automatic segment space
management for massive insert
log file sync The redo log buffer LGWR process not able to keep up
is being flushed − Redistribute I/O
− Decrease commit activity

17
File I/O events
Event name Description Possible tuning

db file scattered read Waiting for a Reduce number of reads

scattered multiblock − Avoid full table scan
read, i.e. a full table − increase db_file_multiblock_read_count
scan − Use ‘cache’ option and keep pool

Reduce cost of reads

− Use faster disks
− distribute I/O differently

db file sequential Waiting for a read Reduce number of reads

read one block at a time − Increase db_block_buffers
− increase block size
− change indexing strategy
− use rowid

Reduce cost of reads

− Use faster or more disks
− distribute I/O differently

Locking and Latching events

Event name Description Possible tuning

latch free Waiting for a certain Check latches with high number of
(more details in 10g) latch to become sleeps from v$latch and take
available appropriate steps

enqueue Waiting for an Use v$lock to identify locks, typical

(in 10g names is enqueue (lock) causes:
more intuitive) − Holding row locks for too long
− Using table locks
− Space management - use locally
managed tablespaces

18
Tuning latch contention
L a tc h D e s c rip tio n P o s s ib le tu n in g
nam e
s h a re d P ro te c tin g th e s h a re d p o o l. − R e d u c e p a rs in g b y u s in g b in d
pool H e a v ily u s e d d u rin g p a rs in g - in v a ria b le s
p a rtic u la r h a rd p a rs e . N o t u s e d − A v o id h a rd p a rs in g
d u in g e x e c u te − U s e c u rs o r_ s h a rin g

lib ra ry P ro te c tin g th e lib ra ry (S Q L ) c a c h e − R e d u c e p a rs in g

cache in th e s h a re d p o o l. H e a v ily u s e d − S e t s e s s io n _ c a c h e d _ c u rs o rs
d u rin g s o ft a n d h a rd p a rs in g , − c u rs o r_ s h a rin g h a s o n ly m in o r
m in o r u s e d u rin g e x e c u te e ffe c t

ro w c a c h e P ro te c tin g th e d a ta d ic tio n a ry − A v o id h a rd p a rs in g
in fo rm a tio n , o n ly n e e d e d d u rin g − c u rs o r_ s h a rin g w o rk s w e ll
h a rd p a rs e

Tuning latch contention

Latch name Description Possible tuning

cache buffer Protects the hash chains of − Reduce need for buffers
chain cache buffers. Oracle9i and − Often caused by hot blocks, e.g.
later normally doesn’t show index root block
it.

cache buffer lru Protects the LRU chains of − Increase db_block_lru_latches

chain the cache buffers

19
 SQL*Net events

Event name Description Possible tuning

SQL*Net more data All but the first of Can indicate slow
to/from client multiple packages in networks
same direction
SQL*Net message Foreground process None, expected high
from client waiting for message when e.g. waiting for
from client user input

Parsing and executing SQL

statements
Oracle processes SQL statements:
parse to verify syntax and access rights of the
SQL statement
execute to actually process data
fetch in queries to send retrieved data to the client

SQL> alter session set PARSE #1:c=10000,e=128791,p=0,cr=3,c

EXEC #1:c=0,e=157,p=0,cr=0,cu=0
2 sql_trace = true;
FETCH #1:c=0,e=479,p=0,cr=1,cu=2

20
Parsing SQL statements

The hard parse does syntax checking

– High CPU cost
– Very high contention for several latches
– A parse is hard when the SQL is not already in the
library cache
The soft parse verifies access rights
– Some CPU cost
– High contention for several latches
– A parse is soft, if the SQL statement is already found
in the library cache

Application coding - category 1

parse(“select * from emp where empno=1234”);

execute();
fetch();

Uses a literal (1234)

Causes a hard parse for each SQL statement
Cannot use the shared SQL area

Only recommended for DSS type applications

21
Application coding - category 2

eno = 1234;
parse(“select * from emp where empno=:1”);
bind(“:1”, eno);
execute();
fetch();

Uses a bind variable (:1) in stead of literal

Causes a soft parse for each SQL statement
Will use the shared SQL area

Application coding - category 3

parse(“select * from emp where empno=:1”);

bind(“:1”, eno);
loop
eno = <some value>;
execute();
fetch();
end loop;

Only one single parse

Efficiently uses the shared SQL area

22
 SQL_TRACE data - recap
PARSE #3:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,

You don’t want library cache misses

During parse – this was a hard parse
During execute – the statement was aged out
– With frequent executes, this is a sign of too small
shared pool (ask you DBA for more!)

mis=0 – Number of library cache misses

Does it really matter?

Show CPU and latch wait time spent in Oracle

using the three application categories
Shown for 1, 3, 6, 12 and 24 concurrent sessions
Sessions simply makes 1000 selects using a
primary key like the examples shown on the
previous slide
Absolute value shown has no significance
– But results are directly comparable

23
Hard parse (using literals)
2500
Cat. 1, using a
literal
2000
Cat. 2, using soft
parse
1500
Cat. 3, repeating
1000 execute only

500

0
1 3 6 12 24

Soft parse/no parse

160
Cat. 2, using soft
140 parse
120 Cat. 3, repeating
100 execute only
80
60
40
20
0
1 3 6 12 24

24
Cheating using
session_cached_cursors
160 Cat. 2, using soft
140 parse
120
Cat. 3, repeating
100 execute only
80
Cat. 2 with
60 session_cached_
40 cursors
20
0
1 3 6 12 24

Using session_cache_cursors

Parameter that makes Oracle cache statements

on the server side
Can be set for the whole database or per session
The value specifies the number of cursors cached
per session
The trade off is CPU for searches vs. less latch
contention
Good values are around 10-20

25
Summary

Tuning means measuring time

Figure out, what really takes time
Reduce time
– Make it more efficient (e.g. SQL tune, reduce locking)
– Do it fewer times (cache data in client, reduce
parsing)
Don’t expect, that you always know all details

Very Frequent Problems

Poor SQL
– Can be caused by the optimizer
– Most often, it is not
Bad database design
– For the purists, everything should be 5th normal form
– For the practical, performing approach, 3½th normal
form is fine☺
Poor application coding practices
– Too much parsing

26
QUESTIONS
ANSWERS

27