Transcript 14 - Elte

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
14-2
Copyright © 2004, Oracle. All rights reserved.
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 (ADDM)
14-4
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
14-5
Copyright © 2004, Oracle. All rights reserved.
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 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
14-10
Copyright © 2004, Oracle. All rights reserved.
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-13
Copyright © 2004, Oracle. All rights reserved.
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
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 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
14-17
Copyright © 2004, Oracle. All rights reserved.
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 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
• Repairing unusable indexes
• Manually collecting optimizer statistics
• Automating statistics collection
14-20
Copyright © 2004, Oracle. All rights reserved.