Oracle Explain explain plan

[abcdefghiklkm]

Dear ALL

CAN SOMEONE PLEASE EXPLAIN HOW TO USE THE EXPLIAN PLAN IN ORACLE 9I

 

[Scunningham99]

HERE WE ARE USING HINT + FIRST_ROWS. WHEN WE "SET AUTOTRACE ON;" WE CAN SEE THE EXECUTION PLAN IS USING THIS HINT, AND THE RESULTS OF THE COST OF THE EXECUTION. "Optimizer=HINT: FIRST_ROWS".

WE CAN CHANGE THIS TO ANY OF THE VALUES BELOW, BE IT + CHOOSE, +RULE ETC (SEE BELOW)!:-

IF WE DO NOT PUT THIS HINT IN IT WILL USE THE DEFAULT FROM THE INITIALIZATION PARAMETER OPTIMIZER_MODE=.

SO HINTS ARE USED TO OVERIDE THIS INIT.ORA PARAMETER SETTI
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

rule

The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.

choose

The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.

If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

first_rows_n

The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

first_rows

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

all_rows

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
he Optimizers (Cost - Based and Rule - Based) may choose to use any index
available on a table if it can be used for a particular query and if the Access Path
this index offers makes it more desirable than other indexes or even accessing
the table directly.

A change in available statistics may influence the Optimizers and thus the choice
of a particular Access Path and hence a particular index.

So if the stats are not so clever (ie estimate not compute) or there are none at all, then CBO may choose
to do a full table scan instead!

We can use hints to overide the default CBO initialization setting.

Sy UK

 

[Scunningham99]

THESE ALL SET OPTIMIZER MODE AT STATEMENT LEVEL VIA HINTS.
WE CAN ALSO SET AT THE SESSION LEVEL BY ISSUING:-

SQL> ALTER SESSION SET OPTIMIZER_MODE=<mode>;

ie

1 SELECT /*+ FIRST_ROWS */ *
2 FROM (SELECT /*+ INDEX(LEAD_PK) */ l.lead_id
3 FROM p_lead l
4 WHERE 1 = 1
5 AND l.lname1_upper LIKE 'KELCHAK%')
6* WHERE ROWNUM <= 16


# Turn Autotrace on for session

sql> set autotrace traceonly explain;
OR SQL> SET AUTOTRACE ON; --THIS DOES BOTH EXPLAIN AND STATISTICS.


# to turn it off issue, SQL> set autotrace off;

# TO TURN AUTOTRACE ON FOR ANOTHER SESSION ISSUE:-

SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID=>'155', SERIAL#=>'27845', SQL_TRACE=>TRUE);




# FOR EVERY USER SET :-
SQL_TRACE=TRUE IN THE INIT.ORA


Sy UK