Performance Tuning Methods

Download Report

Transcript Performance Tuning Methods 

Performance Tuning Methods
Reactive Tuning at its Best
Author:
Vladimir Andreev
Semantec GmbH
Semantec GmbH
Benzstr. 32
D-71083 Herrenberg, Germany
www.semantec.de
Semantec Bulgaria OOD
Hubcha Str. 8
1618 Sofia, Bulgaria
Lector:
Stoyan Ivanov
Semantec Bulgaria OOD
About us

More than ten years of Oracle-related
experience




Application design and development
Database administration
Performance tuning
Performance analysis
2
Developers and DBAs

Developers:


Control the design and implementation
Work in a sandbox






No danger
Low data volumes
Low concurrency
Different platform
Incomplete system architecture
Work on a schedule
3
Developers and DBAs

DBAs:


Have no control over the design and
implementation
Deal with production systems




Controlled changes
High availability requirements
Real user and data loads
Have tasks with higher priority than performance



Availability
Maintenance
End user support
4
Tuning Modes

Proactive




Is planned
Low time pressure
Any methods and tools
are allowed
No scope or target

Reactive





Cannot be planned
High time pressure
Methods constrained by
live data and packaged
application
Scope limited to a single
(group of) problems
A target always exists
5
The Plan for Success



Must be simple and straightforward
Must lead to fast solutions
Must cover all possible cases
6
The Plan for Success
1.
2.
3.
4.
Reproduce the problem
Define the target
Find the bottleneck component
If the bottleneck happens to be in the
database, use response time analysis
to find the reason
7
Things to do first

Reproduce the problem

Trust the users for


Do not trust the users for





Effects of the problem
Reasons for the problem
Candidate solutions
Scale down, but carefully
Define the target
Keep baselines
8
Find the bottleneck component

Tier Walk




Start from the end-user perspective
Try to decompose the Application Response Time into time
spent at all individual tiers
Concentrate on the tier where most time is spent
Inside Out


Server_Idle_Time = User_Think_Time +
Client_Processing_Time
User Think Time can be ignored:


When tuning batch jobs without user interaction
By limiting the observation period to exclude user interaction
9
Tier Walk
Client Machine
Formatted
page
Click
Database Server
Web Server
Declare … begin pkg.proc(param); … end;
SQL*Net packets
SQL*Net packets
<html>…</html>
http://server/pls/dad/pkg.proc?param
Browser
Oracle Client
<html>…</html> (in a PL/SQL table)
Web Server Machine
10
Inside Out

Benefits


Client applications are generally not instrumented
The Oracle Database Server has an excellent set of statistics:




Tools

V$ Views (and StatsPack)






Complete
Standardised
Fairly well documented
V$Session (Logon_Time)
V$Session_Event (Event, Time_Waited)
V$Session_Wait (Seconds_In_Wait)
V$SesStat (‘CPU used by this session’)
STATS$Idle_Event (Event)
SQL_Trace


Event 10046, Level 12
tkprof, version 9.0 and above
11
Response Time Analysis

Basics





System-Level



Response Time = Service Time + Wait Time
Service Time = CPU time
Wait Time = Idle Time + Non-Idle Time
Non-Idle Time = I/O Waits + Other Waits
Aggregated statistics prevent accurate analysis in multi-session
environment
Maybe useful for overall performance analysis, but not for reactive
tuning
Session-Level


Reflect a linear (non-parallel) process
Allow the tuner to ignore the other processing taking place in the
server
12
OK, it is the database:
CPU

CPU-Bound

Logical reads



(Re-)Parsing




Inefficient SQL
Inefficient Application Design
SQL with literals
Shared Pool Size
Invalidation
PL/SQL


Deterministic functions
Tools: DBMS_Profiler
13
OK, it is the database:
Disk I/O

I/O-Bound

Inefficient SQL + inadequate buffer sizes





Table and index scans
Disk sorts
Hash Joins
Inefficient Application Design: heavy client
(or PL/SQL) processing
Hot disks
14
Network I/O
Communication step
Network wait event
The server waits for the client to send
a request
SQL*Net message from client
The client submits a request, which is
split into several pieces by SQL*Net
and the first piece is sent to the server
The server waits for the next piece of
the request
SQL*Net more data from client
The server assembles the request,
processes it, and sends the first chunk
of the results using send()
SQL*Net message to client
The server sends subsequent chunks
of the result using send()
SQL*Net more data to client
After the last chunk is sent, the server
starts waiting for the next request
SQL*Net message from client
15
OK, it is the database:
Contention

Contention

Latches and locks




Shared pool
Library cache
Cache buffer chains
Hot blocks



Segment headers (freelists)
Rollback segment headers
Leading block of an index
16
Want to know more?
Company:
Names:
Address:
Semantec GmbH., Semantec Bulgaria OOD
Vladimir Andreev, Stoyan Ivanov
Hubcha Str. 8
1618 Sofia, Bulgaria
+359(2)9603911
+359(2)9603921
+359(2)9603946
[email protected]
[email protected]
Internet: www.semantec.de
Telephone:
Telephone:
Fax:
E-Mail:
17