optimizer_index_caching和optimizer_index_cost_adj

optimizer_index_caching
有些（大多数）索引位于缓冲区高速缓存中，可以使用 optimizer_index_caching参数告诉
oracle能够在缓冲区中找到索引块的平均百分比，即是索引在缓冲区中出现的几率
值范围: 0 - 100
默认值0 ，对cbo来说，意味着0％的数据块（使用索引访问）可以在oracle‘s SGA的buffer cache中发现。
即所有的对索引的访问都将需要物理读（每一个对buffer cache的逻辑读都产生一个对I/o子系统的物理读），
也可以看作是：对buffer cache 0％的hit ratio。该参数只影响CBO计算访问索引块时候的成本，和涉及的表无关
调整范围应该在 65-95


optimizer_index_cost_adj
用来告诉oracle高速缓存多少表数据的参数。这个数越小，表访问单个块的成本就越低；
理解它的办法是：想象这个数反映执行多块IO（全表扫描）的成本与执行单个IO（索引读取）的成本。
保持为100，则多块IO与单块IO成本相同。设为50优化程序认为访问单块IO的成本为多块IO的一半。

小结：
这两个参数的设置不会加快执行计划的执行速度，它们只是影响optimizer对执行计划的选择。
必须要明确，对它们的调节并不影响在cache中实际缓冲的index数量和在multiblock I/O中single-block I/O的代价。
DBA只是通过它告诉Oracle预计会有多少index将被缓存。从而影响optimizer选择不同的执行计划。

一般而言，默认的参数值很可能对系统不合适
对多数系统而言考虑如下极端情况：
optimizer_index_caching = 0  optimizer_index_cost_adj = 100 的默认值，一般时候数据仓库报表系统。
optimizer_index_caching = 90  optimizer_index_cost_adj = 25一般时候事务处理/OLTP系统。

确定方法
通过 select event,average_wait from v$system_event where event like 'db file s%'
语句查询顺序读和离散读的等待值，并用 sequential reads / scattered reads 的比来作为 optimizer_index_cost_adj 的设置

关于上面的确定方法，tom说：

that is just using the ratio of waits for indexed reads/full scan reads. it is a valid "start

point". but it'll have to be iteratively set as the very act of setting it will change the ratio

as you'll start doing more index reads (most likely)

 

 

 

案例：

Hello Tom!

I try to find out "good" values for the optimizer_index_cost_adj and optimizer_index_caching parameters.
For the cost_adj parameter i used the following query to find out a starting point (as you recommended):

SQL> select round((select average_wait
     from v$system_event
     where event='db file sequential read')/(select average_wait
     from v$system_event
     where event='db file scattered read')* 100)
     as starting_point  from dual


When i execute this statement on database 1 i get:
STARTING_POINT
--------------
          7558

I was a little bit stunned by this value. As far as i know, this would mean that an index access is
much more "expensive" than an full table access.

Here are all the columns for the two events form the v$system_event table:
SQL> select * from v$system_event
 where event in ('db file sequential read', 'db file scattered read');

EVENT                      TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------------------- ----------- -------------- ----------- ------------
db file sequential read       92814138              0  3787666073   40,8091499
db file scattered read         4349122              0     2348427   ,539977264


Then i inspected database 2. Here, i found the other extreme:
SQL> select round((select average_wait
     from v$system_event
     where event='db file sequential read')
     /(select average_wait
      from v$system_event
     where event='db file scattered read')* 100)
     as starting_point from dual;

STARTING_POINT
--------------
             0


0!!! Then i looked into the v$system_event table (i thought, perhaps timed_statistics is disabled),
but:
SQL> select * from v$system_event
     where event in ('db file sequential read', 'db file scattered read');


EVENT                        TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------- ----------- -------------- ----------- ------------
db file sequential read        119504401              0     7598589   ,063584177
db file scattered read         368384847              0  1,1029E+10   29,9391977

So the ratio is correct (0.063584177 / 29.9391977 = .0021).
Are these values from the two databases realistic? How are these extreme ratios possible?
(I suppose i did something wrong.)
And: What should i do now with the optimizer_index_cost_adj parameter on these databases?


Followup    November 12, 2003 - 10am Central time zone:
well, don't put words in my mouth -- they recommended it, i'm not endorsing it.
looks like that database has been up a while (hope so!) -- aggregate numbers over a long period are
"meaningless". try cost_adj = 10, or 25, or 90, or 100.
but wait -- first -- have you IDENTIFIED A PERFORMANCE PROBLEM that you are actually trying to
solve and have you (most important -->) identified the root cause!
don't change anything until you do.

4 stars Reset of v$system_event possible?   November 13, 2003 - 2am Central time zone
Bookmark | Bottom | Top
Reviewer: Michael  from Germany

Yes, database 2 is up since mid of august and database 1 is up since a week.
Is there a way to "reset" the v$system_event table (without a restart of the database) to get the
data of, for example, a single hour?
Followup    November 13, 2003 - 6am Central time zone:
you would create table x as select * from v$system_event;
wait a bit.......
create table y as select * from v$system_event;
now, join X to Y and diff the values -- that'll be the IO's and such during that "wait a bit"  period.

 

 

附录：

Creating an Oracle index cache
 

Oracle Tips by Burleson Consulting

05 Sep 2004

While the Oracle cost-based SQL optimizer (CBO) does a wonderful job in determining the best execution plan for all SQL statements, it is the job of the Oracle professional to ensure that the CBO has all of the external information that it requires. This external information includes:

• Schema statistics - Using the dbms_stats package to collect table, index and partition information.
   
• Column histograms - Get histograms on skewed columns and foreign key table join columns.
   
• Parameter settings - These include optimizer_index_cost_adj, optimizer_mode, pga_aggregate_target and optimizer_index_caching. These are broad-brush parameters that influence the overall behavior of the CBO.

The Oracle professional can "hint" the optimizer about the amount of indexes that exist in the Oracle data cache with the optimizer_index_caching parameter. As we may know, index blocks that are in the data cache (db_cache_size) can be accessed without doing a physical I/O, and are far faster. If we can tell Oracle how much of our index blocks are expected to be in the data cache, then the CBO can make a better decision about whether to perform an index scan of a full-table scan for a query. Let's take a closer look at this important parameter.

Optimizer_index_caching

The optimizer_index_caching parameter is set by the DBA to help the optimizer know, on average, how much of an index resides inside the data buffer.  The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100. This parameter lets you adjust the behavior of the cost-based optimizer to select the best way to access the desired SQL query results:

• Nested loop joins
• Hash join access
• Full-index scans
• Full-table scan access

The cost of executing a nested loop join where an index is used to access the inner table is highly dependent on the caching of that index in the buffer cache. The amount of index caching depends on many factors, such as the load on the system and the block access patterns of different users that the optimizer cannot predict. Setting optimizer_index_caching to a higher percentage makes nested loop joins look less expensive to the optimizer, which will be more likely to pick nested loop joins over hash or sort merge joins.

According to the Oracle documentation:

"OPTIMIZER_INDEX_CACHING favors using selective indexes. That is, if you use a relatively low value for this parameter, the optimizer effectively models the caches of all non-leaf index blocks. In this case, the optimizer bases the cost of using this index primarily on the basis of its selectivity. Thus, by setting OPTIMIZER_INDEX_CACHING to a low value, you achieve the desired modeling of the index caching without over using possibly undesirable indexes that have poor selectivity."

If we can segregate the index blocks into a separate RAM area then we can accurately predict the correct percentage for optimizer_index_caching and aid the CBO in making the best access decision.

 

Starting with Oracle9i we have the ability to configure multiple block sizes. We can define tablespaces with block sizes of 2K, 4K, 8K, 16K, and 32K, and match tablespaces with similar sized tables and indexes.

 

Many Oracle professionals still fail to appreciate the benefits of multiple block sizes and do not understand that the marginal cost of I/O for large blocks is negligible. A 32K block fetch costs only 1 percent more than a 2K block fetch because 99 percent of the disk I/O is involved with the read-write head and rotational delay in getting to the cylinder and track.

This is an important concept for Oracle indexes because indexes perform better when stored in large block size tablespaces. They perform better because the b-trees are better balanced, and there is less overall disk overhead with sequential index node access.

Research by the popular author Robin Schumacher shows that Oracle indexes built in a 32k blocksize requires less logical I/O's for multi-block index range scans and index fast full scans, and also shows that indexes build with less levels. Click here to read his findings.

Now let's review the steps for creating a separate index buffer.

Create an index buffer

It's easy to create a separate index buffer in Oracle9i and we can perform the operation while the database is active. We start by moving all indexes to a separate tablespace, defined to a separate data cache and then set optimizer_index_caching to the correct value.

• Allocate a 32k cache buffer - Start by creating a region of RAM for a 32k data cache.
• alter system set db_32k_cache_size = 100m;
• Allocate a 32k tablespace - Here we use the blocksize argument to associate the tablespace with the data buffer. Note that with this syntax we are using Oracle Managed Files (OMF), so we do not need to specify the data file name:
• create tablespace index_ts_32k blocksize 32k;
• Move the indexes into the 32k tablespace - This command moves the indexes into the 32k tablespace with no interruption to existing index queries. It rebuilds the indexes as temporary segments, and makes sure that the new index is usable before dropping the old index.
• alter index cust_idx rebuild online tablespace index_ts_32k;

Now that the indexes are segregated into a separate tablespace and index buffer, we can run dictionary scripts to predict with relative accuracy, the amount of the indexes that we can expect to see in the RAM index buffer.

select   value - blocks optimizer_index_caching

from

v$parameter p,dba_segments s

where  name = 'db_32k_cache_size' and tablespace_name = 'INDEX_TS_32K';

This estimated value will provide a good average for the amount of an index that can be expected to reside in the index cache. This assume equal index access by the application, but you can query the v$bh view to make sure that there is no skew in index access.

Conclusion

Oracle provides many tools for the Oracle professional to help the CBO always make the best decision about the way to access Oracle data. By working toward the optimal settings you can ensure that the majority of your SQL always executes quickly and efficiently.