New Optimizer Improvements

The Cost Based Optimizer is at the heart of the Oracle kernel and plays a large part in the efficient execution of SQL statements in Oracle Database 10g. This is a very complex and involved topic; at the very least, however, you need to know about some of the new features in the optimizer that is now available in Oracle Database 10g. This chapter discusses the most important set of changes, along with some conceptual information that will help you keep your database running efficiently. We can promise you that the going will be heavy in this chapter, but reading it is well worth it because of the useful nuggets of information about the optimizer it contains.

As you saw in previous chapters, the Optimizer in Oracle Database 10g has been substantially improvedso much so that the familiar but staid Rule Based Optimizer (RBO) has finally been placed to rest. Although the official de-support of the RBO is probably the most visible improvement in this area, there are a number of other improvements, as well as documented pitfalls of which you need to be aware.

When we use the term Optimizer when discussing Oracle Database 10g, we mean the Cost Based Optimizer, or CBO. Oracle Corporation, in fact, is proposing to refer to the CBO as the Oracle Query Optimizer (OQO) in future versions.

Before you launch into the changes to the Optimizer in Oracle Database 10g, however, you'll need some basic understanding of the terms that we will use later on, as well as an overview of how the Optimizer works. As we describe some of these elements, we will also mention how certain aspects have been changed in the Optimizer in Oracle Database 10g.

RBO and CBO: A Comparison

The Optimizer is at the heart of the Oracle kernel's access layer and determines how the required data should be accessed. Until Oracle 7, the Rule Based Optimizer (RBO) was the only optimizer in use. In Oracle 7 and onward, Oracle Corporation made the Cost Based Optimizer (CBO) available in the database. The difference between the two optimizers is simple: The RBO instructs the Oracle Kernel to get to the data based on a set of rules, while the CBO performs this action using the estimated cost of access. It does this by performing calculations based on statistics about objects such as tables, indexes, columns, and histograms, and choosing the best access route or execution plan among the many available paths that have the lowest cost of access. (The CBO, in fact, was turned on by the CHOOSE keyword in the OPTIMIZER_MODE initialization parameter's value in previous versions of the software.) This possible execution plan can be determined using the EXPLAIN PLAN statement. When the SQL is traced, the actual execution plan can also be seen in raw form in the trace file or in a processed form in the TKPROF output after the trace file is processed. The plan that was used for the actual execution can also be seen via the V$SQL_PLAN view on Oracle 9i databases and above immediately after the SQL statement completes executing.

What Does Cost Mean?

The CBO uses cost as a basis for all its decisions. So what exactly does this cost mean? There have been (and will certainly continue to be) various arguments about what cost means within the Oracle user community. A precise explanation that everyone agrees on does not really exist. The cost has no relation to the run time of the query; a query with higher cost may complete sooner than a query with lower cost. The cost is, however, closely related to the estimated number of I/O operations. In fact, in some cases, the cost is a function of the number of I/O requests. Whatever the case may be, it is a fact that cost is the currency used by the Optimizer to compare two or more competing execution paths. More on this later!

Both the CBO and the RBO produce execution plans. The RBO cannot, however, use statistical information about the object when generating an execution plan. The CBO, on the other hand, is flexible enough to adapt its access path according to this information. Thus, if the size and nature of the objects varies, the CBO can adapt and adjust its execution plan accordingly. The CBO can also cater to new types of objects such as function-based indexes, partitioned tables, bitmap indexes, and so on, as well as perform new methods of access such as hash joins, while the RBO does not. For these reasons, Oracle has been discouraging the use of the RBO and has in fact fully de-supported its use with Oracle Database 10g onward. This is one of the major changes in the Optimizer. Indeed, it forces all users to use the Cost Based Optimizer, because that is the only optimizer that Oracle Database 10g supported. In fact, Oracle manuals and other documents use the words Optimizer or Query Optimizer instead of the term CBO. We use the same terminology here; hence, all references to the Optimizer specifically mean the CBO unless otherwise stated. For more details on the obsolescence of the RBO, see MetaLink Note #189702.1.

Moving to the Cost Based Optimizer

Even though the CBO has been around for more than a decade, many applications continue to use the RBO. If you are responsible for one such RBO-based application, you might want to read a paper titled "Safely Navigating the RBO to CBO Minefield," written by one of the authors of this book and available on the Internet at http://www.geocties.com/john_sharmila/links.htm. This paper, although written for Oracle 8i, espouses practical principles that are applicable to the Optimizer of today, and points out pitfalls that you should avoid and myths that still abound.

What the Optimizer Does

A SQL statement can be executed in many different ways, such as using full table scans, index scans in certain cases, and join methods such as nested loops and hash joins when one table is joined to one or more others. The Query Optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time. Thus, improvements in the Optimizer greatly improve the holistic performance of the databases based on Oracle Database 10g, as you will see later in this chapter.

We will now look at how the Optimizer works. The various operations of the Optimizer include:

• Evaluation of expressions and conditions. First, the Optimizer must evaluate all expressions and conditions in the specified SQL statements and simplify these conditions and expressions as much as possible.

• Statement transformation. For complex statements involving correlated subqueries or views, the optimizer transforms the original statement into an equivalent join statement. In other circumstances, the Optimizer can break up a complex query into smaller chunks. After this is done, the Optimizer may merge views, push predicates, and perform query rewrites if supporting structures such as materialized views are available. These actions are controlled to some extent by certain Optimizer parameters, including the hidden COMPLEX_VIEW_MERGING initialization parameter.

• Choice of optimizer goals. The Optimizer determines the goal of optimization. The default goal, or mode, is ALL_ROWS. This optimizes for throughput and is best suited for batch and OLAP processing, which requires all the selected rows to be returned as quickly as possible. The other goal, or mode, is FIRST_ROWS, which optimizes for best response, where the first set of rows is returned as quickly as possible. This goal mostly influences the join method. Note that the default value of the OPTIMIZER_MODE parameter in Oracle Database 10g is ALL_ROWS rather than CHOOSE, as in previous versions. In fact, because there is no choice, the CHOOSE as well as the RULE values for the OPTIMIZER_MODE parameter have been made obsolete in Oracle Database 10g. The functionalities for these hints remain, but Oracle Corporation does not support their use, and promises to remove even these functionalities in a future release.

• Choice of access paths. For each table accessed by the statement, the Optimizer chooses one or more of the available access paths to obtain table data. This includes full table scans; row ID scans; various types of index scans including index unique scans, index range scans, index skips scans, and fast full index scans; and cluster and hash access.

• Choice of join orders. When parsing a join statement that joins more than two tables, the Optimizer chooses the first pair of tables to join, and then the next table that is joined to the result, and so on. For the best run times, it is important that the first join produces the smallest result set (or number of rows returned from the join) and increases in number as the join progresses. A large number of join methods is available, including nested loop, hash, sort merge, Cartesian, and outer joins. The Optimizer must pick the type of join that is appropriate for the intended Optimizer goal, objects involved, and execution environment for that SQL.

Throughout these operations, the estimated cost of access for each of these paths is continually calculated and evaluated, and the best plan chosen on the basis of lowest cost.

Full Table ScansGood or Bad?

Full table scans (FTSes) have historically been considered evil. Indeed, performance analysts have always been advised to avoid FTS. That said, there are cases when an FTS may actually result in less I/O and, with fewer computing resources being consumed. Without going into details, keep in mind that the DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) parameter kicks in to enable reading of MBRC-number of blocks in a single I/O. In a simple example, suppose a table consists of 16 blocks and the MBRC is set to 16, then the entire table could be read into the buffer cache in just one I/O operation, even if just one row was actually required. In comparison, an indexed read may have required at least two or even three readsfirst the root block, next, a leaf block if present, and finally the actual data block, all of these operations using a single I/O each. In many cases, the operating system performs its own read improvements, such as read ahead and track caching, and can satisfy the I/O requirement of an FTS with far less actual I/O.

So how does the Optimizer make all these decisions? The answer to this is the key to this entire chapter. Simply put, the Optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement. The cost is an estimated value that is proportional to the expected resource use needed to execute the statement with a particular plan. In Oracle Database 10g, the Optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which now include I/O and CPU. The key word here is estimated. In other words, the Optimizer applies certain calculations using internal algorithms to the statistics stored in the data dictionary for the involved objects. Expressed in another way, the cost of a query is arrived at in the following manner:

Cost = (Costing Algorithms, Influenced by Optimizer Settings and System Statistics) -> (Operating on Object Statistics)

When the object statistics are incorrect, do not exist, or do not reflect the current state of the object, or when the Optimizer settings are incorrectly configured, the Optimizer may arrive at the wrong cost values for various alternatives. As a result, the query chooses an inefficient path, resulting in poor performance. These two sets of inputsnamely, object statistics and Optimizer settingsare under the direct control of the performance analyst. It is obvious that you should have the proper set of up-to-date object statistics as well as the correct values for the Optimizer settings in order for the Optimizer to perform optimally.

What is more subtle, though, is that the algorithms themselves can have a major influence on the cost calculations. As well, the Oracle kernel must make some assumptions about the environment in the form of the cost of I/O and the availability of CPU resources. If the parameters of the environment are not known or if a bug is present in the algorithms, then the cost model could again go wrong. In Oracle Database 10g, most of these risks have been mitigated in some form or other, as discussed in the next few sections.

It is important to note that the Oracle Database 10g Optimizer can operate in an Advanced Automatic Tuning mode, which it does when invoked by the SQL Tuning Advisor. You saw details of how this is done and what it entails in Chapter 13, "Effectively Using the SQL Advisors."

The Relevance of Object Statistics

As you saw before, the Optimizer is very dependent on correct and up-to-date object statistics. These statistics include the following:

• Table statistics, including the number of rows, number of blocks both used and empty, average row length, and so on. This is exposed via the DBA_TABLES view. When table statistics are not present, the Optimizer assumes some internal default values for number of blocks, average row length, and so on in order to proceed.

• Column statistics, including the number of distinct values (NDV) in a column, low and high values, the number of nulls, and density. This is exposed via the DBA_TAB_COL_STATISTICS view, as well as through the more familiar DBA_TAB_COLUMNS view. Data distribution for columns is available in a separate structure known as a histogram. This is exposed via the DBA_HISTOGRAMS view.

• Index statistics, including the number of leaf blocks, the index level, the clustering factor, and so on. This is exposed via the DBA_INDEXES or the DBA_IND_STATISTICS view. As with table statistics, when index statistics are missing, the Optimizer assumes some default values for level, number of leaf blocks, number of distinct keys and so on.

  The default values for missing table and index statistics is detailed in the Oracle Database 10g Performance Tuning Guide in Chapter 15, "Managing Optimizer Statistics," in the section titled "Handling Missing Statistics."

  
  

• System statistics, including I/O performance and utilization as well as CPU performance and utilization. This is stored in the undocumented AUX_STATS$ table in the SYS schema. Although, in a strict sense, system statistics are not really object statistics, we will discuss them within the context of this topic for the sake of continuity.

COMPUTE Versus ESTIMATE

Oracle Database 10g collects object statistics by scanning the table or index to obtain values such as the average row size, the number of distinct values, the clustering factor, and so on. Oracle allows you to estimate these values using a sample number of table or index blocks or compute these values accurately using a full scan. When the objects are large, a proportionally larger amount of I/O, and thus time and CPU resources, is spent collecting these statistics. Hence, practical issues of time and resources, especially on today's large databases, mandate that a sample set of blocks from the object be scanned and the statistics estimated using the ESTIMATE option rather than computed via the COMPUTE option. Note, though, that there are cases when the use of the ESTIMATE option would automatically upgrade into a COMPUTE optionwhen the SAMPLE_SIZE is greater than 50% or the object being sampled is less than 100 blocks.

Even if the matter of using ESTIMATE or COMPUTE is settled, the argument about what percentage of data should be used for estimation continues. Oracle Database 10g (and even Oracle 9i) allows you to specify a parameter DBMS_STATS.AUTO_SAMPLE_SIZE in the statistics-gathering routines that can be used to automatically estimate the best value for the estimate percentage. We suggest using this parameter, especially for very large objects, as the Optimizer scales down the amount of I/O used for the sample while ensuring that accuracy is not lost. The downside is some extra I/O sampling for probes as the Optimizer works with smaller or larger samples, going in either direction until it gets the right level. The initial sample takes into account the original size estimate from the object data size available in the DBA_EXTENTS view.

All these object and system statistics are maintained by the built-in DBMS_STATS PL/SQL package, a well-known package that has existed since Oracle 8i and needs no explanation. The various procedures, including GATHER_TABLE_STATS, GATHER_INDEX_STATS, and GATHER_SYSTEM_STATS, can be used to gather and store the aforementioned object and system statistics. Object statistics can also be manually set, exported to and imported from specially created statistics tables, and manipulated in many other ways using other procedures in DBMS_STATS.

New in Oracle Database 10g is the capability to lock down object statistics at the table or the schema level using the LOCK_TABLE_STATS or the LOCK_SCHEMA_STATS. When this is done, procedures such as GATHER, SET, and so on cannot be used to change the statistics; in fact, an error will be raised in the event an attempt is made to perform this action. Many other new procedures and functions are now available in Oracle Database 10g, and details about them can be found in the Oracle Database 10g PL/SQL Packages and Types Reference.

The Relevance of System Statistics

DBMS_STATS Versus ANALYZE

Prior to the arrival of DBMS_STATS in Oracle 8i, the ANALYZE command was used to collect object statistics. Although the ANALYZE command was deprecated in Oracle 9i, many sites continue to use this command to collect such statistics. Be aware that ANALYZE does not collect certain important sets of statistics related to partitions as well as some of the newer objects in Oracle 8i and above, and hence should not be used to collect object statistics. It is, however, still present as a valid command and should be used only to collect nonOptimizer related information, such as information about chained rows and freelist blocks, as well as to perform the VALIDATE function on tables and indexes. For more details, refer to MetaLink Note #236935.1.

System statistics are different from object statistics in that they quantify the capability of the environment in which the database operates. In other words, system statistics can be used to describe the performance characteristics of the hardware platform including the CPU and I/O. The Optimizer can then compare the CPU costs and I/O costs. Because the speed of the CPU, as well as I/O performance, varies widely between different configurations, it is essential that the optimizer be able to factor these values into its cost calculation model. Rather than relying on a fixed formula for combining CPU and I/O costs, Oracle provides a facility for gathering information about the characteristics of an individual system during a typical workload in order to determine the CPU speed and the performance of the various types of I/O including single-block, multi-block, and direct-disk I/Os. By tailoring the system statistics for each hardware environment, Oracle's cost model can be very accurate on any configuration from any combination of hardware vendors.

Although the concept of system statistics was introduced in Oracle 9i, these statistics were not gathered or used by default. In Oracle Database 10g, system statistics are collected by default, and the costing model uses them automatically. In addition, a number of additional parameters such as I/O seek time and I/O transfer speed are collected and used in cost calculations.

The DBMS_STATS.GATHER_SYSTEM_STATS procedure can be used for gathering system statistics. The data thus collected can be stored in a user-defined statistics table under a named category. Later, you can use the DBMS_STATS.IMPORT_SYSTEM_STATS procedure to make these values active. This can be used to set, for example, the appropriate values for I/O during a batch-processing period favoring multi-block I/O and later changed during an OLTP period to favor single-block I/O. For details on how this can be done, see MetaLink Note #149560.1.

The collection of object statistics invalidates the currently cached SQL cursors that refer to those objects whose statistics were collected. Collection of system statistics, on the other hand, does not invalidate current SQL cursors. However, only new SQL statements being parsed after the collection completes will use the latest system statistics for cost calculation.

You should be very careful how and when these statistics are collected, because they exert a powerful influence on cost calculations. Although the CPU speed remains constant, the estimated I/O figures could be unnaturally skewed because of unusual activity taking place during collection. In case you want to play safe, we recommend that you do not collect system statistics at random, but instead set these values once and for all by using values specified by the vendor of the hardware configuration in use. You could also use an I/O benchmarking tool such as IOZone (http://www.iozone.org) to determine the values for multiple types of reads, and use that benchmark to set the values for the system statistics.

The current, live system statistics are stored in the AUX_STATS$ table in the SYS schema. Listing 14.1 shows a sample of the contents of this table.

Listing 14.1. Sample System Statistics in AUX_STATS$

 SQL> column sname format a20 SQL> column pname format a15 SQL> column pval1 format 999999.999 SQL> column pval2 format a20 SQL> column status format a15 SQL> select * from sys.aux_stats$; SNAME                PNAME                 PVAL1 PVAL2 -------------------- --------------- ----------- -------------------- SYSSTATS_INFO        STATUS                      COMPLETED SYSSTATS_INFO        DSTART                      04-18-2005 01:40 SYSSTATS_INFO        DSTOP                       04-18-2005 02:40 SYSSTATS_INFO        FLAGS                 1.000 SYSSTATS_MAIN        CPUSPEEDNW          584.441 SYSSTATS_MAIN        IOSEEKTIM             4.573 SYSSTATS_MAIN        IOTFRSPEED        11554.658 SYSSTATS_MAIN        SREADTIM              4.576 SYSSTATS_MAIN        MREADTIM              5.644 SYSSTATS_MAIN        CPUSPEED            580.000 SYSSTATS_MAIN        MBRC                 11.000 SYSSTATS_MAIN        MAXTHR SYSSTATS_MAIN        SLAVETHR 

System Statistics in a RAC Environment

The AUX_STAT$ view does not have the INST_ID column that can be used to identify the instance in a Real Application Cluster (RAC). Thus, the statistics apply globally across all nodes. If you use different nodes in a RAC cluster for different purposessay, one for reporting and one for OLTP type of applicationthen the system stats will not provide the true picture for any one node, and the CPU costing may not be accurate for that type of workload.

You can see from Listing 14.1 that the system statistics were collected in the space of an hour (DSTART/DSTOP). The CPU speed for both at workload (CPUSPEED) and no workload (CPUSPEEDNW) is shown in million cycles per second. The single-/multi-block I/O (SREADTIM/MREADTIM) values are the wait time, in milliseconds, to read single- and multi-block reads. MBRC is the estimated best value for DB_FILE_MULTIBLOCK_READ_COUNT. The I/O seek time (IOSEEKTIM) is the seek time in milliseconds influenced by both the disk latency and operating system buffering overhead. The I/O transfer speed (IOTFRSPEED) is the number of bytes transferred from the disk each millisecond. These values were collected to show actual values; hence, we recommend that you use the DBMS_STATS.SET_SYSTEM_STATS procedure to set predetermined values in the system statistics tables.

The Relevance of Dictionary Statistics

The conventional wisdom has always been that statistics on data dictionary objectsthat is, on objects owned and used by SYS and SYSTEM schemas for internal operationsshould never be collected. Although this restriction was removed starting in Oracle 9i, it is still common to find that object statistics do not exist for the SYS, SYSTEM, and other system-related schemas such as MDSYS and CTXSYS. Starting in Oracle Database 10g, however, object statistics on these data dictionary objects are collected and used. The DBMS_STATS.GATHER_DICTIONARY_STATS procedure is used for this purpose, and you are encouraged to keep the dictionary statistics up to date.

Dynamic Sampling

One of the most important new features in the Optimizer is the automatic collection of these object statistics using what is called dynamic sampling. When the Optimizer determines that either the required object statistics are stale or, worse still, that tables or indexes involved in the query do not have statistics at all, estimated values of object statistics are automatically collected for these objects. Although dynamic sampling was introduced in Oracle 9i Release 2 using the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter with a default value of 1, the default level of this parameter was increased to 2 in Oracle Database 10g.

The value of this level can range from 0 to 10, with a value of 0 effectively disabling dynamic sampling and increasing levels taking more aggressive estimates for collections of statistics. For example, at level 1, the optimizer samples all tables that do not have statistics if the following criteria are met:

• There is at least one table with no statistics in the query.

• This table is joined to another table or appears in a subquery or non-mergeable view.

• This table has no indexes.

• This unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. An undocumented parameter OPTIMIZER_DYN_SMP_BLKS controls the number of blocks used for sampling and is set at 32 by default.

At level 2, all tables and objects that are not analyzed will be sampled regardless of the exclusions at level 1, and the number of blocks sampled doubles to 64. At level 3, all objects at level 2, as well as analyzed tables where a guess at selectivity was made, are sampled. At level 4, all objects at level 3 along with tables that have single-table predicates that reference two or more columns are sampled.

At levels 5 through 9, the criteria for previous levels are applied, but the number of blocks sampled doubles at every level. At level 10, all tables that meet the criteria for level 9 are selected, but the whole table is sampled. Although the default value of 2 is sufficient in most cases, you can easily go up to a value of 4 or more in order to fully equip the Optimizer with correct statistics in DSS types of applications. The end result is that having missing statistics is no longer a major concern.

Dynamic sampling is very useful when using temporary tables and staging tables in your application. The nature of these tables is such that the volume of data varies significantly during the course of a day, but the object statistics may not reflect this correctly depending on what time they were collected. For example, staging tables may be purged at the end of the day, but may have millions of rows during the processing part of the day. If object statistics were collected at the end of the day, these tables would be empty, and the statistics would not accurately reflect the picture that evolves during the day. In the case of temporary tables that exist for the duration of the transaction or session only, dynamic sampling will provide an accurate picture during the time when a query is executed against such a temporary table.

Downside of Dynamic Sampling

Dynamic sampling occurs at parse time, when the cost calculation is performed, so be prepared to suffer longer parse times and increased resources allocated and recorded under the parse time elapsed values in the Time and Wait model statistics (V$SYS_TIME_MODEL view) discussed in Chapter 10, "Adopting a New Approach to Tuning." Because this is performed using recursive SQL, the counts in V$SYSSTAT for recursive calls will go up as well. Depending on the level that has been set, some amount of I/O and CPU is used to perform sampling and related calculations. As well, dynamic sampling occurs only when the OPTIMIZER_FEATURES_ENABLE initialization parameter is set to at least 9.2.0 or higher. Dynamic sampling also cannot be performed on external tables or on remote tables in other databases accessed via database links.

When dynamic sampling is not set or enabled, or if object statistics cannot be collected because the objects are either remote or in external tables, the Optimizer is forced to assume certain fixed values. This includes an almost ridiculous assumption of 2,000 rows for remote tables and a row length of 100 bytes. Both dynamic sampling and the details of these assumptions are discussed in the Oracle Database 10g Performance Tuning Guide. Note that dynamic sampling can also be forced by the new DYNAMIC_SAMPLING hint in a SQL statement.

Table and Index Monitoring

As mentioned previously, one of the main factors influencing the Optimizer is the presence (or absence, or staleness, as the case may be) of up-to date object statistics, not to mention whether those object statistics are the correct set. The amount of data stored in tables (and in their indexes by extension) varies depending on the level of DML activity on the tables. Although DBAs schedule periodic collection of statistics for all objects in their databases, the blind collection of object statistics may result in wasted I/O and computing resources as well as varying performance when the statistics collection window spills over into an active period. This is where table and index monitoring comes in.

Table monitoring was introduced in Oracle 8i, and index monitoring in Oracle 9i. When monitoring is switched on for selected tables, counts for DML activity on those tables, through execution of INSERT, DELETE, and UPDATE statements, are recorded and exposed via the DBA_TAB_MODIFICATIONS view. Depending on the level of DML activity on these tables, a DBA could schedule selective collection of object statistics so that only the very active tables are analyzedthis, too, according to the volume of DML. In Oracle Database 10g, table monitoring is switched on by default when the STATISTICS_LEVEL is set to TYPICAL or ALL. SMON collects the information by scanning V$SQL; the information is updated periodically. This is a very useful tool in helping to reduce the cost, resource, and time window for collection of object statistics while keeping the statistics up to date. Any object missed here would then be caught by dynamic sampling.

For further details on usage, please refer to MetaLink Note #102334.1. Although this note was written for Oracle 8i, the contents are applicable even in Oracle Database 10g with the only caveat being that monitoring is now automatically enabled.

How Often Should You Collect Object Statistics?

The frequency of collection of object statistics has been a source of contention for many DBAs. Many sites resort to a daily or weekly collection of statistics on all objects. This is usually counterproductive, however, because such collections, when not completed in time, can actually result in sudden variations in performance as statistics are incompletely collected. As well, they impose a regularoften unnecessaryload on the system when the statistics are collected. Table monitoring, available from Oracle 8i onward, should always be used to determine only those objects that merit such collection. We recommend setting up a process whereby only those objects that undergo a change in 10% of the total rows should be included in a round of object statistics collection.

Note that the counters for a table in the DBA_TAB_MODIFICATIONS view are reset when object statistics is collected for that table. That way, the most active tables bubble up for collection again and again and always have fresh statistics. Inactive tables, which do not need object statistics to be collected as often, would still have up-to date statistics as the contents are determined not to have changed as per the DBA_TAB_MODIFICATIONS view.

Index monitoring exposes the use of indexes in the V$OBJECT_USAGE view, specifically in the USED column, and can be used to ascertain the presence of unused indexes. Note that this information can be picked up in the SQL Access Advisor, as shown in Chapter 13. MetaLink Note #144070.1 has more details on this topic.

Parameters Influencing the Optimizer

Certain Optimizer-related initialization parameters, both well-known and hidden, influence the costing algorithms and hence the cost calculations and, ultimately, the efficient execution of the query. Many books, websites, and even MetaLink Notes refer to these parameters; these should be read and understood. In addition, these parameters should not be changed without a complete understanding as well as Oracle support's advice.

You saw two of these initialization parametersnamely OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_SEARCH_LIMITin earlier chapters. A complete list of parameters that can directly influence the Optimizer is shown in Listing 14.2. This can be obtained fairly easily using a little-known trace event numbered 10053. You will see how to use this trace later; suffice it to say that it can reveal a lot about what is happening under the surface.

Listing 14.2. Initialization Parameters

   *************************************   PARAMETERS WITH ALTERED VALUES   ******************************   db_file_multiblock_read_count       = 16   sqlstat_enabled                     = true   statistics_level                    = all   *************************************   PARAMETERS WITH DEFAULT VALUES   ******************************   optimizer_mode_hinted               = false   optimizer_features_hinted           = 0.0.0   parallel_execution_enabled          = true   parallel_query_forced_dop           = 0   parallel_dml_forced_dop             = 0   parallel_ddl_forced_degree          = 0   parallel_ddl_forced_instances       = 0   _query_rewrite_fudge                = 90   optimizer_features_enable           = 10.1.0   _optimizer_search_limit             = 5   cpu_count                           = 2   active_instance_count               = 1   parallel_threads_per_cpu            = 2   hash_area_size                      = 131072   bitmap_merge_area_size              = 1048576   sort_area_size                      = 65536   sort_area_retained_size             = 0   _sort_elimination_cost_ratio        = 0   _optimizer_block_size               = 8192   _sort_multiblock_read_count         = 2   _optimizer_max_permutations         = 2000   pga_aggregate_target                = 24576 KB   _pga_max_size                       = 204800 KB   _sort_space_for_write_buffers       = 1   _query_rewrite_maxdisjunct          = 257   _smm_auto_min_io_size               = 56 KB   _smm_auto_max_io_size               = 248 KB   _smm_min_size                       = 128 KB   _smm_max_size                       = 1228 KB   _smm_px_max_size                    = 7371 KB   _cpu_to_io                          = 0   _optimizer_undo_cost_change         = 10.1.0   parallel_query_mode                 = enabled   parallel_dml_mode                   = disabled   parallel_ddl_mode                   = enabled   optimizer_mode                      = all_rows   _optimizer_percent_parallel         = 101   _always_anti_join                   = choose   _always_semi_join                   = choose   _optimizer_mode_force               = true   _partition_view_enabled             = true   _always_star_transformation         = false   _query_rewrite_or_error             = false   _hash_join_enabled                  = true   cursor_sharing                      = exact   _b_tree_bitmap_plans                = true   star_transformation_enabled         = false   _optimizer_cost_model               = choose   _new_sort_cost_estimate             = true   _complex_view_merging               = true   _unnest_subquery                    = true   _eliminate_common_subexpr           = true   _pred_move_around                   = true   _convert_set_to_join                = false   _push_join_predicate                = true   _push_join_union_view               = true   _fast_full_scan_enabled             = true   _optim_enhance_nnull_detection      = true   _parallel_broadcast_enabled         = true   _px_broadcast_fudge_factor          = 100   _ordered_nested_loop                = true   _no_or_expansion                    = false   optimizer_index_cost_adj            = 100   optimizer_index_caching             = 0   _system_index_caching               = 0   _disable_datalayer_sampling         = false   query_rewrite_enabled               = true   query_rewrite_integrity             = enforced   _query_cost_rewrite                 = true   _query_rewrite_2                    = true   _query_rewrite_1                    = true   _query_rewrite_expression           = true   _query_rewrite_jgmigrate            = true   _query_rewrite_fpc                  = true   _query_rewrite_drj                  = true   _full_pwise_join_enabled            = true   _partial_pwise_join_enabled         = true   _left_nested_loops_random           = true   _improved_row_length_enabled        = true   _index_join_enabled                 = true   _enable_type_dep_selectivity        = true   _improved_outerjoin_card            = true   _optimizer_adjust_for_nulls         = true   _optimizer_degree                   = 0   _use_column_stats_for_function      = true   _subquery_pruning_enabled           = true   _subquery_pruning_mv_enabled        = false   _or_expand_nvl_predicate            = true   _like_with_bind_as_equality         = false   _table_scan_cost_plus_one           = true   _cost_equality_semi_join            = true   _default_non_equality_sel_check     = true   _new_initial_join_orders            = true   _oneside_colstat_for_equijoins      = true   _optim_peek_user_binds              = true   _minimal_stats_aggregation          = true   _force_temptables_for_gsets         = false   workarea_size_policy                = auto   _smm_auto_cost_enabled              = true   _gs_anti_semi_join_allowed          = true   _optim_new_default_join_sel         = true   optimizer_dynamic_sampling          = 2   _pre_rewrite_push_pred              = true   _optimizer_new_join_card_computation = true   _union_rewrite_for_gs               = yes_gset_mvs   _generalized_pruning_enabled        = true   _optim_adjust_for_part_skews        = true   _force_datefold_trunc               = false   _optimizer_system_stats_usage       = true   skip_unusable_indexes               = true   _remove_aggr_subquery               = true   _optimizer_push_down_distinct       = 0   _dml_monitoring_enabled             = true   _optimizer_undo_changes             = false   _predicate_elimination_enabled      = true   _nested_loop_fudge                  = 100   _project_view_columns               = true   _local_communication_costing_enabled = true   _local_communication_ratio          = 50   _query_rewrite_vop_cleanup          = true   _slave_mapping_enabled              = true   _optimizer_cost_based_transformation = linear   _optimizer_mjc_enabled              = true   _right_outer_hash_enable            = true   _spr_push_pred_refspr               = true   _optimizer_cache_stats              = false   _optimizer_cbqt_factor              = 50   _optimizer_squ_bottomup             = true   _fic_area_size                      = 131072   _optimizer_skip_scan_enabled        = true   _optimizer_cost_filter_pred         = false   _optimizer_sortmerge_join_enabled   = true   _optimizer_join_sel_sanity_check    = true   _mmv_query_rewrite_enabled          = false   _bt_mmv_query_rewrite_enabled       = true   _add_stale_mv_to_dependency_list    = true   _distinct_view_unnesting            = false   _optimizer_dim_subq_join_sel        = true   _optimizer_disable_strans_sanity_checks = 0   _optimizer_compute_index_stats      = true   _push_join_union_view2              = true   _optimizer_ignore_hints             = false   _optimizer_random_plan              = 0   _query_rewrite_setopgrw_enable      = true   _optimizer_correct_sq_selectivity   = true   _disable_function_based_index       = false   _optimizer_join_order_control       = 3 

The new parameters that appear in Oracle Database 10g and that are now acknowledged as influencing the Optimizer have been highlighted in bold in Listing 14.2. As you can see, numerous hidden parameters influence the Optimizer. Interestingly enough, a number of parameters, including CPU_COUNT, now play a part in describing the environment. As well, you may also note that parameters that were previously exposed, such as HASH_JOIN_ENABLED, HASH_MULTIBLOCK_IO_COUNT, and the previously discussed OPTIMIZER_SEARCH_LIMIT, are now hidden. Indeed, Oracle Database 10g Release 1 has 151 parameters, both hidden and exposed, that influence the Optimizer, while Oracle 9i Release 2 has just 62 parameters of this nature, revealing the extent of internal algorithmic changes in the Optimizer.

Hidden Parameters

Note that there are no "silver bullet" hidden parameters, such as MAKE_SQL_GO_FASTER, in Oracle Database 10g. Even so, Oracle Database 10g continues to depend on these initialization parameters, and has in fact increased the number of hidden parameters from 588 in Oracle 9i Release 2 to a humungous 912 in Oracle Database 10g Release 1. As before, these parameters should not be changed without Oracle Support's advice.

Oracle Database 10g has reduced the number of exposed parameters that influence the Optimizer while at the same time increasing the number of hidden parameters in this genre. This enables simplification from a user's point of view while providing greater control of the Optimizer to both the internal algorithms as well as Advanced Oracle Support for tuning purposes.

Cardinality, Selectivity, and Column Usage

Cardinality is the number of rows expected to be returned by the query or the row set. The Optimizer needs to have some mechanism to compute the cardinality of the query. Selectivity, on the other hand, is a factor with a value between 0 and 1 and is a measure of the percentage of rows from the table that are estimated to be selected by the query. The cardinality can be seen in the output of an EXPLAIN PLAN command in the CARD or CARDINALITY column. Both these values depend heavily on column statistics; hence, it is important that these statistics be kept up to date. When column statistics do not reflect the current data distribution, the Optimizer may choose an inefficient execution path.

The availability of additional structures such as histograms and indexes on columns can greatly help in providing good selectivity for queries involving these columns. So how do you decide which columns need these supporting structures? The answer lies in a little-known feature called column usage tracking, which was actually introduced in Oracle 9i itself. In Oracle Database 10g, it is turned on by default and hence collects information about how a particular column is used.

In Oracle Database 10g, the Oracle SMON (System Monitor) process regularly gathers information on columns that are used in query predicates and updates them automatically into a table named COL_USAGE$ in the SYS schema. Unlike the MON_MOD$ table, also in the SYS schema, which records table-level DML activity such as the number of rows changed by INSERT, DELETE, and UPDATE operations, and exposes them via the DBA_TAB_MODIFICATIONS view, the COL_USAGE$ view is neither documented nor wrapped by a legible view. The column names, however, are very revealing; these are detailed in Table 14.1.

Table 14.1. Description of Columns in the COL_USAGE$ View

Column Name	Description
OBJ#	Object number. Corresponds directly to the OBJ# column in the SYS.OBJ$ table. You can use this to determine the name of the table containing that column.
INTCOL#	Column number. Corresponds directly to the COL# column in the SYS.COL$ table. You can use this to determine the name of the column involved.
EQUALITY_PREDS	Number of times this column uses an equality predicate of the form table.column = constant.
EQUIJOIN_PREDS	Number of times this column was used in an equijoin using a predicate of the form table1.column1 = table2.column2.
NONEQUIJOIN_PREDS	Number of times this column was used in an nonequijoin using a predicate of the form table1.column1 != table2.column2.
RANGE_PREDS	Number of times this column was used as a predicate of the form table1.column1 BETWEEN constant1 AND constant2.
LIKE_PREDS	Number of times this column was used as a predicate of the form table1.column1 LIKE like_constant.
NULL_PREDS	Number of times this column was used as a predicate of the form table1.column1 IS NULL.
TIMESTAMP	The timestamp when the column was last recorded as having been used as a predicate in any query. This is updated by SMON once every 15 minutes.

Using the values in this table, you can determine whether additional indexes and histograms can be created on these columns based on how the columns are referred and by what type of predicates.

As usual, there are some caveats. Be aware that SMON obtains this information by scanning the library cache in the shared pool and updating this table once every 15 minutes or so. In an active database with memory pressure on the shared pool, this information may be lost. This information may also be lost when the database is shut down unless you invoke the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure beforehand.

Other Apparent Uses of COL_USAGE$

The Oracle kernel seems to utilize the information stored in the COL_USAGE$ table to determine which columns should have histograms when the options => GATHER AUTO parameter is specified during object statistics-gathering at all levels. As well, it seems that this information is being used to recommend creating (and dropping) additional indexes in the various advisors.

Tracing the Optimizer

No account of the Optimizer would be complete without mention of the 10053 event. Just as the 10046 event produces a detailed trace of the execution of SQL, the 10053 event produces a detailed trace of the inner workings of the Optimizer. In fact, the list of initialization parameters affecting the Optimizer was determined using such a trace. This trace is very cryptic and hard to read, but if understood, produces a wealth of information about the Optimizerincluding how the final cost was arrived at as well as the various alternate routes (and their costs) considered. Listing 14.3 shows a snippet of the 10053 trace file. You can see the various objects and their statistics being considered. For example, CDN is the estimated cardinality for the table, NBLKS is the number of blocks in the table, and LVLS and CLUF are the number of levels and clustering factor of the named index. You can also see the effects of CPU and I/O costing in the calculations.

You can look at MetaLink Note #225598.1, titled "How to Obtain Tracing of Optimizer Computations (EVENT 10053)," if you are interested in further details.

Listing 14.3. Snippet of 10053 Event Trace File

 QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0   fro(0): flg=0 objn=49788 hint_alias="EMPLOYEES"@"SEL$1" *************************************** BASE STATISTICAL INFORMATION *********************** Table stats    Table: EMPLOYEES   Alias: EMPLOYEES   TOTAL ::  CDN: 13696  NBLKS:  496  AVG_ROW_LEN:  117 Index stats   Index: EMP_EMAIL_UK  COL#: 4     TOTAL ::  LVLS: 1   #LB: 136  #DK: 13696  LB/K: 1  DB/K: 1  CLUF: 13696   Index: EMP_EMP_ID_PK  COL#: 1     TOTAL ::  LVLS: 1   #LB: 136  #DK: 13696  LB/K: 1  DB/K: 1  CLUF: 13696   Index: EMP_EMP_ID_PK  COL#: 1     TOTAL ::  LVLS: 1   #LB: 26  #DK: 13696  LB/K: 1  DB/K: 1  CLUF: 235   Index: EMP_JOB_IX  COL#: 7     TOTAL ::  LVLS: 1   #LB: 37  #DK: 19  LB/K: 1  DB/K: 120  CLUF: 2285   Index: EMP_MANAGER_IX  COL#: 10     TOTAL ::  LVLS: 1   #LB: 67  #DK: 18  LB/K: 3  DB/K: 148  CLUF: 2675   Index: EMP_NAME_IX  COL#: 3 2     TOTAL ::  LVLS: 1   #LB: 256  #DK: 13696  LB/K: 1  DB/K: 1  CLUF: 13689 _OPTIMIZER_PERCENT_PARALLEL = 0 *************************************** SINGLE TABLE ACCESS PATH   TABLE: EMPLOYEES  Alias: EMPLOYEES     Original Card: 13696  Rounded Card: 13696  Computed Card: 13696.00   Access Path: table-scan  Resc:  97  Resp:  97   Access Path: index (index-ffs)     Index: EMP_EMAIL_UK     rsc_cpu: 968516   rsc_io: 27     ix_sel:  0.0000e+00    ix_sel_with_filters:  1.0000e+00   Access Path: index-ffs  Resc:  27  Resp:  27   Access Path: index (index-ffs)     Index: EMP_EMP_ID_PK