Reasons for Poor Performance Programs 60% Design 20% System 2.5% Database 17.5% Source: ORACLE Performance Tuning 1
SMS TOOL DBA Monitoring TOOL Administration TOOL Performance Insight Backup SQL TUNING DBA Capacity Planning Job Control System Job Center POPSQL Console
Tool Tuning * * * Response * *
System DataBase Application Memory Disk I/O CPU
Tuning Tool Tool Methodology : Tuning Area Application(SQL) DBMS System Design Tuning Activity
Implementation Support Feed-back Performance SQL Tuner Support Reporting Supplied Utilities Customization Open Architecture Itself Easy Operation Work Load
Performance System
System Analysis Report; -., Oracle configuration, system resources (CPU, Memory, Disk I/O, Network I/O), SQL and Database design -Performance (tuning hint) - -, Ver4 XML,.
XML - ( ) - (tuning hint)
REPORT TOOL
REPORT TOOL
Bottleneck analysis ( 4 ) Disk I/O CPU Good Balance Network I/O ) Memory Memory OR IST-02002:System overhead by paging increases Paging influences response more than normal condition. CPU Available!! Disk I/O Available!! Network I/O Available!! Memory Full!! IST-02005:Use multi-thread server connections It's possible to reduce paging by using multi-thread server(shared server) which is suitable for many users. IST-02007:Reduce DB_BLOCK_BUFFERS The hit rate of database buffer in peak time is maintained more than 90 %. Taking away paging overhead by reducing DB_BLOCK_BUFFERS(INIT.ORA) takes priority.
- -, Report Capacity Planning Tool Bottleneck Tuning
.!!
Number of SQLs executed and response Number of SQLs executed 200000 180000 160000 140000 120000 100000 80000 60000 40000 20000 0 06- Aug- 00 08- Aug- 00 09- Aug- 00 12- Aug- 00 13- Aug- 00 14- Aug- 00 15- Aug- 00 16- Aug- 00 17- Aug- 00 22- Aug- 00 25- Aug- 00 30- Aug- 00 31- Aug- 00 01- Sep- 00 02- Sep- 00 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 Response coefficien Executed SQL Max. Connection Response ratio ( )
!! Bottleneck chart 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 06- Aug- 00 08- Aug- 00 09- Aug- 00 12- Aug- 00 13- Aug- 00 14- Aug- 00 15- Aug- 00 16- Aug- 00 17- Aug- 00 22- Aug- 00 25- Aug- 00 30- Aug- 00 31- Aug- 00 01- Sep-00 02- Sep-00 ratio
Average ratio of CPU usage User CPU System CPU I/O, IDLE.
Job Class Job Job Class priority Job Job Class =
Message queuing system
Job Console Trouble.
Job Control Schedule Job (Daily, weekly, monthly) Job - Calendar - Calendar ( :, )
SQeeL GUI Web HTML. HTML SQeeL. GUI, CGI. Demand Compile, SQeel Compile. eelvm,. GUI (Web),
Example of SQeeL <eel" arguments resultval "> <eel" global i, j, col_name "> <eel" col_name=keys(resultval[0]) "> <HTML> <HAED> <TITLE>SQeeL demo(result)</title> </HAED> <BODY> <eel" if count(resultval) < 0 "> ERROR = ${resultval} <eel" endif "> <TABLE border="1"> <eel" if count(resultval) > 0 "> <TR> <eel" loop i=0; i<count(col_name); i++ "> <TH> ${col_name[i]} </TH> <eel" endloop "> </TR> <eel" loop j=0; j<count(resultval); j++ "> <TR> <eel" loop i=0; i<count(col_name); i++ "> <TD> ${resultval[j][col_name[i]]}
Console
SQL SCOPE TABLE SCOPE INDEX SCOPE TABLESPACE SCOPE
SQL 3 ( ) 1. SQL 2. SQL ( HEAVY SQL SQL ) 3. --- SQL --- SQL --- SQL
WHERE Full Scan Before SQL tuning
Before SQL tuning Physical read = 2865 blocks
Before SQL tuning Index not used
Edit SQL Index scan After SQL tuning
After SQL tuning Physical read = 4 blocks (2865 blocks before tuning)
After SQL tuning Index in use
migrated row
Fragment Coalesce fragment Coalesce
<mode 1> <mode 2> (1) CPU/Memory Usage (1) CPU/Memory usage (2) Oracle system I/O (2) Oracle system I/O (3) Top five sessions with heavy physical reads (3) Top five sessions with heavy physical read (4) Oracle datafile I/O (5) SQL statement with the heaviest load
<mode 3> <mode 4> (3) Top five sessions with heavy physical read (5) SQL statement with the heaviest load (4) Oracle datafile I/O
Technical Support & Consulting
Oracle database server environment