Transcript Document
Monitoring and Improving Performance Copyright © 2005, Oracle. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Troubleshoot invalid and unusable objects • Gather optimizer statistics • View performance metrics • Deal with performance issues 13-2 Copyright © 2005, Oracle. All rights reserved. Performance Monitoring Memory allocation issues Input/outpu t device contention Application code problems 13-3 Resource contention ? DBA Copyright © 2005, Oracle. All rights reserved. Network bottlenecks Monitoring Methodologies • • Reactive Proactive: – Server-generated alerts – Automatic Database Diagnostic Monitor (ADDM) 13-4 Copyright © 2005, Oracle. All rights reserved. Database and Instance Metrics Several performance statistics are available through: • Data dictionary • Dynamic performance views • Optimizer statistics DBA 13-5 Copyright © 2005, Oracle. All rights reserved. Data Dictionary Metrics You can view the status of: • PL/SQL code objects • Indexes 13-7 Copyright © 2005, Oracle. All rights reserved. Invalid and Unusable Objects • • 13-8 PL/SQL code objects are recompiled. Indexes are rebuilt. Copyright © 2005, Oracle. All rights reserved. Optimizer Statistics Optimizer statistics are: • Not real time • Persistent across instance restarts • Collected automatically SQL> SELECT COUNT(*) FROM hr.employees; COUNT(*) ---------214 SQL> SELECT num_rows FROM dba_tables 2 WHERE owner='HR' AND table_name = 'EMPLOYEES'; NUM_ROWS ---------107 13-10 Copyright © 2005, Oracle. All rights reserved. Using the Manage Optimizer Statistics Page 13-12 Copyright © 2005, Oracle. All rights reserved. Manually Gathering Optimizer Statistics 13-14 Copyright © 2005, Oracle. All rights reserved. Dynamic Performance Views Dynamic Performance views are: • Real time • Nonpersistent across instance restarts SQL> SELECT name, value FROM v$sysstat 2 WHERE name='sorts (memory)' ORDER BY name; NAME VALUE --------------- ---------sorts (memory) 1979183 SQL> / NAME VALUE --------------- ---------sorts (memory) 1979184 13-15 Copyright © 2005, Oracle. All rights reserved. Viewing Metric Information 1. Use the All Metrics link in the Related Links region. 2. Drill down for in-depth analysis. 13-16 Copyright © 2005, Oracle. All rights reserved. Reacting to Performance Issues Use Enterprise Manager to: • Find key performance issues • Drill down to the root cause 13-17 Copyright © 2005, Oracle. All rights reserved. Reacting to Performance Issues Drill down to performance measurements to identify bottlenecks. • 13-18 CPU user: A specific update statement Copyright © 2005, Oracle. All rights reserved. Tuning Tips Considerations: • 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 representative production environment to see the effects. • There are written volumes and multiday classes dedicated to tuning an Oracle database. 13-19 Copyright © 2005, Oracle. All rights reserved. Tuning Activities The three activities in performance management are: • Performance planning • Instance tuning • SQL tuning 13-20 Copyright © 2005, Oracle. All rights reserved. Performance Planning • • • • • • 13-21 Investment options System architecture Scalability Application design principles Workload testing, modeling, and implementation Deploying new applications Copyright © 2005, Oracle. All rights reserved. Instance Tuning • • • • 13-23 Have well-defined goals. Allocate memory to database structures. Consider I/O requirements in each part of the database. Tune the operating system for optimal performance of the database. Copyright © 2005, Oracle. All rights reserved. Tuning Memory Allocation SGA Database buffer cache Streams pool Redo log buffer Large pool Fixed SGA Shared pool Java pool SGA_TARGET = 8G What is the optimal allocation? STATISTICS_LEVEL = TYPICAL 13-24 Copyright © 2005, Oracle. All rights reserved. Memory Tuning: Guidelines • • Try 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. • 13-26 Use the Memory Advisor. Copyright © 2005, Oracle. All rights reserved. Memory Tuning Guidelines for the Library Cache • 13-28 • • • Establish 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 the library cache. Copyright © 2005, Oracle. All rights reserved. Tuning I/O • • • • • Look 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 tablespace. • Reduce contention for high I/O data files by segregating them. Consider striping all data across all disks. Consider using Automatic Storage Management. • • 13-30 Copyright © 2005, Oracle. All rights reserved. Tuning SQL • • • • 13-32 Using the SQL Tuning Advisor Using indexes and clusters Using optimizer hints Using V$SQL_PLAN and EXPLAIN PLAN Copyright © 2005, Oracle. All rights reserved. Tuning Recommendations 13-34 Copyright © 2005, Oracle. All rights reserved. Recommendation Plan 13-35 Copyright © 2005, Oracle. All rights reserved. SQL Statistics select count(*) from x where object_id < 340 select count(*) from x where object_id < 220 Each statement causes a hard parse. 13-36 Copyright © 2005, Oracle. All rights reserved. Identifying Duplicate SQL Bind variable candidates 13-37 Copyright © 2005, Oracle. All rights reserved. Summary In this lesson, you should have learned how to: • Troubleshoot invalid and unusable objects • Gather optimizer statistics • View performance metrics • Deal with performance issues • Describe some basic tuning steps 13-38 Copyright © 2005, Oracle. All rights reserved. Practice Overview: Monitoring and Improving Performance This practice covers the following topics: • Detecting and repairing unusable indexes • Using EXPLAIN PLAN • • 13-39 Gathering statistics Using the Performance page in Enterprise Manager Copyright © 2005, Oracle. All rights reserved.