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.

Thursday, September 11, 2014

How to See When Your Database Was Opened

This was tested on Oracle 11.2: 

SQL> l
  1* select startup_time from v$instance
SQL> /
 
STARTUP_TIME
------------
24-MAY-12
 
SQL>


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: