RE: optimizer
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 16:24:21 -0500
What is the value of duplicating stuff people can find in other sites into
this list?
Waleed
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
Sent: Friday, February 20, 2004 2:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: optimizer
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
