Oracle Tuning Methodology

The Oracle Tuning Methodology consists of 10 ordered steps. Tune the:

1. Business Rules
   


2. Data Design
   


3. Application Design
   


4. Database Logical Design
   


5. Database Operations
   


6. Access Paths
   


7. Memory
   


8. Physical Database Implementation
   


9. Resource Contention
   


10. Platform
    

Broadly, the steps divide into Application Tuning and Database Tuning. Divide them more specifically into:
Application Tuning = steps 1, 2, 3
Database Design Tuning = steps 4, 5, 6
Server Tuning = steps 7, 8, 9, 10

To tune you must:

1. Establish a benchmark (initial performance profile prior to tuning)
   


2. Define specific tuning goals
   


3. Measure progress towards those goals
   


4. Keep statistics (to create the benchmark & measure improvements on it)
   


5. Tuning measurements:
   


6. Throughput – A measurement of how much work the system performs over a time period
   


7. Response Time – How long it takes for a user to get a response from the system
   

• There are three key hardware resources to optimize:
  


• CPU
  


• Disk
  


• Memory
  


• There are three kinds of systems or applications:
  Transactional - Many users, short DML transactions. Usually short rows favoring small block sizes. Values maximum throughput. Examples: inventory management or order taking. Aka (Also known as) Online Transaction Processing or OLTP.

• Decision Support - Few to no updates (all updates are done during mass insertion or batch update in off-hours). Big read-only queries do full table scans (FTS). Favors large block size and tuning for scans. Might benefit from such features as bitmapped indexes and materialized views. Values response time. Examples: management information systems. Aka Decision Support Systems (DSS) or Data Warehouses (DW).

• Hybrid - A mixture of OLTP and DSS/DW systems. Hard to tune since the goals and tuning parameters (parms) of the two basic types of systems conflict. If you can separate OLTP and DSS/DW and put them on different platforms, your tuning task is way easier.
  To collect timing statistics, set TIMED_STATISTICS=TRUE in the init.ora (default is FALSE). This requires recycling the instance. Or you can dynamically issue:
  ALTER SYSTEM SET TIMED_STATISTICS = TRUE ; This populates statistics into the data dictionary for the V$ views, UTLBSTAT/UTLESTAT utilities, and SQL Trace (aka TKPROF). (Use of these tools is explained below.)

Next Coming: Oracle Tuning Flow-chart

http://easyoracledba.blogspot.com/