14 Performance Monitoring Copyright 2004 Oracle All rights

  • Slides: 17
Download presentation
14 Performance Monitoring Copyright © 2004, Oracle. All rights reserved.

14 Performance Monitoring Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this lesson you should be able to do the following: •

Objectives After completing this lesson you should be able to do the following: • Troubleshoot invalid and unusable objects • Gather optimizer statistics • View performance metrics • React to performance issues 14 -2 Copyright © 2004, Oracle. All rights reserved.

Performance Monitoring Memory allocation issues Resource contention Input/output device contention ? Application code problems

Performance Monitoring Memory allocation issues Resource contention Input/output device contention ? Application code problems 14 -3 DBA Copyright © 2004, Oracle. All rights reserved. Network bottlenecks

Monitoring Methodologies • • Reactive Proactive – Server-generated alerts – Automated Database Diagnostic Monitor

Monitoring Methodologies • • Reactive Proactive – Server-generated alerts – Automated Database Diagnostic Monitor (ADDM) 14 -4 Copyright © 2004, Oracle. All rights reserved.

Database and Instance Metrics Several hundred different performance statistics are available through: • Data

Database and Instance Metrics Several hundred different performance statistics are available through: • Data dictionary • Dynamic performance views • Optimizer statistics DBA 14 -5 Copyright © 2004, Oracle. All rights reserved.

Data Dictionary Metrics Object status: • PL/SQL code objects • Indexes 14 -7 Copyright

Data Dictionary Metrics Object status: • PL/SQL code objects • Indexes 14 -7 Copyright © 2004, Oracle. All rights reserved.

Invalid and Unusable Objects • • 14 -8 PL/SQL code objects are recompiled. Indexes

Invalid and Unusable Objects • • 14 -8 PL/SQL code objects are recompiled. Indexes are rebuilt. Copyright © 2004, Oracle. All rights reserved.

Optimizer Statistics Optimizer statistics are: • Not real-time • Persistent across instance restarts SQL>

Optimizer Statistics Optimizer statistics are: • Not real-time • Persistent across instance restarts 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 14 -10 Copyright © 2004, Oracle. All rights reserved.

Manually Gather Optimizer Statistics • • 14 -12 If database was not created with

Manually Gather Optimizer Statistics • • 14 -12 If database was not created with the DBCA If tables are extremely volatile Copyright © 2004, Oracle. All rights reserved.

Automate Optimizer Statistics Collection Use the Oracle Scheduler to automate customized statistics collection. 14

Automate Optimizer Statistics Collection Use the Oracle Scheduler to automate customized statistics collection. 14 -13 Copyright © 2004, Oracle. All rights reserved.

Schedule Optimizer Statistics Collection Statistics should be gathered as needed to ensure the optimizer

Schedule Optimizer Statistics Collection Statistics should be gathered as needed to ensure the optimizer can make appropriate decisions. 14 -14 Copyright © 2004, Oracle. All rights reserved.

Dynamic Performance Views Dynamic Performance views are: • Real-time • Non-persistent across instance restarts

Dynamic Performance Views Dynamic Performance views are: • Real-time • Non-persistent 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 14 -15 Copyright © 2004, Oracle. All rights reserved.

Viewing Metric Information • • 14 -16 Use the All Metrics link in the

Viewing Metric Information • • 14 -16 Use the All Metrics link in the Related Links region. Drill-down for in-depth analysis. Copyright © 2004, Oracle. All rights reserved.

Reacting to Performance Issues Use Enterprise Manager to: • Find key performance issues •

Reacting to Performance Issues Use Enterprise Manager to: • Find key performance issues • Drill down to the root cause 14 -17 Copyright © 2004, Oracle. All rights reserved.

Reacting to Performance Issues Drill down into performance measurements to identify bottlenecks • 14

Reacting to Performance Issues Drill down into performance measurements to identify bottlenecks • 14 -18 Key bottleneck: log buffer space Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson you should have learned how to: • Troubleshoot invalid and

Summary In this lesson you should have learned how to: • Troubleshoot invalid and unusable objects • Gather optimizer statistics • View performance metrics • React to performance issues 14 -19 Copyright © 2004, Oracle. All rights reserved.

Practice 14: Monitoring Performance This practice covers the following: • Viewing performance metrics •

Practice 14: Monitoring Performance This practice covers the following: • Viewing performance metrics • Repairing unusable indexes • Manually collecting optimizer statistics • Automating statistics collection 14 -20 Copyright © 2004, Oracle. All rights reserved.