Advisor Central

---

Now that we are on the topic of the advisors, let's go back and take a more detailed look at each of the advisors in the Advisor Central page. This page offers links to the following advisors, along with the ability to create a task for these advisors:

SQL Tuning Advisor

The SQL Tuning Advisor gives recommendations on changing SQL statements, to be rewritten so that they are more efficient. The SQL statements are referenced by a new column that is part of V$ACTIVE_SESSION_HISTORY, called SQL_ID. ADDM will detect statements that appear to be inefficient, and/or are leading to a disproportionate number of waits, and may suggest that you run the SQL Tuning Advisor against a given SQL statement. The SQL Tuning Advisor, when accessed from Advisor Central, will allow you to look at the top SQL for a specific period of time, create SQL tuning sets, or choose to view/create snapshots and/or baselines.

If a high-usage SQL statement shows up in an ADDM run, that statement will be displayed in the ADDM Analysis section of the EM database home page. Clicking on the statement will take you into a slightly different look, in the ADDM Finding Details view. From here, you will be able to run the SQL Tuning Advisor against the particular SQL statement.

Top SQL and Period SQL

The Top SQL link shows us by default the Spot SQL view, which shows five-minute intervals over the past hour. The highlighted section can be moved to display any five-minute interval over the past hour. The top SQL statements will be ordered by activity percentage, CPU percentage, and wait percentage, respectively, with the SQL_ID being the identifier for a given statement. The Period SQL link will allow a look at SQL statements over a longer period of time (a week), with the highlighted section giving you a look at a given 24-hour period within that week.

Clicking on the SQL_ID will give you an in-depth look at that particular statement, including the actual statement itself, the execution plan, current statistics, and execution history. The Current Statistics link has valuable information on the amount of space the statement takes in the shared_pool, as well as loads (parses) and invalidations, referring to the number of times this particular statement has been aged out of the shared_pool. A statement with a high number of loads and/or invalidations may be a candidate for pinning in the shared_pool.

Creating SQL Tuning Sets

SQL tuning sets can be created from within this page to allow a group of statements, either by type or by time period, to be analyzed. You may even want to create a tuning set based on just a single statement. Once the set is created, you can view the SQL statements by cost, buffer gets, disk reads, and so on. You can then choose to run either the SQL Tuning Advisor or the SQL Access Advisor against the tuning set. When choosing the criteria to create a tuning set, you might want to look at all of the statements in a given period of time, you may look at all SELECT statements within a period of time, or you may want to just grab the top one or two statements to create a tuning set.

SQL Access Advisor

The SQL Tuning Advisor will not give suggestions on adding or removing indexes, or making other physical changes. Changes like this would change the access method of a statement without changing the actual statement itself. For those types of recommendations, we refer you to the SQL Access Advisor. The SQL Access Advisor gives recommendations on either creating materialized views or creating indexes, or both, in order to give more efficient access for SQL statements. As input, you can use current and/or recent SQL activity that is still cached in memory. In addition, you can use a workload from the SQL Repository (requires that a SQL tuning set be created), or you can get a user-defined workload from a table. In addition, you can create a hypothetical workload, given certain inputs.

Memory Advisor and ASMM

In Oracle 9i, the concept of a dynamically resizable SGA was introduced, giving a DBA the ability to adjust one SGA parameter downward and then increase another value upward in its place. The Memory Advisor in Oracle Database 10g allows shared memory management to be enabled, which essentially automates this same process-hence the term automatic shared memory management, or ASMM. When shared memory management is enabled, a new parameter, SGA_TARGET, is set to a numeric value that becomes the ceiling on the total SGA size. Once shared memory management is enabled, you are essentially allowing Oracle to automatically adjust the values that define the total SGA size, on the fly, up to the total for SGA_TARGET.

ASMM Ceilings and Floors

As an oversimplified example, consider if your total SGA size is 1GB, divided equally between db_cache, large_pool, shared_pool, and java_pool. This would essentially give you values of around 250MB for each of these parameters. With shared memory management enabled, Oracle will monitor the stats associated with these SGA values, and may decide to reduce one parameter in favor of increasing another. For example, suppose Oracle detects that during a certain interval, the buffer cache hit ratio is lower than 80 percent, while at the same time the amount of free memory in the large_pool is relatively high. Oracle may decide to reduce the large_pool_size by 50 percent (to 125MB), and then dynamically increase the db_cache_size by 50 percent to 375MB. The overall memory used for the SGA is still the same, meaning that we have not exceeded our ceiling (as defined by SGA_TARGET). However, the distribution of that memory has been altered, with the overall effect of increasing performance by using that memory where it is most needed.

When ASMM is enabled (by setting the SGA_TARGET), you should explicitly set the automatically managed pieces of the SGA to 0. In Oracle Database 10g Release 1, this includes the four parameters mentioned above: shared_pool_size, large_pool_size, java_pool_size, and db_cache_size (only the default db_cache_size is automatically tuned). All other SGA-related parameters should still be manually set. If you set one of the above parameters, defined as auto-tunable, to an explicit value in the spfile or init file, that value will be used as the floor for that parameter-meaning that while Oracle may be allowed to increase the amount of memory allocated to that particular piece of the SGA, it will not be allowed to drop below the explicitly set value. So, continuing with our example, if we set SGA_TARGET to 1GB, and then set each of the above parameters to an explicit value of 250MB, no automatic tuning can be done because the floor for each parameter adds up to the total of SGA_TARGET, which is our ceiling. This leaves no room to increase or decrease anything. For this reason, when SGA_TARGET is set via the Memory Advisor in Enterprise Manager, Enterprise Manager will automatically unset all of the other SGA parameters (that is, set them to 0 values), thus giving Oracle the freedom to modify all parameters on the fly as it sees fit (whether to higher values or lower values). You can view the current settings that Oracle has arrived at for each of the SGA components by querying the view V$SGA_DYNAMIC_COMPONENTS, and these values can also be viewed in the Memory Advisor on EM.

Non-ASMM Tunable SGA Components  The above is an oversimplified example, as the SGA is, of course, comprised of more than just those four parameters. SGA_TARGET must actually account for the entire SGA, including a new parameter in Oracle Database 10g: STREAMS_POOL_SIZE. Aside from that, SGA_TARGET must account for values for the LOG_BUFFER, any KEEP pools, the RECYCLE pool, and any nondefault buffer caches. However, the KEEP pools, the RECYCLE pool, nondefault buffer caches and Streams_Pool values are not currently auto-managed, so these parameters must be set explicitly, even while the other main components of the SGA should be set to 0.

Note 

Oracle cannot increase the total SGA beyond the value for SGA_MAX_SIZE, which by default is set exactly equal to the total SGA size. If you attempt to increase the total SGA size (using the Memory Advisor Wizard) to a value greater than SGA_MAX_SIZE, you will receive an error.

Memory Advice Parameters

If you choose not to enable shared memory management, the Memory Advisor will still be able to give advice on sizes for parameters within the SGA, such as DB_CACHE_SIZE and SHARED_POOL_SIZE, as well as provide advice on the setting of PGA_AGGREGATE_TARGET. This advice is enabled automatically if STATISTICS_LEVEL is set to either TYPICAL or ALL. For advice on the buffer cache, you must also have the parameter DB_CACHE_ADVICE=ON (this will default to ON if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL).

The following views are populated when the above parameters are set to their default values:

v$db_cache_advice v$shared_pool_advice v$java_pool_advice v$mttr_target_advice v$pga_target_advice 

This information is formatted and available in graphical format via the Memory Advisor on Enterprise Manager. By clicking on the Advice button next to the Buffer Cache values in the Memory Advisor, you will see calculations of what the difference would be in number of block reads for various values of DB_CACHE_SIZE. Similarly, the Advice button next to the Shared Pool value (see Figure 3-3) will give you estimates of parse times under different values of SHARED_POOL_SIZE.

By the same token, within the Memory Advisor, you can view estimates on the PGA workload by going to the PGA link (see Figure 3-3 again) and clicking on the Advice button there. This value, of course, depends on PGA_AGGREGATE_TARGET being set to a nonzero value in the init.ora (or spfile).

Figure 3-3: Memory Advisor

Note 

Many of these parameters can be changed dynamically, both in memory and for future startups, if using an spfile. For example, SGA_TARGET, DB_CACHE_ADVICE, and STATISTICS_LEVEL can all be changed on the fly, and SGA parameters such as DB_CACHE_SIZE and SHARED_POOL_SIZE can all be lowered (set to 0) dynamically via an ALTER SYSTEM command.

Additional Advisors

The previous advisors merited sections on their own due to the impact that they potentially have and the new features that they bring with them to Oracle Database 10g. In addition to the advisors mentioned, Advisor Central provides links to other new advisors that can offer useful insights into the performance and layout of your database.

MTTR Advisor

The MTTR Advisor allows making changes to fast_start_mttr_target, which essentially controls how long we want instance recovery to take, should we have a crash. Setting a target value here will cause Oracle to make internal changes to how frequently data is flushed to disk, or checkpointed, to ensure that if a crash recovery occurs, the time to roll forward will be close to the targeted value. This means the crash recovery will complete within x number of seconds, and the database will then be opened and made available. The view V$MTTR_TARGET_ADVICE is used to provide an idea of what additional I/O might be incurred if the MTTR is reduced. This advisor also allows for the setting up of a flash recovery area, and enabling database archivelog mode. This is not tuning related, but of course is intrinsic to high availability, so these topics are covered more appropriately in other chapters.

Segment Advisor

The Segment Advisor essentially looks at space usage and advises on whether you can free up space. It also checks for fragmentation, and determines whether reorganizing individual segments and/or making changes to entire tablespaces can gain efficiencies. If it finds segments that can be reorganized, it will highlight them and provide a recommendation to do a shrink of the segment, and offer the ability to schedule a job to do so immediately or schedule the run for some future time. In Oracle Database 10g, a table or index segment can be compacted via the alter <object> <object_name> shrink … command. This command can be done while the table or index is online, without impacting user access to the segments and without the need to rebuild the indexes afterward. However, row movement must be enabled on the table in question first. Note in Figure 3-4 that the segment in question has generated advice, but the advice cannot be executed because row movement is not enabled. Thus, the selection is grayed out. In order to make any changes to this table, you will have to enable row movement (alter table PROD_EMP enable Row Movement) and then rerun the Segment Advisor. Then you will be able to select the option under the Recommendations section to shrink the segment.

Figure 3-4: Segment Advisor

Undo Management Advisor

The Undo Management Advisor first takes you into a review of the undo management parameters and allows you to make or change settings for enabling automatic undo management, which will automatically tune the UNDO_RETENTION time for you based on longest running queries and available space in the UNDO tablespace. In addition, the Undo Management Advisor allows for making changes to the UNDO tablespace, or actually changing the undo to a different tablespace altogether. From here, you can actually launch into the Undo Advisor itself, and Oracle will give advice on what the undo retention should be (in seconds) to avoid Snapshot Too Old errors (ORA-1555). In addition, the Undo Advisor will estimate the size that the UNDO tablespace needs to be in order to retain that amount of undo. Since Oracle Database 10g provides you with the opportunity to guarantee the retention period, the Undo Advisor will take data from the past seven days (by default), analyze that data, and determine how much undo has been generated during that period. The Undo Advisor will then provide a graph, showing you how large the UNDO tablespace needs to be to achieve the desired UNDO_RETENTION time without running out of space in the UNDO_TABLESPACE.

---