Oracle Tuning
Ashok Kapur Hawkeye Technology, Inc.
Agenda
Oracle Database Structure Oracle Database Access Tuning Considerations Oracle Database Tuning Oracle Tuning Tools
06/14/2002
Hawkeye Technology, Inc.
Oracle Database Structure
06/14/2002
Hawkeye Technology, Inc.
Logical Structure
06/14/2002
Hawkeye Technology, Inc.
Logical Structure
Tablespaces
SYSTEM RBS or UNDO TEMP User Data and Indices
Schema Tables and Indices Segments (Data, Index, RBS, Temp) Extents
06/14/2002 Hawkeye Technology, Inc. 5
Physical Structure
06/14/2002
Hawkeye Technology, Inc.
Physical Structure
Database
Data Parameter Files
Instance
Processes Memory Structures
06/14/2002
Hawkeye Technology, Inc.
Physical Database
Data Files Redo Log Files Control Files Parameter File Archive Log Files
06/14/2002
Hawkeye Technology, Inc.
Parameter File
Read at instance startup Specifies values for various system wide parameters Size parameters
SGA, db buffer, sort area size,
Tuning parameters
06/14/2002 Hawkeye Technology, Inc. 9
Database Instance
Processes
Process Monitor (PMON) System Monitor (SMON) Database Writer(s) (DBWRx) Recovery (RECO) Archiver (ARCx) Checkpoint (CKPT) Log Writer (LGWR) Server Processes .
Memory Structure
System Global Area (SGA) Program Global Area (PGA)
06/14/2002 Hawkeye Technology, Inc. 10
Memory Structure
SGA
Database buffer cache Redo log buffer Shared Pool
Shared SQL Area PL/SQL Procedures and Packages Dictionary Cache
PGA
Heap Sort Area
06/14/2002 Hawkeye Technology, Inc. 11
Oracle Database Access
06/14/2002
Hawkeye Technology, Inc.
12
Database Connection
Sql*Net Listener Server Process
06/14/2002
Hawkeye Technology, Inc.
13
Data Access
Access data via SQL or PL/SQL Processing of SQL in three phases:
Parse Execute Fetch
Execution plan depends on the optimizer mode:
Role based Cost based
06/14/2002
Hawkeye Technology, Inc.
14
Tuning Considerations
06/14/2002
Hawkeye Technology, Inc.
15
Tuning Considerations
OLTP vs DSS Response Time vs Throughput Response Time = Service Time + Wait Time Tuning Tradeoffs
Affect of tuning on rest of the system
Frequency of use
06/14/2002 Hawkeye Technology, Inc. 16
Special Tuning Considerations
Oracle Parallel Server Multi-Threaded Server Replication Distributed Queries
06/14/2002
Hawkeye Technology, Inc.
17
Consider All Options
Business Rules Database Design and Application Design System Architecture Network: LAN and WAN System: Hardware and Operating System Application Tuning Database Tuning
06/14/2002 Hawkeye Technology, Inc. 18
Oracle Database Tuning
06/14/2002
Hawkeye Technology, Inc.
19
Proactive vs Reactive
Proactive Tuning
Balance IO Distribution Manage Object Extents Manage batch jobs Manage long running and poorly tuned queries Database parameter review and tuning React to a trouble report or performance problem
Reactive Tuning
06/14/2002 Hawkeye Technology, Inc. 20
Tuning Steps
Identify and define the problem Set clear and measurable tuning goals Take initial performance measurements Iteratively apply tuning changes and measure performance on TEST system Document ALL tuning changes After goal is reached, apply changes to PRODUCTION.
06/14/2002 Hawkeye Technology, Inc. 21
What Is Tunable in Oracle
File placement DB Block Size SGA Size Listener setup Init Parameters Table/tablespace mapping Table/Index parameters RBS/Undo parameters Other Features: OPS, Replication, MTS,
06/14/2002 Hawkeye Technology, Inc. 22
How to Tune
SQL Statements
Can they be rewritten for better data access? Review Execution plan for use of Indexes
Resource Contention
RBS or Undo CPU Memory IO Network
06/14/2002 Hawkeye Technology, Inc. 23
How to Tune
RBS or Undo Contention
Add more Rollback Segments Change RBS segment size
CPU Contention
Tune SQL Distribute workload to off-peak hours Add more CPU
Memory Contention
Change SGA size Change PGA size Consider Multi-Threaded Server Config
06/14/2002 Hawkeye Technology, Inc. 24
How to Tune
IO Contention
Moving Data files to balance IO Moving Tables to different Tablespace to balance IO Adding/Removing Indexes Change table structures: going to Partioned tables Maybe consider Tuning SQL Review fragmentation including chained/migrated rows
Network Contention
Adding LAN/WAN resources Modifying Listener configuration Review application design
06/14/2002 Hawkeye Technology, Inc. 25
Oracle Tuning Tools
06/14/2002
Hawkeye Technology, Inc.
26
Oracle Tuning Tools
Explain Plan SQL Trace
Session Level System Level
Dynamic Performance Views
V$SYSSTAT V$SESSSTAT V$WAITSTAT V$SGASTAT V$ROLLSTAT V$LATCH V$LIBRARYCACHE V$SORT_USAGE V$SQLAREA
06/14/2002
Hawkeye Technology, Inc.
27
Oracle Tuning Tools
UTLBSTAT and UTLESTAT STATSPACK Oracle Enterprise Manager Performance Tool Vendors
Quest Software Precise .
06/14/2002 Hawkeye Technology, Inc. 28
Contact Information
Ashok Kapur Hawkeye Technology, Inc. 561-236-4204 afkapur@hawkeyetechnology.com http://www.hawkeyetechnology.com
06/14/2002
Hawkeye Technology, Inc.
29
