Optimiser oddity in PL./SQL

An interesting little quirk I came across recently which may affect anyone trying to package SQL into PL./SQL to produce an 'object-like' front-end.

Regardless of the optimizer_mode set in the init.ora file, and SQL sent to the database from PL/SQL will be sent with a local optimizer_goal of CHOOSE.

I called the support desk about this, and was told that this is not a bug - it was just deemed to be a sensible approach. Unfortunately it is something of an irritant at one of the sites I have just visited.

NB If you want to prove the point start a database with optimizer_mode=first_rows, then start a session with SQL_TRACE=true.

Fire off a pl/sql block containing a simple sql statement, then check the PARSE/EXEC/FETCH lines in the trace file.

You will find that the lines for the cursor for the pl/sql block contains:

'og=2'

which is the first_rows optimizer_goal, but the cursor for the sql contains

'og=4'

which is the CHOOSE optimizer_goal.

--
Jonathan Lewis Received on Wed Dec 23 1998 - 03:23:46 CST