Transcript SQL Access Advisor

Automatic Performance Management
Copyright © 2006, Oracle. All rights reserved.
Objectives
Tuning
Statistics
SGA Attach
AWR
Advisors
Async. Commit
After completing this lesson, you should be able to:
• Diagnose database performance issues
• Configure the Automatic Workload Repository
• Access the database advisors
• Use the SQL Access Advisor to improve database
performance
• Use asynchronous COMMIT effectively
9-2
Copyright © 2006, Oracle. All rights reserved.
Tuning Activities
The three activities in performance management are:
• Performance planning
• Instance tuning
• SQL tuning
9-3
Copyright © 2006, Oracle. All rights reserved.
Performance Planning
•
•
•
•
•
•
9-4
Investment options
System architecture
Scalability
Application design principles
Workload testing, modeling, and implementation
Deploying new applications
Copyright © 2006, Oracle. All rights reserved.
Instance Tuning
•
•
•
•
9-6
Have well-defined goals.
Allocate memory to database structures.
Consider I/O requirements in each part of the
database.
Tune the operating system for optimal
performance of the database.
Copyright © 2006, Oracle. All rights reserved.
Performance Tuning Methodology
The tuning steps:
• Tune from the top down. Tune:
– The design before tuning the application code
– The code before tuning the instance
•
Tune the area with the greatest potential benefit.
– Identify the longest waits.
– Identify the largest service times.
•
9-7
Stop tuning when the goal is met.
Copyright © 2006, Oracle. All rights reserved.
Statistics Collection
•
•
Performance tuning depends on the collection of
accurate statistics.
There are different types of statistics:
– Optimizer statistics
– System statistics
•
There are different methods of collecting
statistics:
– Automatically, through GATHER_STATS_JOB
– Manually, with the DBMS_STATS package
– By setting database initialization parameters
– By importing statistics from another database
9-8
Copyright © 2006, Oracle. All rights reserved.
Oracle Wait Events
•
•
9-10
Tuning
> Statistics
SGA Attach
AWR
Advisors
Async. Commit
A collection of wait events provides information
about the sessions or processes that had to wait
or must wait for different reasons.
These events are listed in the V$EVENT_NAME view.
Copyright © 2006, Oracle. All rights reserved.
System Statistics
V$SYSSTAT
• statistic#
• name
• class
• value
• stat_id
V$SYSTEM_WAIT_CLASS
• wait_class_id
• wait_class#
• wait_class
• total_waits
• time_waited
V$EVENT_NAME
• event_number
• event_id
• name
• parameter1
• parameter2
• parameter3
• wait_class
9-11
V$SGASTAT
• pool
• name
• bytes
V$SYSTEM_EVENT
• event
• total_waits
• total_timeouts
• time_waited
• average_wait
• time_waited_micro
Copyright © 2006, Oracle. All rights reserved.
Displaying Session-Related Statistics
V$STATNAME
• statistic#
• name
• class
V$SESSTAT
• sid
• statistic#
• value
V$SESSION_EVENT
• sid
• event
• total_waits
• total_timeouts
• time_waited
• average_wait
• max_wait
• time_waited_micro
• event_id
9-13
V$EVENT_NAME
• event#
• name
• parameter1
• parameter2
• parameter3
V$SESSION
• sid
• serial#
• username
• command
• osuser
• seq#
• event#
• event
• p1/2/3text
• p1/2/3
• p1/2/3raw
• wait_class
• wait_time
• seconds_in_wait
• state
• ...
Copyright © 2006, Oracle. All rights reserved.
Displaying Service-Related Statistics
For n-tier environments, because session statistics are
not as helpful, you can see service-level statistics in
these views:
• V$SERVICE_EVENT: Aggregated wait counts and
wait times for each service, on a per event basis
• V$SERVICE_WAIT_CLASS: Aggregated wait
counts and wait times for each service on a wait
class basis
9-14
Copyright © 2006, Oracle. All rights reserved.
Troubleshooting and Tuning Views
Instance/Database
V$DATABASE
V$INSTANCE
V$PARAMETER
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$PROCESS
V$BGPROCESS
V$PX_PROCESS_SYSSTAT
V$SYSTEM_EVENT
Memory
V$BUFFER_POOL_STATISTICS
V$LIBRARYCACHE
V$SGAINFO
V$PGASTAT
9-15
Disk
V$DATAFILE
V$FILESTAT
V$LOG
V$LOG_HISTORY
V$DBFILE
V$TEMPFILE
V$TEMPSEG_USAGE
V$SEGMENT_STATISTICS
Contention
V$LOCK
V$UNDOSTAT
V$WAITSTAT
V$LATCH
Copyright © 2006, Oracle. All rights reserved.
Dictionary Views
•
The following dictionary and special views provide
useful statistics after using the DBMS_STATS
package:
–
–
–
–
•
9-16
DBA_TABLES, DBA_TAB_COLUMNS
DBA_CLUSTERS
DBA_INDEXES
DBA_TAB_HISTOGRAMS
This statistical information is static until you
reexecute the appropriate procedures in
DBMS_STATS.
Copyright © 2006, Oracle. All rights reserved.
Diagnosis of Hung or
Extremely Slow Databases
Tuning
Statistics
> SGA Attach
AWR
Advisors
Async. Commit
Use for problem analysis when the database is
performing very slowly, or is hung:
• Direct access to SGA for performance monitoring
(memory access mode)
–
–
–
–
V$SESSION
V$SESSION_WAIT
V$SYSTEM_EVENT
V$SYSSTAT
– Hang analysis using Enterprise Manager
9-17
Copyright © 2006, Oracle. All rights reserved.
Using Memory Access Mode
1
4
2
3
9-18
Copyright © 2006, Oracle. All rights reserved.
Using the Hang Analysis Page
Micro-window
9-19
Copyright © 2006, Oracle. All rights reserved.
Automatic Workload
Repository
MMON
ADDM finds
top problems.
SYSAUX
SGA
In-memory
statistics
6:00 a.m.
7:00 a.m.
8:00 a.m.
9:00 a.m.
Snapshot 1
Snapshot 2
Snapshot 3
Snapshot 4
9:30 a.m.
9-21
Copyright © 2006, Oracle. All rights reserved.
Tuning
Statistics
SGA Attach
> AWR
Advisors
Async. Commit
AWR Snapshot Baselines
Relevant period
in the past
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( start_snap_id IN NUMBER ,
end_snap_id
IN NUMBER ,
baseline_name IN VARCHAR2);
9-23
Copyright © 2006, Oracle. All rights reserved.
Tuning
Statistics
SGA Attach
AWR
> Advisors
Async. Commit
Advisory Framework:
Overview
SQL Tuning
Advisor
PGA
Buffer Cache
Advisor
Memory
ADDM
PGA Advisor
SGA
SQL Access
Advisor
Library Cache
Advisor
Segment Advisor
Space
Undo Advisor
9-24
Copyright © 2006, Oracle. All rights reserved.
Database Control and Advisors
9-26
Copyright © 2006, Oracle. All rights reserved.
Typical Advisor Tuning Session
Create an advisor task.
Adjust task parameters.
Perform analysis.
No
Implement
recommendations.
9-27
Copyright © 2006, Oracle. All rights reserved.
Accept
results?
Yes
Manually Invoking ADDM
9-28
Copyright © 2006, Oracle. All rights reserved.
Using the SQL Tuning Advisor: Review
•
•
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
9-29
Copyright © 2006, Oracle. All rights reserved.
SQL Access Advisor: Overview
Workload specification
-SQL statement
-SQL Tuning Set
-SQL cache contents
-Statistics
-Schema name
Recommendations
-Indexes
-Materialized views
-Materialized view logs
SQL Access
Advisor
9-30
Copyright © 2006, Oracle. All rights reserved.
Typical SQL Access Advisor Session
9-31
Copyright © 2006, Oracle. All rights reserved.
Workload Source
9-32
Copyright © 2006, Oracle. All rights reserved.
Recommendation Options
9-33
Copyright © 2006, Oracle. All rights reserved.
Recommendation Options
9-34
Copyright © 2006, Oracle. All rights reserved.
Reviewing Recommendations
9-35
Copyright © 2006, Oracle. All rights reserved.
Asynchronous COMMIT
•
•
•
•
The default COMMIT behavior is to wait until redo is
saved in the redo log files.
The default behavior can now be changed to “not
wait.”
Asynchronous COMMIT is useful for high
transaction throughput.
However, transactions may be lost.
– Machine crashes
– File I/O problems with redo log files
9-36
Tuning
Statistics
SGA Attach
AWR
Advisors
> Async. Commit
Copyright © 2006, Oracle. All rights reserved.
Using Asynchronous COMMIT
•
Possible combinations:
–
–
–
–
•
IMMEDIATE, WAIT (this is the default)
IMMEDIATE, NOWAIT
BATCH, WAIT
BATCH, NOWAIT
System- or session-level examples:
ALTER SYSTEM SET COMMIT_WRITE = IMMEDIATE, WAIT
ALTER SESSION SET COMMIT_WRITE = IMMEDIATE, NOWAIT
•
COMMIT statement examples:
COMMIT WRITE BATCH WAIT
COMMIT WRITE BATCH NOWAIT
9-37
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Diagnose database performance issues
• Configure the Automatic Workload Repository
• Access the database advisors
• Use the SQL Access Advisor to improve database
performance
• Use asynchronous COMMIT effectively
9-38
Copyright © 2006, Oracle. All rights reserved.
Practice Overview: Using ADDM to
Diagnose Performance Problems
This practice covers the following topics:
• Viewing and interpreting ADDM findings to
diagnose a performance problem
• Implementing those findings
9-39
Copyright © 2006, Oracle. All rights reserved.