Less 14

Performance Monitoring

Copyright © 2004, 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 • React to performance issues

Copyright © 2004, Oracle. All rights reserved.

Performance Monitoring Memory Memory allocation allocation issues issues Resource Resource contention contention

Input/output Input/output device device contention contention

? Application Application code code problems problems

DBA

Copyright © 2004, Oracle. All rights reserved.

Network Network bottlenecks bottlenecks

Monitoring Methodologies

• •

Reactive Proactive – Server-generated alerts – Automated Database Diagnostic Monitor (ADDM)

Copyright © 2004, Oracle. All rights reserved.

Database and Instance Metrics

Several hundred different performance statistics are available through: • Data dictionary • Dynamic performance views • Optimizer statistics

DBA Copyright © 2004, Oracle. All rights reserved.

Data Dictionary Metrics

Object status: • PL/SQL code objects • Indexes

Copyright © 2004, Oracle. All rights reserved.

Invalid and Unusable Objects

• •

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> 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

Copyright © 2004, Oracle. All rights reserved.

Manually Gather Optimizer Statistics

• •

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.

Copyright © 2004, Oracle. All rights reserved.

Schedule Optimizer Statistics Collection

Statistics should be gathered as needed to ensure the optimizer can make appropriate decisions.

Copyright © 2004, Oracle. All rights reserved.

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

Copyright © 2004, Oracle. All rights reserved.

Viewing Metric Information

• •

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 • Drill down to the root cause

Copyright © 2004, Oracle. All rights reserved.

Reacting to Performance Issues

Drill down into performance measurements to identify bottlenecks

•

Key bottleneck: log buffer space

Copyright © 2004, 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 • React to performance issues

Copyright © 2004, Oracle. All rights reserved.

Practice 14: Monitoring Performance This practice covers the following: • Viewing performance metrics • Repairing unusable indexes • Manually collecting optimizer statistics • Automating statistics collection

Copyright © 2004, Oracle. All rights reserved.