Optimizer Modes   «Prev  Next»

Show Parameter Modes

In the simulation, you should have been able to practice using the show parameters mode command to view optimization modes and practice editing those modes in your init.ora file.

show parameter

SQL> show parameter user_dump_Dest
--------------------- -------------------------------- -------
user_dump_dest string f:\app\stelios\diag\rdbms\snc1\snc1\trace
SQL> host dir f:\app\stelios\diag\rdbms\snc1\snc1\trace\*DIAG*.trc
Volume in drive F is My Passport
Volume Serial Number is 1E65-69CC
Directory of f:\app\stelios\diag\rdbms\snc1\snc1\trace

09/15/2012 12:41 PM 1,096 snc1_diag_2548.trc
10/05/2012 12:54 PM 68,133 snc1_ora_4980_DIAG.trc
2 File(s) 69,229 bytes
0 Dir(s) 788,285,857,792 bytes free
SQL> host notepad f:\app\stelios\diag\rdbms\snc1\snc1\trace\snc1_ora_4980_DIAG.trc

This method, which is part of the event infrastructure, has the added advantage that it can capture trace for an SQL statement inside a PL/SQL block. An example of this is shown below. A package specification and body are created to calculate the area of a circle. Then we identify the SQL ID inside the PL/SQL package and trace only the matching statement.

SQL> host type area.sql
create or replace package getcircarea as
function getcircarea(radius number)
return number;
end getcircarea;
create or replace package body getcircarea as
function getcircarea (radius number) return number
is area number(8,2);
select 3.142*radius*radius into area from dual;
return area;
end getcircarea;
set serveroutput on size 100000;
area number(8,2);
area:= getcircarea.getcircarea(10);
dbms_output.put_line('Area is '||area);

SQL> select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%';
select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%'

alter session set tracefile_identifier='PLSQL';
alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]';
SQL> @area
Package created.
Package body created.
Area is 314.2
PL/SQL procedure successfully completed.
Session altered.

Understanding the Rule-Based Optimizer

The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it will use to access the database.
The RDBMS kernel defaults to the rule-based optimizer under a number of conditions, including:
  1. OPTIMIZER_MODE = RULE is specified in your INIT.ORA file
  2. OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, andno statistics exist for any table involved in the statement
  3. An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued
  4. An ALTER SESSION SET OPTIMIZER_MODE = CHOOSEcommand has been issued, and no statistics exist for any table involved in the statement
  5. The rule hint (e.g., SELECT /*+ RULE */. . .) has been used in the statement
The rule-based optimizer is driven primarily by 20 condition rankings, or "golden rules." These rules instruct the optimizer how to determine the execution path for a statement, when to choose one index over another, and when to perform a full table scan. These rules are fixed, predetermined, and, in contrast with the cost-based optimizer, not influenced by outside sources (table volumes, index distributions).
Oracle8i introduced a new hint, INDEX_JOIN, that allows you to join multi-column indexes.