An Overview of 10G Advisors

The "Adminstratorless" Database
I have read quips and quotes from various industry pundits proclaiming that the next release of so-and-so database would no longer require DBAs for support. Oh, OK… I'm still waiting. Database vendors know that they must add new features to be competitive. Each new release of Oracle contains so many new features that I feel like I have to learn to support the database all over again (which is why I like this job, by the way). As the manager of a database group, I spend way more time than I would like trying to make sure my organization understands all of the features that Oracle offers. The more solutions the database inherently provides, the more cost effective it becomes. These integrated features allow technical personnel to solve business problems without the additional costs of writing custom code and/or integrating multiple vendor solutions. Its part of my job to make sure my organization leverages these features when appropriate.

Oracle10G allows us to administer disk, lash multiple computers together to enable grid computing and the list goes on, and on, and on.... It took me four different articles to cover just a subset of the new features available in Oracle's latest release. The database may become easier to administer in some areas, but there are so many new features being incorporated into the product that administrative duties are just being migrated to other areas. Oracle ASM now allows my unit to administer disk. How many Oracle DBAs could have predicted that they would have the capability to manage their own storage subsystems? Can any one of us predict what IBM, Oracle and Microsoft have up their sleeves in their next "latest and greatest" release? Not me. But THAT is what makes this job exciting.

Database Advisor Overview
OK, enough ranting and back to the topic at hand. Advisors are user invoked utilities that perform detailed analysis on key components of the Oracle database. Advisors provide comprehensive recommendations and some advisors allow what-if scenarios to be performed. Oracle states that advisors do consume resources to provide these detailed recommendations so we are currently testing all of the advisors to determine each one's actual resource utilization. As soon as we compile our findings, I'll make sure to make that information available to you.

If you take a look at the database home page, you'll notice a link titled Advisor Central. The Advisor Central home page allows administrators to access all of the advisors that are available in 10G Enterprise Manager. The Advisor Tasks section of the page displays output from advisor executions. By default, only the ADDM advisor is set to run automatically after the database is started. So, if you haven't manually scheduled other advisors, ADDM will be the only task execution that will appear in the task output section of the page.

Let's take a high level look at each of the advisors available on the Advisor Central home page. Starting with my next blog, I'll provide more in-depth information on each of the individual advisors.

ADDM
Before we discuss ADDM, a brief overview of the Automatic Workload Repository that it uses as input is warranted. The Automatic Workload Repository collects performance statistics (and the SQL text itself) for all SQL statements executed in the database. It is a historical performance data warehouse that stores SQL statement CPU, memory and I/O resource consumption. AWR runs by default and Oracle states that it does not add a noticeable level of overhead. The information in this repository is used as input for the various advisors. This historical information will help administrators finally answer questions like "my program ran long two days ago, can you fix it?"

A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository. MMON also provides Oracle10g with a server initiated alert feature, which notifies database administrators of potential problems (out of space, max extents reached, performance thresholds, etc.).

The Automatic Database Diagnostic Monitor analyzes the information contained in the Automatic Workload Repository every 30 minutes to pinpoint problems and provide automated recommendations to administrators. If ADDM requires additional information to make a decision, it will activate other advisories to gather more information. ADDM's output includes a plethora of reports, charts, graphs, heartbeats and related visual aids. In general, ADDM provides users with a top-down analysis of performance bottlenecks and their associated resolutions. Oracle recommends that DBAs should review ADDM output first when performing performance problem analysis.

Oracle documentation touts the following ADDM benefits:

• Provides hourly diagnostic reports by default
• The tool's intelligence contains hundreds of years of Oracle expertise
• Identifies the root cause of the problem, not just the symptoms
• Problem diagnosis based on decades of tuning expertise
• ADDM incurs minimal overhead during execution

SQL Access Advisor and SQL Tuning Advisor
These two new advisories will help Oracle DBAs with the "fine art" of SQL tuning. In the past SQL tuning could be defined more of an art than as a science. Hopefully, these two new advisories will put the science back into the SQL tuning process.

The SQL Access Advisor uses the Automated Workload Repository to provide recommendations on creating Oracle objects (additional indexes, materialized views, etc.) to increase the performance of poorly performing SQL statements.

The utility is able to accept input from current and historical workloads, SQL Tuning Sets (user defined collections of various SQL statements) and SQL statements contained in user defined tables. The tool is also able to create a hypothetical workload against a group of predefined tables that use database defined referential integrity constraints. We are currently testing the hypothetical workload feature here at Giant Eagle. I'll present our findings to you when I focus on the SQL Access Advisor in an upcoming blog.

The SQL Tuning Advisor also uses the Automated Workload Repository to capture and identify high resource consuming SQL statements. An intelligent analyzer is then used to assist administrators in tuning the offending SQL statements.

Take a look at this screen print of the panel that appears when you click on the SQL Tuning Advisor link on the Advisor Central Home Page. You'll notice that the SQL Tuning Advisor can not be called directly from Advisor Central. The utility can only be called from the Top SQL, SQL Tuning Sets, Snapshots and Preserved Snapshot Sets panels. We'll discuss each of those calling panels in-depth in the SQL Tuning Advisor blog.

The SQL Tuning Advisor provides advice and includes information justifying why it generated those recommendations. This prevents the advisor from becoming a "black hole" that pumps out recommendations without supporting information. The recommendations may include collecting statistics on objects, new index creation, restructuring the SQL statement or the creation of a SQL Profile to create and "freeze" a more optimal access path.

The SQL Tuning Advisor uses the Oracle10G cost based optimizer to rewrite the poorly performing SQL to create the SQL Profile, which is then stored in the data dictionary. Each time the poorly performing SQL statement executes, the rewritten statement stored in the data dictionary is used in its place.

The optimizer's job is to create the most high performance access path in the shortest time possible. Its easy to understand how these two objectives can be viewed as "mutually exclusive." In a perfect world, you would give the optimizer as much time as it needs to create the most optimal access path. In the real world you need to get the statements executing as quickly as possible. That includes optimization. Since the SQL Tuning Advisor does not have time constraints, the tool can take as much time as necessary to optimize the statement. The SQL Tuning Advisor Home Page provides a drop down menu that allows the administrator to "throttle" the amount of time the utility spends optimizing the statement.

The SQL Tuning Advisor will be especially beneficial to administrators who support third-party applications. Before the advent of Stored Outlines (predecessor to SQL Plans), once the administrator identified the canned application's poorly performing SQL, the third-party vendor was contacted to change the SQL code and the changed code then implemented in test and finally in production to effect the tuning change. Anyone who has experience with third-party application vendors knows that this is often a time consuming (if not impossible) process. Creating SQL Plans allows the administrator to correct the poorly performing SQL statement immediately without vendor assistance.

MEMORY ADVISOR
The Memory Advisor can only be used when the automatic memory tuning feature is disabled. So before we begin our discussion on the Memory Advisor, I'll provide you with a few quick details on automatic SGA segment sizing.

The automatic SGA segment sizing feature of Oracle10G simplifies administration of the various memory components that make up the System Global Area (SGA). In previous releases, database administrators were required to specify the amount of memory allocated to the buffer cache, shared pool, java pool and large pool. Setting, monitoring and adjusting these parameters was time-consuming and oftentimes guesswork. Sizing them incorrectly usually led to poor performance and/or out-of-memory conditions.

Oracle10G administrators are able to set a single, dynamic parameter, SGA_TARGET, to allocate memory to the entire Oracle SGA. Oracle10G uses the value contained in SGA_TARGET to determine how much memory to allocate to the buffer cache, shared pool, large pool and java pool. During normal database operations, the database engine periodically reviews memory usage and redistributes memory between the components accordingly.

For those administrators who still want to maintain more granular control over the size of the various SGA components, Oracle 10G Grid Control contains a memory advisor that provides sizing advice. This advisor is pretty straight-forward. If you have the automatic SGA segment sizing feature disabled, an advice button appears next to each SGA component. Clicking on the advice button takes the user to a "what if" chart that shows the relationship between the SGA component's size and hit ratio.

MTTR ADVISOR
Setting the FAST_START_MTTR_TARGET parameter to control the amount of time the database takes to perform instance recovery must be done with some forethought. Administrators must balance the needs of fast instance recovery with the performance implications of setting the value of FAST_START_MTTR_TARGET too small. Before we begin discussing the advisor, some background information is in order.

Periodically, Oracle's DBWR flushes blocks from the data buffer cache to the database files. One mechanism that initiates this event is a checkpoint. Checkpoints ensure that modified data buffers are written to the database files on a regular basis.Oracle records changes using System Change Numbers (SCNs). Each transaction is assigned a SCN to identify the changes made by the SQL statements within that transaction. The SCN that is associated with the current checkpoint has all of its changes flushed from the data buffer cache to the database files. If a database failure occurs, Oracle knows that all transactions that have SCNs higher than that number must be "replayed" during the recovery process. All SCNs lower than the number associated with the checkpoint have already been flushed to the database files and don't need to be recovered. The more blocks that need to be recovered, the longer the recovery will take. So, frequent checkpoints reduce recovery time by reducing the amount of blocks needing to be recovered. But checkpointing too frequently negatively impacts runtime performance by overworking the DBWn processes.

Once again, Oracle10G comes to the rescue with another new advisor, the MTTR Advisor. You can use MTTR Advisor to evaluate the effect of different FAST_START_MTTR_TARGET settings on system performance, compared to your chosen setting. Oracle populates the dynamic performance view V$MTTR_TARGET_ADVICE with the information collected by MTTR Advisor.

SEGMENT ADVISOR
Getting rid of unused space in a table improves the performance of full table scans. Removing unused space in index structures provides quicker index access due to a more compact tree. The additional benefit is a more efficient utilization of space because the space freed is available for other objects. In releases prior to Oracle10G, getting rid of free space above and below a table's high-water mark was usually done by the tried-and-true, Export, Drop Table, Import or ALTER TABLE MOVE commands. Administrators could also use the ALTER INDEX REBUILD statement to reorganize index segments to create a more compact index tree.

Oracle10G objects that reside in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk using the "ALTER………SHRINK" statement. The shrink statement is not limited to just heap tables and indexes, Oracle also allows index-organized tables, partitions, subpartitions, materialized views and materialized view logs to be shrunk using ALTER commands. In addition, the CASCADE option can be used to propagate the shrink operation to all dependent objects except materialized views, LOB indexes, IOT mapping tables and overflow segments.

Administrators are able to use Oracle10G's Segment Advisor to identify candidates for shrink operations. The advisor estimates the amount of unused space that will be released when the shrink operation is run on the particular object. A wizard is available that allows users to evaluate all objects in the database, all objects in a specific tablespace or all objects owned by a particular schema.

Although we have tested this in our labs, we have yet to test the segment advisor in an active 10G environment. The DBA running the 10G project here likes to "aggressively test and implement" new releases here. Rest assured that in future blogs, I'll be providing you with a wealth of information on the segment shrink statement and the segment advisor

UNDO ADVISOR
A transaction uses an undo segment to record before images of data it intends to change. If the transaction fails before committing, Oracle uses the before images to rollback or undo the uncommitted data changes. Oracle also uses undo segments for statement-level read consistency. Read consistency ensures that all data returned by a query comes from the same point-in-time (query start time). Lastly, undo segments provide before images of data to help the instance roll back failed transactions during instance recovery.

You don't have to be an Oracle expert to know that even though we have migrated to system-managed undo segments, administering them can be "somewhat troublesome." Out of space conditions, contention, poor performance and the perennial favorite "snap shot too old" errors have been plaguing Oracle database administrators for over a decade. System managed undo segments has lessened, but not entirely alleviated, this burden.

The parameter UNDO_RETENTION specifies the amount of time that Oracle attempts to keep undo data available. Setting this parameter too low may result in "snapshot too old" errors being generated. This occurs when a query that is attempting to maintain read consistency fails to obtain a before image of a changed data block. The data block has been aged out or overwritten by more current before images. Setting the parameter too high may cause undo data to fill up the undo tablespace which leads to out of space errors.

The 10G Undo Advisor provides recommendations on setting the UNDO_RETENTION parameter as well as sizing the undo tablespace. The administrator sets the amount of time that the advisor will analyze and the advisor reviews the workload that was executed during the specified time period.The advisor is also able to analyze the impact of new undo retention settings.

Next Up
In-depth analysis of each advisor