18
Using Oracle Expert Effectively

This chapter presents four major areas to consider as you seek to maximize the benefits provided by Oracle Expert. These areas include:

By following the guidelines presented in this chapter, you will minimize the hardware resources and the time required for Oracle Expert to complete an evaluation.

Defining the Appropriate Tuning Scope

Oracle Expert provides the ability to selectively refine the scope of a tuning session. The tuning process consists of selecting both the appropriate tuning scope and collecting the appropriate subset of information about the database environment.

The tuning scope helps direct Oracle Expert into specific problem areas. This reduces both the amount of information to be collected and the time required to complete the analysis. For large or complex database environments, this means you get results in minutes rather than hours or days.

For example, performing data access analysis for a particular schema rather than a table in that schema results in large performance differences. Oracle Expert eliminates from consideration SQL statements that are not relevant to the currently selected tuning focus. Many more statements can be eliminated early in the evaluation process when the data access tuning focus has been specified at a table level, so less work is required than if a complete schema is tuned.

The tuning scope determines the options that are available from the Collection page. Focused tuning eliminates the need to collect certain types of information, which saves time.

Providing Complete and Accurate Data

The recommendations generated by Oracle Expert are only as good as the information you provide. The more complete and accurate the data is, the better the recommendations will be, because Oracle Expert will not have to make as many assumptions.

For example, if you are performing instance parameter tuning, you should supply accurate system information. System information includes details such as the amount of physical memory and different resource uses for the system. The instance tuning rules use this information during the evaluation of many of the parameters.

Another area that affects the evaluation process is the tuning session characteristics. Specific rules within the Oracle Expert knowledge base are dependent on tuning session characteristic settings. For example, the type of database environment you specify, either Data Warehousing, OLTP, or Multipurpose, has a large influence on the evaluation process. If you allow this particular tuning session characteristic to default to OLTP and your database environment is primarily Data Warehousing, certain recommendations that optimize performance for Data Warehousing environments will not be recommended.

It is also important to ensure that the information available in the Oracle Expert repository is up-to-date. Changes to the database, such as server upgrades or adding an index to a table, influence the evaluation process. Providing up-to-date information will result in up-to-date recommendations.

Using Iterative Tuning for Improved Performance

Oracle Expert maintains historical information about your database environment within its repository. Successive iterations of a tuning session provide additional performance optimizations over time. These successive iterations are also useful for tuning the environment as resources and workloads fluctuate.

Iterative tuning is particularly useful for instance parameter tuning. Oracle Expert provides two mechanisms for collecting statistics about the instance. These are:

single snapshot
duration-based statistics collection

The single snapshot mechanism uses the information from a single snapshot of the Oracle Server's dynamic performance tables. This is a subset of the information available from the duration-based mechanism. In addition, the single snapshot approach cannot handle situations where the dynamic performance tables overflow the space allocated for them.

The duration-based snapshot mechanism uses the differences between two snapshots from the Oracle Server's dynamic performance tables. Multiple instance statistics records can be collected using this mechanism for a user-selected time period and duration. This approach collects additional statistics that are not available from the single snapshot approach

To effectively use Oracle Expert's instance parameter rules, you should collect instance statistics using the duration-based snapshot mechanism for periods when the database is heavily used. This is the period when potential problems and resource bottlenecks will be most evident. In addition, the Oracle Expert rules are implemented to be conservative when making recommendations that result in resource reductions. Resource reduction rules require a minimum of 10 instance statistics samples to be collected before the rules are considered to be valid. This can be changed by setting the "Samples for statistical significance" common rule.

Another advantage of performing iterative tuning with Oracle Expert over time is that as changes within the database environment are made, such as server upgrades or hardware changes, Oracle Expert will automatically identify any dependencies that occur.

Taking Advantage of Rules

The Oracle Expert rule mechanism allows advanced users to have ultimate control over the analysis process. Rules can be used to:

override the Oracle Expert recommendations
limit the evaluation of specified objects within the database environment
restrict or disable other rules
constrain the range of certain Oracle Expert recommendations

For example, the "Percent physical memory available" rule controls the amount of physical memory on a system that can be used for a database instance. By default, the "Percent physical memory available" rule is 80 percent of the total memory available. If the user knows that the system must support multiple instances, the "Percent physical memory available" rule can be instantiated for the system and adjusted to a value of 40 percent. The subsequent analysis would ensure that no more than 40 percent of the total memory on the system was used for the instance.

Rules reside with the object that they affect. The "Percent physical memory available" rule resides with the system object, because it affects the amount of total memory available to the instance on the system. The "Maximum sorted indexes per table" rule resides with the table, because it affects the number of indexes allowed on the table.

The following table lists all of the rules currently available at the database instance parameters level. Oracle Expert uses these parameters to generate instance tuning recommendations. The more information Expert has about your environment, the better the recommendations will be. For detailed descriptions of these parameters, see the Oracle8 Administrator's Guide.

Table 18-1 Oracle Expert Database Parameters

Common	gc_releasable_locks
async_write	log_buffer
buffer_pool_keep	log_checkpoint_interval
buffer_pool_recycle	log_checkpoint_timeout
checkpoint_process	open_cursors
compatible	parallel_automatic_tuning
db_block_buffers	parallel_max_servers
db_block_lru_latches	parallel_min_servers
db_file_multiblock_read_count	shared_pool_size
db_file_simultaneous_writes	shared_pool_reserved_min_alloc
db_writers	shared_pool_reserved_size
db_writer_processes	sort_direct_writes
dbwr_io_slaves	sort_write_buffer_size
disk_async_io	sort_write_buffers
gc_db_locks	sort_area_retained_size
gc_files_to_locks	sort_area_size

Using Existing Analysis Statistics for Very Large Tables

Oracle Expert does a "select distinct count *" for each column in the table when an Expert Scan is performed. This can take a very long time for large tables. To save time, use existing analysis statistics if they are current.

Index Rebuild Detection Requires Analysis Statistics

To make recommendations, the Check for Optimal Data Access requires ANALYZE statistics. Expert Scan only generates statistics for indexes that should be rebuilt.

Use SQL History to Avoid Extra SQL Collections

To avoid extra SQL collections, collect SQL History and use it as the source for each tuning session collection. The SQL History collection prevents having to recollect SQL from the cache or trace.

18 Using Oracle Expert Effectively