optimizer

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 20 Feb 2004 15:45:17 -0400
Message-ID: <001b01c3f7ea$11279300$2501a8c0@dazasoftware.com>

If you forgot some point about optimizer here are some point you should remember about how to set in Oracle 9i, basically.

3.1 Optimizer
The optimizer is who defines de optimum access path to get data. To get that real optimum access the database and the session must give enough information about what he is doing. There are parameter which instructs the optimizer how to interpret the data and what to do.
3.1.1 OPTIMIZER_INDEX_COST_ADJ (1-10000) MUST BE SET Default value 100, a value of 100 indicates that access a table from an index is the same that accessing all the table, THIS IS FALSE. Suggested values:
10-30 OLTP database, too much inserts, y 50 Data Warehousing, big queries few inserts I suggest to start with a value of 10 and then you can increase it. 3.1.2 OPTIMIZER_INDEX_CACHING (0-99) MUST BE SET This parameter sets the possibility to find blocks accessed through an index in memory.
Default value is 0, 0 means that no one block accessed through index will be found in memory THIS IS FALSE. A value of 90% is advisable. 3.1.2.1 Example
An example that shows how a value incorrect in both parameter can change your execution plan.
SQL> select * from cuentas_me where cts_cuenta = '1'; Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=288) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUENTAS_ME' (Cost=2 Card=2 Bytes=288)

2 1 INDEX (SKIP SCAN) OF 'CST_CTS_CUENTA' (UNIQUE) (Cost=3 Card=1) ****USES INDEX
Statistics

24 consistent gets ** this is 28*8K needed to be read

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ= 10000;
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=0;
SQL> select * from cuentas_me where cts_cuenta = '1';

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=2 Bytes=288) 1 0 TABLE ACCESS (FULL) OF 'CUENTAS_ME' (Cost=39 Card=2 Bytes= 288) **** DO A FULL SCAN
Statistics

388 consistent gets ** this is 388*8K hended to be read, this is more than 1,600%
3.1.3 DB_FILE_MULTIBLOCK_READ_COUNT MUST BE SET This parameter indicates the number of block that are read by the operating system at once, the optimizer uses this value to evaluate the cost of a full scan against through an index.
In Windows NT is 128, in consequence the value should be 128/(block size)8K=16
3.1.4 OPTIMIZER_FEATURES_ENABLE
Every Oracle release , the optimizer change the way it interprets the information, and are introduce new features. But as all this change can cause that old execution plans "to fail" , Oracle offer a way to use a previous optimizer behavior Is not advisable to change this parameter. 3.1.5 OPTIMIZER_MAX_PERMUTATIONS (4-80000) This parameter indicates the maximum number of permutation the optimizer analyze before generate an execution plan, when there are table unions. A value of 80,000 indicates there is no limit This value is used to reduce the time to get an execution plan, and to get a better execution plan in tuning phase.
This parameter works with _OPTIMIZER_SEARCH_LIMIT parameter, ( NOTE the underscore means this is a hidden parameter, only to be modified by experts).
What some experts uses to do a high tune is to set the value in _OPTIMIZER_SEARCH_LIMIT equal to the number of tables in the query and increases the value of OPTIMIZER_MAX_PERMUTATIONS to 80,000. To get the best execution plan.
Once they get it they end the optimization and uses hints to force the best execution plan.
Is not advisable to change this parameter permanently. 3.1.6 OPTIMIZER_MODE
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. CHOOSE
This is the default value, it uses database statistics to get the best execution plan.
=ALL_ROWS
Cost-based approach to get the minimum resource use to complete the entire statement, for example to get reports.
=FIRST_ROWS_n
Cost-based approach, to get best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000, Oracle suggest to use it, for example
ALTER SESSION

     SET OPTIMIZER_MODE = FIRST_ROWS(10);
      Select /* FIRST_ROWS(10) */ column from table

=FIRST_ROWS only, not suggested
Uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Note: Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. =FIRST_ROWS is available for backward compatibility and plan stability.
RULE
This is discontinued from 10G, so doesn't deserve attention, it was a method that used fixed rules to get the execution path, as it didn't use statistics it was frequently inaccurate.

More information in http://www.evdbt.com/SearchIntelligenceCBO.doc

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 15:15:53 CST