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>

DBMS_SESSION.SET_SQL_TRACE

This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

You can start and stop tracing an entire instance as follows:

SQL> alter system set sql_trace = true;

 

System altered.

 

SQL> alter system set sql_trace = false;

 

System altered.

 

SQL>

You can also start and stop tracing an instance by setting the sql_trace initialization parameter.

 

Going through some old notes, I saw a different way of tracing your current session and decided to try it out (the first statement is just to make it easy to see the trace file):

SQL> l

  1* alter session set tracefile_identifier = 'ANDREW'

SQL> /

 

Session altered.

 

SQL> exec dbms_session.set_sql_trace(true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from dual

  2  /

 

D

-

X

 

SQL> exec dbms_session.set_sql_trace(false);

 

PL/SQL procedure successfully completed.

 

SQL>

Then I looked in the directory specified by the user_dump_dest parameter and saw the associated trace file below:

Solaris > ls *ANDREW.trc

PQEDPT1_ora_27267_ANDREW.trc

Solaris >

Bug 106242

There was no year 0 in the calendar and Oracle's to_date function recognises this:

SQL> alter session set

  2  nls_date_format = 'DD-MON-YYYY BC'

  3  /

 

Session altered.

 

SQL> select to_date('01-JAN-0000 AD','DD-MON-YYYY AD') from dual

  2  /

select to_date('01-JAN-0000 AD','DD-MON-YYYY AD') from dual

               *

ERROR at line 1:

ORA-01841: (full) year must be between -4713 and

+9999, and not be 0

 

SQL>

However, Oracle's algorithm for date arithmetic works differently. I believe this is bug 106242 but My Oracle Support does not make it clear. The example below was tested on Oracle 11. First display 31st December 1 BC:

SQL> select

  2  to_date('31-DEC-0001 BC', 'DD-MON-YYYY BC')

  3  from dual

  4  /

 

TO_DATE('31-DE

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

31-DEC-0001 BC

 

SQL>

Display the next day. This should be 1st January 1 AD (as there was no year 0) but it isn't:

SQL> select

  2  to_date('31-DEC-0001 BC', 'DD-MON-YYYY BC') + 1

  3  from dual

 4  /

 

TO_DATE('31-DE

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

01-JAN-0000 AD

 

SQL>

_trace_files_public

In an earlier post, I looked at the tracefile_identifier parameter. This post, tested on an Oracle 9 database, uses it in conjunction with _trace_files_public. First create a trace file without setting the _trace_files_public parameter:

 

  1* alter session set tracefile_identifier = before

SQL> /

 

Session altered.

 

SQL> select value from v$parameter

  2  where name = '_trace_files_public'

  3  /

 

no rows selected

 

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> select sysdate from dual

  2  /

 

SYSDATE

---------

30-DEC-11

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

 

This creates a trace file with permissions of 640 so, if you are not in the dba group, you will not be able to read it:

 

TEST9 > ls -ltr

total 8

-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc

TEST9 >

 

Set _trace_files_public to true and repeat the test:

 

SQL> alter session set tracefile_identifier = after

  2  /

 

Session altered.

 

SQL> select value from v$parameter

  2  where name = '_trace_files_public'

  3  /

 

VALUE

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

TRUE

 

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> select sysdate from dual

  2  /

 

SYSDATE

---------

30-DEC-11

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

 

This creates a trace file with permissions of 644, which anybody can read:

 

TEST9 > ls -ltr

total 24

-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc

-rw-r--r-- 1 oracle dba 2302 Dec 30 12:07 mvltst_ora_56368_AFTER.trc

TEST9 >

 

This can be useful on a test machine, where developers want to read their trace files without DBA intervention.

Automatic Parallel Execution

In 11g release 2, Oracle introduced the following new parameters:

 

parallel_degree_policy

parallel_min_time_threshold

 

The default setting of parallel_degree_policy is manual. With this value, the automatic parallel execution of SQL statements is disabled. This is how Oracle worked before 11g release 2.

 

Setting this parameter to auto enables this functionality. Oracle then parses an SQL statement, works out its execution plan and estimates how long it will take. If this value is greater than  parallel_min_time_threshold seconds, the statement runs in parallel. You can see what difference this makes in the example below, which runs the same query twice.

 

Automatic parallel execution is turned off for the first run:

 

SQL> select value from v$parameter

  2  where name = 'parallel_min_time_threshold'

  3  /

 

VALUE

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

AUTO

 

SQL> select value from v$parameter

  2  where name = 'parallel_degree_policy'

  3  /

 

VALUE

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

MANUAL

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

 4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:02:00.41

SQL> set timing off

 

And the query takes just over 2 minutes. Automatic parallel execution is then turned on for the second run:

 

SQL> alter session set

  2  parallel_min_time_threshold = 1

  3  /

 

Session altered.

 

SQL> alter session set

  2  parallel_degree_policy = 'AUTO'

  3  /

 

Session altered.

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

  4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:00:37.15

SQL> set timing off

 

And the query takes less than 40 seconds.

Oracle Managed Files (Part 1)

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:

1. In the init.ora or server parameter file.
2. In an alter session or alter system statement.

The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';

 

Session altered.

 

SQL>

 

Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:

 

SQL> create tablespace andrew

  2  datafile size 10m

  3  /

 

Tablespace created.

 

SQL>

 

Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:

 

SQL> l

  1  select file_name, bytes from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

 

SQL>

 

Add a datafile to the tablespace and check the name(s) of the datafile(s) again:

 

SQL> alter tablespace andrew add datafile size 5m

  2  /

 

Tablespace altered.

 

SQL> select file_name, bytes from dba_data_files

  2  where tablespace_name = 'ANDREW'

  3  /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

/mnt/redhat/o1_mf_andrew_7fh2zndg_.dbf      5242880

 

SQL>

 

Look at the files at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -1

o1_mf_andrew_7fh2qylt_.dbf

o1_mf_andrew_7fh2zndg_.dbf

TEST9 >

 

Drop the tablespace:

 

SQL> drop tablespace andrew

  2  /

 

Tablespace dropped.

 

SQL>

 

Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -l

total 0

TEST9 >