Monday, September 29, 2014

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>

Thursday, September 25, 2014

PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:
 
SQL> conn / as sysdba
Connected.
 
SQL> create profile for_andrew
  2  limit password_reuse_max 1
  3  /
 
Profile created.
 
SQL>
 
I created a user called ANDREW and gave him the FOR_ANDREW profile:
 
SQL> create user andrew
  2  identified by old_password
  3  profile for_andrew
  4  /
 
User created.
 
SQL> grant create session to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
I connected to the database as user ANDREW:
 
SQL> conn andrew/old_password
Connected.
SQL>
 
I tried to reuse the existing password but this failed as I had expected it would:
 
SQL> alter user andrew
  2  identified by old_password
  3  /
alter user andrew
*
ERROR at line 1:
ORA-28007: the password cannot be reused
 
SQL>
 
I used a different password before trying to reuse the original one. This failed with the same error, which I had not expected:
 
SQL> alter user andrew
  2  identified by new_password
  3  /
 
User altered.
 
SQL> alter user andrew
  2  identified by old_password
  3  /
alter user andrew
*
ERROR at line 1:
ORA-28007: the password cannot be reused
 
SQL>
 
I did some research and saw that the PASSWORD_REUSE_TIME was set to DEFAULT. This meant that it had the same value as the DEFAULT profile where it was set to UNLIMITED:
 
SQL> conn / as sysdba
Connected.
SQL> select limit from dba_profiles
  2  where profile = 'FOR_ANDREW'
  3  and resource_name = 'PASSWORD_REUSE_TIME'
  4  /
 
LIMIT
----------------------------------------
DEFAULT
 
SQL> select limit from dba_profiles
  2  where profile = 'DEFAULT'
  3  and resource_name = 'PASSWORD_REUSE_TIME'
  4  /
 
LIMIT
----------------------------------------
UNLIMITED
 
SQL>
 
According to the Oracle 11.1 documentation:
 
These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.
 
It then went on to say:

For these parameter to have any effect, you must specify an integer for both of them. 

... which I found a bit misleading. However, it clarified this a few lines later as follows: 

If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password. 

I set PASSWORD_REUSE_TIME to 1 minute and checked that I still could not reinstate the original password:

SQL> alter profile for_andrew
  2  limit password_reuse_time 1/1440
  3  /
 
Profile altered.
 
SQL> conn andrew/new_password
Connected.
SQL> alter user andrew
  2  identified by old_password
  3  /
alter user andrew
*
ERROR at line 1:
ORA-28007: the password cannot be reused
 
SQL>
 
However, I waited for a minute and found that I could:
 
SQL> exec sys.dbms_lock.sleep(60);
 
PL/SQL procedure successfully completed.
 
SQL> alter user andrew
  2  identified by old_password
  3  /
 
User altered.
 
SQL>
 
I do not know which Oracle process controls this functionality but it seemed a bit hit and miss. When I repeated the test, I sometimes found I had to wait more than a minute before I could change the password back to its original value.