oracle 性能与调整学习笔记

《oracle 性能与调整学习笔记》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、024PerformanceAndTuningstudynotesPerformanceTuningStudyGuideLesson2TuningOverview1.TuningStepsnTunethedesign.nTunetheapplicationnTunememorynTuneI/OnTunecontentionnTunetheoperatingsystem.2.TuningGoalsnAccesstheleastnumberofblocksnCacheblocksinmemorynShareapplicationcodenReadandwrited

2、ataasquicklyaspossiblenEnsurethatusersdonotwaitforresourcesnPerformbackupsandhousekeepingwhileminimizingimpactLesson3AlertandTraceFiles1.ObjectivesnDescribethelocationandusefulnessofthealertlogfile.nDescribethelocationandusefulnessofthebackgroundanduserprocesstracefiles.2.Thealertlo

3、gfileofdatabasecontainsthefollowinginformation:nInternalerrors(ORA-600)，andblockcorruptionerrors(ORA-1578)nOperationsthataffectdatabasestructuresandparameters,andstatementsuchasCREATEDATABASE,STARTUP,SHUTDOWN,ARCHIVELOGandRECOVERnThevalueofallnondefaultinitializationparameters3.Back

4、groundProcessTraceFilesIfanerrorisdetectedbyabackgroundprocess,theinformationisdumpedintoatracefile.4.UserTraceFilesWhenresourceconsumptionoccursduringstatementprocessing,serverprocessescangeneratetracefilesattheuser’srequest.Thisiscalledusertracefiles.5.Initializationparameter:nBAC

5、KGROUND_DUMP_DEST:StoresalertlogfileandbackgroundprocessestracefilesnUSER_DUMPT_DEST:Storesusertracefiles.53024PerformanceAndTuningstudynotesnSQL_TRACE:UsedtoenableordisablesqltracefilesnMAX_DUMP_FILE_SIZE:Limitsthesizeofusertracefiles,specifiedinO/Sblocks.1.在会话期间打开允许用户级别跟踪：(Session

6、-LevelTracing)EXECUTEdbms_system.set_sql_trace_in_session(8,12TRUE);注意8是SID，12是SERIAL#,可以通过V$SESSION来查找，并且DBMS_SYSTEM包属于SYS用户，普通的用户无法执行。53024PerformanceAndTuningstudynotesLesson4UtilitiesandDynamicPerformanceViews1.学习目标n通过下列手段来搜集统计信息：ü性能视图和故障解决视图üUTLBSTAT和UTLESTAT报告输出üOracle等待事件üORA

7、CLE企业管理器提供的相应工具n描述闩类型n使用企业管理器(EnterpriseManager,EM)来为预定的环境设定事件(event)2.Oracle提供的视图、实用脚本和工具：n动态故障解决、性能视图和数据字典：n实用性能分析脚本：UTLBSTAT.SQL和UTLESTAT.SQLnOracle等待事件n企业管理器事件服务nOracle分析和调整包3.V$视图和X$表的比较nV$视图是基于X$表来产生的n从V$FIXED_TABLE中可以查看有那些V$视图和X$表nX$表不经常使用，而且名字短、晦涩难懂nV$视图内容在实例启动时生成，关闭时清除4.故障解

8、决和性能调整常用视图介绍(T代表故障恢