OPTIMIZER_MODE = FIRST_ROWS_N

I have known about the first_rows optimizer mode for some time. This tells Oracle to use an execution path which will return the first few rows as quickly as possible. However, I recently read about the first_rows_n optimizer mode, which apparently appeared first in Oracle 9. This tells Oracle to use an execution path which will return the first n rows quickly, where n can be 1, 10, 100 or 1000. I decided to try it out in an Oracle 11.2 database. First I checked that Oracle would accept the expected values of n:

SQL> alter session set optimizer_mode = first_rows

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_10

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_100

  2  /

 

Session altered.

 

SQL>

…then, before checking the final option, I started to trace my SQL*Plus session:

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1000

  2  /

 

Session altered.

 

SQL> select sysdate "first_rows_1000" from dual

  2  /

 

first_rows_1000

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

29-SEP-14

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

I ran the trace file through tkprof and looked at the explain plan for the query I had just run. It did not seem to be aware exactly which optimizer mode I had used: 

********************************************************************************

 

SQL ID: 9u1zkyyn9vbt4

Plan Hash: 1546270724

select sysdate "first_rows_1000"

from

dual

 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        2      0.01       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0          0          0           2

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

total        8      0.01       0.00          0          0          0           2

 

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 5  (SYSTEM)

 

Rows     Row Source Operation

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

      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: FIRST_ROWS

      1   FAST DUAL

 

********************************************************************************

 

Finally, I tried to use an invalid value for n. This time the error message explained exactly where I had gone wrong: 

SQL> alter session set optimizer_mode = first_rows_99

  2  /

ERROR:

ORA-00096: invalid value FIRST_ROWS_99 for parameter

optimizer_mode, must be from among first_rows_1000,

first_rows_100, first_rows_10, first_rows_1,

first_rows, all_rows, choose, rule

SQL>

ORA-01123

I was reading through an old book recently and it said that you could not put a tablespace into hot backup mode if the database was in NOARCHIVELOG mode. This seemed reasonable to me but I wondered what might happen if you tried to do this so I ran the following SQL in an Oracle 11.2 database:  

SQL> l

  1* alter tablespace users begin backup

SQL> /

alter tablespace users begin backup

*

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery

not enabled

 

SQL>

If this happens to you, you have two choices:

•  Don't do it again OR
• Put the database into ARCHIVELOG mode first.

I hope to be doing some more serious posts about hot backup and recovery in the near future.

How to See When Your Database Was Opened

This was tested on Oracle 11.2:

FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:

 

SQL> create table t1 (c1 number)

  2  /

 

Table created.

 

SQL> alter system set fixed_date = none

  2  /

 

System altered.

 

SQL>

 

…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to create statistics for the table. Both of them set the LAST_ANALYZED column in USER_TABLES to the current date:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL>

 

I set FIXED_DATE to 25^th December 2014 and tried again:

 

SQL> alter system set fixed_date = '25-DEC-2014'

  2  /

 

System altered.

 

SQL>

 

DBMS_STATS.GATHER_TABLE_STATS set the LAST_ANALYZED column to the new FIXED_DATE value. I believe this is not what Oracle intended and may be a result of bug 8892343, which states:

 

The value of the LAST_ANALYZED column in USER_TABLES views is affected by the setting of the FIXED_DATE parameter when it should be independent of that value.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

25-DEC-14

 

SQL>

 

…whereas the old ANALYZE command set the LAST_ANALYZED column to the underlying system date:

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL>

ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

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

ENGLISH_UNITED KINGDOM.WE8ISO8859P15

 

SQL>

 

I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

 

Somebody typed the following command in the database by mistake:

 

SQL> l

  1* create database sonar character set utf8

SQL> /

create database sonar character set utf8

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I logged in to PL/SQL Developer again and found that I got an ORA-01460 when I tried to look at the same code in the database:

I also noticed that the database had a new character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

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

ENGLISH_UNITED KINGDOM.UTF8

 

SQL>

I guess this is a bug as the user account which typed the create database command had no special privileges and Oracle replied with an ORA-01031. Therefore no damage should have been caused.

Three days later:

I logged in as a privileged user and tried to change the character set back but this failed, in line with the Oracle documentation:

SQL> conn / as sysdba

Connected.

SQL> alter database character set we8iso8859p15

  2  /

alter database character set we8iso8859p15

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

 

SQL>

 

So, as a last resort, I logged back in with an ordinary user and tried to reverse the damage like this:

 

SQL> conn andrew/reid

Connected.

SQL> create database sonar character set we8iso8859p15

  2  /

create database sonar character set we8iso8859p15

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This seemed to have the desired effect, at least in the short term as the character set went back to its original value:

 

SQL> select sys_context('userenv','LANGUAGE')

  2  from dual

  3  /

 

SYS_CONTEXT('USERENV','LANGUAGE')

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

AMERICAN_AMERICA.WE8ISO8859P15

 

SQL>

 

I will report back as the problem develops…

A Simple Example with Indexes

I imagine there are many reasons why Oracle might (or might not) use an index. I guess there are also many reasons why you might (or might not) WANT Oracle to use an index.

I got the idea for this example from a book written by Mark Gurry and ran it on Oracle 11.2.

 

I dedicate it to Oliver, who thinks that database administrators spend all day creating indexes.

 

First I created a table:

SQL> create table t1

  2  as select * from dba_segments

  3  /

 

Table created.

 

SQL>

 

…then I made sure it contained enough data:

 

SQL> begin

  2  for a in 1..8 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL>

I added an index:

SQL> create index i1 on t1(owner, extents)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

…then I ran a query against the table:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:27.29

 

Execution Plan

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

Plan hash value: 3693069535

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |    16 |  2438  (29)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |

|*  2 |   TABLE ACCESS FULL| T1   | 10388 |   162K|  2438  (29)| 00:00:03 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("EXTENTS"=1 AND "OWNER"='SYS')

 

 

Statistics

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

        365  recursive calls

          0  db block gets

      37109  consistent gets

      37046  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>

 

The query had to look at 23% of the rows in the table:

 

SQL> l

  1  select round

  2  ((select count(*) from t1

  3    where owner = 'SYS' and extents = 1)

  4  /

  5  (select count(*) from t1) * 100)

  6* as percentage from dual

SQL> /

 

PERCENTAGE

----------

        23

 

SQL>

 

…so it did a full table scan instead of using the index and the elapsed time was 27 seconds. I wanted to improve on this so I added the bytes column to the index:

 

SQL> drop index i1

  2  /

 

Index dropped.

 

SQL> create index i2 on t1(owner, extents, bytes)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I ran the query again. This time, Oracle could get all the information it needed from the index so the elapsed time went down to 3 seconds:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:03.34

 

Execution Plan

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

Plan hash value: 494139663

 

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |      |     1 |    16 |   581  (47)| 00:00:01 |

|   1 |  SORT AGGREGATE       |      |     1 |    16 |            |          |

|*  2 |   INDEX FAST FULL SCAN| I2   |   518K|  8099K|   581  (47)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OWNER"='SYS' AND "EXTENTS"=1)

 

 

Statistics

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

        432  recursive calls

          0  db block gets

       6843  consistent gets

        569  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>