Enjoy Craigs presentation!

But remember that his presentation was created to be delivered by Craig and in a conference presentation situation. That is, it was not created to be read like a book. Here are options to get more information and enhance your career: If you have questions about the presentations, feel free to email Craig at [email protected]. If you want to read Craigs Oracle Performance Firefighting Book, it is available in both PDF and print format at OraPub.com. If you would like to join one of Craigs Oracle performance courses, we invite you to browse our training web-site at Training.OraPub.com. He teaches multiple times each year a locations around the world! We hope you enjoyed Craigs presentation! All the best in your Oracle performance endeavors! The OraPub Team.

11/10/11

History of Oracle Performance Analysis

Craig A. Shallahamer Founder - OraPub, Inc. [email protected]

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

OraPub is about Oracle performance

OraPub is all about Oracle performance management; systematic and quantitative firefighting and predictive analysis. Web site started in 1995 and the company was founded in 1998 by Craig Shallahamer. OraPub has always been about disseminating Oracle database centric technical information. Consulting, training, books, papers, and products are now being offered. We have been on-site in 24 countries and our resources have been received in probably every country where there are DBAs.

Resources
Training Unique Blog Free Tools Free Papers Books Products Consulng
History

11/10/11

Short resume...kind of...

Studies economics, mathematics, and computer science at university in California, US. Started working with Oracle technology in 1989 as a Forms 2.3 developer on Oracle version 5. Soon after started performance firefighting...daily! Co-found both Oracles Core Technology and System Performance Groups. Left Oracle to start OraPub, Inc. in 1998. Authored 24 technical papers and worked in 24 countries. Authors and teaches his classes Oracle Performance Firefighting, Adv Oracle Performance Analysis, and Oracle Forecasting & Predictive Analysis. Authored the books, Forecasting Oracle Performance and Oracle Performance Firefighting. Oracle ACE Director. Frequent blog contributor: A Wider View
History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

My two books...
OraPub discount code: OTN35
History

11/10/11

The progress has been amazing

When it all started...
to disk rao = 24350 / 573721 = 0.042 Sort Thats not good. The rao needs to be closer to 0.010. Lets increase the sort area and recycle the instance. Performance should be beer... I hope.

What we can do today...

SQL statement is really slow and weve got a This CPU boleneck. If we reduce the logical IOs down

to 100 for this parcular SQL statement, its elapsed me will drop from 30 seconds down to 2 seconds, the me to process a logical IO will decrease by 23%, and the overall me (CPU and wait) between 1pm and 2pm will drop from 12 hours down to 8 hours.

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

11/10/11

SQL> l 1 select a.value/(a.value+b.value) Ratio 2 from v$sysstat a, 3 v$sysstat b 4 where a.name = 'sorts (disk)' 5* and b.name = 'sorts (memory)' SQL> / RATIO -------- 0.00105 1 row selected.

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Like diagnosing a child...

History

11/10/11

Let the book writing begin

The original Bee Book was one of the first Oracle performance books. It focused on SQL tuning and ratio analysis. It was a milestone and a benchmark for other books.
History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Getting some perspective

Oracle couldnt get by some SGA latching issues that were caused when I put the SGA over 1M... Oracle support consistently told me that NOBODY needs an SGA over 1M. Rich Niemeic, Oracle Support in 1987 The speed of the hardware was less than my PC. Mark Gurry, relang to the late 1980s They told me they never added any indexes so they dont have a lisng for that but, could I help them anyway? I said: Yes, I am quite sure that I can help you. Rich Niemeic, talking with a prospect Oracle was not viewed as a serious database for mission crical systems, yet we were given applicaons to implement with hundreds of users which was unheard of at the me. Mark Gurry, about a late 1980s project
History

11/10/11

Tuning tips...really?

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

...but users dont feel a ratio

History

11/10/11

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

History

11/10/11

Oracle speaks to us!

Through instrumentaon, Oracle tells us when it waing for something other than consuming CPU.

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Example of Oracle instrumentation

[oracle@fourcore ~]$ ps -eaf|grep oracleprod18 oracle 19759 19750 1 11:10 ? 00:00:01 oracleprod18 (DESCRIPTION=. . . . . . [oracle@fourcore ~]$ strace -p 19759 . . . gettimeofday({1273083131, 538145}, NULL) = 0 readv(11, [ {"\6\242\0\0\260B\17\1\25z\352\1\0\0\1\6G\32\0\0\1\0\0\0\352F\1\0\322x\352\1"..., 8192}, {"\6\242\0\0\261B\17\1\25z\352\1\0\0\1\6\315]\0\0\1\0\0\0\352F\1\0\324x\352\1"..., 8192}, {"\6\242\0\0\262B\17\1\25z\352\1\0\0\1\6\360\"\0\0\1\0\0\0\352F\1\0\325x\352\1"..., 8192}, {"\6\242\0\0\263B\17\1\25z\352\1\0\0\1\6\253\336\0\0\1\0\0\0\352F\1\0\327x\352\1"..., 8192}, {"\6\242\0\0\264B\17\1\25z\352\1\0\0\1\6\243\231\0\0\1\0\0\0\352F\1\0\330x\352\1"..., 8192}, {"\6\242\0\0\265B\17\1\25z\352\1\0\0\1\6\325\t\0\0\1\0\0\0\352F\1\0\331x\352\1"..., 8192}, {"\6\242\0\0\266B\17\1\25z\352\1\0\0\1\6`3\0\0\1\0\0\0\352F\1\0\333x\352\1"..., 8192}, {"\6\242\0\0\267B\17\1\25z\352\1\0\0\1\6\332'\0\0\1\0\0\0\352F\1\0\334x\352\1"..., 8192}], 8) = 65536 Gettimeofday({1273083131, 538458}, NULL) = 0

O11.2 linux

538458-538145 = 313s

History

11/10/11

An example of a sleeping process

[oracle@fourcore spin_classes]$ ps -eaf|grep oracleprod18 . . . oracle 24477 24476 72 13:58 ? 01:22:03 oracleprod18 . . . . . . [oracle@fourcore spin_classes]$ strace -rp 24477 Process 24477 attached - interrupt to quit . . . 0.000034 select(0, [], [], [], {0, 10001}) = 0 (Timeout) 0.011137 gettimeofday({1264117983, 18827}, NULL) = 0 . . . 0.000034 select(0, [], [], [], {0, 10001}) = 0 (Timeout) 0.010299 gettimeofday({1264117983, 112927}, NULL) = 0 . . . 0.000030 select(0, [], [], [], {0, 10001}) = 0 (Timeout) 0.011160 gettimeofday({1264117983, 245021}, NULL) = 0 . . . 0.000048 select(0, [], [], [], {0, 10001}) = 0 (Timeout) 0.010456 gettimeofday({1264117983, 271123}, NULL) = 0 . . . 0.000084 select(0, [], [], [], {0, 10001}) = 0 (Timeout) 0.011098 gettimeofday({1264117983, 589336}, NULL) = 0 . . .

11gR2 Linux
History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

A platform independent perspective

SQL> @swpctx Remember: This report must be run twice so both the initial and final values are available. If no output, press ENTER twice. Database: prod3 28-FEB-09 04:48pm Report: swpctx.sql OSM by OraPub, Inc. Page 1 System Event CHANGE (blue line) Activity By PERCENT Time Waited % Time Avg Time Wait Wait Event (sec) Waited Waited(ms) Count(k) ----------------------------- ----------- -------- ---------- -------- db file scattered read 57.090 52.69 2.8 20 read by other session 41.150 37.98 14.1 3 latch: cache buffers chains 1.340 1.24 5.4 0 control file parallel write 0.770 0.71 70.0 0 log file sync 0.660 0.61 110.0 0 log file parallel write 0.490 0.45 49.0 0 db file sequential read 0.090 0.08 8.2 0 latch: cache buffers lru chain 0.020 0.02 0.6 0 latch free 0.000 0.00 0.0 0 db file parallel write 0.000 0.00 0.0 0 direct path write 0.000 0.00 0.0 0

History

11/10/11

But there is more than wait time

SQL> select distinct session_state 2 from v$active_session_history; SESSION_STATE ------------- ON CPU WAITING 2 rows selected.

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

10

11/10/11

Repeatedly looking at sessions

SQL> / . . . SAMPLE_ID TO_CHAR(SAMPLE_TIME, SESSION_ID SESSION EVENT ---------- -------------------- ---------- ------- -------------------40698803 2011-Nov-07 10:54:56 11 WAITING read by other sessio 40698803 2011-Nov-07 10:54:56 13 WAITING latch: cache buffers 40698803 2011-Nov-07 10:54:56 1513 WAITING latch: cache buffers 40698803 2011-Nov-07 10:54:56 1517 ON CPU 40698803 2011-Nov-07 10:54:56 1520 WAITING latch: cache buffers 40698804 2011-Nov-07 10:54:57 11 ON CPU 40698804 2011-Nov-07 10:54:57 13 ON CPU 40698804 2011-Nov-07 10:54:57 1513 ON CPU 40698804 2011-Nov-07 10:54:57 4530 ON CPU 40698804 2011-Nov-07 10:54:57 1517 ON CPU 40698804 2011-Nov-07 10:54:57 1520 ON CPU 40698804 2011-Nov-07 10:54:57 10 ON CPU 40698804 2011-Nov-07 10:54:57 4527 WAITING read by other sessio 40698805 2011-Nov-07 10:54:58 11 WAITING read by other sessio 40698805 2011-Nov-07 10:54:58 13 ON CPU 40698805 2011-Nov-07 10:54:58 1513 ON CPU 40698805 2011-Nov-07 10:54:58 1517 ON CPU . . . SQL> l 1 select sample_id,to_char(sample_time,'YYYY-Mon-DD HH24:MI:SS'), 2 session_id, 3 session_state, 4 event, 5 sql_id 6 from v$active_session_history 7* order by sample_id SQL_ID ------------- a3gxuy8hqucpt b3xamffks401k b3xamffks401k b3xamffks401k b3xamffks401k a3gxuy8hqucpt b3xamffks401k b3xamffks401k b3xamffks401k b3xamffks401k b3xamffks401k 4fp8mxuut85g4 b3xamffks401k a3gxuy8hqucpt b3xamffks401k b3xamffks401k b3xamffks401k

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

A simple system RTA report

SQL> @ashrt 15 % Database: prod3 20-FEB-09 06:08pm Report: ashrt.sql OSM by OraPub, Inc. Page 1 ASH Response Time Analysis Report (last 15 min, SQL_ID=%) % Service Time % Queue Time % Qt IO % Qt Other (cpu) (wait) (wait) (wait) -------------- ------------ --------- ----------- 36.55 63.45 95.29 4.94 Activity Detail % Time -------------------------------------- ------- db file scattered read 39.54 CPU 36.78 read by other session 20.23 latch: cache buffers chains 2.99 4 rows selected.
History

11

11/10/11

Whats the IO SQL?

SQL> @ashsqlpcte 15 db%scat Database: prod3 20-FEB-09 06:08pm Report: ashsqlpcte.sql OSM by OraPub, Inc. Page 1 System Level ASH: Find SQL by event (last 15 min) % Time Wait Event SQL ID ADDRESS HASH_VALUE Waited ----------------------------- ------------- -------- ---------- ------- db file scattered read 3r5xuxmggzwt8 5516AE70 3741315880 31.76 db file scattered read ajgxt6x8dmsay 55159240 1356456286 14.71 db file scattered read fp67qwqz44m2m 550554C4 3192015955 3.53 db file scattered read 0fbbnwzj80b0q 5504BADC 3800050710 2.94 db file scattered read 22zj7nt1bv1qc 54FA504C 1119717068 2.35 db file scattered read dmbxunrzpcj92 54FDA630 4283843874 2.35

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Whats the IO SQLs profile?

SQL> @ashrt 15 3r5xuxmggzwt8 Database: prod3 20-FEB-09 06:09pm Report: ashpct.sql OSM by OraPub, Inc. Page 1 ASH Response Time Analysis Report (last 15 min, SQL_ID=3r5xuxmggzwt8) % Service Time % Queue Time % Qt IO % Qt Other (cpu) (wait) (wait) (wait) -------------- ------------- -------- ------------ 5.45 94.55 96.15 4.00 Activity Detail % Time -------------------------------------- ------- db file scattered read 50.00 read by other session 40.91 CPU 5.45 latch: cache buffers chains 3.64 4 rows selected.
History

12

11/10/11

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

May of 2001

Wrien in October of 2001 for IOUG.

History

13

11/10/11

Getting still closer to the user

While still not true end-to-end time, it is a significant step forward. Notice the similarities with Operations Research.
History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

One way to format a TBA

Fireghng Diagnosc Template

History

14

11/10/11

Total time, over an interval(s)

Time related to all the work occurred for a specific period of time.
Perhaps 1.8M block reads occurred during this interval.

Non-Idle Wait Time

Other IO Srvr Prc BG Prc

CPU Time

Source: Confio Softwares Igniter product. History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

There exists a relaonship between workload intensity and me. time workload intensity

History

15

11/10/11

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

July 2009

History

16

11/10/11

Modeling elapsed time

E = units of work x time per unit
E (time/exec) = WL(work/exec) x RT(time/work)

More? Blog; Ancipang SQL Elapsed Times

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Abstracting a workload is like relating...

weather by inches of rain

strength by age and sex

re wear by miles driven

History

17

11/10/11

Representing time associated with a single unit of work.

Suppose over a one hour interval there is a massive OS IO read boleneck, 1.8M block reads occurred, and there was 3500 seconds of associated CPU and non-idle wait me. Therefore, on average, each PIO took 1.94 ms to complete.
History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Contrasting RT based approaches

Response Time (ms/PIO)

Total Response Time 3500s

Non-Idle Wait Time 1500s CPU Time 2000s

Focus: This is what occurred. This is what we should do.

Workload (PIO/ms)

Focus: This is what we can expect this soluon to do. This is the elapsed me change.
History

18

11/10/11

Reality check: IO constrained systems

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Production System: CPU constrained

source: http://aberdave.blogspot.com/2011/03/response-time-analysis-based-on-awr.html History

19

11/10/11

Gaining a deeper understanding

CBC Latches 256 SQL Stmt Instance Instance Median SQL RT WL Change Change Samples Elapsed (ms/lio) (lio/ms) Time (s) 0.03623 - 120 227 14.224 2.968 - -79.13% 243 399

32768 0.00856 -76.36%

Collecon interval was 2 hours. OraPubs Elapsed Time Sampler was used to collect elapsed mes. RT components gathered from v$sysstat, v$sys_me_model, and v$system_event. Time based on mestamp data type.
source: E Analysis 1a.xlsx, not tuned History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Visually showing the impact

+96% WL Change -76% RT Change

source: More Latches RT Compare...xlsx History

20

11/10/11

The history of Oracle performance analysis

Rao Analysis Wait Analysis 1980 1990 2000 2010

SQL Tuning

Time Based Analysis Sampling Based Analysis Unit of Work TBA

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

So whats next?
SQL statement is really slow and weve got a This CPU boleneck. If we reduce the logical IOs down

What we can do today...

to 100 for this parcular SQL statement, its elapsed me will drop from 30 seconds down to 2 seconds, the me to process a logical IO will decrease by 23%, and the overall me (CPU and wait) between 1pm and 2pm will drop from 12 hours down to 8 hours.

With the unicaon of Unit of Work Time Based Analysis and Operaons Research, we are nally in the posion to create automated and powerful predicve tools.
History

21

11/10/11

History of Oracle Performance Analysis

Craig A. Shallahamer Founder - OraPub, Inc. [email protected]

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

Want to dig deeper?

Craigs Blog A W i d e r V i e w Training from OraPub Oracle Performance Firefighting (I) Adv Oracle Performance Analysis (II) Oracle Forecasting & Predictive Analysis Books

Santa Clara February 2012

Santa Clara November Oracle Performance Firefighting (C. Shallahamer) 2011 !!

History

22

11/10/11

History of Oracle Performance Analysis

Craig A. Shallahamer Founder - OraPub, Inc. [email protected]

History

2011 OraPub, Inc. - Craig Shallahamer make this presentation on Wednesday, November 9, 2011 at the NoCOUG 100th meeting in Silicon Vallley area.

23