QUERY_REWRITE_ENABLED

In some books and also in Oracle’s own documentation, it says that the QUERY_REWRITE_ENABLED initialisation parameter has to be set to TRUE before the optimiser will be able to use a function based index. I decided to check this for myself.

According to Oracle’s own documentation, the default for this parameter in Oracle 9 was FALSE. This does seem to be correct:

ORACLE 9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Oct 9 16:57:46 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled'

  4  /

VALUE      ISDEFAULT

---------- ---------

false      TRUE

SQL>

Oracle’s own documentation also says that, in later versions, the default value is TRUE. This seems to be correct too:

ORACLE 10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 9 17:04:04 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled';

VALUE      ISDEFAULT

---------- ---------

TRUE       TRUE

SQL>

I did the rest of this test on Oracle 9.2.0.7.0. First I set the parameter to FALSE:

SQL> alter session

  2  set query_rewrite_enabled = false

  3  /

Session altered.

SQL>

Then I created a table with a function based index:

SQL> create table andrew

  2  as select * from dba_objects

  3  /

Table created.

SQL> create index fbi

  2  on andrew(to_char(created,'YYYYMM'))

  3  /

Index created.

SQL>

I checked that Oracle knew it was a function based index:

SQL> select index_type, funcidx_status

  2  from user_indexes

  3  where index_name = 'FBI'

  4  /

INDEX_TYPE             FUNCIDX_STATUS

---------------------- ---------------

FUNCTION-BASED NORMAL  ENABLED

SQL>

I monitored the usage of the index:

SQL> alter index fbi monitoring usage

  2  /

Index altered.

SQL>

Then I ran a query on the table. I did not expect Oracle to use the index, as QUERY_REWRITE_ENABLED was set to FALSE, but it did (you may need to use your browser's zoom button to read the execution plan):

SQL> set autotrace on explain

SQL> select count(*) from andrew

  2  where to_char(created,'YYYYMM') = '201201'

  3  /

  COUNT(*)

----------

       181

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=1 Card=35

          1 Bytes=3159)

   

SQL> set autotrace off

SQL>

... and when I checked afterwards, Oracle confirmed that the index had been used:

SQL> select index_name, start_monitoring,

  2  monitoring, used from v$object_usage

  3  /

INDEX_NAME START_MONITORING    MONITORING USED

---------- ------------------- ---------- ----

FBI        10/09/2012 18:27:01 YES        YES

SQL>

So, on the basis of this test, I do not agree with the documentation.

os_authent_prefix

In Oracle 10, the default value for os_authent_prefix was ops$:

 

SQL> l

  1  select value, isdefault

  2  from v$parameter

  3* where name = 'os_authent_prefix'

SQL> /

 

VALUE      ISDEFAULT

---------- ---------

ops$       TRUE

 

SQL>

 

So, if you created an ops$oracle user with a password:

 

SQL> grant dba to ops$oracle

  2  identified by andrew

  3  /

 

Grant succeeded.

 

SQL>

 

And you were logged onto the server hosting your database as UNIX user oracle:

 

TEST10 > whoami

oracle

TEST10 >

 

... you could logon to the database externally:

 

TEST10 > sqlplus /

 

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 3 14:41:28 2012

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL>

 

... or by using the password, which may not have been what you intended:

 

SQL> conn ops$oracle/andrew

Connected.

SQL>

 

To stop this happening, you could add the following line to your database’s parameter file:

 

os_authent_prefix = ""

Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:

 

SQL> l

  1  select ksppinm

  2  from   SYS.X$KSPPI

  3* where  substr(KSPPINM,1,1) = '_'

SQL> /

 

 

KSPPINM

-------------------------------------------------------

_trace_files_public

_latch_recovery_alignment

_spin_count

_latch_miss_stat_sid

_max_sleep_holding_latch

_max_exponential_sleep

_use_vector_post

_latch_class_0

_latch_class_1

_latch_class_2

_latch_class_3

 

Etc.

 

KSPPINM

-------------------------------------------------------

_xsolapi_sql_use_bind_variables

_xsolapi_sql_prepare_stmt_cache_size

_xsolapi_sql_result_set_cache_size

_xsolapi_debug_output

_xsolapi_cursor_use_row_cache

_xsolapi_cursor_max_rows_to_cache_per_req

_xsolapi_cursor_max_time_for_partial_cache

_xsolapi_source_trace

 

613 rows selected.

 

SQL>

 

What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = 'sql_trace'

SQL> /

 

NAME       VALUE      ISDEFAULT

---------- ---------- ---------

sql_trace  FALSE      TRUE

 

SQL>

 

In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:

 

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

no rows selected

 

SQL>

 

... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

NAME                 VALUE      ISDEFAULT

-------------------- ---------- ---------

_trace_files_public  TRUE       FALSE

 

SQL>

 

Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.

CPU_COUNT

According to Oracle’s own documentation for 10g release 1:

 

On most platforms, Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.

 

The following test was done on an Oracle 9 database running on Tru64:

 

Tru64 > psrinfo -n

number of processors on system = 1

Tru64 > psrinfo -v

Status of processor 0 as of: 12/02/11 15:19:04

  Processor has been on-line since 02/19/2011 17:29:11

  The alpha EV6.7 (21264A) processor operates at 618 MHz,

  has a cache size of 2097152 bytes,

  and has an alpha internal floating point processor.

 

Tru64 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 2 15:19:28 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> col value format a10

SQL> col isdefault format a9

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE      ISDEFAULT

---------- ---------

1          TRUE

 

SQL>

 

The following test was done on an Oracle 9 database running on Linux:

 

Linux > cat /proc/cpuinfo | grep -i 'processor' | wc -l

2

Linux > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Dec 2 15:40:12 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

 

SQL> col value format a10

SQL> col isdefault format a9

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE      ISDEFAULT

---------- ---------

2          TRUE

 

SQL>

 

The following test was done on an Oracle 10 database running on Solaris:

 

Solaris > psrinfo -p -v

The physical processor has 2 virtual processors (0 16)

  UltraSPARC-IV+ (portid 0 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (2 18)

  UltraSPARC-IV+ (portid 2 impl 0x19 ver 0x22 clock 1500 MHz)

Solaris > sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 2 15:52:40 2011

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL> col isdefault format a9

SQL> col value format a5

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE ISDEFAULT

----- ---------

4     TRUE

 

SQL>

 

So it looks as if it works as intended. I went to an Oracle seminar recently, where they said that cpu_count works differently in 11g release 2. Once I have investigated, I will cover this in a future post.

ISDEFAULT

There is a column in V$PARAMETER called ISDEFAULT:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         TRUE

SQL>

You might think that this tells you whether a particular parameter is set to its default value but this is not the case. If you add a recyclebin=on line to the parameter file and bounce the database, the same query runs as follows:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         FALSE

SQL>

That’s because an ISDEFAULT value of FALSE simply means that the parameter has been included in the parameter file. You can see what I mean on the screen print below (click to enlarge it). On the left is a SQL*Plus session which lists those parameters with ISDEFAULT = FALSE and on the right is the database’s parameter file. You will see that they match: