36849_kolk
TRANSCRIPT
-
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