oracle 10g performance tuning 02.ppt

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,2-,*,Copyright 2006,Oracle.All rights reserved.,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,General 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 step 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 processes 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 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,Statistic 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 1090 18.0,DB time 605.8,Common Tuning Problems,The most common tuning problems:,SQL statements,Session management,Shared pool sizing and contention,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 statistics;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 solution 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 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 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.,Application 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 the 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 baseline.,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 set 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 performance:,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 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 statistics,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 measurable,Summary,In this lesson,you should have learned how to:,Write appropriate tuning goals,Identify the proper methodology for tuning in different development phases,Balance performance and safety trade-offs,Identify common tuning problems,Log a performance Service Request with Oracle Support,