Less13 Performance Tb3
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Less13 Performance Tb3 as PDF for free.
More details
- Words: 902
- Pages: 28
13
Performance Management
Copyright © 2005, Oracle. All rights reserved.
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/outpu t 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
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
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.
SQL Tuning Advisor Options and Recommendations
13-10
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.
Using the SQL Tuning Advisor: Example
13-12
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.
SQL Tuning Advisor: Identifying Duplicate SQL
Bind variable candidates
13-14
Copyright © 2005, Oracle. All rights reserved.
Using the SQL Access Advisor
13-15
Copyright © 2005, Oracle. All rights reserved.
Perf Mon Tuning Adv > Access Adv Memory Stats Invalid Obj
Full Notes Page
13-16
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.
Enabling Automatic Shared Memory Management (ASMM)
Click Enable to enable Automatic Shared Memory Management.
13-18
Copyright © 2005, Oracle. All rights reserved.
Enabling Automatic Shared Memory Management (ASMM) Full Notes Page
13-19
Copyright © 2005, Oracle. All rights reserved.
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 V$SYSSTAT • statistic# • name • class • value • stat_id V$SYSTEM_EVENT • event • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro Cumulative stats Wait events 13-22
Session specific
… Access Adv Memory > Stats Invalid Obj
Service specific
V$SESSTAT • sid • statistic# • value
V$SERVICE_STATS • service_name_hash • service_name • stat_id • stat_name • value
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 • service_name_hash • event • event_id • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro
Copyright © 2005, Oracle. All rights reserved.
Dynamic Performance Statistics Full Notes Page
13-23
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 V$LOG_HISTORY V$DBFILE V$TEMPFILE 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
Invalid and Unusable Objects Full Notes Page
13-26
Copyright © 2005, Oracle. All rights reserved.
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.
Performance Management
Copyright © 2005, Oracle. All rights reserved.
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/outpu t 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
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
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.
SQL Tuning Advisor Options and Recommendations
13-10
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.
Using the SQL Tuning Advisor: Example
13-12
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.
SQL Tuning Advisor: Identifying Duplicate SQL
Bind variable candidates
13-14
Copyright © 2005, Oracle. All rights reserved.
Using the SQL Access Advisor
13-15
Copyright © 2005, Oracle. All rights reserved.
Perf Mon Tuning Adv > Access Adv Memory Stats Invalid Obj
Full Notes Page
13-16
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.
Enabling Automatic Shared Memory Management (ASMM)
Click Enable to enable Automatic Shared Memory Management.
13-18
Copyright © 2005, Oracle. All rights reserved.
Enabling Automatic Shared Memory Management (ASMM) Full Notes Page
13-19
Copyright © 2005, Oracle. All rights reserved.
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 V$SYSSTAT • statistic# • name • class • value • stat_id V$SYSTEM_EVENT • event • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro Cumulative stats Wait events 13-22
Session specific
… Access Adv Memory > Stats Invalid Obj
Service specific
V$SESSTAT • sid • statistic# • value
V$SERVICE_STATS • service_name_hash • service_name • stat_id • stat_name • value
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 • service_name_hash • event • event_id • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro
Copyright © 2005, Oracle. All rights reserved.
Dynamic Performance Statistics Full Notes Page
13-23
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 V$LOG_HISTORY V$DBFILE V$TEMPFILE 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
Invalid and Unusable Objects Full Notes Page
13-26
Copyright © 2005, Oracle. All rights reserved.
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.
Related Documents
Less13 Performance Tb3
December 2019 3
Less13 Performance Tb3
May 2020 3
Less05 Storage Tb3
May 2020 16
Less04 Instance Tb3
December 2019 12
Less16 Recovery Tb3
December 2019 12
Less07 Schema Tb3
December 2019 13More Documents from "yairr"
Les 05
May 2020 15
Les 07
May 2020 12
Less05 Storage Tb3
May 2020 16
Installing The Oracle Database Software
May 2020 15
Les 09
May 2020 13