ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TUN--Oracle9i performance improvements(zt)

TUN--Oracle9i performance improvements(zt)

原创 Linux操作系统 作者:vongates 时间:2019-04-01 16:42:05 0 删除 编辑
Oracle9i performance improvements

Index Monitoring

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.

 

CURSOR_SHARING

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.

 

FIRST_ROWS

Four new modes to replace FIRST_ROWS when setting optimizer_mode:

FIRST_ROWS_1
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000

The FIRST_ROWS hint can take any value for the number of rows:

select /*+ first_rows(50) */   *  from emp;

 

DBMS_STATS

DBMS_STATS.GATHER_INDEX_STATS (
DBMS_STATS.GATHER_TABLE_STATS (
DBMS_STATS.GATHER_SCHEMA_STATS (
DBMS_STATS.GATHER_DATABASE_STATS (

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 repeatgathers stats using the same criteria used during the previous call to dbms_stats
method_opt => 'for all columns size autocreate histograms only if the data is skewed
method_opt => 'for all columns size skewonlyalways create histograms, regardless

 

System Statistics

DBMS_STATS.GATHER_SYSTEM_STATS (

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 => intervalSample mode
gathering_mode => [ start | stop ]Create a sample interval for gathering stats directly into the data dictionary
interval => nSample time in minutes
stattab => table_nameTable to store system workload profiles
statid => identifierA name for the sample period. Eg. 'nightlybatch' or 'dailyoltp'
statown => schemaSchema to store system workload profile table

exec dbms_stats.create_stat_table ('SCOTT','STATTAB','USERS');
exec dbms_stats.import_system_stats('STATTAB','dailyoltp','SCOTT');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51703/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    449
  • 访问量
    293159