Transcript Oracle Performance Optimization Using the Wait Interface – 7, 8i, 9i and Beyond Thursday, April 8th 2004 Gaja Krishna Vaidyanatha Independent Consultant [email protected].

Oracle Performance
Optimization Using the Wait
Interface – 7, 8i, 9i and Beyond
Thursday, April 8th 2004
Gaja Krishna Vaidyanatha
Independent Consultant
[email protected]
Upcoming Book – Unprecedented
Stuff
Oracle Insights : Tales of the
Oaktable
A 12-author Book Project












Dave Ensor
Tim Gorman
Kyle Hailey
Anjo Kolk
Tom Kyte
Jonathan Lewis
Connor McDonald
Cary Millsap
James Morle
Mogens Norgaard
David Ruthven
Gaja Krishna Vaidyanatha
Published by Apress
(http://www.apress.com)
2
Confession#1
I am not an expert…not by
any stretch of the imagination.
Confession#2
I am an engineer not a
scientist.
Confession#3
Advanced Tuning, Turbo-charged
Tuning, Push-Me-For-MorePower Tuning…Lies…Just Plain
Lies…
Or is it called Marketing these
days!!!
Confession#4
There is only one way to
optimize Oracle performance
– The Right Way...Using the
Wait Interface
‘It’s almost ‘duh!’…what is
common sense is not
common practice’
- Steven Covey
Plan of Action
What is Performance Optimization?
What is the Oracle Wait Interface (OWI)?
The Method Behind the Madness
The Guts of OWI
Diagnosing an Oracle performance problem – A Demo
Application Performance Optimization – A DBA’s
Perspective
Oracle Instance/Database Configuration & Optimization
Conclusion
8
What is Performance
Optimization?
Systematic and deliberate effort to achieve a system runtime
goal, by eliminating one or more bottlenecks
The “goal” needs to be clearly defined

Query X runs in 40 minutes and needs to run in less than 4 minutes.
Requires a simple diagnostic method that is repeatable

No expert methods please, we have enough of those
Remember – Keep one thing in clear focus


Reducing logical I/O is the ultimate goal in any performance
tuning engagement
This has a huge positive impact on response time
9
What is Performance
Optimization?
Requires capability to perform in-depth analysis of
the diagnostic data
Facilitate the path to resolving the underlying
problem
There is only one way to optimize Oracle
performance


The RIGHT Way!!!
Everything else is just marketing gimmicks
Advanced Tuning
Turbo Tuning…push me for more power!!!
10
What is the Oracle Wait Interface
(OWI)?
Instrumentation within the Oracle Kernel that provides
execution/wait times for various code paths




Timestamp – t1
Code X
Timestamp – t2
Time spent = t2 – t1
Was introduced in 7.0.12
Helps us find “where is the time spent?”

Resource hogs are usually the bottlenecks
Externalized via a set of V$ views


V$System_Event, V$Session_Event, V$Session_Wait
Trace files generated via event# 10046
The only source of real performance diagnostic data

V$Sysstat, V$Sessstat and such are supplementary data sources
11
The Method Behind the
Madness
The Method Behind the Madness –
Allow me to blow some steam off!!!
Almost every cache-hit ratio out there is
unintelligent

Just flat boring numbers
The most relevant ratio


(Executions below response time goal/Executions
above response time goal) * 100
Check out Jonathan Lewis’s Fan Hit Ratio
Survivor Story
13
The Method Behind the Madness –
The Mantra
Response Time = Service Time + Wait Time
Throughput = Service Time + Wait Time
Service Time = fn (Hardware specs, Laws of Physics)
Wait Time = fn (Wasteful Processing, Resource
Contention, Substance Smoked Before Application
Coding) -- ;-)
You can never eliminate waits

Why would you care for some waits if the application is within
the response time goal?
14
The Method Behind the Madness
1.
2.
3.
Set Tuning Goals
Benchmark current performance (if feasible)
Identify the performance bottleneck
I.
II.
III.
IV.
4.
5.
Gather Oracle Diagnostic Data using OWI – Prong I
Gather OS Diagnostic Data – Prong II
Correlate both data sources
Analyze tkprof trace data (9i and up includes wait data)
Make one change at a time
Rinse and repeat until tuning goal is achieved
This is version-independent and will work from version
7.0.12 and up
15
The Method Behind the Madness –
Set Tuning Goals
Why are you tuning?
How does the business benefit from your effort?

If SQL X runs in 0.3 seconds instead of 0.5, we will close
40% more orders each day
Can you please define what “fast” means?
Example - This SQL statement runs in 45 seconds
and needs to complete within 7 seconds.
Do you suffer from CTD?
16
The Method Behind the Madness
1.
2.
3.
Set Tuning Goals
Benchmark current performance (if feasible)
Identify the performance bottleneck
I.
II.
III.
IV.
4.
5.
Gather Oracle Diagnostic Data using OWI – Prong I
Gather OS Diagnostic Data – Prong II
Correlate both data sources
Analyze tkprof trace data (9i and up includes wait data)
Make one change at a time
Rinse and repeat until tuning goal is achieved
This is version-independent and will work from version
7.0.12 and up
17
The Method Behind the Madness –
Benchmark Current Performance
Timing of tracing/data collection depends on the time duration
of the problem
Record response time before and after a specific change
Check whether TIMED_STATISTICS=TRUE
•
•
•
15% alleged overhead is a myth
Does not show up in trace timing data
Set to FALSE if and only if there is a platform-specific bug
STATISTICS_LEVEL=TYPICAL



Introduced in 9iR2
Valid values are BASIC, TYPICAL, ALL
Control Switch for Performance Data Collection
18
The Method Behind the Madness –
Benchmark Current Performance
For a system-wide Problem
•
Create a snapshot using STATSPACK
•
•
or
Use top, identify the PID and then proceed
STATSPACK requires deleting one or more events from
STATS$IDLE_EVENT
SQL*Net message to client
Analyze reports online on http://www.oraperf.com
For a localized Problem
•
•
Run the SQL statement
May not be possible or feasible to always do this
Effective problem diagnosis “after the fact” can be done only
with long-term historical collections
19
The Method Behind the Madness
1.
2.
3.
Set Tuning Goals
Benchmark current performance (if feasible)
Identify the performance bottleneck
I.
II.
III.
IV.
4.
5.
Gather Oracle Diagnostic Data using OWI – Prong I
Gather OS Diagnostic Data – Prong II
Correlate both data sources
Analyze tkprof trace data (9i and up includes wait data)
Make one change at a time
Rinse and repeat until tuning goal is achieved
This is version-independent and will work from version
7.0.12 and up
20
The Method Behind the Madness –
Identify the performance bottleneck
Gather required diagnostic data (Prong I)

V$System_Event
Conglomerate of all events since instance startup

V$Session_Event
Historical event data for all currently connected sessions

V$Session_Wait
Waits of currently active sessions
1 row/active session
Data here is merged into V$Session in Oracle 10g
21
The Method Behind the Madness –
Identify the performance bottleneck
Gather required diagnostic data (Prong I)

Determine the PID from top (Similar effort on
Windows)
Helps identify top CPU consumers

Sample scenarios – Determining “location of the
pain”
One process is hogging the CPU
 * Pain caused by one PID
22
The Method Behind the Madness –
Identify the performance bottleneck
Gather required diagnostic data (Prong I)

Sample scenarios – Determining “location of the
pain”
Multiple processes are hogging the CPUs
 * Pain caused by multiple PIDs…Pick one PID at a time
Multiple processes are evenly utilizing the CPU, but not
hogging it
 * Pain caused by multiple PIDs…Pick one PID
 * This usually is an system-wide I/O problem
23
The Method Behind the Madness –
Identify the performance bottleneck
Gather required diagnostic data (Prong I) – Data Correlation



Determine the PID from top
Helps identify top CPU consumers
Determine SPID of the server process to be traced
Map the PID to the Oracle Server Process (join v$process,
v$session)
select S.Username, P.Spid, S.Sid, S.Serial#
from V_$SESSION S, V_$PROCESS P
where S.PADDR = P.ADDR
and S.Username = upper('&&oracle_user_name')

Trace the session
alter session set event….. – Prior to Oracle8i
dbms_system.set_ev(…) – Oracle 8i and up
dbms_monitor.* - Oracle 10g and up
24
The Method Behind the Madness –
Why is the OS Prong required?
CPU or memory bottlenecks are not revealed
within OWI
Some memory bottlenecks can cause subcentisecond waits within Oracle

Paging and Latch Free
System-level I/O Bottlenecks
Helps identify bottlenecks external to Oracle
25
The Method Behind the Madness –
Prong II : CPU Bottlenecks
CPU Utilization

sar –u 5 10000
%usr, %sys, %wio, %idle
%wio and %sys typically should be < 10-15%
0% idle is OK, so long as %wio and %sys is not
high
Comparable commands in Windows
CPU Run Queues

Run queue is the only deterministic CPU bottleneck metric
Average run queue <= (2 * (# of CPUs)

vmstat 5 10000 – Reveals system run queue
The column – “r” in the vmstat output is the CPU run queue

top – Runnable provides the CPU run queue
26
The Method Behind the Madness –
Prong II : Memory Bottlenecks
Memory Utilization


vmstat –S 5 10000
sr (scanrate) should be in and around 0
Bug in Solaris 2.6/2.7


swapins and swapouts should be 0
Level of paging should also be at a minimum
(In this day and age, paging should be nonexistent)
Oversized SGAs (to attain high cache-hit ratios) are potential
culprits for paging and swapping
Check for high I/O activity on swapfile devices
27
The Method Behind the Madness –
Prong II : I/O Bottlenecks
I/O Utilization

sar –d 5 10000
Response times of > 20ms on devices indicates
I/O bottlenecks
Response Time = Service Time (AvServ) + Wait Time (AvWait)
Queuing is an indication of high response time and high wait
times
High disk queue numbers + high response times
 I/O contention
I/O requests should be evenly balanced across all
devices

Fewer the devices, better the overall utilization
28
The Method Behind the Madness
1.
2.
3.
Set Tuning Goals
Benchmark current performance (if feasible)
Identify the performance bottleneck(s)
I.
II.
III.
IV.
4.
5.
Gather Oracle Diagnostic Data using OWI – Prong I
Gather OS Diagnostic Data – Prong II
Correlate both data sources
Analyze tkprof trace data (9i and up includes wait data)
Make one change at a time
Rinse and repeat until tuning goal is achieved
This is version-independent and will work from version
7.0.12 and up
29
The Method Behind the Madness –
Making one change at a time
Change should be driven by diagnostic data
Do not make arbitrary changes

Example : Mass changes to init.ora
Remember Newton’s Third Law of Motion

For every action there is an equal and opposite reaction
One of the oldest Texas quotes

If it ain’t broke, don’t fix it!
30
The Method Behind the Madness
1.
2.
3.
Set Tuning Goals
Benchmark current performance (if feasible)
Identify the performance bottleneck(s)
I.
II.
III.
IV.
4.
5.
Gather Oracle Diagnostic Data using OWI – Prong I
Gather OS Diagnostic Data – Prong II
Correlate both data sources
Analyze tkprof trace data (9i and up includes wait data)
Make one change at a time
Rinse and repeat until tuning goal is achieved
This is version-independent and will work from version
7.0.12 and up
31
The Method Behind the Madness Summary
Set tuning goals
Benchmark
Identify bottlenecks
Make one change at a time
Rinse and repeat until
tuning goal is achieved
Two-pronged approach
OWI Diagnostics – Prong I
(V$System_Event,V$Session_Event,
V$Session_Wait)
Perform necessary drilldowns from
OWI to (V$SQL, V$Latch,
V$Session)
OS Diagnostic Data – Prong II
Required for additional
information only if Prong I does
not reveal the problem
32
The Guts of OWI
(Credits : Jonathan Lewis
for some of the trace
events)
OWI
V$System_Event – Wait data since last instance startup
V$Session_Event – Wait data for connected sessions
V$Session_Wait/v$Session – Wait data for current sessions
waiting
For a dynamic problem - Tracing sessions using the 10046
event
Additional sample drilldown views



V$Session (In 10g, we have session-wait information here)
V$Sql
V$Latch
34
V$System_Event
•
Columns
Event
Total_Waits
Total_Timeouts
Time_Waited – in centiseconds
Avg_Wait – in centiseconds
Time_Waited_Micro – in microseconds
35
V$Session_Event
•
•
•
Same as V$System_Event
This is each session’s wait history
Additional Columns
– Sid – Session identifier
– Max_Wait – In centiseconds
•
•
Volatile data, data is lost as sessions log on and
off
Broken in 9iR2 on some platforms
– SID in this view does not match SID in v$session
– It is “one off” – one less than v$session
36
V$Session_Wait – The Hound
Dog
Complex view to understand
Wait statistics are reported as they happen
Reveals the vicinity of the bottleneck…;-)
Columns you should care about
–
Sid – Session Identifier
–
Seq# – Occurrence count for a wait
–
Event – Name of the event
–
P1TEXT..P3TEXT – Description of P1..P3
–
P1..P3 – Location of the bottleneck
–
State – Session state
–
Wait_time – Completed wait (in seconds)
–
Seconds_In_Wait – Wait in Progress (in seconds)
37
V$Session_Wait (P1..P3)
p1 - p3
– Provides location of the wait events
– Example : For “db file sequential read”
p1 is the file#
p2 is the block#
p3 is the number of blocks in the I/O call
–
Example: For “latch free”
p2 is the latch#
Look up the latch with latch# in v$latch
38
V$Session_Wait (State)
WAITING
–
Currently waiting for the event.
WAITED UNKNOWN TIME
–
Timed_Statistics is not set to TRUE, i.e., is set to FALSE.
WAITED SHORT TIME
–
Waited for less than 1 centisecond.
WAITED KNOWN TIME
–
If the resource that is waited upon is gained at a later
time, the state changes from Waiting to Waited Known
Time.
39
V$Session_Wait (Wait_Time)
The value for this column is STATE dependent.
If state = (WAITING or WAITED UNKNOWN TIME or
WAITED SHORT TIME)
then
Wait_Time = Irrelevant;
End If;
If state = (WAITED KNOWN TIME)
then
Wait_Time = Actual wait time, in seconds;
End If;
40
V$Session_Wait (Seconds_In_Wait)
• The value for this column is STATE dependent.
• If STATE = (WAITED UNKNOWN TIME or WAITED
SHORT TIME or WAITED KNOWN TIME) then
Seconds_In_Wait = Irrelevant;
• End If;
• If STATE = (WAITING) then
Seconds_In_Wait = Actual Wait Time In Seconds;
• End If;
41
V$Session_Wait (Seconds_In_Wait)
• If Wait_Time = 0 then
Seconds_In_Wait = Current Wait
Elsif Wait_Time > 0 then
Seconds_In_Wait = Seconds since start of previous wait
End if;
• (Seconds_In_Wait – Wait_Time)/100
- Active # of seconds since end of last wait
• Check Bug Nos. – 2803772, 2843192, 2873528
- Seconds_In_Wait resets on every seq# increment
- Non-zero Wait_Time shows up as “Null Event”.
42
Types of Wait Events
Non-Idle versus Idle
Foreground versus Background
There are obvious exceptions in both categories
Non-Idle is usually foreground related
•
•
•
•
db file sequential read
db file scattered read
SQL*Net message to client
SQL*Net more data to client
Idle is usually background related
•
•
•
pmon timer
smon timer
SQL*Net message from client – Exception, this is for a foreground
43
Event# 10046
Flexible SQL_TRACE
Levels
– 0 = Off = Setting SQL_TRACE = FALSE
– 1 = Statistics = Setting SQL_TRACE = TRUE
–
–
–
4 = Statistics + Bind Variable Values
8 = Statistics + Wait Events
12 = Statistics + Wait Events + Bind Variable Values
BEWARE - At level 8 and above, large amounts of
trace information will be generated
44
Enabling 10046 – dbms_system.* - 8i
PROCEDURE SET_BOOL_PARAM_IN_SESSION
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ -------SID
NUMBER
IN
SERIAL#
NUMBER
IN
PARNAM
VARCHAR2
IN
BVAL
BOOLEAN
IN
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ -------SID
NUMBER
IN
SERIAL#
NUMBER
IN
PARNAM
VARCHAR2
IN
INTVAL
BINARY_INTEGER
IN
PROCEDURE SET_EV
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ -------SI
BINARY_INTEGER
IN
SE
BINARY_INTEGER
IN
EV
BINARY_INTEGER
IN
LE
BINARY_INTEGER
IN
NM
VARCHAR2
IN
45
Enabling 10046 – The Recipe
1. Identify the session’s process ID (SPID)
- select S.Username, P.Spid, S.Sid, S.Serial#
from V$SESSION S, V$PROCESS P
where S.PADDR = P.ADDR and S.Username = ‘XXX’;
2. Set timed_statistics to true (if not already set)
exec dbms_system.set_bool_param_in_session(sid, serial#,
'TIMED_STATISTICS', TRUE);
3. Prevent trace file truncation due to default setting of
MAX_DUMP_FILE_SIZE
exec dbms_system.set_int_param_in_session(sid, serial#,
'MAX_DUMP_FILE_SIZE', 2147483647);
4. Turn on trace
exec dbms_system.set_ev(sid, serial#, 10046, 8, '')
5. Run the application
6. Turn off trace
exec dbms_system.set_ev(sid, serial#, 10046, 0, '')
7. Locate trace file in USER_DUMP_DEST using SPID from #1
8. Run TKPROF on trace file
46
Some Useful Diagnostic Events & a
not-so-well-know package
10046 – SQL Tracing and Wait Data Collection
10053 – Optimizer tracing
10032, 10033 – Sort tracing
10128 – Partition tracing
10391 – Parallel Query tracing
10060 – Query Transformations tracing (Query
unnesting/View Merging)
7. 10730 – SQL Statement tracing (Row-level security)
1.
2.
3.
4.
5.
6.
DBMS_OLAP – For diagnosing queries to Materialized Views and Query rewrites
47
Reading the output of TKPROF
call count
Parse
1
Execute 1
Fetch 27
cpu
0.02
0.01
0.24
elapsed
0.02
0.01
0.36
disk
0
0
1230
query
0
0
2342
current
0
0
0
rows
0
0
399
Totals
0.27
0.39
1230
2342
0
399
29
Elapsed = CPU + (Wait for I/O or Context Switch)
Disk = Physical I/O
Query + Current = Logical I/O
48
Getting Oracle’s Plan of Action
Query - SELECT a.id,a.name,b.name
FROM author a, book b
WHERE a.author_id = b.book_author_id
AND a.author_id = 101
ORDER BY b.name;
Reading the Execution Plan



Query Plan
-------------------------------------------------------------------------------1.0 SELECT STATEMENT Statement1 Cost = 148
2.1 SORT ORDER BY (7th)




3.1 FILTER (6th)
4.1 NESTED LOOPS (5th)
5.1 TABLE ACCESS BY ROWID AUTHOR (2nd)
6.1 INDEX UNIQUE SCAN AUTHOR_ID UNIQUE (1st)
5.2 TABLE ACCESS BY ROWID BOOK (4th)
6.2 INDEX RANGE SCAN BOOK_AUTH_ID NON-UNIQUE (3rd)
49
Putting it all together
Multiple snapshots and “deltas” on v$system_event
reveal “db file sequential read” as the wait event with
the highest frequency
This wait event is for “single block reads”
We start our path down to v$session_event and then
to v$session_wait
P1 and P2 reveal the file# and block# of the object
With dba_extents and dba_ data_files, the culprit
segment(s) are singled out
50
Putting it all together
Join v$session_wait with v$sql gets the SQL
executed by the waiting sessions
Using OS prong you collect I/O statistics. Device
response times are sub-optimal

sar –d reveals device response times at 80ms
Data collection and analysis diagnoses the problem
as “index overuse”.
Root cause – Too many RULE Hints
51
What’s new in Oracle 10g ?
- Active Session History!
New source of Oracle database performance data in 10g
An active session is one which is in a user call



Parse
Execute
Fetch
Provides historical information about recently sampled
“active” sessions
ASH = V$SESSION_WAIT++ with History

Note: In 10g V$SESSION_WAIT is integrated with
V$SESSION
It facilitates spot analysis of both foreground and
background sessions
52
Extreme Example #1
8GB SGA (4.5GB Shared Pool)

Pre-tuning state
Init.ora was tuned like crazy.
To attain a high library cache-hit ratio memory was periodically added (in
vain).
Bad response times with online queries.
System was experiencing severe parsing hiccups.



Bottleneck - Severe shared pool latch contention
Cause - Lack of bind variable usage
Post-tuning state - On fixing the application, shared pool was
shrunk to 256MB. Pre-tuning state symptoms vanished.
53
Extreme Example #2
6GB SGA (4GB Database Buffer Cache)

Pre-tuning State
Tuned the heck out of Init.ora parameters.
Cache-hit ratios were nice and balmy (90s).
Terrible response times.
High-levels of CPU usage.



Bottleneck – Severe contention for the cache buffers chains and
cache buffers lru chain latches. Moderate contention on db file
sequential read and buffer busy waits.
Cause - Correlated sub-queries, queries forced to use indexes,
lack of enough freelists many concurrent insert tables
Post-tuning State - Fixed the application, added more freelists
and freelists groups to the relevant tables, DB cache sized to 1GB
54
The Method & the Madness –
Useful Sites
•
•
•
•
•
www.orapub.com - Craig Shallahamer’s website
www.hotsos.com - Cary Millsap’s website
www.evdbt.com - Tim Gorman’s website
www.jlcomp.demon.co.uk - Jonathan Lewis
www.scaleabilities.co.uk - James Morle
55
The Method & the Madness – Useful
Papers & Books
• The YAPP Paper – By Anjo Kolk, Shari Yamaguchi &
Jim Viscusi
• James Morle’s “Scaling Oracle8i”
• Jonathan Lewis’s “Practical Oracle8i”
• Cary Millsap’s “Optimizing Oracle Performance”
• Tom Kyte’s “Effective Oracle by Design”
• Oracle Performance Tuning 101 – Yours Truly et. al
56
Diagnosing an Oracle
Performance Problem – A
Demo
Diagnosing an Oracle Performance
Problem – A Demo
View script toolkit
Run an I/O-intensive PL/SQL program
Use scripts to identify performance problem
58
Application Performance
Optimization – A DBA’s
Perspective
(Credits : Wolfgang
Breitling & Tim Gorman
on the Optimizer
Section)
Application Performance
Optimization – A DBA’s Perspective
What is the 80-20 rule?
Optimization Methodologies
Controlling the Optimization Method
Controlling the Optimizer’s Behavior
Using Hints
Calculation of object statistics – Why, How, How Much and
How Often?
Indexing Strategies

When should you rebuild your fragmented indexes?
Join Strategies
Managing Database Latching
Some low-hanging fruit in SQL Land
60
What is the 80-20 rule?
- My Perspective
80% of the world’s problems are NOT created by a
DBA - ;-)

There is a nasty corollary that can be derived from this
Oracle Instance Configuration and System
Optimization will provide < 20% impact
20% (or less) of your SQL will inflict 80% of more of
the pain in your system
80% or more of the I/O on your system is generated
by SQL on < 20% of the objects on the system
61
Optimization Methodologies
Rule





Rigid
Preferred method prior to Oracle 7.3.4
Stable across releases (except in 10g)
De-supported in 10g
Does not need any statistics
Cost





Flexible
Preferred method in Oracle 8.0 and higher
Requires object-level statistics
By default, is naïve with its assumptions
Utilizes new functionality in the database across different releases
62
Optimization Methodologies –
Rule
A bunch of rules - 15 basic rules
Rule #15 - Full Table Scan
Rule#10 – Access by a Composite Index Prefix
Rule#9 – Access by a Single-Column Index or Index
Merge
Rule#8 – Access by a Composite Index (An index
with more than 1 column) – entire key
Rule#1 - Access by ROWID
63
Optimization Methodologies –
Cost
Basic Premise - Execute SQL in the least expensive
way
Requires statistics to determine cost of the plan
Cost = XXXX


Prior to 9iR2 it was the cost of performing I/O
In 9iR2, the CPU cost is also factored in
Need to explicitly set DBMS_STATS.SET_SYSTEM_STATS
Stats are stored in SYS.AUX_STATS$

Affected by DB_FILE_MULTIBLOCK_READ_COUNT
64
Optimization Methodologies –
Cost
Statistics can be - ESTIMATEd or COMPUTEd


ANALYZE is rumored to be on the de-support train
In 9i and up use DBMS_STATS
Early releases of 8i was buggy especially with bitmapped
indexes. Worth a shot.

For Oracle Applications use FND_STATS or the new
equivalent
Use histograms in version 7.3 and up on skewed
data but only on SQL with “hard-coded values”
65
Oracle 9i Optimizer Changes
Histograms are used even with bind variables
This is done with BIND VARIABLE VALUE PEEKING



This is done at the FIRST PARSE of the cursor
If bind variable values change, the plan does not change
UNTIL the SQL is aged out
So based on the bind-variable value, the plan may not be
optimal
66
Oracle 9i – More data on your
SQL
Enhanced statistics in V$SQL and V$SQLAREA
V$SQLAREA – Some interesting columns




Fetches
CPU_Time
Elapsed_Time
Child_Latch
V$SQL – Some interesting columns




Fetches
Plan_Hash_Value
CPU_Time
Elapsed_Time
In 10g, there is a replacement for SQL_Hash_Value =
SQL_ID
67
Oracle 9i – More data on your
SQL
PLAN_TABLE



Create new PLAN_TABLE after each upgrade –
utlxplan.sql
Contains the execution plan for SQL statements for
EXPLAIN PLAN requests
New Columns
CPU_COST
IO_COST
TEMP_SPACE
ACCESS_PREDICATES (Start and Stop values for Indexes)
FILTER_PREDICATES
68
Oracle 9i – More data on your
SQL
V$SQL_PLAN_STATISTICS



Provides statistics at the row-source level and also
cumulative values
STATISTICS_LEVEL = ALL
Statspack Snap Level > 5
69
Oracle 9i – New Access Paths
INDEX_JOIN



Small indexes with all columns to resolve the query
without visiting the table
In 8i _INDEX_JOIN_ENABLED=FALSE
In 9i, it defaults to TRUE
Index Skip Scan

No longer require the leading column in the index to be
referenced in the WHERE clause
70
Controlling the Optimization
Method
At the Instance Level – OPTIMIZER_MODE



FIRST_ROWS, ALL_ROWS, CHOOSE, RULE
CHOOSE + STATISTICS = ALL_ROWS
Preferred Setting = CHOOSE
Setting to ALL_ROWS prior to 9iR1 can cause
statistics collection on the dictionary objects of
SYS
This can cause deadlocks and significant delays
as queries to the Oracle Dictionary is optimized
using RULE-based.
71
Controlling the Optimization
Method
At the Session Level – alter session….
At the Statement Level - /*+ HINT */
Changing initialization parameters



DB_FILE_MULTIBLOCK_READ_COUNT
HASH_MULTIBLOCK_IO_COUNT
HASH_AREA_SIZE
Setting Work-area Policy to AUTO and setting
PGA_AGGREGATE_SIZE provides better stability against
session-level parameters that affect the optimizer

Note PGA_AGGREGATE_SIZE does not limit PL/SQL tables
72
Controlling the Optimizer’s
Behavior
Default values of some of the Optimizer-related parameters (OPTIMIZER_*)
require modification when “execution plans” go south-bound.




OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MAX_PERMUTATIONS
OPTIMIZER_DYNAMIC_SAMPLING
In Oracle 9i, the following are required if you run into bug#2992537

EVENT 10076 – Ensure cartesian products are costed
alter session set events = ‘10076, trace name context forever’;



_ORDERED_NESTED_LOOP = FALSE – Ensure INDEX_FFS is cheaper than a
range scan
_TABLE_SCAN_COST_PLUS_ONE = FALSE – Ensure INDEX_FFS(single cost) is
cheaper than a range scan
_OPTIMIZER_NEW_JOIN_CARD_COMPUTATION = FALSE – Ensure cost
calculated is close to that of Oracle 817
In Oracle 8 and before it was as easy as setting OPTIMIZER_SEARCH_LIMIT = 1
Now it is _OPTIMIZER_SEARCH_LIMIT and the mininum value is 3
73
Controlling the Optimizer’s
Behavior
Useful for tuning packaged applications
Useful for tuning environments that require both
hash and nested loop joins
Please test first before deploying in production

Testing can be done at the session-level
Read Tim Gorman’s paper – “Searching for
intelligent life in the Oracle Optimizer” at
http://www.evdbt.com
Read Wolfgang Breitling’s paper – “What is new in
the Oracle 9i CBO” at http://www.centrexcc.com
74
Controlling the Optimizer’s Behavior
– OPTIMIZER_INDEX_CACHING
Tells the optimizer the probability that it will find an index block
in the cache
Controls optimizer propensity to pick an index for SQL
statement (usually using the nested loops join method)


Defaults to 0
Range of values - 0 – 100
When set to a high value (90 or higher), the optimize is
encouraged to use nested loops over other join methods
This parameter should be changed on all transactional
systems
75
Controlling the Optimizer’s Behavior –
OPTIMIZER_INDEX_COST_ADJ
Controls the optimizer’s propensity to use index
scans

Functionality similar to OPTIMIZER_INDEX_CACHING
Defaults to 100
Range of values - 1- 10000
Need to determine the appropriate value on a perdatabase basis
76
Controlling the Optimizer’s Behavior –
OPTIMIZER_INDEX_COST_ADJ
Calculating OPTIMIZER_INDEX_COST_ADJ







SELECT EVENT, AVERAGE_WAIT
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE ‘db file s%’;
EVENT
AVERAGE_WAITS
========================= ==============
db file sequential read
.33178629
db file scattered read
2.190087
In the above example, the average single-block I/O requests takes
only 15% of the average multi-block I/O. On this system
set OPTIMIZER_INDEX_COST_ADJ to 15.
Source - Tim Gorman’s paper
77
Controlling the Optimizer’s Behavior –
OPTIMIZER_MAX_PERMUTATIONS
Number of plan permutations that the optimizer
chooses from.

Choice of driving table is key for query performance
Defaults to 80000 - Driving table is usually the
smallest table
When set < 80000 - Driving table is determined from
8 different plans
The plan with the lowest cost is picked
The default is 2000 in Oracle 9i and up
78
Controlling the Optimizer’s Behavior –
OPTIMIZER_DYNAMIC_SAMPLING
New parameter in Oracle 9i
Normally used for unanalyzed tables, but there are
exceptions
Assists in better estimation of cardinality and
selectivity
Relevant for long-running queries (over a few
seconds)
When OPTIMIZER_FEATURES_ENABLE is set to <
9.0.2, there will be no dynamic sampling
79
Controlling the Optimizer’s Behavior –
OPTIMIZER_DYNAMIC_SAMPLING
Range of values 0-10







0 – No dynamic sampling
1 – No dynamic sampling if query has less than 2 tables, table has
no indexes, has not been analyzed, a full-scan is deemed expensive
2 – Dynamic sampling on all un-analyzed tables, # of sampled
blocks=32
3 – #2 + for tables where selectivity estimation was done
4 – #3 + single table predicates that refer 2 or more columns in the
WHERE clause
5 thru 9 – Same as 4, but with sampled blocks=64, 128, 256, 1028,
4096
10 – Same as 4, but all blocks in the table are sampled
If the number of blocks at level 5-9 > 50% of the total blocks,
every block is sampled
80
10053 Trace File
Analysis
( Credits : Peter Bach of
the OakTable)
Determining what the Optimizer
is doing ***
Setting 10053



dbms_system.set_ev (…)
Valid levels – 1 or 2
Unlike other events level 2 produces less than 1
Looking at the output
Dissecting the plethora of information that is being
presented
Read Wolfgang Breitling’s paper on
http://www.centrex.com

A Look under the Hood of CBO: The 10053 Event
82
The SQL
select
offer.code_label, sda.index1_value, count(*)
from
service_history sh,
reference_code market,
service_da_array sda,
reference_code offer
where
sh.service_id = sda.service_id and
sh.service_status_code = 3 and
to_date('15/12/2003 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
between sh.effective_start_date and sh.effective_end_date and
market.reference_type_id = 903285 and
market.reference_code = sh.general_1 and
market.code_label in ('MINNEAPOLIS') and
sda.derived_attribute_id = 907438 and
to_date('15/12/2003 12:00:00', 'dd/mm/yyyy hh24:mi:ss')
between sda.effective_start_date and sda.effective_end_date and
offer.reference_type_id = 905238 and
offer.reference_code = sda.index1_value
group by
offer.code_label, sda.index1_value;
83
The Old Execution Plan &
Autotrace Output
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1333 Card=1 Bytes=101)
1 0 SORT (GROUP BY) (Cost=1333 Card=1 Bytes=101)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE_! HISTORY' (Cost=2 Card=9974 Bytes=279272)
3 2
NESTED LOOPS (Cost=1260 Card=1 Bytes=101)
4 3
NESTED LOOPS (Cost=1258 Card=1 Bytes=73)
5 4
MERGE JOIN (CARTESIAN) (Cost=3 Card=1 Bytes=42)
6 5
TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE' (Cost=2 Card=1 Bytes=21)
7 6
INDEX (RANGE SCAN) OF 'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE) (Cost=1 Card=26)
8 5
BUFFER (SORT) (Cost=1 Card=1 Bytes=21)
9 8
TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE' (Cost=1 Card=1 Bytes=21)
10 9
INDEX (RANGE SCAN) OF 'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE)
11 4
TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE_DA_ARRAY' (Cost=1255 Card=50 Bytes=1550)
12 11
INDEX (RANGE SCAN) OF 'I_SERVICE_DA_ARRAY_DA' (NON-UNIQUE)
13 3
INDEX (RANGE SCAN) OF 'P_SERVICE_HISTORY' (UNIQUE) (Cost=1 Card=1)
Statistics
---------------------------------------------------------0 recursive calls
0 db block gets
8977230 consistent gets
0 physical reads
1556 redo size
772 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
84
10053 Output
GENERAL PLANS
***********************
Join order[1]: REFERENCE_CODE [MARKET] REFERENCE_CODE [OFFER]
SERVICE_DA_ARRAY [SDA] SERVICE_HISTORY [SH]
Now joining: REFERENCE_CODE [OFFER] *******
NL Join
Outer table: cost: 2 cdn: 1 rcz: 18 resp: 2
Inner table: REFERENCE_CODE
Access path: tsc Resc: 110
Join: Resc: 112 Resp: 112
OPTIMIZER PERCENT INDEX CACHING = 75
Access path: index (join stp)
Index: I_REFERENCE_CODE_REF_TYPE
TABLE: REFERENCE_CODE
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 0.0000e+00 TB_SEL: 2.1968e-04
Join: resc: 3 resp: 3
Join cardinality: 0 = outer (0) * inner (26) * sel (1.0000e+00) [flag=0]
Grouping column cardin! ality [CODE_LABEL] 26
Grouping column cardinality [INDEX1_VAL] 176
Best NL cost: 3 resp: 3
Join result: cost: 3 cdn: 1 rcz: 36
85
10053 Output
Now joining: SERVICE_DA_ARRAY [SDA] *******
NL Join
Outer table: cost: 3 cdn: 1 rcz: 36 resp: 3
Inner table: SERVICE_DA_ARRAY
Access path: tsc Resc: 2402
Join: Resc: 2405 Resp: 2405
OPTIMIZER PERCENT INDEX CACHING = 75
Access path: index (no sta/stp keys)
Index: I_SERVICE_DA_ARRAY
TABLE: SERVICE_DA_ARRAY
RSC_CPU: 0 RSC_IO: 7166
IX_SEL: 1.0000e+00 TB_SEL: 5.1224e-03
Join: resc: 7169 resp: 7169
OPTIMIZER PERCENT INDEX CACHING = 75
Access path: index (join stp)
Index: I_SERVICE_DA_ARRAY_DA
TABLE: SERVICE_DA_ARRAY
RSC_CPU: 0 RSC_IO: 1255
IX_SEL:&nbs! p; 0.0000e+00 TB_SEL: 1.2500e-01
Join: resc: 1258 resp: 1258
Join cardinality: 0 = outer (0) * inner (8753) * sel (5.6818e-03) [flag=0]
Grouping column cardinality [CODE_LABEL] 26
Grouping column cardinality [INDEX1_VAL] 176
Best NL cost: 1258 resp: 1258
86
10053 Output
SM Join
Outer table:
resc: 3 cdn: 1 rcz: 36 deg: 1 resp: 3
Inner table: SERVICE_DA_ARRAY
resc: 1561 cdn: 8753 rcz: 31 deg: 1 resp: 1561
using join:1 distribution:2 #groups:1
SORT resource
Sort statistics
Sort width:
598 Area size: 1048576 Max Area size:
Blocks to Sort:
1 Row size:
50 Rows:
1
Initial runs:
1 Merge passes:
1 IO Cost / pass:
Total IO sort cost: 73
Total CPU sort cost: 0
Total Temp space used: 0
SORT resource
Sort statistics
Sort width:
598 Area size: 1048576 Max Area size:
Blocks to Sort:
25 Row size:
45 Rows:
8753
Initial runs:
1 Merge passes:
1 IO Cost / pass:
Total IO sort cost: 97
Total CPU sort cost: 0
Total Temp space used: 0
Merge join Cost: 1734 Resp: 1734
104857600 Degree: 1
145
104857600 Degree: 1
169
87
10053 Output
Join cardinality: 0 = outer (0) * inner (8753) * sel (5.6818e-03)
Grouping column cardinality [CODE_LABEL] 26
Grouping column cardinality [INDEX1_VAL] 176
Best NL cost: 1258 resp: 1258
SM Join
Outer table:
resc: 3 cdn: 1 rcz: 36 deg: 1 resp: 3
Inner table: SERVICE_DA_ARRAY
resc: 1561 cdn: 8753 rcz: 31 deg: 1 resp: 1561
using join:1 distribution:2 #groups:1
SORT resource
Sort statistics
Sort width:
598 Area size: 1048576 Max Area size:
Blocks to Sort:
1 Row size:
50 Rows:
1
Initial runs:
1 Merge passes:
1 IO Cost / pass:
Total IO sort cost: 73
Total CPU sort cost: 0
Total Temp space used: 0
SORT resource
Sort statistics
Sort width:
598 Area size: 1048576 Max Area size:
Blocks to Sort:
25 Row size:
45 Rows:
8753
Initial runs:
1 Merge passes:
1 IO Cost / pass:
Total IO sort cost: 97
Total CPU sort cost: 0
Total Temp space used: 0
Merge join Cost: 1734 Resp: 1734
[flag=0]
104857600 Degr! ee: 1
145
104857600 Degree: 1
169
88
10053 Output
HA Join
Outer table:
resc: 3 cdn: 1 rcz: 36 deg: 1 resp: 3
Inner table: SERVICE_DA_ARRAY
resc: 1561 cdn: 8753 rcz: 31 deg: 1 resp: 1561
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 ! Deg: 1
hash_area: 128
(max=12800) buildfrag: 1
probefrag: 23
ppasses: 1
Hash join Resc: 1565 Resp: 1565
Join result: cost: 1258 cdn: 1 rcz: 67
89
The New Execution Plan & Autotrace Output
–10076 and the 3 underscore parameters are
set
Execution Plan
---------------------------------------------------------&! nbsp; 0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1747 Card=12 Bytes=1212)
1 0 SORT (GROUP BY) (Cost=1747 Card=12 Bytes=1212)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE' (Cost=1 Card=26 Bytes=546)
3 2
NESTED LOOPS (Cost=1674 Card=12 Bytes=1212)
4 3
NESTED LOOPS (Cost=1596 Card=78 Bytes=6240)
5 4
HASH JOIN (Cost=1442 Card=77 Bytes=3773)
6 5
TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE' (Cost=2 Card=1 Bytes=21)
7 6
INDEX (RANGE SCAN) OF 'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE) (Cost=1 Card=26)
8 5
TABLE ACCESS (FULL) OF 'SERVICE_HISTORY' (Cost=1439 Card=9974 Bytes=279272)
9 4
TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE_DA_ARRAY' (Cost=2 Card=1 Bytes=31)
10 9
INDEX (RANGE SCAN) OF 'I_SERVICE_DA_ARRAY' (NON-UNIQUE) (Cost=1 Card=1)
11 3
INDEX (RANGE SCAN) OF 'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE)
Statistics
---------------------------------------------------------14 recursive calls
0 db block gets
12395 consistent gets
0 physical reads
0 redo size
772 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
90
Using Hints
Modifying the Optimizer’s Behavior will reduce the number of
times you will need to use hints
Hints to the optimizer is equivalent of “application-level hardcoding”.
Across releases, hints pose an additional administrative
overhead of performing regression testing.
Across releases, the Optimizer has gotten very smart.
Stored Outlines  Stored Hints

Time for a magical performance demo
AVOID USING HINTS AS MUCH POSSIBLE!!!
91
Calculation of Object Statistics
The Burning Question – How often should I analyze
and collect stats?
Standard Answer – As often as your data changes

That is the WRONG answer
Reasonable Answer – As often as the statistical
composition of your data changes
It does not matter if you pump in 1,000,000 rows
everyday

Collect object statistics when the “statistical distribution
changes”
92
Calculation of Object Statistics
Why can’t I just analyze everyday?



ANALYZE invalidates all of the object’s SQL in the cache
That initiates a “hard parse” the next time around of
every SQL statement
If your system is already suffering from shared pool latch
and library cache latch problems, the pain of re-parsing
every SQL in the cache, will outweigh the benefit of new
statistics
Setting the MONITORING attribute has limited value

Change is measured with a static percentage – 20%???
93
Indexing Strategies
The columns of an index should be ordered based
on access patterns NOT distinct values
There are a zillion types of indexes today

Enlighten yourselves and your developers
Contrary to some papers, bitmapped indexes should
be used primarily for read-intensive applications

Locking is done at the extent-level in the bitmaps
Use PARALLELISM for creation and access
Use NOLOGGING for creation
94
Indexing Strategies
If PGA_AGGREGATE_SIZE is not used,
use large SORT_AREA_SIZE and
SORT_AREA_RETAINED_SIZE for index creations
Indexes can be rebuilt online and in-place today
Don’t make index rebuilding a “regular maintenance task”
every week
Rebuild only when there is a need, here are some symptoms




Application Response Time for a given query using a said index
access is unacceptable
There is trace data to corroborate that Index I/O is the key issue
The number of blocks physically read is a significant number when
compared to the total number of rows returned
Clustering Factor of the index is bad and the query performs well
defined range scans.
95
Join Strategies
The normal joins



Nested Loops
Sort-Merge
Hash
Hash Joins are great for the following scenario



Table A (small, say with 1000 rows)
Table B (big, say 1,000,000 rows)
SQL has a WHERE clause predicate that processes
most of Table B
96
Managing Database Latching
A latch is a piece of code run within the Oracle
executable where access is serialized


With a few exceptions, one and only one process can run
this code at any given time
Examples :
Reading a block in the DB Cache
Writing a block in the DB Cache
Moving blocks in the LRU List
Hard parsing a SQL statement
Soft parsing a SQL statement
Allocating space in the redo log buffer
Writing redo entries in the redo log buffer
97
Managing Database Latching
Wasteful demand for latches in the database can be
usually managed by the following




Reducing logical I/O demand for SQL
Avoiding unnecessary updates to tables
Designing and coding SQL that use bind variable
Using SESSION_CACHED_CURSORS
98
Some low-hanging fruit in SQL
Land
SQL-intensive code in PL/SQL
Compute-intensive code in Java/C/C++
Don’t code SQL and PL/SQL like Pascal

Replacing simple PL/SQL (if…then…else..end if) with DECODE
when possible
Reduce the number of context switches between SQL and
PL/SQL

Design your code to do as much in SQL
Use Bind Variables


Hard-coding values usually is a bad practice
Prior to 9i, you may use this to force the optimizer to use histograms
99
Some low-hanging fruit in SQL
Land
Analytical functions may look complicated, but can provide
very powerful programming capabilities
Making the code in triggers just a call to a procedure

Put all the code in the procedure
Use X$DUAL with a view on top of it instead of DUAL
Use Set Operators (MINUS, INTERSECT)


After all, we are still a relational database
Equivalent Functionality of an Outer-join between Table A and Table
B implemented using the MINUS set operaiton
Select blah1, blah2 from A
MINUS
Select blah3, blah4 from B; -- What is in A that is NOT in B
100
Some low-hanging fruit in SQL
Land
Indexes on foreign key columns for master-detail
transactional tables
Using non-unique indexes for UNIQUE constraints


Helps if you turn constraints ON and OFF
With this, the index is NOT dropped
Using the NOVALIDATE option with ENABLE of a
constraint
Creating hash clusters with 1 table

For read-only tables that are “look-up tables”
101
Great literary works on good
application design and coding
Tom Kyte’s “Effective Oracle by Design”
John Beresniewicz’s “Oracle Built-In Packages”
Steven Feuerstein’s “Oracle PL/SQL Programming &
Oracle PL/SQL Best Practices”
Guy Harrison’s “High Performance Tuning”
102
Oracle Instance
Configuration &
Optimization
Back to the Basics – Why are we
optimizing the Instance?
What are the wait events in your database?
Are you suffering from CTD?
Are you running your life on cache-hit ratios?
Is it time for a drink or two?
Friendly Advice - Don’t touch anything without
corroborating wait events that warrant a
configuration change
104
Oracle Architecture Overview
System Global Area
Database Buffer Cache
PMON
L
Main
R
U
Aux.
SMON
Dnn
Misc. Buffers
Repl.
Dirty
Shared Pool Area
RECO
Dictionary
Cache
SNPnn
Server Process
Redo
Log
Buffer
Snn
Misc. Queues
Pnn
Bootstrap Segment
Library
Cache
LCKnn
PGA
DBWR
CKPT
LGWR
ARCH
User Process
/u01
/u02
/u03
DATA
INDX
SYSTEM
/u04
RBS
/u05
TEMP
/u06
/u07
LOG1A
LOG1B
/u08
LOG2A
/u09
LOG2B
/u03
CNTRL1
/u04
CNTRL2
/u10
Archived
Redo
Logs
$ORACLE_
HOME/dbs
initSID.ora
105
What happens when you press
“Enter”?
SQL Statement Processing





Parse
Define
Bind
Execute
Fetch (For SELECT only)
106
SQL Statement Processing Parse
Steps in Parsing






Syntax Check
Object Resolution
Security Check
Build Parse Tree & Execution Plan
Store Parse Tree & Execution Plan in the Shared SQL
Area (Lib. Cache)
In 9i and up, bind variable value peeking is performed
Hard vs. Soft Parse
107
SQL Statement Processing Define
Resolve and map data types on the client and the
server
SQL*Net is involved
ARRAYSIZE negotiated
108
SQL Statement Processing - Bind
All bind variables(:v_id, :v_name etc.) are bound with
their current values
SQL is not hard-parsed for varying values
Facilitates re-use of SQL
109
SQL Statement Processing Execute
Execution Plan is applied on the SGA
Data is read or written to
If SQL is DML then
Redo & Rollback is generated;
Transactional operations are performed;
End If;
110
SQL Statement Processing Fetch
Data is fetched for SELECTs only
Number of fetches is subject to ARRAYSIZE

ARRAYSIZE has a direct impact on the amount of logical
I/O in your database
Perform array fetches and PL/SQL array processing when
possible


SQL*Plus - SET ARRAYSIZE n
Forms - Set the Records Fetched Block Property Sheet
111
Memory Allocation &
Management
Configure no more than 50% of the memory on the box to all
Oracle SGAs


In 10g, there will be an equivalent for the SGA too
The feature is called Auto SGA Tuning
Lock the memory for the SGA


This slows the paging daemon, utilizes less resources
Platform specific – MLOCK_SGA = TRUE or equivalent
Account memory for the filesystem buffer cache and the
operating system
Give the rest for Oracle PGAs


If 9i and up use PGA_AGGREGATE_SIZE
For prior releases use reasonable sort and hash area sizes
112
Memory Allocation &
Management
SQL lives in the Shared Pool


Configure SHARED_POOL_SIZE
Configure SHARED_POOL_RESERVED_SIZE for
systems with large PL/SQL packages
Java in the database lives in the Java Pool

Configure JAVA_POOL_SIZE
If using MTS, RMAN or Parallel Query

Configure LARGE_POOL_SIZE
All of this is automatically managed in 10g
113
Memory Allocation &
Management
Data lives in the database buffer cache
Configured with DB_BLOCK_SIZE and the various
DB_*CACHE parameters


Controls the size of a database block size
Every database should be at least 8K
Larger blocksizes provide



~40% increase query performance
Shorter Indexes
More data in each block
Use the formula DB_BLOCK_SIZE = F-S blocksize >= O-S
pagesize

This affects not only memory but also how I/O is performed
Buffer cache management has changed in 8i
114
Memory Allocation &
Management
Change journals in the database are first recorded to
the redo log buffer

-
-
Configure LOG_BUFFER
Size is based on the waits, bigger is definitely
not better
512K - 1Mb. is good for most environments
Badly sized redo log files can sometimes reflect
in waits for redo log buffer space allocation
115
Design your database for
performance
Pick the right database block size, in 9i there is support
for multiple block size
Block-level storage configuration


INITRANS, MAXTRANS, PCTUSED, PCTFREE, FREELISTS AND
FREELISTS GROUPS
Try Automatic Segment Space Management (ASSM)
in 9i – Given the lack of maturity, there are bugs
Adequate freelists and freelists groups need to be configure
for “concurrent” insert-intensive tables
In 8i and up use only locally-managed tablespaces


Extent management, space management is easier
Your INSERT statements will incur less “recursive SQL” for space
management
116
Design your database for
performance
Use locally-managed temporary tablespaces

Remember the files show up in DBA_TEMP_FILES
Use Global Temporary Tables for time-sensitive reporting

This will prevent tons of unnecessary redo generation
If you monitor the filesystem space, make your tablespaces
AUTOEXTEND
Avoid the RAID5 storage configuration for write-intensive
applications


RAID 10 is the preferred and cheaper option in the long run
Check out BAARF
117
Design your database for
performance
Use Automatic Undo Management from 9i and up
Prior to 9i, avoid using OPTIMAL for rollback
segment configuration

Disks are cheap, ORA-1555s and burning CPU cycles
are not
Remember, latch contention is caused due to
inefficient application design


Latches serialize code access within the Oracle
executable
Any contention for latches usually indicate bad SQL
118
Design your database for
performance
The main CPU consumers within your Oracle
database are


Logical I/O
Parsing
Reducing logical I/O positively affects query
performance, resource contention and performance
scalability
Reducing logical I/O will have the single-most
positive effect on response time
119
Thanks & References – Sorted in
Ascending Alphabetical Order of Last
Name
Steve Adams
John Beresniewicz
Wolfgang Breitling
Dave Ensor
Tim Gorman
Kyle Hailey
Anjo Kolk
Tom Kyte
Jonathan Lewis
Connor McDonald
Cary Millsap
James Morle
Mogens Norgaard
Craig Shallahamer
And many more in the OakTable (http:www.oaktable.net)
120
Conclusion
Oracle Performance Tuning is not wizardry or witchcraft
Response time and business drivers are key to any
performance optimization engagement
If you go after the “wait events” you will be successful in
unearthing the bottlenecks
Laundry list tuning that use a plethora of cache-hit
ratios is a waste of time
Oracle 10g makes the life of a DBA easier, but don’t worry
your job is still pretty secure
Don’t let CTD get the best of you
Good luck and may the force be with you!!
121