Extreme performance tuning
1 / 113


  • Updated On :

EXTREME PERFORMANCE TUNING. GAME PLAN FOR SUCCESS. Chris Lawson. Agenda. What is Extreme Performance Tuning? Seven attributes of Extreme Perf Tuning Chris’ favorite tuning techniques Some pitfalls to avoid. When it doesn’t work— the missing piece of the puzzle. A Key Question.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'EXTREME PERFORMANCE TUNING' - lona

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Extreme performance tuning




Chris Lawson


  • What is Extreme Performance Tuning?

  • Seven attributes of Extreme Perf Tuning

  • Chris’ favorite tuning techniques

  • Some pitfalls to avoid

When it doesn’t work—

the missing piece of

the puzzle

A key question
A Key Question

After you attend a seminar on performance tuning, do you discover that you have become a performance expert?

Performance tuning is not
Performance Tuning is Not:

  • Having really tricky scripts

  • Running colorful programs

  • Delivering long reports full of generalities

  • Being smarter, faster, better-looking than other DBAs

The paradox of performance tuning
The Paradox of Performance Tuning

  • Being extremely good means being adept in multiple areas

  • A single strength will not work.

  • Example: New DBA uses tool (e.g., OEM) to analyze performance

  • Q: What will be the likely outcome?

The 7 facets of extreme performance
The 7 Facets of Extreme Performance

Logical Reasoning

Good Judgment


Hands-on Experience




Q which ones are taught in seminars
Q: Which Ones are Taught in Seminars?

Logical Reasoning

Good Judgment


Hands-on Experience




Only these are taught in class
Only These are Taught in Class

Logical Reasoning

Good Judgment


Hands-on Experience




Let s examine the 7
Let’s Examine the 7

  • Good Judgment

  • Logical Reasoning

  • Good Techniques

  • Scripts

  • Tools (maybe)

  • Focus

  • Hands-on Experience

Strategy for success
Strategy for Success

Logical Reasoning

Good Judgment



Hands-on Experience




Good judgment thinking clearly
Good Judgment: Thinking Clearly

Difficult to explain—it’s a sound approach to gray areas

Q: How do you spot this in candidates?

I don’t mean I.Q.

“The man who was too smart to talk to”

Good Judgment

Good judgment thinking clearly1
Good Judgment: Thinking Clearly

  • Easier to spot bad judgment than good

  • Bad judgment stands out

Good Judgment

Tale of the “No-good Nested Loops”

Good judgment may be hidden bad judgment stands out
Good Judgment May be Hidden;Bad Judgment Stands Out

“The Thrill of Thrill Hill”

The result . . .

Good Judgment ?

Good Judgment

  • Firms want people with good judgment, but how do you measure?

  • Common sense reasoning maybe not so common?

  • Some examples of smart people using not-so-smart judgment

A Class for Good Judgment?

  • Realistically, I probably can’t teach you good judgment.

  • Is there some class to attend, to teach you to not do certain things, like these:

Common sense reasoning not so common4
Common Sense Reasoning not so Common . . .

NOT PG&E employees

Note parallelism

Degree = 3

Common sense reasoning not so common5

On-Call DBA

Common Sense Reasoning not so Common . . .

DBA of the day

DBA in training

Strategy for success1
Strategy for Success

Logical Reasoning

Good Judgment


Hands-on Experience






Doggedly pursue the root cause

Losing focus some distractions
Losing Focus: Some Distractions

  • Pet ideas—maybe RAC or Streams?

  • To redesign the application—let’s make it more “elegant”

  • Trying to solve problem in general

  • My favorite: Avoid solving root problem by blaming others

“The burnt-up power box”

An unhelpful tactic
An Unhelpful Tactic

When my application runs slowly, likely root cause is found in:

  • My app,

  • Database issue, or

  • Somebody else's’ app

“Those other guys are slowing the disk!”

Staying on focus is hard work
Staying on Focus is Hard Work

It’s tempting to switch to generalities. Benefit:

  • You don’t need to know much.

  • Avoids hard analysis.

  • Everybody knows what “Add CPUs” means.

    Cary Milsap calls this using the “aggregate”

Avoid the dark side aggregate method
Avoid the Dark Side“Aggregate” Method

  • Avoiding hard work of detailed analysis is immensely appealing.

  • It’s widespread because anyone can become an expert

  • You “fix” lots of problems at once!

  • Solution usually ”Add more hardware.”

    But it only works for a while . . .

Focus stay on target
Focus: Stay on Target!

  • Home-in on the exact problem & fix

  • “Micro” not “Macro.”

  • Solutions are directly applicable to the problem at hand, so…

  • You can estimate improvement

  • It will often be a huge pickup

Strategy for success2
Strategy for Success

Logical Reasoning


Good Judgment


Hands-on Experience




This is a huge deal
This is a Huge Deal …

Similar to staying focused

My definition:

“An inductive process of working from facts to conclusion.”

Logical Reasoning

Barriers to logical reasoning
Barriers to Logical Reasoning

  • Presupposing form of the answer

  • Pet solutions

Logical Reasoning

“Must be the db writer..”

A logical process physician to magician
A Logical Process:“Physician to Magician”

  • State problem

  • Gather facts

  • Identify bottleneck

  • Create solution

  • Document & Declare Victory

Logical Reasoning

Strategy for success3
Strategy for Success

Logical Reasoning

Good Judgment


Hands-on Experience





There really isn t a substitute
There Really Isn’t a Substitute

“We don’t want DBAs with OCP!”

(esp. if in bold red on the resume)

Hands-on Experience

An important question
An Important Question

  • Why is experience so important?

  • What does it achieve?

Why is experience so critical
Why is Experience So Critical?

  • Save time by skirting crazy ideas.

  • Reduces scope of possible solutions

  • Know how Oracle works in practice

  • Pattern recognition: “Aha—I’ve seen that one before!”

  • Gain credibility with your customers.

  • Inspired by other ideas you’ve seen.

You can t transfer experience
You Can’t Transfer Experience

“Spend 5 minutes & tell me what you do and …”

Hands-on Experience

A big turn off the hard work
A Big Turn-off: the Hard Work

Expert: 10,000 hours of deliberative practice

Hands-on Experience

Talent Is Overrated

By Geoff Colvin

Strategy for success4
Strategy for Success

Logical Reasoning

Good Judgment


Hands-on Experience




Helpful in some cases
Helpful in Some Cases


  • A matter of personal preference--could be useful.

  • Caution: Tool may direct your effort: “OEM says …”

  • Pitfall: Never gain competence ?

A useful analogy >

Doctor versus dba what do you want in a doctor
Doctor versus DBA:What do YOU want in a Doctor?

  • Pleasant?

  • Nice Office?

  • Cheap?

My thoughts
My Thoughts

  • Takes the job seriously

  • Listens to what you say

  • Understands the problem

  • Has expertise in the field

  • Knows practical solutions

  • How often is he sued?*

* Courtesy Dan Grey, PG&E

Do you ask the doctor this
Do You Ask the Doctor This?

“What brand of stethoscope do you use?

Strategy for success5
Strategy for Success

Logical Reasoning

Good Judgment


Hands-on Experience





Scripts are important


Scripts Are Important

  • Ten DBAs > 10 different scripts.

  • There usually isn’t just “1 way.”

  • Your process should drive your scripts, not the other way around.

  • “Roll your own” scripts or borrow ideas from others

Be open to different methods
Be Open to Different Methods

  • There’s more than 1 way to solve a problem.

  • There’s tons of smart people here. Learning from others is better than formal classes—it’s supremely practical.

  • It’s hard for anyone to be an expert at more than 1 thing.

  • Watch for people who have a better way at doing something and copy them!

Script ideas
Script ideas

  • Most of my scripts are from others

  • Other ideas came from user groups and forums, etc.

  • One idea (following) came from a beginner with almost no experience!

What i learned from a newbie object waited on
What I Learned from a NewbieObject Waited-on

SeleCT DISTINCT v$session.module, Sid, username,

Substr(program,1,19) PROG , sql_text,


From V$Session, V$Sql, dba_objects o

Where v$session.status = 'ACTIVE'

And username is not null

and o.object_id = row_wait_obj#

And v$session.sql_hash_value = hash_value

and v$session.sql_address = v$sql.address

and username <> 'SYS'

The reality of scripts
The Reality of Scripts

“Good scripts, by themselves

do not a performance expert make.”

Strategy for success6
Strategy for Success

Logical Reasoning

Good Judgment



Hands-on Experience




Aha good techniques
Aha! Good Techniques

  • Absolutely critical

  • This is where we can learn from others

  • White papers, seminars

  • Pretty close to experience


Ten DBAs will use 10 different techniques


But—not all techniques are created equal.

Here are some sub-optimum Techniques


Bad techniques
Bad Techniques

When Driving forklift, do not drive near edge of loading dock.

Bad techniques1
Bad Techniques

Do not drive car into power lines

Bad techniques2
Bad Techniques

Think carefully when putting name of company on van.

Common sense reasoning not so common8
Common Sense Reasoning not so Common . . .

Not the greatest choice of names

Develop your own techniques
Develop Your Own Techniques

Which method should I use?

Your Way May Be the Best!

“That’s not the way Tom does it!”

Aha chris ten techniques
Aha! Chris’ Ten Techniques

Here are 10 techniques I use

You will have others of course


Technique 1 use simple rules of thumb
Technique #1: Use Simple Rules of Thumb

Q: What are some simple metrics that you like to use?

Develop rules of thumb
Develop Rules of Thumb

  • My favorite: Single-block read rate


    round(100*total_waits/time_waited) rate

    from v$system_event

    where event like '%db file sequential read%'

    order by 1

  • Samples from unrelated databases:

    200, 320, 125, 307, 472, 154

Some rules of thumb i use
Some Rules of Thumb I Use

SQL ordered by Cluster Wait Time

  • On RAC, I expect about 15% overhead due to cluster.

  • You can see this in AWR reports: SQL ordered by Cluster Wait Time

Other rac rules of thumb
Other RAC Rules of Thumb

  • On RAC, I expect to see a fast interconnect between nodes.

  • Script “node_times.sql” calculates how fast nodes communicate.

  • Uses very simple internode event called 'gc cr grant 2-way‘

  • I found that 1 ms internode rate is typical.

Sample rac node rate
Sample RAC Node Rate


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

30131 5 14-oct-10-23:00 24935961 1.5

30147 5 15-oct-10-15:00 1276505 1.1

30148 5 15-oct-10-16:00 2415870 1.3

30216 5 18-oct-10-12:00 1370563 1.5

30240 5 19-oct-10-12:00 615880 1.4

30241 5 19-oct-10-13:00 584385 1.1

30242 5 19-oct-10-14:00 1184510 1.3

30248 5 19-oct-10-20:00 2043124 1.6

30249 5 19-oct-10-21:00 6142631 .9

Reference node times sql
Reference: Node_Times.sql

WITH BASE AS (SELECT instance_number, SNAP_ID, TOTAL_WAITS, time_waited_micro/1000 timemsec,

LAG(time_waited_micro/1000, 1, 0) OVER (ORDER BY snap_id) AS PREV_TIME_MSEC,

LAG(total_waits, 1, 0) OVER (ORDER BY snap_id) AS PREV_waits

FROM dba_hist_system_event

WHERE event_name ='gc cr grant 2-way'

and instance_number = 5

and snap_id > 30130)

SELECT b.SNAP_ID, b.instance_number NODE,

to_char(begin_interval_time, 'dd-mon-yy-hh24:mi') BEG,



dba_hist_snapshot S

where b.instance_number = s.instance_number

and b.snap_id = s.snap_id

and (total_waits-prev_waits) > 99900


Technique 2 map complex joins
Technique # 2: Map Complex Joins

A VERY powerful technique

Graph complex joins how
Graph Complex Joins: How?

  • List the tables in the join

  • Show join condition

  • Note size of table

  • Note how selective the filters are

Thanks to Daniel Tow, of “Singing Sql” who introduced me to this method 13 years ago.

Map complex joins
Map Complex Joins

Table 2

Table 1

Join conditions

Table 3

Table 4

Table 5

Table 6

Sample 3
Sample 3

I cover this in detail in The Art & Science of Oracle Performance Tuning

Technique 3 verify parallel processing
Technique # 3 Verify Parallel Processing

Please Don’t “bolt-on” parallelism

“But the cost is less if I use

“default” parallelism.”

Consider why you are using parallelism
Consider WHY You are Using Parallelism

Is this what I intended?

Is this what i intended
Is This What I Intended?

Are slaves doing multi-block or single-block reads?

For Reference:The Parallel Slave Script (part 1)

column child_wait format a30

column parent_wait format a30

column server_name format a4 heading 'Name'

column x_status format a10 heading 'Status'

column schemaname format a10 heading 'Schema'

column x_sid format 9990 heading 'Sid'

column x_pid format 9990 heading 'Pid'

column p_sid format 9990 heading 'Parent'

column program format a12

break on p_sid skip 1

set linesize 200

For reference the parallel slave script part 2
For Reference:The Parallel Slave Script (part 2)

select x.server_name , x.pid as x_pid , x.sid as x_sid,

w2.sid as p_sid , v.osuser , v.schemaname , program , w1.event

as child_wait, w2.event as parent_wait

from v$px_process x , v$lock l, v$session v

, v$session_wait w1 , v$session_wait w2

where x.sid <> l.sid(+)

and to_number (substr(x.server_name,2)) = l.id2(+)

and x.sid = w1.sid(+) and l.sid = w2.sid(+)

and x.sid = v.sid(+) and nvl(l.type,'PS') = 'PS'

and x.status not like 'AVAIL%'

and w2.event not like 'SQL*Net%‘ order by 1,2

Technique 4 get familiar with wait events
Technique # 4 Get Familiar with Wait Events

  • Sequential versus Scattered reads

  • Log sync

  • SQL*Net message from client

  • [RAC] gc current request

  • Esp. the top summary in AWR

Use ash to find wait events for a specific time
Use ASH to Find Wait Events for a Specific Time

With P1 As (Select /*+Parallel(a 6) */

Distinct Sample_time, Session_id, Sql_text, Event, Instance_number, Blocking_session

From Dba_hist_active_sess_history A,

V$sqltext B Where A.Sql_id = B.Sql_id

AND Sample_time Like '30-SEP-09 10.52%AM'

And Piece = 0

) Select Instance_number Ins,session_id, SAMPLE_TIME, Sql_text, Event, Blocking_session

From P1 Order By 3

  • AWR is an aggregate and gives summary.

  • ASH is great for nailing specific, brief issue.

  • Drawback: 10 minute runtime on big db.

Technique 5 find bind variables
Technique #5Find Bind Variables

You don’t need to guess.

Find bind variables
Find Bind Variables

Select INST_ID, c.name||'/'||c.value_string bind_var

from GV$sqlarea a, dba_users b,

v$sql_bind_capture c

where b.user_id=a.parsing_user_id

and b.username != 'SYS‘ and c.address=a.address

and a.sql_id = [enter]

  • We often need to know typical values the user sets for a bind variable.

  • Useful for testing a performance solution

  • For historical, use DBA_HIST_SQLBIND

Find bind variables1
Find Bind Variables

Actual case:

Sql looks good, but

always scans all partitions.

Technique 6 understand stored outlines
Technique # 6 Understand Stored Outlines

Another extremely powerful technique

Example: Critical Sql has changed exec plan in production. It’s vendor code.

What are you going to do?

Stored outlines how they work
Stored Outlines: How They Work

A stored outline preserves an execution plan:

  • Oracle captures the hints that ensure a particular plan.

  • When that exact sql is run in the future, Oracle applies those sql hints to keep same exec plan.

  • Outlines are stored in 3 tables.

A powerful trick with stored outlines
A Powerful Trick with Stored Outlines

  • What if you don’t want to preserve a plan—you want a different plan?

  • The scheme:

    • Create outline of exact sql;

    • Use sql hint to create another outline with plan you want;

    • Trick Oracle to use the new exec plan even when we don’t supply the sql hint.

For reference stored outline detail
For reference: Stored Outline detail*

  • Turn on stored outline gathering.

  • Run Sql. Then run 2nd sql with hint added.

  • We now have 2 stored outlines:

    No hint >> Oracle uses Outline 1 (bad plan)

    With hint >> Oracle uses Outline 2 (good plan)

  • Reverse hints so that Oracle will apply Outline 2 when it sees the sql without the hint.

  • Update Outln.Ol$hints << this table has the hints

    Set Ol_name =

    Decode(ol_name, One, Two, Two, One)

*Thanks to Simon Leung for this tip!

Technique 7 become adept w sql hints
Technique #7 Become Adept w/Sql Hints

Q: Why do we ever need to use sql hints?

Some hints i use a lot
Some Hints I Use a Lot









Technique 8 don t blindly apply oltp ideas
Technique # 8Don’t Blindly Apply OLTP Ideas

  • Indexing strategies for OLTP are not appropriate for large batch jobs.

  • “Always use index” is not a reliable maxim for batch jobs.

  • “Row by row” processing is often a disaster.

  • Consequence of wrong exec plan with batch job is catastrophic.

  • Large batch jobs often need a sql hint.

Technique 9 don t presuppose form of solution
Technique #9 Don’t Presuppose Form of Solution

  • This is very common, & impedes entire process.

  • Similar error: Assuming problem even exists.

  • Adding to confusion: Problem descriptions are often inaccurate, or really a conclusion, not an observation:

“You need more indexes”

“We are having contention.”

“We’re out of memory.”

“It is a capital mistake to theorize in advance of the facts.”

Sherlock Holmes, A Scandal in Bohemia

Case presupposed solution wastes several days at a publisher
Case: Presupposed Solution Wastes Several Days at a Publisher

  • Scenario: Unknown performance problem on critical database.

  • DBA advised: “Don’t spend any time checking out the network. We already know it’s fine.”

  • The real bottleneck was . . .


Case 2 presupposed solution more threads are better
Case 2: Presupposed Solution: More Threads are Better!

  • Scenario: Payment processing job uses 4 threads, but still slow.

  • Without identifying actual bottleneck, threads set to 10.

  • New run time Same as Before!

  • Reason: This solution addressed transactions that only accounted for 10% of runtime.

  • Real bottleneck was a single Insert statement that didn’t use the multiple threads.

Technique 10 insist on root cause before solution
Technique # 10 Insist on Root Cause Before Solution

  • When a solution is proposed, this suggests that the root cause has been identified.

  • So ask (nicely),

    “What problem does this fix?”

Wait i ve forgotten something
Wait—I’ve Forgotten Something!

I’m missing a critical component in performance tuning

What is missing
What is Missing!


Logical Reasoning

Good Judgment


Hands-on Experience




A clue what saved 3 lives
A Clue: What Saved 3 Lives?

  • April 14, 1970, Apollo 13 explosion.

  • Problem: No more air scrubbers.

  • Result: Crew will soon die a slow and painful death--poisoned by carbon dioxide.

  • Your Mission: Devise an air scrubber using simple materials.

  • Timeline: A day or so.

The pep talk
The Pep Talk

Gene Kranz: “Failure is not an option.

This crew is coming home.

Now get going!”

Can we use our extreme strategy
Can We Use our “Extreme” Strategy?

  • Tools: No way.

  • Logic: Helpful.

  • Judgment: Helpful, but inadequate.

  • Techniques: None

  • Scripts: None

  • Focus: Helpful, but inadequate.

  • Hands-on Experience: Never done before.

A bizarre contraption saves lives
A “Bizarre Contraption” Saves Lives

Bill of Materials:

A bunch of cardboard

(1) plastic bag,

(1) air hose,

(1) sock,

+ some duct tape.

Extreme performance tuning1
Extreme Performance Tuning

Logical Reasoning

Good Judgment


Hands-on Experience






  • Without creativity, we are “dull people in a dull profession.”

  • A “thought experiment” to foster creativity: Pretend people will die

  • Ask, “Isn’t there anything you can do?”

The un-Innovative Innovation magazine

Don t be a dullard
Don’t Be a Dullard!

DBA taking class at OpenWorld

[“before” picture”]

This DBA didn’t have any creativity

After following chris performance tuning program
After Following Chris’ Performance Tuning Program

“After” picture

Look what happened!

Focus case study getting some breathing room
Focus Case Study: Getting Some “Breathing Room”

  • Daily inventory report: 48 hours.

  • Root cause: Running 5 million Sql

  • Director: “Fix it today!”

  • Short-term answer: Change report-server parameter to run 20 threads

  • Result: 2 hour runtime.

  • Everyone kept their jobs.

Let s test your creativity
Let’s Test Your Creativity

  • Scenario: Critical batch job to company needs to finish faster.

  • Runs 1 million sql via one session.

  • Bottleneck is mostly disk.

  • No time to redesign program.

  • Pretend people will die unless you fix it.

  • What can you do?

Just run super boost

  • Just warm up the cache

  • Used 25 threads to touch the blocks that will be needed shortly.

  • Got about 50% pickup in performance.

Summary the art science of oracle performance tuning
Summary: The Art & Science of Oracle Performance Tuning

  • Extreme Performance tuning demands good analytical skills—“left brain” thinking.

  • It also requires excellent “right-brain thinking”

“It’s almost all art!”

-- Brian Keating, independent consultant

Thank you for attending
Thank You for Attending!

Extreme Performance Tuning: The Greatest Job on Planet Earth

[email protected]