Understanding & Tuning Oracle Database - Chapter 2

Continued from Chapter 1

Topic Covered : TOP Timed Events Explained 

Top Timed Events:

Top timed events are a good place to start when tuning as it always gives an indication of the areas that use most of the DB time. Top timed event sections are available on both AWR & Statspack report.

DB Time  = DB CPU  (Service Time by CPU) + Non idle Wait Time (Time waiting for various DB instance resources).  Tuning aims to reduce the DB time by reducing the CPU time and wait time.

When we do compare CPU and wait time, it gives an impression of current behaviour. If CPU time is dominant, then it a good sign, however high CPU time sometimes caused by poorly written SQLs. If we do encounter increase in wait time in proportion to load , then its a sign of problem are and need tuning.  

Tuning advice as per Thumb rule, it might differ for actual scenarios :

• If high/very high CPU time is observed, Mostly the SQL needs tuning.
• If high Wait time is observed, mostly the DB Instance needs tuning. No gain will achieve by simply adding CPUs/nodes. 
• If the CPU time doesn't decrease as load increases can scale better. Adding more CPUs and RAC would be beneficial.

DB time is the total time spent in database calls for user sessions. This comes from Time Model statistics names V$SYS_TIME_MODEL & V$SESS_TIME_MODEL.

Time Model explained: The names are very much self explanatory on what they represent. However I have explained few of them.

1. DB Time: Amount of elapsed time (in microseconds) spent on database user level calls. Its a cumulative time spent for each users calls from the time the instance is started. The time taken by pt 2 till pt 9 are subset of DB Time, excluding pt 10; the time spent on background process like PMON.

2. DB CPU: Amount of CPU time spent on database user level calls. 
3. Parse Time:  Amount of time spent on parsing SQL statements. 
4. SQL Execute elapsed time: Amount of elapsed time SQL statements are executing.
5. Connection management call elapsed time: Time spent on connecting and disconnecting session calls.
6. Sequence load elapsed time: Time spent on getting the next sequence number from data dictionary. 
7. PL/SQL Execution/compilation elapsed time: Time spent running the PL/SQL interpreter and compiler respectively.
8. JAVA execution elapsed time: Time spent on running the JVM.
9. Inbound PL/SQL RPC elapsed time: Time spent on executing inbound remote procedure calls. It includes time spent on SQL execution and running JVM.
10. RMAN CPU time: Time spent by RMAN backup/restore operations.

Understanding & Tuning Oracle Database - Chapter 1

Lets understand the terms and performance problems in Oracle Database and how to tune it effectively.

Topic Covered :
1. Common Performance problems.
2. Available tools to capture DB metrics.
3. Tuning Methodology
4. Tools for tuning
5. Tuning Objectives

Common Performance Problems in an application:

Application Issues : Poorly written SQL, Poor session management, serialized resources, etc..

Instance Issues : Memory, Process, I/O and Instance configs
OS Issues : I/O, SWAP, Parameters settings and Network configs

There is a variety of tools are available to capture the metrics and advice on tuning. All are based on the same methodology as mentioned below.

Tools :

1. Basic Diagnostics Tools :
       Dynamic Performance Views
       Statistics
       Metrics
2. AWR ( Optional tuning packs with Enterprise editions) / Statspack ( free with all the editions)
3. ADDM ( Automatic Database Diagnostics Monitor) (Optional tuning packs - licensed)
4. DBA Scripts - DBA create their own SQL scripts to capture the DB Performance.

All tuning tools are based on the basic diagnostics  tools (Dynamic performance views, statistics and metrics collected by the instance.)

Tuning Methodology :

Follow the top-down approach if the project is in design/development phase. Tune the design -> Tune the application -> Tune the DB instance. During the testing and production phases, tuning is more bottom-up approach.
Ex 1: Eliminate the Full Index scan that causes the I/O contention before looking into the table layout on disk.

Use appropriate data structures and designs -
Ex 2: Reverse key indexes may work well in RAC environment to reduce hot blocks but it may also lead to large number of blocks being transported between the instances if they write to same table.

Identify greatest bottleneck and tune it to get the highest potential benefit out of it. Use DB time to find the most common bottlenecks in the DB. Your aim should to "Reduce the longest waits and largest service times".

Tuning goals are always come from SLA. When the SLA fails, we do look for tuning the potential problem area. However the tuning goals aren't always specific. To start with tuning, we do need following criteria

• Measurable
• Achievable
• Specific
• Cost Effective

• Define the Problem and derive a goal - Define the problem statement and Derive the expectation. Source of Information could be users, stats, metrics and different reports.
• Collect Host & DB Stats - Collect Host & DB Stats for couple of past tests and compare with baseline.
• Look for common performance errors- Check of general errors before jumping to advanced stage and keep digging until you find the cause.
• Build a trial solution -  Build a solution and implement it If successful, Validate the issue and plan for implementing the solution in real environment.

Tools for Tuning ( these tools are discussed in subsequent chapters)

• Time Model
• Top Wait Events
• Alert Logs
• Trace files
• Dynamic Performance Views & Tables
• Statspack

Optionals

• AWR
• ADDM

Tuning Objectives

• Reduce Response Time/ minimize user wait time 
• Increase throughput - Decrease the time to perform a job
• Increase load capabilities- Increase the capacity to handle more concurrent requests
• Reduce Recovery Time - This is useful when the business have high instance availability requirement. There might be thousands to millions of dollar of loss to business for DB instance goes down. Tuning recovery balance out the additional disk writes of rego files.

Continue to Chapter 2