Transcript Document

Automatic Management
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Describe the various tools used to diagnose
database performance issues
• Access the database advisors
• Use the SQL Tuning Advisor to improve database
performance
12-2
Copyright © 2004, Oracle. All rights reserved.
Oracle Wait Events
•
•
12-3
A collection of wait events provides information
on 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 © 2004, 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
12-4
V$SGASTAT
• pool
• name
• bytes
V$SYSTEM_EVENT
• event
• total_waits
• total_timeouts
• time_waited
• average_wait
• time_waited_micro
Copyright © 2004, 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
12-6
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 © 2004, 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
12-7
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 © 2004, Oracle. All rights reserved.
Statistics Collection
•
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
– Importing statistics from another database
12-8
Copyright © 2004, Oracle. All rights reserved.
Automatic Optimizer Statistics Collection:
Overview
•
Oracle8i Database provides DBMS_STATS package:
– DBA determines how to gather statistics.
– DBA determines when to gather statistics.
•
Oracle9i Database determines how to gather
statistics:
– Statistics can be gathered using a single command.
– DBA determines when to gather statistics.
•
Oracle Database 10g fully automates statistics
gathering:
– DBA no longer needs to gather statistics.
– Table monitoring is used by default.
12-9
Copyright © 2004, Oracle. All rights reserved.
Dictionary and Special Views
•
The following dictionary and special views provide
useful statistics after using the DBMS_STATS
package:
–
–
–
–
•
12-11
DBA_TABLES, DBA_TAB_COLUMNS
DBA_CLUSTERS
DBA_INDEXES, INDEX_STATS
INDEX_HISTOGRAM, DBA_TAB_HISTOGRAMS
This statistical information is static until you
reexecute the appropriate procedures in
DBMS_STATS.
Copyright © 2004, Oracle. All rights reserved.
Statspack
•
Execute statspack.snap to collect statistics.
•
Automate the collection of statistics using the
spauto.sql script.
Produce a report using the spreport.sql script.
Set TIMED_STATISTICS to TRUE to collect timing
information.
•
•
12-12
Copyright © 2004, Oracle. All rights reserved.
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.
9:30 a.m.
12-13
Copyright © 2004, Oracle. All rights reserved.
Snapshot 1
Snapshot 2
Snapshot 3
Snapshot 4
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);
12-14
Copyright © 2004, Oracle. All rights reserved.
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
12-15
Copyright © 2004, Oracle. All rights reserved.
Database Control and Advisors
12-17
Copyright © 2004, Oracle. All rights reserved.
Typical Advisor Tuning Session
Create an advisor task.
Adjust task parameters.
Perform analysis.
No
Implement
recommendations.
12-18
Copyright © 2004, Oracle. All rights reserved.
Accept
results?
Yes
Manually Invoking ADDM
12-19
Copyright © 2004, Oracle. All rights reserved.
Application Tuning Challenges
I can do
it for you!
SQL workload
How can I
tune my
high-load
SQL?
ADDM
High-load
SQL
12-20
SQL Tuning
Advisor
Copyright © 2004, Oracle. All rights reserved.
DBA
SQL Tuning Advisor Overview
Comprehensive SQL Tuning
Automatic Tuning Optimizer
Statistics Check
Optimization Mode
Detect Stale/Missing
Statistics
Plan Tuning
Optimization Mode
Plan Tuning
(SQL Profile)
Access Analysis
Optimization Mode
SQL Analysis
Optimization Mode
12-21
Add Missing Index
Run Access Advisor
SQL Tuning
Advisor
Restructure SQL
Copyright © 2004, Oracle. All rights reserved.
SQL Tuning Advisor Options and
Recommendations
12-22
Copyright © 2004, 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
12-23
Copyright © 2004, Oracle. All rights reserved.
Using the SQL Tuning Advisor: Example
12-24
Copyright © 2004, Oracle. All rights reserved.
Using the SQL Access Advisor
12-25
Copyright © 2004, Oracle. All rights reserved.
The Undo Management Page
12-27
Copyright © 2004, Oracle. All rights reserved.
Automatic Undo Retention Tuning
•
Proactive tuning:
– Undo retention is tuned for longest-running query.
– Query duration information is collected every 30
seconds.
•
Reactive tuning:
– Undo retention is gradually lowered under space
pressure.
– Oldest unexpired extents are used first.
– Undo retention never goes below either
UNDO_RETENTION or 15 minutes (whichever is less).
•
12-28
Enabled by default
Copyright © 2004, Oracle. All rights reserved.
The Undo Advisor Page
12-29
Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe the various tools used to diagnose
database performance issues
• Access the database advisors
• Use the SQL Tuning Advisor to improve database
performance
12-30
Copyright © 2004, Oracle. All rights reserved.
Practice 12 Overview:
Optimizing Database Performance
This practice covers proactively tuning your database
using ADDM.
12-31
Copyright © 2004, Oracle. All rights reserved.