oracle10g英文原厂培训tb.pptxVIP

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 allocationissuesResourcecontentionInput/outputdevicecontention?Network bottlenecksApplication code problemsDBAMonitoring MethodologiesReactiveProactive:Server-generated alertsAutomatic Database Diagnostic Monitor (ADDM)Database and Instance MetricsSeveral performance statistics are available through:Data dictionaryDynamic performance 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 piled.Indexes are rebuilt.Invalid and Unusable Objects Full Notes PageOptimizer StatisticsOptimizer 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 Statistics 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 (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 Performance IssuesDrill down to performance measurements to identify bottlenecks.CPU user: A specific update statementTuning TipsConsiderations:There are some gene