alter index IND1 monitoring usage;
Track whether or not the index has been used with view V$OBJECT_USAGE.
NB. Issuing this command will invalidate every SQL statement in the library cache which references the index.
Skip scan index access
This new feature is designed to speed up index range scans. Especially beneficial if the leading column has a low cardinality. It takes advantage of how B*Tree indexes are organized. By examining the contents of branch blocks and by considering the values in non-leading keys, Oracle can immediately eliminate any leaf blocks which cannot possibly contain the data searched for. This way, less blocks need to be scanned thereby reducing IO thereby improving performance.
This new feature makes it possible to use non-leading keys of concatenated indexes (previously you had to use leading key values in the search). Consequently, this feature is likely to render secondary indexes for referencing non-primary column data superfluous to requirements. They can be dropped (after being closely monitored with the new monitoring function) making a further positive contribution to performance.
This new algorithm is implemented by default.
This init.ora parameter can now be set dynamically at both system and session level. Can now be set to similar as well as the existing force and exact modes:
exact - Still the default. Do not attempt to share cursors at all. Use literal value always.
force - Replace literal values with bind variables without checking for the existence of histograms or skewed data
similar - Use literal value only if it would make a difference to the execution plan to do so. For example, where there are histograms present or highly skewed data
Cached Execution Plans
A new dictionary view V$SQL_PLAN acts as kind of a real-time, global plan table for all SQL statements in the library cache. This is one of the most underrated new features in my opinion.
Four new modes to replace FIRST_ROWS when setting optimizer_mode:
The FIRST_ROWS hint can take any value for the number of rows:
select /*+ first_rows(50) */ * from emp;
The METHOD_OPT argument now supports new method options when specifying 'FOR ALL COLUMNS SIZE ...'
|method_opt => 'for all columns size n'||the sample size in %|
|method_opt => 'for all columns size repeat||gathers stats using the same criteria used during the previous call to dbms_stats|
|method_opt => 'for all columns size auto||create histograms only if the data is skewed|
|method_opt => 'for all columns size skewonly||always create histograms, regardless|
The 9i cost based optimizer now has the ability to take into account the system workload profile at a particular time of day when calculating an execution plan. You can sample and store a workload profile directly into the data dictionary or sample and store several workload profiles for different times of the day in a local table and switch them in and out of the data dictionary as necessary. For example, you may want to create 'nightlybatch' and 'dailyoltp' profiles if the workload profile is different during those times.
|gathering_mode => interval||Sample mode|
|gathering_mode => [ start | stop ]||Create a sample interval for gathering stats directly into the data dictionary|
|interval => n||Sample time in minutes|
|stattab => table_name||Table to store system workload profiles|
|statid => identifier||A name for the sample period. Eg. 'nightlybatch' or 'dailyoltp'|
|statown => schema||Schema to store system workload profile table|
exec dbms_stats.create_stat_table ('SCOTT','STATTAB','USERS');
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51703/，如需转载，请注明出处，否则将追究法律责任。