Database Tuning Tools - Notes

Database Tuning Tools Basic Tools (Dynamic Performance Views, Statistics, Metrics, EM pages) AWR or Statspack ADDM DBA Scripts Performance Tuning Methodology: 1. Data Collection 2. Data Analysis 3. Solution Implementation Proactive monitoring with ADDM Reactive monitoring with Server-generated alerts and AWR Monitoring & Tuning Packs: 1. Oracle Database Diagnostic Pack (DBMS_WORKLOAD_REPOSITORY, DBMS_ADVISOR, V$ACTIVE_SESSION_HISTORY, DBA_HIST_*, DBA_ADVISOR_* 2. Oracle Database Tuning Pack (DBMS_SQLTUNE, DBMS_ADVISOR 3. Oracle Configuration Management Pack Without Packs: 1. SQL Traces 2. Statspack 3. System statistics 4. Wait model 5. Time model 6. OS statistics 7. Metrics 8. Service statistics 9. Histograms 10. Optimizer statistics 11. SQL statistics Tuning Methodology: 1. Check the OS stats and general machine health before tuning the instance. 2. Tune from the top down: Start with the design, then application, and then instance. 3. Tune the area with greatest potential benefit. Identify the biggest bottleneck, and tune it. 4. Stop tuning when you meet your goals. General Tuning Session: 1. Define the problem and state the goal. 2. Collect current statistics, and compare them with baseline statistics. 3. Consider common performance errors. 4. Build a trial solution. Why performance degraded and how to resolve it.

5. Implement and measure the change. 6. Did the solution meet the goals, if not, do it again. Defining the Problem: 1. 2. 3. 4.

Listen to user feedback Check the alert log and trace files for errors. Check the parameter file for any diagnostic or inappropriate parameter setting. Check for CPU and disk queuing, disk utilization, and memory swapping. Identify processes and resource usage. 5. Use the available tools, such as Statspack or ADDM, to identify SQL statements in the application that are consuming the most resources. 6. Collect instance and OS statistics. Setting the Priority: 1. Choose the problem that has the greatest impact 2. Analyze the system performance in terms of work done (CPU or service time) versus time spent waiting for work (wait time). 3. Determine which component consumes the greatest amount of time. 4. Drill down and tune that component, if appropriate. Each server process is in one of the states: - Idle: waiting for something to do or sleeping - Running code: using the CPU or in run queue. - Waiting (blocked): for some resource to become available or requested activity to be completed. Common Tuning Problems: 1. SQL statements or application tuning 2. Session management (connects, disconnects, open cursors, cursor caching) 3. Memory issues are high on the list of instance tuning problems. It includes sizing different parts of SGA and contention of memory resources (Shared pool sizing and contention, Buffer cache sizing and contention, data block contention) 4. Redo log and redo buffer tuning (in OLTP applications). 5. Undo tuning and I/O issues 6. Locking issues Tuning with ADDM: 1. View the ADDM report a. Collect current stats, and compare with previous set. b. Compare to performance issues knowledge base. c. Define problem and make recommendations. 2. Review recommendations and build trial solution plan. 3. Implement the recommendations and measure the change. 4. Review the next ADDM report and decide "did the solution meet the goal?" Tuning Objectives:

1. Minimizing response time or reducing user wait time. 2. Increasing throughput, which means decreasing time to perform a job or set of jobs. 3. Increasing load capabilities 4. Decreasing recovery time DB Wait Time = sum of all waits for availability of resources. DB CPU Time = sum of time spent working on the request. DB Time = DB Wait Time + DB CPU Time Tuning Life Cycle Phases: 1. Development: Application design and programming. Whenever possible, tuning should be started at this level. With a good design, many tuning problems do not occur. For example, typically, its good to fully normalize tables to reduce redundancy, this can result in high number of table joins. By demoralizing tables, performance can improve dramatically. 2. Testing: Database configuration 3. Deployment: Adding new application to an existing database 4. Production: Troubleshooting and tuning Performance VS Safety Trade-Offs Factors that affect the performance but increase security, recoverability: 1. Multiple control files, more writes 2. Multiple redo log members in a group 3. Frequent checkpointing 4. Backing up data files 5. Performing archiving 6. Block check sums 7. Number of concurrent users and connections

Monitoring and Tuning Tool: Overview Services Alert log Trace files Performance views

Optimizer statistics SQL statistics Base statistics Histograms Metrics Service statistics ASH

2-25

tkprof trcsess System statistics Session statistics Wait model Time model Alerts ASH reports

Copyright © 2006, Oracle. All rights reserved.

Monitoring and Tuning Tool: Overview Services AWR EM performance pages Metric baseline EM policies ADDM Advisors Direct SGA monitor Hang analyzer Statspack 2-26

AWR baselines

Compare periods

Baselines

Copyright © 2006, Oracle. All rights reserved.

Performance Tuning: Type of Data Gathered 1. Cumulative statistics a. Wait events with time information b. Time model 2. Metrics: Statistic rates (per second, per transaction, per session) 3. Sample statistics: Active session history (data gathered in past) a. Statistics by session b. Statistics by SQL c. Statistics by Service d. Other dimensions Performance Tuning: Tools 1. Basic a. EM pages b. Alert log (checkpoints, archiving, alter system commands etc) c. Trace files (background processes problem logging) User Trace files: can be generated at the request of DBA, Instance level tracing is enabled by enabling SQL_TRACE parameter, Session level tracing by issuing alter session set sql_trace=true; Background processes trace files d. Dynamic performance views and tables 2. Add-in: Statspack 3. Options: a. Diagnostic pack (AWR) b. Tuning pack Dynamic Performance Views: 1. Owned by SYS user 2. Different views are available at different times a. The instance has been started

b. The database is mounted c. The database is open 3. Query V$FIXED_TABLES to see all view names 4. These views are often referred as "v-dollar" views 5. Read consistency not guaranteed as data is dynamic Statistics Level STATISTICAL_LEVEL 1. 2. 3. 4.

BASIC: No advisory or other statistical data is collected. TYPICAL: This is default and recommended value. ALL: Additional time OS statistics and row source execution stats. Query V$STATISTICS_LEVEL to determine which other parameters are affected by STATISTICAL_LEVEL parameter: select statistics_name, activation_level from v$statistics_level

System wide statistics V$SYSSTAT V$SESSION V$SESSION_EVENT V$SESSION_WAIT V$SGASTAT V$EVENT_NAME V$SYSTEM_EVENT V$STATNAME V$SERVICE_STATS V$SERVICES V$SERVICE_EVENT V$SESSION_WAIT_CLASS Wait Events