Sure, we'll work with the query "select ename from emp where empno = :empno"

1) prepare. That parses/compiles the query. If the query was syntactically invalid, you did not have access to the objects, etc -- it would fail.

2) describe. In languages like Pro*C this would fill in a data structure that describes all of the INPUTS to the query. In this case, the data structure would say "I see :empno as a bind variable". It would tell us of one bind variable

3) bind INPUTS. In this step -- you would provide to us the value of :empno

4) describe outputs. In this step, you ask us "what output might this query result in". In this case we would say "there is one column, it is named ENAME, it is a varchar2(10)".

5) define outputs. Here you would tell us "where" to put the value of ename and what datatype you would like to use. In the prior step we told you this was a varchar2(10) -- you tell us what datatype you would like to fetch this into. This makes more sense when you fetch a number/date -- you can tell us to put the results into a STRING type instead and we'll convert the number/date into a string for you.

6) open. This "executes" the query. It just opens the result set. In most cases -- this is instantaneous (we do not answer the query here -- that happens in the FETCH phase)

7) fetch. Get the data, fetch the rows

8) close. finish up, close the cursor (if you won't be using it again in this application). releases all resources.

Chapter 16, Dynamic SQL.

I cover only PLSQL dynamic sql which does not support step #2 below (describe the INPUTS -- bind variables ). Otherwise all of the steps are covered in detail there.

In order to get the bind variable names (inputs) from a query in PLSQL you must parse the query text yourself.

function get_binds( p_stmt in varchar2 ) return dbms_sql.varchar2_table
as
l_binds dbms_sql.varchar2_table;
l_in_literal boolean default false;
l_ch varchar2(1);
i number default 1;
l_str varchar2(255);
l_add boolean;
begin
loop
exit when ( i > length(p_stmt) );
l_ch := substr( p_stmt, i, 1 );

if ( l_ch = '''' )
then
l_in_literal := NOT l_in_literal;
elsif ( l_ch = '-' AND substr(p_stmt,i+1,1) = '-' )
then
i := nvl(instr( p_stmt, chr(10), i ),0);
if ( i = 0 ) then i := length(p_stmt); end if;
elsif ( l_ch = '/' AND substr(p_stmt,i+1,1) = '*' )
then
i := nvl(instr( p_stmt, '*/', i ),0);
if ( i = 0 ) then i := length(p_stmt); end if;
end if;

if ( l_ch = ':' and NOT l_in_literal )
then
l_str := ':';
for j in i+1 .. length(p_stmt)
loop
l_ch := upper(substr( p_stmt, j, 1 ));
exit when l_ch not between '0' and '9'
AND l_ch not between 'A' and 'Z'
AND l_ch <> '_';
l_str := l_str || upper(l_ch);
i := j;
end loop;
l_add := (length(l_str) > 1);
for k in 1 .. l_binds.count
loop
if ( l_binds(k) = l_str )
then
l_add := false;
exit;
end if;
end loop;
if ( l_add )
then
l_binds(l_binds.count+1) := l_str;
end if;
end if;
i := i+1;
end loop;

return l_binds;
end get_binds;

It doesn't make sense to map the phases of procedurally executing a query with the above layers. I'll make a couple of comments

- OCI, isn't really a layer, it is an API. You could use OCI to prepare, bind, define, open, fetch, etc -- but it is not a "layer". Its like ODBC or JDBC - just an API

- UPI & OPI, user/oracle program interface, are more APIs -- they are the lowest level API but just API's none the less.

Many of the remaining items in your list will apply -- perhaps at many times. A statement will be compiled during a parse -- but that statement might contain a call to a plsql function that does dynamic selects itself. So, a FETCH from that statement might hit the compile "layer" as well. Normally parse = compile.

There is no straightforward, one to one mapping. In my opinion, it is not very important in day to day operations to understand these layers.

You know, if you have the seven steps -- it would be helpful to put them in in the first place (less guessing on my part).

1, 2, 3, 4, 5, 6 are all part of PARSE.
7 is part of fetch or execute (execute is what you do to an UPDATE for example)
You led me to believe with your question that you had "heard there were 7 stages" but didn't know what they were.

the dictionary definition of heuristic is:

Main Entry: heu·ris·tic
Pronunciation: hyu-'ris-tik
Function: adjective

Etymology: German heuristisch, from New Latin heuristicus, from Greek heuriskein to discover; akin to Old Irish fo-fúair he found
Date: 1821

: involving or serving as an aid to learning, discovery, or problem-solving by experimental and especially trial-and-error methods <heuristic techniques> <a heuristic assumption>; also : of or relating to exploratory problem-solving techniques that utilize self-educating techniques (as the evaluation of feedback) to improve performance <a heuristic computer program>
................

Simply put, it is the set of rules followed by the software to optimize your query. RBO uses rules (heuristics) built into it. The CBO does not.


An example of [4] would be the fact that:

select * from t where x in ( 1, 2, 3 )

is actually rewritten as

select * from t where ( x = 1 or x = 2 or x = 3 )

before being processed. A much mnore complex case could be a materialized view, such as:

create materialized view emp_rollup
enable query rewrite
as
select deptno, sum(sal) sal
from emp
group by deptno;


Now, given that all the necessary settings have been done (see
the data warehousing guide for a comprehensive example) your end
users can query:

select deptno, sum(sal) from emp where deptno in ( 10, 20)
group by deptno;

and the database engine will rewrite the query to go against the
precomputed rollup, not the details -- giving you the answer in
a fraction of the time it would normally take (depending on the volume of data of course).

No, I would not consider this. The rewrite happens on a hard parse only and happens so fast -- I would not even consider it. In fact, depending on the optimizer mode, the rewrite might not take place (i should have mentioned that in the last comment). For example (assuming an index on empno)

scott@ORA817DEV.US.ORACLE.COM> select empno from emp where empno in ( 1,2,3,4 );

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=12)
INDEX (FAST FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=4 Bytes=12)

scott@ORA817DEV.US.ORACLE.COM> select /*+ FIRST_ROWS */ empno from emp where empno in ( 1,2,3,4 );

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=8 Card=4 Bytes=12)
INLIST ITERATOR
INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE) (Cost=8 Card=4 Bytes=12)

Here the query was NOT rewritten with the CBO, the CBO instead tends to use the INLIST ITERATOR to "loop over" the values in an IN LIST


Use "where x in ( ..... )" when you have a list. If need be, it'll be rewritten.

"fix it"

should not be really hard.

a) USUALLY -- almost always, yes.

b) it gets some parse locks and for DML that modifies (eg: select for update), it'll get actual data locks during open.

c) not only loosely, they are one in the same.

they are identical, all 8.

the prepare phase is done under the covers a little different, but you yourself still always do all 8 steps.