13 Performance Management Copyright 2005 Oracle All rights

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

Performance Monitoring Memory allocation issues Input/output device contention Application code problems 13 -3 Resource

Performance Monitoring 13 -4 Copyright © 2005, Oracle. All rights reserved.

Performance Monitoring 13 -5 Copyright © 2005, Oracle. All rights reserved.

Performance Monitoring 13 -6 Copyright © 2005, Oracle. All rights reserved.

Performance Monitoring: Top Sessions 13 -7 Copyright © 2005, Oracle. All rights reserved.

Performance Monitoring: Top Services 13 -8 Copyright © 2005, Oracle. All rights reserved.

SQL Tuning Advisor: Overview Automatic Tuning Optimizer Comprehensive SQL tuning Statistics check optimization mode

SQL Tuning Advisor Options and Recommendations 13 -10 Copyright © 2005, Oracle. All rights

Using the SQL Tuning Advisor • • Use the SQL Tuning Advisor to analyze

Using the SQL Tuning Advisor: Example 13 -12 Copyright © 2005, Oracle. All rights

SQL Tuning Advisor: SQL Statistics select count(*) from x where object_id < 340 select

SQL Tuning Advisor: Identifying Duplicate SQL Bind variable candidates 13 -14 Copyright © 2005,

Using the SQL Access Advisor 13 -15 Copyright © 2005, Oracle. All rights reserved.

Managing Memory Components • Perf Mon Tuning Adv Access Adv > Memory Stats Invalid

Enabling Automatic Shared Memory Management (ASMM) Click Enable to enable Automatic Shared Memory Management.

Manually Setting Shared Memory Management 13 -20 Copyright © 2005, Oracle. All rights reserved.

Using the Memory Advisor 13 -21 Copyright © 2005, Oracle. All rights reserved.

Dynamic Performance Statistics Systemwide Session specific … Access Adv Memory > Stats Invalid Obj

Troubleshooting and Tuning Views 13 -24 Instance/Database V$DATABASE V$INSTANCE V$PARAMETER V$SPPARAMETER V$SYSTEM_PARAMETER V$PROCESS V$BGPROCESS

Invalid and Unusable Objects Effect on Performance: • PL/SQL code objects are recompiled. •

Summary In this lesson, you should have learned how to: • Use Enterprise Manager

Practice Overview: Monitoring and Improving Performance This practice covers the following topics: • Detecting

Slides: 24

Download presentation

Objectives After completing this lesson, you should be able to do the following: • Use Enterprise Manager to monitor performance • Tune SQL by using the SQL Tuning Advisor • Tune SQL by using the SQL Access Advisor • Use Automatic Shared Memory Management (ASSM) • Use the Memory Advisor to size memory buffers • View performance-related dynamic views • Troubleshoot invalid and unusable objects 13 -2 Copyright © 2005, Oracle. All rights reserved.

Performance Monitoring Memory allocation issues Input/output device contention Application code problems 13 -3 Resource contention ? DBA Network bottlenecks Copyright © 2005, Oracle. All rights reserved. > Perf Mon Tuning Adv Access Adv Memory Stats Invalid Obj

SQL Tuning Advisor: Overview Automatic Tuning Optimizer Comprehensive SQL tuning Statistics check optimization mode Detect stale or missing statistics Plan tuning optimization mode Tune SQL plan (SQL profile) Access analysis optimization mode Add missing index Run access advisor SQL analysis optimization mode 13 -9 Perf Mon > Tuning Adv Access Adv Memory Stats Invalid Obj SQL Tuning Advisor Restructure SQL Copyright © 2005, Oracle. All rights reserved.

Using the SQL Tuning Advisor • • Use the SQL Tuning Advisor to analyze SQL statements and obtain performance recommendations. Sources for SQL Tuning Advisor to analyze – Top SQL: Analyzes the top SQL statements currently active – SQL Tuning Sets: Analyzes a set of SQL statements you provide – Snapshots: Analyzes a snapshot – Baselines: Analyzes a baseline 13 -11 Copyright © 2005, Oracle. All rights reserved.

SQL Tuning Advisor: 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 -13 Copyright © 2005, Oracle. All rights reserved.

Managing Memory Components • Perf Mon Tuning Adv Access Adv > Memory Stats Invalid Obj Automatic Shared Memory Management: – Is recommended to simplify management – Enables you to specify the total SGA memory through one initialization parameter – Enables the Oracle server to manage the amount of memory allocated to the shared pool, Java pool, buffer cache, streams pool, and the large pool • Manually setting shared memory management: – Sizes the components through multiple individual initialization parameters – Uses the Memory Advisor to make recommendations 13 -17 Copyright © 2005, Oracle. All rights reserved.

Dynamic Performance Statistics Systemwide Session specific … Access Adv Memory > Stats Invalid Obj Service specific V$SYSSTAT • statistic# • name • class • value • stat_id V$SESSTAT • sid • statistic# • value V$SERVICE_STATS • service_name_hash • service_name • stat_id • stat_name • value V$SYSTEM_EVENT • event • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro V$SESSION_EVENT • sid • event • total_waits • total_timeouts • time_waited • average_wait • max_wait • time_waited_micro • event_id V$SERVICE_EVENT • service_name_hash • event_id • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro Cumulative stats Wait events 13 -22 Copyright © 2005, Oracle. All rights reserved.

Troubleshooting and Tuning Views 13 -24 Instance/Database V$DATABASE V$INSTANCE V$PARAMETER V$SPPARAMETER V$SYSTEM_PARAMETER V$PROCESS V$BGPROCESS V$PX_PROCESS_SYSSTAT Disk V$DATAFILE V$FILESTAT V$LOG_HISTORY V$DBFILE V$TEMPSEG_USAGE V$SEGMENT_STATISTICS V$SYSTEM_EVENT Memory V$BUFFER_POOL_STATISTICS V$LIBRARYCACHE V$SGAINFO V$PGASTAT Contention V$LOCK V$UNDOSTAT V$WAITSTAT V$LATCH Copyright © 2005, Oracle. All rights reserved.

Invalid and Unusable Objects Effect on Performance: • PL/SQL code objects are recompiled. • Indexes are rebuilt. 13 -25 Copyright © 2005, Oracle. All rights reserved. Perf Mon Tuning Adv Access Adv Memory Stats > Invalid Obj

Summary In this lesson, you should have learned how to: • Use Enterprise Manager to monitor performance • Tune SQL using the SQL Tuning Advisor • Tune SQL using the SQL Access Advisor • Use Automatic Shared Memory Management • Use the Memory Advisor to size memory buffers • View performance-related dynamic views • Troubleshoot invalid and unusable objects 13 -27 Copyright © 2005, Oracle. All rights reserved.

Practice Overview: Monitoring and Improving Performance This practice covers the following topics: • Detecting and repairing unusable indexes • Using the SQL Tuning Advisor • Using the Performance page in Enterprise Manager 13 -28 Copyright © 2005, Oracle. All rights reserved.