Extreme performance tuning
This presentation is the property of its rightful owner.
Sponsored Links
1 / 113

EXTREME PERFORMANCE TUNING PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

EXTREME PERFORMANCE TUNING

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

EXTREME PERFORMANCE TUNING

GAME PLAN

FOR SUCCESS

Chris Lawson


Agenda

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

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

Techniques

Hands-on Experience

Scripts

Focus

Tools


Q which ones are taught in seminars

Q: Which Ones are Taught in Seminars?

Logical Reasoning

Good Judgment

Techniques

Hands-on Experience

Scripts

Focus

Tools


Only these are taught in class

Only These are Taught in Class

Logical Reasoning

Good Judgment

Techniques

Hands-on Experience

Scripts

Focus

Tools


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

(1)

Techniques

Hands-on Experience

Scripts

Focus

Tools


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 ?


Extreme performance tuning

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


Common sense reasoning not so common

Common Sense Reasoning not so Common . . .


Common sense reasoning not so common1

Common Sense Reasoning not so Common . . .

Sybase DBA violated SOX


Common sense reasoning not so common2

Common Sense Reasoning not so Common . . .

Sqlserver DBA

QA ?


Extreme performance tuning

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 common3

Common Sense Reasoning not so Common . . .


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

Techniques

Hands-on Experience

Scripts

Focus

2

Tools


Focus keep your eye on the ball

Focus: Keep Your Eye on the Ball

Focus


Focus

Focus

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

(3)

Good Judgment

Techniques

Hands-on Experience

Scripts

Focus

Tools


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

Techniques

Hands-on Experience

(4)

Scripts

Focus

Tools


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

Techniques

Hands-on Experience

Scripts

Focus

Tools


Helpful in some cases

Helpful in Some Cases

Tools

  • 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

Techniques

Hands-on Experience

Scripts

(6)

Focus

Tools


Scripts are important

Scripts

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,

object_name

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

Techniques

(7)

Hands-on Experience

Scripts

Focus

Tools


Aha good techniques

Aha! Good Techniques

  • Absolutely critical

  • This is where we can learn from others

  • White papers, seminars

  • Pretty close to experience

Techniques

Ten DBAs will use 10 different techniques


Techniques

Techniques

But—not all techniques are created equal.

Here are some sub-optimum Techniques

Techniques


Bad techniques

Bad Techniques

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


Common sense reasoning not so common6

Common Sense Reasoning not so Common . . .

Bomb

safety violation ?


Bad techniques1

Bad Techniques

Do not drive car into power lines


Common sense reasoning not so common7

Common Sense Reasoning not so Common . . .


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?


Extreme performance tuning

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

Techniques


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

    select EVENT, TOTAL_WAITS, TIME_WAITED ,

    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

SNAP_ID NODE BEG #WAITS 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,

(TOTAL_WAITS-PREV_WAITS) "#WAITS",

ROUND((TIMEMSEC-PREV_TIME_MSEC)/(.001+TOTAL_WAITS-PREV_WAITS), 1) "RATE" FROM BASE b,

dba_hist_snapshot S

where b.instance_number = s.instance_number

and b.snap_id = s.snap_id

and (total_waits-prev_waits) > 99900

ORDER BY 1


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 1

Sample 1


Sample 2

Sample 2


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?


Extreme performance tuning

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

FULL

LEADING

ORDERED

USE_NL, USE_HASH

PARALLEL, PARALLEL_INDEX,

INDEX_FFS

INDEX

CARDINALITY


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

THE NETWORK!


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

Techniques

Hands-on Experience

Scripts

Focus

Tools


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.


How did the bizarre contraption get built

How Did the Bizarre Contraption get Built?


Missing ingredient creativity

Missing Ingredient: Creativity


Extreme performance tuning1

Extreme Performance Tuning

Logical Reasoning

Good Judgment

Techniques

Hands-on Experience

Creativity

Scripts

Focus

Tools


Creativity

Creativity??

  • 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 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]


  • Login