1、Monitoring and Improving PerformanceObjectivesAfter completing this lesson, you should be able to do the following:Troubleshoot invalid and unusable objects Gather optimizer statisticsView performance metricsDeal with performance issuesPerformance MonitoringMemory allocationissuesInput/outputdevicec

2、ontentionApplication code problemsResourcecontentionNetwork bottlenecks?DBAMonitoring MethodologiesReactiveProactive:Server-generated alertsAutomatic Database Diagnostic Monitor (ADDM)Database and Instance MetricsSeveral performance statistics are available through:Data dictionaryDynamic performance

3、 viewsOptimizer statisticsDBADatabase and Instance Metrics Full Notes PageData Dictionary MetricsYou can view the status of:PL/SQL code objectsIndexesInvalid and Unusable ObjectsPL/SQL code objects are recompiled.Indexes are rebuilt.Invalid and Unusable Objects Full Notes PageOptimizer StatisticsOpt

4、imizer statistics are:Not real timePersistent across instance restartsCollected automaticallySQL SELECT COUNT(*) FROM hr.employees; COUNT(*) 214SQL SELECT num_rows FROM dba_tables 2 WHERE owner=HR AND table_name = EMPLOYEES; NUM_ROWS 107Optimizer StatisticsFull Notes PageUsing the Manage Optimizer S

5、tatistics PageManage Optimizer Statistics Page Full Notes PageManually Gathering Optimizer StatisticsDynamic Performance ViewsDynamic Performance views are:Real timeNonpersistent across instance restartsSQL SELECT name, value FROM v$sysstat 2 WHERE name=sorts (memory) ORDER BY name;NAME VALUE sorts

6、(memory) 1979183SQL /NAME VALUE sorts (memory) 1979184Viewing Metric Information1.Use the All Metrics link in the Related Links region. 2.Drill down for in-depth analysis.Reacting to Performance IssuesUse Enterprise Manager to:Find key performance issuesDrill down to the root causeReacting to Perfor

7、mance IssuesDrill down to performance measurements to identify bottlenecks.CPU user: A specific update statementTuning TipsConsiderations:There are some general guidelines that serve as a starting point in performance tuning.Your situation may not fit the guideline.Test any changes under a represent

8、ative production environment to see the effects.There are written volumes and multiday classes dedicated to tuning an Oracle database.Tuning ActivitiesThe three activities in performance management are:Performance planningInstance tuningSQL tuningPerformance PlanningInvestment optionsSystem architec

9、tureScalabilityApplication design principlesWorkload testing, modeling, and implementationDeploying new applicationsPerformance Planning Full Notes PageInstance TuningHave well-defined goals.Allocate memory to database structures.Consider I/O requirements in each part of the database.Tune the operat

10、ing system for optimal performance of the database.Tuning Memory AllocationJava poolDatabasebuffer cacheRedo log bufferShared poolLarge poolSGAFixed SGAStreams poolWhat is the optimal allocation?SGA_TARGET = 8G STATISTICS_LEVEL = TYPICAL Tuning Memory Allocation Full Notes PageMemory Tuning: Guideli

11、nesTry to fit the SGA into physical memory.Tune for a high buffer cache hit ratio, with the following caveats:Even valid and necessary full table scans lower it.It is possible that unnecessary repeated reads of the same blocks are artificially raising it.Use the Memory Advisor.Memory Tuning Guidelin

12、es Full Notes PageMemory Tuning Guidelinesfor the Library CacheEstablish formatting conventions for developers so that SQL statements match in the cache.Use bind variables.Eliminate unnecessary duplicate SQL.Consider using CURSOR_SHARING.Use PL/SQL when possible.Cache sequence numbers.Pin objects in

13、 the library cache.Memory Tuning Guidelinesfor the Library Cache Full Notes PageTuning I/OLook for hot spots on the disk.Ensure that you have right-sized redo log files.Consider moving redo logs to separate disks.Consider moving archive logs to separate disks.Look for a high I/O rate on the TEMP tab

14、lespace.Reduce contention for high I/O data files by segregating them.Consider striping all data across all disks.Consider using Automatic Storage Management.Tuning I/O Full Notes PageTuning SQLUsing the SQL Tuning AdvisorUsing indexes and clustersUsing optimizer hintsUsing V$SQL_PLAN and EXPLAIN PL

15、ANTuning SQL Full Notes PageTuning RecommendationsRecommendation PlanSQL Statisticsselect count(*) from x where object_id 340select count(*) from x where object_id 220Each statement causes a hard parse.Identifying Duplicate SQLBind variable candidatesSummaryIn this lesson, you should have learned how to:Troubleshoot invalid and unusable objects Gather optimizer statisticsView performance metricsDeal with performance issuesDescribe some basic tunin