oracle 10g perance tuning 02.ppt

1、Performance Tuning: Overview,Objectives,After completing this lesson, you should be able to do the following: Write appropriate tuning goals Apply the tuning methodology Balance performance and safety trade-offs Identify common tuning problems Log a performance Service Request with Oracle Support,Ge

2、neral Tuning Session,Tuning sessions have the same procedure: 1.Define the problem and state the goal. 2.Collect current statistics. 3.Consider some common performance errors. 4.Build a trial solution. 5.Implement and measure the change. 6.Decide: “Did the solution meet the goal?” No? Then go to ste

3、p 3 and repeat. Yes? Then create a new baseline.,Defining the Problem,To discover and define the problem: 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 memory, I/O, and CPU usage. Identify p

4、rocesses with resource usage anomalies Identify and tune SQL statements that are heavy consumers of CPU or I/O Collect instance and operating system (OS) statistics,Setting the Priority,Choose the problem that has the greatest impact: Analyze system performance in terms of work done (CPU or service

5、time) versus time spent waiting for work (wait time). Determine which component consumes the greatest amount of time. Drill down to tune that component, if appropriate.,Tuning Methodology:Setting the Priority Example,Time Model System Stats DB/Inst: ORCL Snaps: 1-11 - Ordered by % of DB time desc, S

6、tatistic name Statistic Time (s) % of DB time - - - sql execute elapsed time 467.0 77.1 DB CPU 414.2 68.4 parse time elapsed 200.5 33.1 hard parse elapsed time 109.0 18.0 DB time 605.8,Common Tuning Problems,The most common tuning problems: SQL statements Session management Shared pool sizing and co

7、ntention Buffer cache sizing and contention Data block contention Redo log and redo buffer tuning Undo tuning I/O issues Locking issues,ADDM Tuning Session,ADDM tuning session has the same procedure as the general tuning session, but combines steps: 1.View the ADDM report. A.Collect current statisti

8、cs; compare with previous set. B.Compare to performance issues knowledge base. C.Define the problem and make recommendations. 2.Review recommendations. D.Build a trial solution. 3.Implement the recommendation. E.Implement and measure the change. 4.Review the next ADDM report. F.Decide: “Did the solu

9、tion meet the goal?”,Effective Tuning Goals,Effective tuning goals are: Specific Measurable Achievable,Tuning Objectives,The objectives of tuning are: Minimizing response time Increasing throughput Increasing load capabilities Decreasing recovery time,DB Time,DB Time = DB Wait Time + DB CPU Time,CPU

10、 and Wait Time Tuning Dimensions,Scalableapplication,Possiblyneeds SQLtuning,Needsinstance/RACtuning,CPUtime,Waittime,No gain achievedby addingCPUs/nodes,DB time = DB CPU time + DB wait time,Tuning Life Cycle Phases,Application life cycle can be divided into different phases: Application design and

11、development Testing: Database configuration Deployment: Adding a new application to an existing database Production: Troubleshooting and tuning,Tuning Steps During Life Cycle,1.Tune the design. 2.Tune the application. 3.Tune memory. 4.Tune I/O. 5.Tune contention. 6.Tune the operating system.,Applica

12、tion Design and Development,The application can be tuned even in the design and development phases by building and tuning test cases. Check normalization against major functions. Check data structures against access times. Look at points where processes are serialized. Tune the major reports. Tune t

13、he high-volume processes.,Testing: Database Configuration,The testing phase allows tuning at a deeper level: Check physical layout. Monitor for resource contention. Memory utilization Locks Disk hot spots Test for resource exhaustion.,Deployment,Deployment of: New application and database Take basel

14、ine. Monitor growth and performance. New application in existing database Take baseline before deployment. Take baseline after deployment. Compare baselines.,Production,Tuning is reactive. You need to know: What has changed? Where is the baseline?,Collecting a Baseline Set of Statistics,A baseline s

15、et of statistics is used to: Provide a set of statistics that are collected when the system is operating within the bounds set Compare the baseline statistics with current statistics Create a hypothesis about what has changed onthe system,Performance Versus Safety Trade-Offs,Factors that affect perf

16、ormance: Multiple control files Multiple redo log members in a group Frequent checkpointing Backing up data files Performing archiving Block check sums Number of concurrent users and transactions,Filing a Performance Service Request,File a Performance Service Request: Is the problem instancewide or

17、query specific? Identify the root cause. Provide Statspack or AWR reports, and OS statistics. Provide Remote Diagnostics Agent (RDA) reports. Provide SQL_TRACE reports.,RDA Report,Monitoring and Tuning Tool: Overview,Alert log,Trace files,Performance views,Optimizer statistics,SQL statistics,Base st

18、atistics,Histograms,Metrics,Service statistics,ASH,tkprof,trcsess,System statistics,Session statistics,Wait model,Time model,Alerts,ASH reports,Services,Monitoring and Tuning Tool: Overview,Statspack,AWR,EM performance pages,Metric baseline,EM policies,ADDM,Advisors,Direct SGA monitor,Hang analyzer,Baselines,AWR baselines,Compare periods,Services,Practice Overview: Write Tuning Goals,This practice covers the following topics: Transform a problem definition into a goal Make a goal measura