V$SQL PROGRAM_ID
and PROGRAM_LINE#
columns to track SQL back to a PL/SQL stored program unit. To see how an EXPLAIN PLAN
command (issued either directly or via a tool such as SET AUTOTRACE
) may give misleading results for an execution plan, first I will create a table T
with a primary key column PK
. Note that the data type of the column is character.
SQL> create table t as 2 select 3 to_char(rownum) pk, 4 a.* 5 from all_objects a; Table created. SQL> desc t Name Null? Type ----------------------------- -------- ------------- PK VARCHAR2(40) OWNER NOT NULL VARCHAR2(128) OBJECT_NAME NOT NULL VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER
Next I create an index to support the primary key to enable fast lookups on this value.
SQL> create index t_ix on t ( pk ); Index created.
Now I examine a simple SQL statement via EXPLAIN PLAN
to see if it correctly uses the index.
SQL> variable obj number SQL> exec :obj := 123456; PL/SQL procedure successfully completed. SQL> set autotrace traceonly explain SQL> select * from t 2 where pk = :obj; Execution Plan ---------------------------------------------------------- Plan hash value: 3343387620 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 139 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 139 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("PK"=:OBJ)Line 2 in the execution plan indicates that an index will be used, and I happily promote my code into the application, confident that it will run as expected. But this plan is not the plan that will be used by the application. The
EXPLAIN PLAN
command does not interrogate the data types of the bind variable (obj
) that was used in the SQL statement, whereas it will be relevant to the optimizer in a true execution environment. I can see the true plan by running the SQL statement and then using the DBMS_XPLAN
database package to reveal what plan the optimizer actually used at runtime.
SQL> set autotrace off SQL> select * from t 2 where pk = :obj; no rows selected SQL> select * from dbms_xplan.display_cursor(); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID agwvcq8tng6b9, child number 0 ------------------------------------- select * from t where pk = :obj Plan hash value: 1601196873 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 435 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 139 | 435 (1)| 00:00:01 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("PK")=:OBJ)
Because the data type of the obj
bind variable is numeric and the PK
column it was predicated against is a character string, the optimizer performed an implicit TO_NUMBER
function for the PK
column to ensure data type equivalence so that it could make a valid comparison of the values. The presence of TO_NUMBER
in effect changed the SQL statement the optimizer was using to determine an execution plan. As the optimizer becomes more powerful with each release of Oracle Database, this is just one of many possible scenarios where the execution plan at runtime may be totally different from a plan observed with an EXPLAIN PLAN
command or similar.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.