|
Step 10 - Optimize database Performance
|
Learn Oracle - Using Advisors to Optimize Database Performance
Advisors are powerful tools for database management. They provide specific advice
on how to address key database management challenges, covering a wide range of
areas including space, performance, and undo management. In general, advisors
produce more comprehensive recommendations than alerts. This is because alert
generation is intended to be low cost and have minimal impact on performance,
whereas advisors are user-invoked, consume more resources and perform more
Note: If Oracle DBA need more frequent ADDM reporting, Oracle DBA can also
modify the default snapshot interval. This along with the what-if capability of some advisors provides
vital information for tuning that cannot be procured from any other source.
About Performance Advisors
This section primarily with the advisors that can improve performance. These
advisors include the SQL Tuning, SQL Access, and Memory Advisors.
For example, the shared pool memory advisor graphically displays the impact on
performance of changing the size of this component of the SGA.
Examples of situations in which an advisor might be invoked include:
Oracle DBA want to resolve a problem in a specific area, for example, why a given SQL
statement is consuming 50% of CPU time and what to do to reduce its resource
consumption.Oracle DBA would use the SQL Tuning Advisor here.
During application development, Oracle DBA want to tune new schema and its associated
SQL workload for optimal performance. Oracle DBA would use the SQL Access Advisor
here.
Oracle DBA want to tune memory usage to find the optimum size for your memory
structures such as the shared pool or buffer cache. Oracle DBA can use the memory
advisors or ADDM here.
Advisor Description
Automatic Database
Diagnostic Monitor (ADDM)
ADDM makes it possible for the Oracle Database to diagnose
its own performance and determine how any identified
problems can be resolved.
SQL Tuning Advisor This advisor analyzes SQL statements and makes
recommendations for improving performance.
SQL Access Advisor Use this advisor to tune schema to a given SQL workload. For
example, the access advisor can provide recommendations for
creating indexes and materialized views for a given workload.
Memory Advisor
Shared Pool Advisor
(SGA)
Buffer Cache Advisor
(SGA)
PGA Advisor
The Memory Advisor is the main advisor for system memory
and is responsible for optimizing memory on the instance as a
whole. Oracle DBA have the option of having Oracle auto-tune
memory. If Oracle DBA choose not to have Oracle auto-tune memory,
then Oracle DBA can invoke the SGA Advisors or the PGA Advisor to
obtain optimal settings for the components and total size of
the SGA or PGA. See "Using the Memory Advisor".
Using Advisors to Optimize Database Performance
Monitoring and Tuning the Database 10-11
Oracle DBA can invoke an advisor from the Advisor Central home page that is displayed
when Oracle DBA click Advisor Central under the Related Links heading on the Database
Home page, or on other pages where it is listed. You can invoke advisors in other
ways, often through recommendations from ADDM or alerts.
Using the SQL Tuning Advisor
Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this
advisor in response to an ADDM performance finding recommending its use.
Additionally, you can run the SQL Tuning Advisor on the most resource intensive SQL
statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a
user-defined SQL workload.
To run the SQL Tuning Advisor do the following:
1. On the Home Page, under Related Links, click Advisor Central, then click SQL
Tuning Advisor. The SQL Tuning Advisor Links page appears.
2. The advisor can be run on one of the following sources
Top SQL---The consist of recently active top SQL statements from the cursor
cache (Spot SQL) or historical top SQL from the AWR (Period SQL).
SQL Tuning Sets---These consist of SQL statements Oracle DBA provide. An STS can
be created from SQL statements captured by AWR snapshots or from any SQL
workload.
3. For example, Oracle DBA can select Top SQL. The Top SQL page appears. This page has
two tabs, Spot SQL and Period SQL. Spot lists recent top SQL from the cursor
cache, while Period SQL lists historical top SQL captured in the AWR. Oracle DBA must
select an interval to analyze by dragging the shaded box over the period. Oracle DBA then
select one or more SQL statements to analyze during the selected period.
4. Click Run SQL Tuning Advisor. The SQL Tuning Options page appears showing
the SQL statements in the interval. Give your task a name and description, select
the scope for the analysis (Comprehensive or Limited), and select a start time for
the task. Click OK.
5. Navigate back to the Advisor Central page. The status of Advisor Tasks are listed
under this heading in the results section. You must wait until your task status is
COMPLETED. You can check the status by clicking your browser’s Refresh button.
Then, select your task and click View Result.
Segment Advisor The Segment Advisor provides advice on whether an object is
a good candidate for a shrink operation based on the level of
space fragmentation within an object. The advisor also reports
on the historical growth trend of segments. You can use this
information for capacity planning and for arriving at an
informed decision about which segments to shrink.
Undo Advisor The Undo Advisor helps in identifying problems in the undo
tablespace and assists in correctly sizing the undo tablespace.
The Undo Advisor can also be used to set the low threshold
value of the undo retention period for any flashback
requirements. S The SQL Tuning Result page appears. To view recommendations, select the SQL
statement and click View Recommendations. The recommendation can include
one or more of the following:
Create an index to offer alternate, faster access paths to the query optimizer.
Accept SQL profile, which contains additional SQL statistics specific to the
statement that enables the query optimizer to generate a significantly better
execution plan.
Gather optimizer statistics on objects with stale or no statistics
Advice on how to rewrite a query for better performance.
Using the SQL Access Advisor
The SQL Access Advisor helps define appropriate access structures such as indexes
and materialized views to optimize SQL queries. It takes a SQL workload as an input
and recommends which indexes, materialized views, or logs to create, drop, or retain
for faster performance. You can select your workload from different sources including
current and recent SQL activity, a SQL repository, or a user-defined workload such as
from a development environment.
The recommendations that this advisor makes include possible indexes, materialized
views, or materialized view logs that can improve your query performance for the
given workload.
To run this advisor, navigate to the Advisor Central page, and click SQL Access
Advisor. This begins a wizard which starts by prompting Oracle DBA for your workload
source. Oracle DBA then select if you want the advisor to recommend indexes, materialized
views or both. You can select to run the advisor in limited or comprehensive mode.
Limited mode runs faster by concentrating on highest cost statements.
Oracle DBA then schedule and submit your job. Results are posted on the Advisor Central
page. The SQL Access Advisor recommendations are ordered by cost benefit. For
example, a recommendation might consist of a a SQL script with one or more CREATE
INDEX statements, which Oracle DBA can implement by clicking Schedule Implementation.
Using the Memory Advisor
The Memory Advisor helps you tune the size of your memory structures. You can use
this advisor only when automatic memory tuning is disabled.
The memory advisor comprises three advisors that give Oracle DBA recommendations on the
following memory structures:
Shared pool in SGA
Buffer cache in SGA
PGA
To invoke the Memory Advisors, click Memory Advisor on the Advisor Central page.
The Memory Parameters: SGA page appears. This page gives breakdown of memory
usage for the system global area (SGA). This memory area is a group of shared
memory structures that contain data and control information for a single Oracle
instance. The shared pool and buffer cache are part of this area. For more information
on these structures, click Help.
The Automatic Shared Memory Management setting should be disabled in order to
run the advisor. To run either the shared pool advisor or the buffer cache advisor, click
Advice next to the field.
For example, to run the advisor on the shared pool, click Advice next to this field. The
Shared Pool Advice graph appears.
Change in parse time saving is plotted against shared pool size. A higher number for
parse time saving is better for performance. In this example, the graph tells us that a
shared pool size larger than 80M will not improve performance by much. Thus 80M is
the recommended optimal shared pool size.
Similarly, to run the advisor on the buffer cache, click Advice next to this field. The
Buffer Cache Size Advice graph appears, plotting relative change in physical reads
versus cache size. Since a bigger cache implies less disk reads, the smaller number for
change in physical reads is better for performance. Like the shared pool memory, there
is usually an optimal cache size above which performance improvement is negligible.
To run the PGA advisor, click the PGA property page. Running this advisor is similar
to running the SGA advisors. Cache hit percentage is plotted against memory size.
Higher hit ratios indicate better cache performance. The optimal zone for cache hit
percentage is between 75 and 100%.
|
|
|
