Reading notes - "Oracle based SQL optimization" - the first chapter -2

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
The basic concept of CBO optimizer:
Transitivity:
1, A simple predicate transfer
t1.c1=t2.c1 and t1.c1=10, Oracle will automatically add the t2.c1=10 condition.


2, The join predicate transfer
t1.c1=t2.c1 and t2.c1=t3.c1, Oracle will automatically add the t1.c1=t3.c1 condition.


3, Outer join predicate transfer
t1.c1=t2.c1(+) and t1.c1=10, Oracle will automatically use t2.c1 (+ =10) adding conditions.




The limitations of CBO:
1, Between each column of CBO will be the default target SQL statements in where conditions are independent, no relationship.


2, CBO will assume all the goals of SQL are performed separately, and do not interfere with each other.
Don't consider SQL implementation has buffer to Buffer Cache, the next execution does not require access to a physical IO to disk read index leaf block, the data block with index, overestimate the cost.


3, CBO has many restrictions on the histogram statistics.
Oracle 12C, histogram corresponds to the Frequency type of Bucket number can not be more than 254, if the target column distinctvalue more than 254, Oracle will use the Height Balanced type of histogram. For Height Balanced type of histogram, because Oracle does not record all nopopular value value CBO, probability so wrong execution plans than the Frequency type high.
If the collection of histogram statistics for the field of text type, then Oracle will only the text type field text value the first 32 bytes out (only taking the first 15 bytes), and converting it into a floating point number, and then the floating point number as a histogram statistics are stored the text type fields in the data dictionary. For those more than 32 bytes of text fields, as long as the corresponding recorded text value the first 32 bytes of the Oracle collection of the same, histogram statistics, will think of these records text value the same, but the actual is different. Then choose the wrong execution plan.


4, CBO in the analysis of multi table linked to the target SQL, may be missing the right execution plan.
The total number of possible connection sequence of each SQL table is n! Table about three million, 10 connected, 15 tables to connect about ten billion.
CBO up to only consider the finite calculated according to _OPTIMIZER_MAX_PERMUTATIONS may.
As long as the target SQL correct execution plan is not in the finite possibility, then CBO will be missing the right execution plan.
SELECT i.ksppinm name, CV.ksppstvl VALUE, CV.ksppstdf isdefault,
DECODE (BITAND (CV.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx
AND i.ksppinm LIKE '%_optimizer_max_%'
AND i.ksppinm LIKE '/_%' ESCAPE '/' ORDER BY REPLACE (i.ksppinm, '_', '');



NAME
---------------
_optimizer_max_permutations


DESCRIPTION

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

optimizer maximum join permutations per query block



VALUE ISDEFAULT ISMODIFIED ISADJ

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

2000 TRUE FALSE FALSE
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Shelby at December 08, 2013 - 12:39 PM