版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2026高中教资面试生物答辩真题题库参考答案
- 高中教资面试英语答辩历年真题高频考题参考答案
- 户外展销会人员失踪现场寻人活动主办方预案
- 2025-2026学年吉林白城实验高级中学高一上10月月考历史试卷含答案
- 大学物理量子力学波函数概念训练试题及答案
- 高中语文古诗表达山水诗自然景象描写分析训练试题及答案
- 高中物理实验电路测量电感性质实验分析测试试卷及答案
- 高中化学实验有机醛类银镜反应条件探究试题及答案
- 初中物理实验热学蒸发沸腾现象观察记录训练试题及答案
- 初中七年级地理下册北极地区和南极地区第二课时教学设计
- 2026北京市朝阳区卫生健康委员会所属事业单位第二批招聘241人笔试备考题库及答案解析
- 4.1妈妈教学设计
- 2026年内蒙古中考语文试卷及答案
- 成人术中外周静脉输液技术操作及管理指南
- 八年级物理第二学期期中考试质量分析
- 2026-2030中国化机浆市场竞争规划与未来前景发展趋势研究报告
- 2026粉末冶金开发高速钢刀片脱模剂适用性分析报告
- 三角函数压轴题专项训练-2026年高考数学 含答案
- 退役军人创新创业大赛
- 心脏MRI在急性心肌梗死诊断中的临床价值与应用
- 2026年辽宁省公安厅招聘警务辅助人员笔试试题(含答案)
评论
0/150
提交评论