8/4/2019 36849_Kolk

1/36

Copyright 2002 VERITAS Software Corporation. All Rights Reserved. VERITAS, VERITAS Software, the VERITAS logo, and all other VERITAS product names and slogans are trademarks or registeredtrademarks of VERITAS Software Corporation in the US and/or other countries. Other product names and/or slogans mentioned herein may be trademarks or registered trademarks of their respective companies.

8/4/2019 36849_Kolk

2/36

Speaker Name

Speaker TitleVERITAS Software

8/4/2019 36849_Kolk

3/36

OraPerf.com:

Real Life Performance Data

8/4/2019 36849_Kolk

4/36

History ofOraPerf.com

Started in December 1998, in the basement

Cable modem introduction

Redhat Linux

2 CPU (366 Mhz Celeron, over clocked) Based on YAPP

Initially supported only Utlbstat/Utlestat

8/4/2019 36849_Kolk

5/36

YAPP

OraPerf.com is based on YAPP

YAPP = Yet AnotherPerformance ProfilingMethodology

Developed in 1996 as reaction to the Checklist/Ratio tuning

Solved a real (large) customer performanceproblem, and they still use it today.

8/4/2019 36849_Kolk

6/36

So what is YAPP?

Basically (Response) Time based tuning (ortaking a Holistic View of your system)

R = C + W

R = (Response) Time

C = CPU/Service Time

W = Wait Time

The basic idea is to look at all the time that is

spent inside the database.

8/4/2019 36849_Kolk

7/36

So what is YAPP?

Checklist tuning is basically going down your listof ratios:

Buffer cache hit ratio

Latch miss ratio

Parse/execute ratio, etc.

Ratios should never be used as the starting pointof your tuning process!

And what are good percentages for your ratios?

8/4/2019 36849_Kolk

8/36

Use the expert guidelines: > 94 97% in Oracle Applications (Source: Oracle)

> 80% (UNIX files) or > 90% (raw devices) (Source:Oracle)

> 95% online day and > 85% batch (Source: Gurry &Corrigan)

> 99.99999% (Source: Richard Niemic)

So even the experts dont agree!

Oracle Buffer Cache Hit Ratio

8/4/2019 36849_Kolk

9/36

End User focus

End users complain about Response time problems

Throughput problems

These should be used as the starting point ofyour tuning process, not a bad ratio

8/4/2019 36849_Kolk

10/36

Improving TCO

Another reason for starting the tuning processcan be to reduce the Total Cost ofOwner ship ofa system

End users are happy, but can we provide the same

performance/functionality with less? Ratios can never help here.

Looking at where you spend your time, you can

8/4/2019 36849_Kolk

11/36

Why Utlbstat/utlestat?

Run utlbstat and some time later utlestat toproduce a file called report.txt

Shipped with Oracle Release

utlBstat Begin Statistics

utlEstat End Statistics

Many DBAs use this to monitor their database(s)

But .

Dont know how to interpret report.txt

8/4/2019 36849_Kolk

12/36

Problems with Utlbstat/Utlestat

Create/drop tables

Doesnt support OPS/RAC

Only Instance statistics

Not all interesting statistics are collected andreported

8/4/2019 36849_Kolk

13/36

Interesting stats that are missing

For Example: SQL statements

Buffer gets

Disk reads

Parse calls Executes

Version count

Enqueue stats

Detailed latch statistics

8/4/2019 36849_Kolk

14/36

STATSPACK since 8.1.6

Much better than utlbstat/utlestat, but requires abit more administration.

The report/file generated contains much moreinfo, but like report.txt it is difficult to read (filecan be large)

So lets sign up with OraPerf.com and have thefile analyzed.

8/4/2019 36849_Kolk

15/36

OraPerf

Logon or Register and then logon. After login on,you will enter your personal workspace.

8/4/2019 36849_Kolk

16/36

OraPerf

Click on the

BR

OWSE button to start browsing forthe file that you want to upload.

Then click on OPEN.

8/4/2019 36849_Kolk

17/36

OraPerf

If the file has been located, hit the UPLOADFILE(S) button.

One can take the default settings of the form, but

incase the file is not recognized set the file type(check file type) or the version of STATSPACKthat is used (not the RDBMS version).

8/4/2019 36849_Kolk

18/36

8/4/2019 36849_Kolk

19/36

8/4/2019 36849_Kolk

20/36

Oracle Versions Uploaded

0

10

20

30

40

50

60

70

80

Aug-02 Sep-02 Oct-02 Nov-02 Dec-02 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03 Aug-03

7.3

8.0

8.1

9.0

9.2

8/4/2019 36849_Kolk

21/36

Is STATSPACK getting popular?

0

10

20

30

40

5060

70

80

Aug-02 ep-02 Oc -02 No -02 ec-02 Jan-03 Feb-03 a -03 Ap -03 a -03 Jun-03 Jul-03 Aug-03

Bs a s a

a spack

8/4/2019 36849_Kolk

22/36

8/4/2019 36849_Kolk

23/36

Symptom: Too many Logical I/O

Logical I/O or buffer get Relation between logical I/O and CPU usage

Buffer cache hit ratio

(1 (physical reads/(db buffer gets + consistent gets)) Too many logical I/O will give a good buffer

cache hit ratio

Reduce number of executes or logical I/Os per

execute High logical I/O count means normally inefficient

SQL

8/4/2019 36849_Kolk

24/36

From DUAL

Many selects from DUAL CURVAL, NEXTVAL from SEQUENCES

PL/SQL related functions

SYSDATE

Interesting constructs

where rownum = 1

8/4/2019 36849_Kolk

25/36

Symptom 2: Too Slow Physical I/O

Disk capacity increases rapidly Number of random I/O operations doesnt

increase as quickly

Full seek time is biggest component

Prices of disks keep dropping

Disk Array with large Cache

8/4/2019 36849_Kolk

26/36

Symptom 2: Too Slow Physical I/O

More cost effective to buy large disks So database size roughly decides the number of

disks needed

OLT

Pdatabases should be sized based on thenumber of reads/writes per transaction

Total number of reads + writes will decide thenumber of disks needed

8/4/2019 36849_Kolk

27/36

Symptom 2: Too Slow Physical I/O

Heavily accessed data files should be stripedover enough disks

Caching data is important, but where

Oracle buffer cache

File System buffer cache

Disk array cache

8/4/2019 36849_Kolk

28/36

Average Read Time

Single block I/O Average 1 10 msec

Maximum 500 msec

Multi block I/O

Average 1 50 msec

Maximum 500+ msec

Direct Path I/O

Average 1 50 msec Maximum 500+ msec

8/4/2019 36849_Kolk

29/36

Symptom 3: Latch

Synchronization means Latch contention

Enqueue contention

Always a symptom

SLEEPS column in V$LATCH indication ofcontention

Generally Oracle will sleep 1 centi second

FasterCPU means that we spend a lot of timewaiting

So either dont spin at all or spin more

8/4/2019 36849_Kolk

30/36

Symptom 3: Latch contention

Every logical I/O will result in a latch operation Cache buffer chain latch get

So many latch gets and sleeps on this

Latch could be a symptom of too many buffergets (logical I/Os)

Symptom of inefficient SQL

8/4/2019 36849_Kolk

31/36

Symptom 3: Latch contention

For Example Symptom of Hard parsing (not sharing SQL)

Soft parsing (re parsing the same SQL statementsover and over again)

Session_cached_cursors init.ora parameter

Too many Logical I/Os

Hard parsing

Cursor_sharing init.ora parameter (> 8.1.5)

Exact, force, similar

8/4/2019 36849_Kolk

32/36

8/4/2019 36849_Kolk

33/36

OraPerf.com now part VAN!

Veritas Architect Network The Online Community for IT Professionals

Faster and more reliable access

Current members can still use OraPerf.com

Only logon information will be used by Veritas.

New members sign up under the VERITAS online PrivacyPolicy and Terms ofUse.

http://oraperf.veritas.com

Checkout Veritas Indepth forOracle, for better ApplicationTuning. OraPerf is basically high level instance tuning.

8/4/2019 36849_Kolk

34/36

8/4/2019 36849_Kolk

35/36

ANSWERSQUESTIONS

8/4/2019 36849_Kolk

36/36