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 18, 2014

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.

Wednesday, September 10, 2014

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 25th 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>

Monday, September 08, 2014

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…

Wednesday, September 03, 2014

A Simple Example with Indexes

I did this worked example on Oracle 11.2. 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>
 
I got the idea for this example in the book below, written by Mark Gurry: