Rivenes - Oracle Workload Measurement (slides) by rocker12

Oracle Workload Measurement Andy Rivenes (andy@appsdba.com) AppsDBA Consulting Hotsos Symposium 2005

2/7/2005

Slide 1

Introduction • Performance tuning – System level or session/process? • At the system level we can measure workload. • This presentation will explore what you can do with system level information and why it’s NOT performance tuning. 2/7/2005

Slide 2

Informal Survey • I conducted an informal survey from the Oracle-L list and comp.database.oracle archives over the last year. • I looked for anything that involved a conflict/question about system level performance tuning. • There were some passionate disagreements. 2/7/2005

Slide 3

Informal Survey (ii) • There seem to be two camps. On one side we have those that argue that tuning begins with system level info (e.g. statspack reports, v$sysstat, v$system_event). • The second camp says that tuning starts with the actual process that is slow. Any system problems will be discovered as an outcome of the processes resource profile. 2/7/2005

Slide 4

Survey Results (i) • Oracle-L list

2/7/2005

Slide 5

Survey Results (ii) • comp.databases.oracle

2/7/2005

Slide 6

Rates and Capacity • Capacity is the maximum amount of service that a system or resource can perform. • Rates are the measurement of work being performed.

2/7/2005

Slide 7

Response Time • Reponse time is the measure of the duration of some action. • We’ve been told that response time = service time + wait time. • At the system level we have many “actions” taking place. • At the system level, response time is really elapsed time. • Wait time is just excess capacity. 2/7/2005

Slide 8

What is System Performance? • System – My definition: Entire database server -including all hardware, OS, and peripherals • Performance – “response time and throughput that meets desired or acceptable levels [Menasce and Almeida (2000), 216] • Since response time is the direct measure of some action, then we cannot measure a “system’s” response time. • However we can measure a system’s throughput. 2/7/2005

Slide 9

Measuring Workload • Workload – The “rates” of work being performed. • Two types of basic rates in Oracle – CPU rates – File I/O rates

2/7/2005

Slide 10

Measurement Accuracy & Usefulness • CPU measurements seem to be the most problematic. • Accuracy is not as critical since this is not response time optimization. Workload measurement deals with aggregates. • OS statistics can provide an excellent accuracy check. 2/7/2005

Slide 11

Tools • A good tool will … – ignore waits at the system level – be interval based, using deltas between intervals

• What’s wrong with Statspack? – Nothing – Ignore all sections that include system level waits • Top 5 Timed Events • Wait event sections

2/7/2005

Slide 12

Tools (ii) • AWR (10g) – Measures statistics and timed events – Time model statistics • Attempts to measure session level service and wait time in session time lines.

• Other commercial and open source tools are available. 2/7/2005

Slide 13

CPU Utilization • v$sysstat – “CPU used by this session” • Measures user mode CPU usage • Service time from a session’s perspective • CPU usage supports the execution of work in Oracle (e.g. LIO, PL/SQL and Java code). 2/7/2005

Slide 14

CPU Utilization Trend Database CPU Utilization 80

Average Utilization

50 DB CPU Avg(%) OS Usr CPU Avg(%)

9/ 8/ 20 04 9/ 10 /2 00 4 9/ 12 /2 00 4 9/ 14 /2 00 4 9/ 16 /2 00 4

9/ 4/ 20 04 9/ 6/ 20 04

9/ 2/ 20 04

8/ 17 /2 00 4 8/ 19 /2 00 4 8/ 21 /2 00 4 8/ 23 /2 00 4 8/ 25 /2 00 4 8/ 27 /2 00 4 8/ 29 /2 00 4 8/ 31 /2 00 4

Dates Observed

2/7/2005

Slide 15

CPU Interval Usage CPU Elapsed Total CPU Parse CPU Recursive CPU Other CPU DB CPU Interval Date Time(Sec) Time(Sec) Time(Sec) Time(Sec) Time(Sec) Avg Util(%) -------------- ------------ ----------- ----------- --------------- ----------- ----------6/23/2004 0:50 28,904 8611.06 3.74 22.36 8584.96 29.79 6/23/2004 1:50 28,920 8469.10 3.92 20.39 8444.79 29.28 6/23/2004 2:51 28,904 8457.01 2.38 19.71 8434.92 29.26 6/23/2004 3:50 28,416 8240.44 1.97 19.18 8219.29 29.00 6/23/2004 4:50 28,896 8062.04 1.36 18.13 8042.55 27.90 6/23/2004 5:50 28,904 8038.07 0.96 17.80 8019.31 27.81 6/23/2004 6:50 28,896 8038.57 0.96 16.04 8021.57 27.82 6/23/2004 7:50 28,416 7909.93 1.10 16.42 7892.41 27.84 6/23/2004 8:50 28,904 8082.88 1.08 16.33 8065.47 27.96 6/23/2004 9:50 28,896 8108.19 1.49 18.99 8087.71 28.06 6/23/2004 10:50 28,912 8116.23 2.37 17.93 8095.93 28.07 6/23/2004 11:51 28,896 8313.63 1.78 19.49 8292.36 28.77 6/23/2004 12:50 28,424 7945.35 1.10 16.14 7928.11 27.95 6/23/2004 13:50 28,928 8082.06 0.96 16.94 8064.16 27.94 6/23/2004 14:50 28,920 9309.38 1.07 16.63 9291.68 32.19 6/23/2004 15:51 29,032 10723.92 1.08 16.59 10706.25 36.94 6/23/2004 16:50 28,480 11275.53 6.84 23.40 11245.29 39.59 6/23/2004 17:50 28,944 27382.99 14.24 17.27 27351.48 94.61 6/23/2004 18:51 28,944 9729.93 1.11 13.77 9715.05 33.62 6/23/2004 19:50 28,424 7824.25 0.89 13.81 7809.55 27.53 6/23/2004 20:50 28,896 7975.59 1.08 14.03 7960.48 27.60 6/23/2004 21:50 28,896 7951.50 0.99 14.25 7936.26 27.52 6/23/2004 22:51 28,896 7945.91 1.01 14.90 7930.00 27.50 6/23/2004 23:50 28,424 7782.37 0.97 14.58 7766.82 27.38

2/7/2005

Slide 16

I/O Rates • Two basic I/O rates: – Database file I/O – Redo log file I/O

2/7/2005

Slide 17

File I/O • Database file I/O – Foreground reads (client processes) – Background writes (DBWR) – Direct reads and writes (sorting, direct I/O)

• Database file I/O comprises the majority of the I/O in a typical Oracle database.

2/7/2005

Slide 18

File I/O Data (Trend) Database File I/O 25,000,000

Database File Blocks

20,000,000

15,000,000 Phys Blks Read Phys Blks Written 10,000,000

5,000,000

8/ 1/ 20 0 8/ 3/ 4 20 0 8/ 5/ 4 20 0 8/ 7/ 4 20 0 8/ 9/ 4 2 8/ 00 11 4 /2 00 8/ 13 4 /2 0 8/ 15 04 /2 0 8/ 17 04 /2 0 8/ 19 04 /2 0 8/ 21 04 /2 0 8/ 23 04 /2 0 8/ 25 04 /2 0 8/ 27 04 /2 0 8/ 29 04 /2 0 8/ 31 04 /2 00 9/ 2/ 4 20 0 9/ 4/ 4 20 0 9/ 6/ 4 20 0 9/ 8/ 4 2 9/ 00 10 4 /2 0 9/ 12 04 /2 0 9/ 14 04 /2 0 9/ 16 04 /2 0 9/ 18 04 /2 0 9/ 20 04 /2 0 9/ 22 04 /2 00 4

Date

2/7/2005

Slide 19

File I/O Data (Throughput) Database I/O Throughput - 6/22/04 4000000

3500000

File System /u17/oradata/PDSA

3000000

/u16/oradata/PDSA

Total Database Blocks

/u15/oradata/PDSA /u14/oradata/PDSA

2500000

/u13/oradata/PDSA /u12/oradata/PDSA /u11/oradata/PDSA

2000000

/u10/oradata/PDSA /u09/oradata/PDSA /u08/oradata/PDSA

1500000

/u07/oradata/PDSA /u06/oradata/PDSA /u05/oradata/PDSA

1000000

/u04/oradata/PDSA /u03/oradata/PDSA /u02/oradata/PDSA

Sum of Phys Blks Read

23:50

22:50

21:50

20:50

19:50

18:51

17:50

16:51

15:50

14:50

13:50

12:50

9:50

11:50

8:50

7:50

10:51

6:50

5:51

4:50

3:50

2:50

1:50

0:51

23:50

22:50

21:50

20:50

19:50

18:51

17:50

16:51

15:50

14:50

13:50

9:50

12:50

8:50

11:50

10:51

7:50

6:50

5:51

4:50

3:50

2:50

1:50

0:51

500000

Sum of Phys Blks Written

Time (Hourly) Data Time

2/7/2005

Slide 20

Redo Log I/O • Redo log file I/O writes. • Records changes to the database. • Foreground processes wait on commits until redo is flushed to disk. • Redo log I/O can artificially heat an I/O subsystem causing hot spots. • Small sequential writes in port specific sized redo blocks. 2/7/2005

Slide 21

Redo Generation History 80,000,000,000 70,000,000,000 60,000,000,000 50,000,000,000 40,000,000,000 30,000,000,000 20,000,000,000 10,000,000,000 0

5/14 - 9/22/04

2/7/2005

Slide 22

Redo Log I/O Data Redo Generation 1800

1600

1400

Totals

1200

1000

Total Redo (MB) Avg redo blks/write Commits

800

600

400

200

21 :5 2 22 :5 3

20 :5 2

19 :5 2

18 :5 2

17 :5 2

16 :5 2

15 :5 3

14 :5 2

13 :5 2

12 :5 2

11 :5 2

9: 52 10 :5 2

8: 52

7: 52

6: 52

4: 52

5: 52

2: 52

3: 52

0: 52

1: 52

Time

2/7/2005

Slide 23

Other Rates • • • •

Commit rates Sorts (disk & memory) User connection rates Memory usage

2/7/2005

Slide 24

Workload Reduction Example • Daily CPU utilization averaging roughly 70%. • Workload a mix of mostly batch loading and reporting. • No complaints about run times or “performance”

2/7/2005

Slide 25

Workload Reduction Example (ii) • A “group” of similar SQL statements was found that updated an informational table. • These SQL statements were “relatively” efficient by themselves. • However, they were being executed thousands of times. • The statements were “tuned” by adding column ordering to two indexes. 2/7/2005

Slide 26

Workload Reduction Example (iii) Total CPU OS Usr OS Usr OS Usr Elapsed Elapsed DB Logical DB CPU DB CPU CPU CPU CPU Date Time(Sec) Time(Sec) Reads Time(Sec) Avg(%) Avg(%) Min(%) Max(%) -------- ---------- ---------- ---------------- ------------ -------- ------- ------- ------08/18/04 86,352 690,816 7,902,803,855 494,594 71.60 68.33 61.00 73.00 08/19/04 86,431 691,448 7,903,129,277 488,810 70.69 67.46 63.00 72.00 08/20/04 86,395 691,160 7,874,800,107 411,003 59.47 57.33 41.00 67.00 08/21/04 86,382 691,056 7,604,332,092 277,520 40.16 40.21 39.00 42.00 08/22/04 86,398 691,184 7,667,811,263 279,899 40.50 40.67 39.00 43.00 08/23/04 86,405 691,240 5,173,811,098 264,530 38.27 37.42 22.00 52.00 08/24/04 86,389 691,112 3,823,823,589 247,723 35.84 34.83 22.00 49.00 08/25/04 86,437 691,496 2,272,546,617 152,783 22.09 22.25 15.00 30.00 08/26/04 86,383 691,064 1,568,055,602 129,397 18.72 18.46 16.00 23.00 08/27/04 86,382 691,056 1,700,447,592 130,179 18.84 18.63 16.00 24.00 08/28/04 86,436 691,488 1,506,743,354 110,404 15.97 16.08 16.00 17.00 08/29/04 86,373 690,984 1,500,723,241 109,477 15.84 16.00 16.00 16.00 08/30/04 86,390 691,120 1,664,563,387 155,658 22.52 22.50 16.00 32.00 08/31/04 86,402 691,216 1,978,556,216 228,044 32.99 33.54 32.00 37.00 09/01/04 86,146 689,168 1,816,337,865 224,887 32.63 33.29 32.00 36.00 09/02/04 86,380 691,040 1,809,854,359 225,660 32.66 33.25 32.00 35.00 09/03/04 86,382 691,056 1,868,711,926 221,125 32.00 32.58 23.00 35.00 09/04/04 86,435 691,480 1,831,948,026 221,945 32.10 32.79 32.00 34.00 09/05/04 86,375 691,000 1,830,712,911 222,928 32.26 33.08 33.00 34.00 09/06/04 86,431 691,448 1,724,152,638 171,116 24.75 25.29 12.00 33.00 09/07/04 86,396 691,168 1,691,018,286 177,717 25.71 25.83 12.00 89.00 09/08/04 86,376 691,008 1,756,856,856 135,388 19.59 19.63 18.00 21.00 09/09/04 86,383 691,064 1,854,180,735 139,994 20.26 20.38 19.00 23.00 09/10/04 86,434 691,472 1,719,237,070 129,735 18.76 18.83 16.00 21.00 09/11/04 86,373 690,984 1,471,529,971 115,685 16.74 16.92 16.00 18.00 09/12/04 86,433 691,464 1,535,727,550 116,771 16.89 17.08 17.00 18.00 09/13/04 86,374 690,992 1,607,176,065 122,982 17.80 17.92 17.00 19.00 09/14/04 86,429 691,432 1,533,241,650 123,720 17.89 18.04 17.00 20.00 09/15/04 86,378 691,024 1,666,641,611 130,282 18.85 19.25 17.00 28.00

2/7/2005

Slide 27

Workload Reduction Example (iv) 8,000,000,000 7,000,000,000 6,000,000,000 5,000,000,000 4,000,000,000 3,000,000,000 2,000,000,000 1,000,000,000 0

80 70 60 50 40 30 20 10 0

Logical Reads

CPU (Avg Util) 7,000,000

25,000,000

6,000,000

20,000,000

5,000,000

15,000,000

4,000,000

10,000,000

3,000,000 2,000,000

5,000,000

1,000,000

Phys Block Reads 2/7/2005

Phys Block Writes

Slide 28

Workload Reduction Example (v) • CPU workload reduced from 70% to 20%. • No noticeable increase in “performance”. • Job times not substantially reduced. • Was there a benefit?

2/7/2005

Slide 29

Limitations • Need to know the types of workload. • Measurement intervals may need to be adjusted. • Utilization targets will be dependent on the type of workload.

2/7/2005

Slide 30

Conclusion • Workload measurement provides input to capacity planning. • Provides the ability to identify and measure change. • Can help show economic benefits from performance tuning or workload reductions. 2/7/2005

Slide 31