Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

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


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%.
Oracle Tutorial - BLOGS

STEP 10 - Next Topics
Monitoring the health of a database and ensuring that it performs optimally is an important task for a database administrator. This discusses the features and functionality included in Oracle Database that make it easy to proactively monitor database health, identify performance problems, and implement any corrective actions. Click on Oracle tuorial links below:
Proactive Database MonitoringDatabase Monitoring
Oracle Diagnosing Performance ProblemsPerformance Problems
Using Advisors to Optimize Database PerformanceAdvisors for peformance
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Return to Learn Oracle

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info