how to find what is making your oracle database slow

Stop The Guessing! Using Time-Based Analysis to Improve Oracle Performance

By Craig Shallahamer

President & Founder, OraPub

© 2014 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.

Transformyouranalysis

Relevant background info...

• Studied economics, mathematics and computer science at Cal Polytechnic State University San Luis Obispo, California, USA.

• Started working with Oracle technology in 1989 as a Forms 2.3 developer on Oracle version 5.

• Soon after started performance firefighting daily.

• Co-founded both Oracle’s Core Technology and System Performance Groups.

• Left Oracle to start OraPub, Inc. in 1998.

• Authored 24+ technical papers and worked in 31 countries.

• Author two books: Oracle Performance Firefighting and Forecasting Oracle Performance.

• Teaches performance analysis around the world.

• Oracle ACE Director.

• Blogs performance research: A Wider View

https://twitter.com/ioug


http://www.linkedin.com/groups/IOUG-Independent-Oracle-Users-Group-69562/about?trk=anet_ug_grppro


About OraPub Online Institute

Focusing exclusively on Oracle systems performance analysis

• Performance Blog

• Free Tools

• Free Presentations

• Free Papers

• Books

• Consulting

• Training

Resources

Fast-paced one hour seminars

segmented into 8 to 10 digestible modules

for Oracle DBAs and Developers

designed and presented by Craig Shallahamer


How the Guessing Starts

Significant input is required from multiple sources.

So a superior method is required for spot-on diagnosis, individual consistency, and team consistency.


Always remember these four things?

I wish someone told me this years ago...

Insurance

1. Must embrace different perspectives

The OraPub 3-Circle Analysis

Example: The OraPub 3-Circle Analysis

Operating

System

ApplicationOracle

db file seq, 21ms

IO: slow response times (~18ms) on key devicesCPU: 47% busy with run queue < CPU coresMemory: no swapping

PIOR SQL: AC4, CCC

Benefits: The OraPub 3-Circle Analysis

• Speed. It’s applied parallelism! Why tune sequentially when can tune in

parallel? Get everyone involved working towards a common goal.

• Structure. It naturally creates a systematic diagnosis that everyone understands.

• Communication. The natural structure allows for cross-circle communication.

• Confirmation. The multiple perspectives results in a pinpoint diagnosis with

built-in checks.

• Cooperation. Avoids finger-pointing by articulating a common goal where

everyone can contribute to the solution.

• Open. No specific tool, product, or Oracle release is required. The Solarwinds

DPA product naturally embrace OraPub’s 3-Circle Analysis method.

trustworthy,

repeatable,

demonstrable

2. Must be quantitative based

Suppose I need to process 100

pieces of work. Each piece takes

1 second to process. How long

will take take to process all 100

pieces?

3. Understand that ________ is death!

Serialization is death.

ET = Pieces of work X Time to process a piece of work

(understanding elapsed time)

Wall Time = ET / parallelism


4. Tell a compelling story... for your audience


Always remember what four things?


Gathering the diagnostic data

We need correct and trustworthy data before

we can build our framework.

Focusing on the Oracle circle...

The world of Time Based Analysis

» User feel time: OLTP-centric users want snappy response time and batch-centric users want short duration jobs. Therefore, our analysis and methods of communication will be more effective when time based.

» Time Based Analysis:

Combines wait time and CPU consumption

Classifies time to better understand, diagnose, and communicate the performance situation

Allows us to create a quantitative link between the our analysis and the user experience... better understanding the user experience.

Moves us into Unit of Work Time Based Analysis (UOWTBA)

» Keep in mind the relationship between time and work.

Elapsed Time. What is it?

Elapsed Time = CPU Time + Wait Time

Single Serial Session

Time

CPU = 11

Wait = 9

---------

E = 20

Work

300K LIOs

As an Oracle process processes work, it consumes CPU or waits to consume CPU.

Blog search: “rss”

http://youtu.be/aRXMTM-1BfI

Notice: Either CPU or WAIT

The Big Bar − What is in it?

The “big bar” contains all the

CPU consumption and wait time

for all Oracle processes over a

defined time interval.

Bar height slowly increases as

Oracle performs work, one little

piece at a time, consuming CPU

and possibly waiting.

Non-IdleWait Time

CPU Time

Other

IO

Srvr Prc

BG Prc

Time related to all the work occurred for a

specific period of time.

Source: solarwind DPA product.

Perhaps 1.8M PIOs occurred during this interval.

Many “big bars” over a longer period of time


Oracle Process CPU Consumption

An active Oracle process wants to burn CPU!

All Oracle process CPU consumptionover an interval

base view:

v$sys_time_model

DB CPU = 3641

Total CPU = 3641 + 84 = 3725

How does Oracle determine CPU consumption?


Oracle “Wait Time”

When an active process cannot consume CPU, it must wait.

All Oracle process wait time over an interval.

base view:

wait: v$system_event

cpu : v$sys_time_model

NI Wait time = 587 + 565 + 470 + 235 +176 + 180 + other = 1848 sec

How does Oracle determine the wait time?

start_time := get_the_time

Do something...

end_time := get_the_time

wait_time := end_time – start_time

record(wait_time)

Get the time, read, get the time.

start_time := get_the_time

D something...

end_time := get_the_time

wait_time := end_time – start_time

record(wait_time)

$ strace –rp 2518

...

0.000324 clock_gettime(CLOCK_MONOTONIC, {504, 52586559}) = 0


0.000040 pread(257, "\6\242\0\f\0"..., 8192, 427270144) = 8192





0.000040 pread(257, "\6\76 [y\f\0"..., 8192, 427278336) = 8192





0.000040 pread(257, "\6\273\f\0"..., 8192, 427286528) = 8192



Linux, 12.1, seq reads


Building A Diagnostic/TroubleshootingFramework

We need a framework providing consistency, quantitative analysis capabilities with performance improving possibility exposure.

1. What is the OS bottleneck?


Operating system summary: Plenty of capacity

» CPU Subsystem. Average CPU utilization is only 15%.

» Memory. ~30% active. Total: 6G, SGA: 1G, PGA: 0.5G, Other: 0.3GB, No other instance

» IO. Single block reads average 7ms.

» Network. Network team reports no problems and there is no indication of any network problems.


2. Where are the big chunks of Oracle time?


Natural and obvious Oracle time categories...


Thoughtfully fill in the spreadsheet...


The path, the diagnosis, the exposure, the story


Oracle focused opportunities.

» CPU consumption is 68% of DB Time. No obvious Oracle focused solutions.

» Wait Time is 42% of DB Time. It’s all about IO read time. An Oracle focused solution is to increase Oracle caching capability. For example, increasing the buffer cache, using the Keep Pool, using “caching” option.


Oracle focused opportunities.

Limiting data

source to Oracle

views.What are users experiencing?What business processes are slow?What type of SQL contributes to what I see at the OS?What type of SQL correlates with my Oracle time based analysis?

Ask: What type of SQL should I look for?

Situation:

• Oracle CPU consumption is 68% of

DB Time. Look for and tune high

consuming CPU SQL.

• Available CPU, so parallelize long

running CPU intensive SQL.

• High consuming IO read SQL ...

want to reduce IO read

consumption.

• Available IO capacity, so parallelize

long running IO read intense SQL.

CPU/LIO/Buffer Gets

PIO-R

DML

Sorting

Query

Parsing

Long running (elapsed)

...

base views:

v$sql (Statspack),

v$sqlstats

Type of SQL:

Use your tools to quickly find the SQL.

base views:

v$sql (Statspack),

v$sqlstats

Once you know

the type of of SQL

you’re looking for,

Statspack and

AWR will likely

have categorized

the SQL for you,

making it easy to

find the SQL that

warrants your

attention.


Look for reappearing SQL.


The SQL will be there.


Look for the patterns and “popular” SQL.

Sometimes a grid helps to prioritize the SQL we need to focus on.


Create your deliverable using youranalysis method

Notice the “3-Circle

Analysis” structure

is built directly into

the deliverable.

This makes

creating and telling

a compelling

performance story

much easier.


Resource listing

» Presentations: OraPub search, “time analysis”

» Craig’s Blog – Search “time analysis”

» Training from OraPub

Oracle Performance Firefighting (I)

Adv Oracle Performance Analysis (II)

Super Seminars: One day Super Saturdays

» OraPub Online Institute – Any [Device, Time, Location] Tuning Oracle Using An AWR Report

Utilization On Steroids

» Tools at www.orapub.com

OSM Toolkit. OP search, “OSM”.

» Books

Oracle Performance Firefighting.

Forecasting Oracle Performance.

San Jose, CA USA

December 8-12


Special Discount for Solarwinds Webinar Attendees!

GOSOLAR90

90% Off Coupon

First 50 registrations

OOI seminar:

Tuning Oracle Using An AWR Report

Part 1

Email: [email protected]

Twitter: @CShallahamer

OraPub.Com: Everything starts here!

LinkedIn: Connect and network with Craig and the OraPub Group.

Connect with Craig and OraPub.







Using Solarwinds Database Performance Analyzer for Time-based

Analysis

Janis Griffin

Database Performance Evangelist, Solarwinds


Resolve performance issues quickly—free trial

» Try Database Performance Analyzer FREE for 14 days

» Improve root cause of slow performance Quickly identify root cause of issues that impact end-user response

time

See historical trends over days, months, and years

Understand impact of VMware® performance

Agentless architecture with no dependence on Oracle Packs, installs in minutes

www.solarwinds.com/dpa-download/

Questions?



Thank You!

The SOLARWINDS and SOLARWINDS & Design marks are the exclusive property of SolarWinds Worldwide, LLC, are registered with the U.S.

Patent and Trademark Office, and may be registered or pending registration in other countries. All other SolarWinds trademarks, service

marks, and logos may be common law marks, registered or pending registration in the United States or in other countries. All other

trademarks mentioned herein are used for identification purposes only and may be or are trademarks or registered trademarks of their respective

companies.