Disabling AUTOTRACE Causes “SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level” on 11.2.0.1

October 2, 2010

I encountered a fun problem today while performing a couple of tests with the AUTOTRACE feature in SQL*Plus.  I am using a 32 bit 11.2.0.1 Oracle client connected to a 64 bit 11.2.0.1 Oracle database.  It seems that 50% of the time when I issue “SET AUTOTRACE OFF” or “set autotrace off” I receive the SP2-0575 error and my session is disconnected from the database, although it seems to happen randomly when disabling AUTOTRACE.  Interesting problem I think.  Here is a partial output from the script:

SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ=10 SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> HOST sleep 10
SQL>
SQL>SPOOL testresults11201CostAdj10.txt
SQL>
SQL> SHOW PARAMETER OPTIMIZER

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     10
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
(several select statements similar to the following were executed, see yesterday's article for the complete list)
SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=10 AND "T2"."COL1">=1)

SQL>
SQL> SPOOL OFF
SQL> set autotrace off
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ=1 SCOPE=MEMORY;
ERROR:
ORA-03114: not connected to ORACLE

The problem is not specific to the modification of the OPTIMIZER_INDEX_COST_ADJ parameter, and the error does not always happen after changing a specific parameter. 

Metalink (MOS) Doc ID 563269.1 does not apply:

SQL> select owner, object_type from all_objects where object_name='DUAL';

OWNER      OBJECT_TYPE
---------- -------------------
SYS        TABLE
PUBLIC     SYNONYM

FIPS compliance checking (FLAGGER) (reference 2) should not apply:

SQL> show flagger
flagger OFF

As I mentioned, an interesting problem – yet a bit of a headache inducer when trying to use AUTOTRACE.