Some Common Errors - 5 - Not reviewing the alert.log and trace files

It seems that there are DBAs who do not look at the alert.log and/or trace files generated on the server. Neither do they manually login and look at the logs/traces nor do they rely on an automated tool to do so -- or the automated tool is "scripted" or "hardcoded" to look for only a very short list of possible messages.

Queries like

a. The XXX MV Refresh Job is failing. Why is it failing ?

b. A User calls me and tells me that Oracle is reporting that a particular datafile 'N' is offline. When did the file go offline, for what reason ?

c. A particular datablock is corrupt. Since when has it been corrupt ?

d. Are any ORA-1555 errors occuring ?

e. When did Tablespace USERS hit 100% and stop auto-extending ?

can be answered simply by the DBA looking at the alert.log and/or trace files on occassion if not religiously and frequently. Oracle did introduce configurable Alerts in OEM and the views DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY but I am not sure if these are being configured and used.

.

.

.

Something Unique about Unique Indexes

Recently, a forums posting how it could be that a concatenated index on two columns could fail to be Unique while an index on the first column alone *was* Unique.
It doesn't sound logical does it ? If column_A is unique, surely (column_A, column_B) together should also be unique !

Not always.

See :

SQL> create unique index sales_orders_u1 on sales_orders(order_no);

Index created.

SQL> drop index sales_orders_u1;

Index dropped.

SQL> create unique index sales_orders_u2 on sales_orders(order_no,year_month);
create unique index sales_orders_u2 on sales_orders(order_no,year_month)
                                    *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL>  

Why does index sales_orders_u1 get created as Unique Index but sales_orders_u2, with an additional column, fail ?

Think about it for a while ..........
.
.
.
.
.
.


OK. Here's how I setup the table :

SQL> drop table sales_orders  purge;

Table dropped.

SQL> create table sales_orders
2  (order_no number, year_month varchar2(7) not null,
3   product_code number, quantity number, description varchar2(50));

Table created.

SQL>
SQL> insert into sales_orders values (1,'2009JAN',1001,50,'A Sale');

1 row created.

SQL> insert into sales_orders values (2,'2009FEB',2001,10,'Another Sale');

1 row created.

SQL> insert into sales_orders values (NULL,'2009MAR',1001,60,'Third Sale');

1 row created.

SQL> insert into sales_orders values (NULL,'2009MAR',2001,80,'Fourth Sale');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create unique index sales_orders_u1 on sales_orders(order_no);

Index created.

SQL> drop index sales_orders_u1;

Index dropped.

SQL> create unique index sales_orders_u2 on sales_orders(order_no,year_month);
create unique index sales_orders_u2 on sales_orders(order_no,year_month)
                                     *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL> 

What was (er..) unique about the pairing of the two columns ?

Have you caught the reason ?
If not, think for some more time .....

The issue arises from Oracle's handling of NULLs (as in the first column order_no which has 2 rows with NULL values).
A NULL in Oracle is "indeterminate". It is an unknown value. Therefore, two NULLs are never equal.
The Unique Index sales_orders_u1 on order_no alone succeeds because the NULL values in the two rows are not compared as they are not equal to each other (in fact, the two rows are not even captured in the index !).
However, when I create Unique Index sales_orders_u2 with an additional column, Oracle actually compares the values in the second column. Although it would seem that if two NULLs are not alike, then we could afford to ignore comparing the second column. However, Oracle does not ignore the values that are not NULLs. If it finds duplicates in these values (as it finds in year_month), the test for Uniqueness fails.

Update : If the second column, too, has NULLs and there happen(s) to be any row(s) where *both* the first column (order_no) and the second column (year_month) contain a NULL, that/those rows would not be included in the index and would pass the "Uniqueness" test as Oracle cannot compare rows with all NULLs.

The forums posting is here.

This behaviour is documented. Rather than pointing you to the documentation, I'll provide a few other references :

1. Tom Kyte's post "Something about nothing".
2. His follow-up post "Mull about null".
3. My post "Nulls are Indexed, Right ? NOT !"
4. My post "Indexed column (unique or not) -- What if it is nullable".

(I've also touched on NULLs in a few other posts -- e.g. "The difference between NOT IN and NOT EXISTS" and "Counting the rows in a Table".

.
.
.

Some Common Errors - 4 - Not using ARRAYSIZE

When fetching large datasets from the database server to a client (even if the client is sqlplus running on the same host machine) it becomes important to use the right ARRAYSIZE.

I have blogged about this frequently, so rather than creating another test case, I post links to my previous blogs :

Using ARRAYSIZE to reduce RoundTrips and number of FETCH calls

Tuning Very Large SELECTs in SQLPlus

Array Processing, SQL*Net RoundTrips and consistent gets

(Note : The default ARRAYSIZE in Oracle's SQLPlus client is 15).

.

.

.

Using Aliases for Columns and Tables in SQLs

Jonathan Lewis's post "Aliases" and the discussion that follows summarise the issues and "good practices" around using aliases and the proper alias names in SQL statements.

.

.

.

Table and Index Statistics with MOVE/REBUILD/TRUNCATE

Recently, I've noticed a few questions about whether GATHER_STATS should be re-run after a MOVE/REBUILD operation.

The proper answer is "Do a GATHER_STATS if there is a change to the data and/or segment size since the last time statistics were gathered".


Below, I demonstrate how
a) a MOVE does not update Table Statistics
b) a REBUILD *does* update Index Statistics (in 10g and above)
c) a TRUNCATE does not update Table Statistics
d) how these can impact "costing" of Execution Plans.

SQL> drop table target_table purge;

Table dropped.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     50705

SQL> 
SQL> create table target_table as select * from dba_objects where object_id is not null;

Table created.

SQL> create index target_table_ndx on target_table(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TARGET_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER SIZE 250', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- current statistics
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
  2  from user_tables where table_name = 'TARGET_TABLE'
  3  union
  4  select 'Index', index_name, num_rows, leaf_blocks
  5  from user_indexes where index_name = 'TARGET_TABLE_NDX'
  6  order by 1 desc
  7  /

OBJ_T OBJ_NAME                         NUM_ROWS     BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE                        50705        717
Index TARGET_TABLE_NDX                    50705        112

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
  2  from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
  3  union
  4  select 'Index Seg', segment_name, blocks
  5  from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
  6  order by 1 desc
  7  /

SEG_TYPE  SEG_NAME                           BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE                          768
Index Seg TARGET_TABLE_NDX                      128

SQL> 
SQL> REM REM REM ##########################################
SQL> -- a simple move/rebuild
SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL> 
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
  2  from user_tables where table_name = 'TARGET_TABLE'
  3  union
  4  select 'Index', index_name, num_rows, leaf_blocks
  5  from user_indexes where index_name = 'TARGET_TABLE_NDX'
  6  order by 1 desc
  7  /

OBJ_T OBJ_NAME                         NUM_ROWS     BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE                        50705        717
Index TARGET_TABLE_NDX                    50705        112

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
  2  from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
  3  union
  4  select 'Index Seg', segment_name, blocks
  5  from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
  6  order by 1 desc
  7  /

SEG_TYPE  SEG_NAME                           BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE                          768
Index Seg TARGET_TABLE_NDX                      128

SQL> 
SQL> REM REM REM ##########################################
SQL> -- what happens if we have deleted rows ?
SQL> select count(*) from target_table;

  COUNT(*)
----------
     50705

SQL> delete target_table where owner = 'SYS';

22941 rows deleted.

SQL> select count(*) from target_table;

  COUNT(*)
----------
     27764

SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL> 
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
  2  from user_tables where table_name = 'TARGET_TABLE'
  3  union
  4  select 'Index', index_name, num_rows, leaf_blocks
  5  from user_indexes where index_name = 'TARGET_TABLE_NDX'
  6  order by 1 desc
  7  /

OBJ_T OBJ_NAME                         NUM_ROWS     BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE                        50705        717
Index TARGET_TABLE_NDX                    27764         62

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
  2  from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
  3  union
  4  select 'Index Seg', segment_name, blocks
  5  from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
  6  order by 1 desc
  7  /

SEG_TYPE  SEG_NAME                           BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE                          512
Index Seg TARGET_TABLE_NDX                       72

SQL> 
SQL> REM REM REM ##########################################
SQL> -- what happens if we TRUNCATE ?
SQL> truncate table target_table ;

Table truncated.

SQL> alter table target_table move ;

Table altered.

SQL> alter index target_table_ndx rebuild;

Index altered.

SQL> 
SQL> -- statistics after the move/rebuild
SQL> select 'Table' Obj_Type, table_name Obj_Name, num_rows, blocks
  2  from user_tables where table_name = 'TARGET_TABLE'
  3  union
  4  select 'Index', index_name, num_rows, leaf_blocks
  5  from user_indexes where index_name = 'TARGET_TABLE_NDX'
  6  order by 1 desc
  7  /

OBJ_T OBJ_NAME                         NUM_ROWS     BLOCKS
----- ------------------------------ ---------- ----------
Table TARGET_TABLE                        50705        717
Index TARGET_TABLE_NDX                        0          0

SQL> select 'Table Seg' Seg_Type, segment_name Seg_Name, blocks
  2  from user_segments where segment_name = 'TARGET_TABLE' and segment_type = 'TABLE'
  3  union
  4  select 'Index Seg', segment_name, blocks
  5  from user_segments where segment_name = 'TARGET_TABLE_NDX' and segment_type = 'INDEX'
  6  order by 1 desc
  7  /

SEG_TYPE  SEG_NAME                           BLOCKS
--------- ------------------------------ ----------
Table Seg TARGET_TABLE                            8
Index Seg TARGET_TABLE_NDX                        8

SQL> 
SQL> REM REM REM ##
SQL> REM Findings :
SQL> REM 1. Index Statistics are updated at the REBUILD
SQL> REM   -- Note :  10g defaults to COMPUTE STATISTICS with the REBUILD of an Index
SQL> REM              9i and below do not default to COMPUTE STATISTICS
SQL> REM 2. Table Statistics are NOT updated at the MOVE
SQL> 
SQL> REM Implications :
SQL> REM If you have done a MOVE/REBUILD because there has been significant change in data
SQL> REM and you expect the Oracle to generate different execution plans
SQL> REM you might be suprised.
SQL> REM Plans that are influenced by the Index are changed
SQL> REM Plans that cannot use an Index at all are not changed
SQL> REM -- However this can be detrimental to costing for the table in Joins with other Tables
SQL> REM -- as the Join costing for a FullTableScan / HashJoin may not be truthful
SQL> 
SQL> REM for example, although the table has been TRUNCATED see below :
SQL> 
SQL> explain plan for select owner, object_name, object_type from target_table where owner = 'SYS';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4001333823

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 22941 |  1859K|   197   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TARGET_TABLE | 22941 |  1859K|   197   (1)| 00:00:03 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

13 rows selected.

SQL> 
SQL> REM Oracle still expects 22,941 rows for 'SYS' in the table -- even though it has been TRUNCATed
SQL>  

Notice how Table Statistics are not updated but Index Statistics *ARE* updated -- simply because 10g defaults to including a COMPUTE STATISTICS when an Index is Rebuilt.

.
.
.

Some Common Errors - 3 - NOLOGGING and Indexes

As a follow up to "Some Common Errors - 2 - NOLOGGING as a Hint", here I point out that NOLOGGING does not apply to an Index when executing DML on the Table.

NOLOGGING on an Index applies only during a CREATE or REBUILD. Not after either action.

If you have created or, subsequently, set an Index to NOLOGGING, the NOLOGGING doesn't really apply to DML. INSERTs will generate Redo in respect of the necessary updates to the Index.

NOLOGGING on an Index does not apply to DELETE and UPDATE (of Index Key columns) operations at all. All such DML will always generate Undo and Redo.


Below is a simple demonstration of the impact the presence of 1 index has on a 1.6million row INSERT.
The first table has no Index and the INSERT operation with APPEND and NOLOGGING defined at the Table level generates very little Undo and Redo.
The second table does have 1 index only and yet, it generates a very high volume of Undo and Redo -- which are for updates to the Index to add new entries.

SQL> desc source_table;
Name                                                                     Null?    Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER                                                                             VARCHAR2(30)
OBJECT_NAME                                                                       VARCHAR2(128)
SUBOBJECT_NAME                                                                    VARCHAR2(30)
OBJECT_ID                                                                         NUMBER
DATA_OBJECT_ID                                                                    NUMBER
OBJECT_TYPE                                                                       VARCHAR2(19)
CREATED                                                                           DATE
LAST_DDL_TIME                                                                     DATE
TIMESTAMP                                                                         VARCHAR2(19)
STATUS                                                                            VARCHAR2(7)
TEMPORARY                                                                         VARCHAR2(1)
GENERATED                                                                         VARCHAR2(1)
SECONDARY                                                                         VARCHAR2(1)

SQL> select count(*) from source_table;

 COUNT(*)
----------
  1620768

SQL>
SQL> drop table target_no_index purge;

Table dropped.

SQL> create table target_no_index as select * from source_table where 1=2;

Table created.

SQL> alter table target_no_index nologging;

Table altered.

SQL>
SQL> drop table target_with_index purge;

Table dropped.

SQL> create table target_with_index as select * from source_table where 1=2;

Table created.

SQL> alter table target_with_index nologging;

Table altered.

SQL> create index target_ndx on target_with_index(object_id, owner) nologging;

Index created.

SQL> alter index target_ndx nologging;

Index altered.

SQL>
SQL>
SQL> REM REM REM ############################################################
SQL> -- Redo and Undo Stats on NOLOGGING Table with No Indexes
SQL> connect hemant/hemant
Connected.
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                              0
redo size                                                                 0
undo change vector size                                                   0

SQL>
SQL> insert /*+ APPEND */ into target_no_index select * from source_table;

1620768 rows created.

SQL> commit;

Commit complete.

SQL> -- show impact of first insert
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                           2824
redo size                                                            375628
undo change vector size                                               85140

SQL>
SQL> connect hemant/hemant
Connected.
SQL> insert /*+ APPEND */ into target_no_index select * from source_table;

1620768 rows created.

SQL> commit;

Commit complete.

SQL> -- show impact of second insert
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                           1355
redo size                                                            137784
undo change vector size                                               23748

SQL>
SQL>
SQL> REM REM REM ############################################################
SQL>
SQL> REM REM REM ############################################################
SQL> -- Redo and Undo Stats on NOLOGGING Table with 1 Index
SQL> connect hemant/hemant
Connected.
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                              0
redo size                                                                 0
undo change vector size                                                   0

SQL>
SQL> insert /*+ APPEND */ into target_with_index select * from source_table;

1620768 rows created.

SQL> commit;

Commit complete.

SQL> -- show impact of first insert
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                          57906
redo size                                                         149359644
undo change vector size                                            70866196

SQL>
SQL> connect hemant/hemant
Connected.
SQL> insert /*+ APPEND */ into target_with_index select * from source_table;

1620768 rows created.

SQL> commit;

Commit complete.

SQL> -- show impact of second insert
SQL> select     sn.name Stat_Name, ms.value Stat_Value
 2  from v$statname sn, v$mystat ms
 3  where sn.statistic#=ms.statistic#
 4  and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
 5  order by 1
 6  /

STAT_NAME                                                        STAT_VALUE
---------------------------------------------------------------- ----------
redo entries                                                         142198
redo size                                                         249846348
undo change vector size                                           117892788

SQL>  

Thus, we see that INSERT operation on the table "target_with_index" generates significantly more Undo and Redo because of the presence of the index.

.
.
.

An Oracle DBA Interview

Kamran Agayev has published an email interview of me.

.

.

.

Some Common Errors - 2 - NOLOGGING as a Hint

Continuing the Common Errors Series,

this is the second common error : specifying NOLOGGING as a Hint and expecting it to work.

I have seen instances of code like :

INSERT /*+ APPEND NOLOGGING */ INTO 

OR

INSERT /*+ NOLOGGING */ INTO 

OR

DELETE /*+ NOLOGGING */ FROM 

OR

UPDATE /*+ NOLOGGING */ 

All of these will generate Redo -- i.e. none of them will actually be NOLOGGING.
(update : caveat : The INSERT /*+ APPEND NOLOGGING */ will actually be a nologging operation in a database that is running NOARCHIVELOG mode, but not because of the NOLOGGING hint but because of how Oracle handles APPEND in a NOARCHIVELOG database).

There is no such Hint as NOLOGGING.
NOLOGGING can only be specified either as a
a) Table/Index attribute (e.g with an "ALTER TABLE/INDEX")
or
b) as a DDL Clause (in a CREATE or ALTER ... MOVE or ALTER .. REBUILD)

If you specify a "Hint" "NOLOGGING" Oracle calmly ignores it -- intended hints that are invalid are ignored (or, you could say, as I would, treated as comments) without raising an error.

This still raises questions about NOLOGGING in situations like
1. When it is defined at the Tablespace/Table/Index level -- does it apply to all operations ?
2. How does NOLOGGING come into play with respect to Indexes.

Needless to say, there seem to be some misconceptions around these as well. I shall raise those as other "common errors" in the near future.


UPDATE : If you do use the NOLOGGING as Hint, please take note of Oracle Support Note 826893.1.   Any Hint after the invalid NOLOGGING Hint is ignored.  Thus, in this case :

INSERT /*+ NOLOGGING PARALLEL (4) */ ....

the PARALLEL Hint is NOT used. The Insert operates in ignorance of the PARALLEL Hint -- it becomes a Serial Insert.  Similarly, any other Hint after the NOLOGGING is also ignored.
.
.
.

Multiple Block Sizes

Charles Hooper has put together quite a large collection of quotes / references about Multiple Block Sizes in Oracle.

.

.

.