Mohamed Houri’s Oracle Notes

November 14, 2012

Recycle bin: what’s going on?

Filed under: Oracle — hourim @ 11:11 am

Have you ever been confronted to such a kind of explain plan?

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                                |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |                                |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | BIN$FLEwdQFFQfu/hr2Q7TQeag==$0 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))
 

Look carefully to the operation 4

 INDEX RANGE SCAN   BIN$FLEwdQFFQfu/hr2Q7TQeag==$0 

Bizarre!!! From where does this index come from ?

More bizarre is this:

select * from recyclebin;
        no rows selected

select count(*) from user_objects where object_name like 'BIN%';

  COUNT(*)
----------
        45

The Recycle bin is empty while the user_objects table contains 45 objects having a name starting with the BIN characters!!!

I will explain how I came to this situation.

First this is the reproducible model:

create table rang_list_tab
(
  creation_date   date                not null,
  status_code     varchar2(30 char)   not null,
  n1              number              not null,
  v1              varchar2(1 char)    not null
)
partition by range (creation_date)
subpartition by list (status_code)
subpartition template
  (subpartition sub_p1 values ('FULL') ,
   subpartition sub_p2 values ('EMPTY') ,
   subpartition sub_p3 values ('UNKNOWN') ,
   subpartition others  values (default)
   )
(  
  partition p_20100131 values less than ( to_date('20100201','yyyymmdd') ),  
  partition p_20100201 values less than ( to_date('20100202','yyyymmdd') ),  
  partition p_20100202 values less than ( to_date('20100203','yyyymmdd') ), 
  partition p_20100203 values less than ( to_date('20100204','yyyymmdd') ),  
  partition p_20100204 values less than ( to_date('20100205','yyyymmdd')), 
  partition p_20100205 values less than (to_date('20100206','yyyymmdd')),  
  partition p_20100206 values less than ( to_date('20100207','yyyymmdd')),  
  partition p_20100207 values less than ( to_date('20100208','yyyymmdd'))
)
;

create  index idx_cdate on rang_list_tab (creation_date) local ;

create index idx_n1 on rang_list_tab(n1);

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'FULL', 1000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100202','yyyymmdd'), 'FULL', 30000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100203','yyyymmdd'), 'EMPTY', 2000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'UNKNOWN', 1000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100205','yyyymmdd'), 'EMPTY', 2400, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'EMPTY', 500, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 700, 'P');

insert into rang_list_tab values (to_date('20100202','yyyymmdd'), 'UNKNOWN', 1200, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 400, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100206','yyyymmdd'), 'FULL', 600, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'FULL', 700, 'P');

commit;

Now, I will drop this table and flash back it again

drop table rang_list_tab;

flashback table rang_list_tab to before drop;

explain plan for
  2  select * from rang_list_tab
  3  where creation_date = trunc(sysdate-5)
  4  ;

Explained.

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                                |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |                                |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | BIN$ZVFWHaVnTlS+4EFPATDblg==$0 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))

And here we are.

When you flash back a dropped table its attached index is not given back its original name. You need to manually rename it.

alter index "BIN$ZVFWHaVnTlS+4EFPATDblg==$0" rename to idx_cdate ;

Index altered.

mohamed@mhouri> explain plan for
  2  select * from rang_list_tab
  3  where creation_date = trunc(sysdate-5)
  4  ;

Explained.

mohamed@mhouri> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |               |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |               |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_CDATE     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

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

   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))

That is not only the indexes that are not giving back their original name but almost all the table attached objects. Let’s try.

create table t1 (n1 number primary key);
create table t2 (n1 number primary key, n2 number , d2 date);
alter table t2 add constraint t2_fk foreign key(n2) references t1(n1);
alter table t2 add constraint t2_ck_d2 check (d2 is not null);
alter table t2 add constraint t2_uk_d2 unique(d2);

create index t2_fk_i on t2 (n2);

create or replace trigger t2_trg 
before insert on t2
for each row
begin dbms_output.put_line('trigger exists'); end;
/

select constraint_name, constraint_type, r_constraint_name from user_constraints where table_name = 'T2';

CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
SYS_C0015809                   P
T2_FK                          R SYS_C0015804
T2_CK_D2                       C
T2_UK_D2                       U

select index_name, table_name, uniqueness from user_indexes where table_name     = 'T2';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
T2_FK_I                        T2                             NONUNIQUE
T2_UK_D2                       T2                             UNIQUE
SYS_C0015809                   T2                             UNIQUE

select trigger_name, table_name from user_triggers where table_name    = 'T2';

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
T2_TRG                         T2

Table t2 has 4 constraints, 3 indexes and 1 trigger. I will drop it, flash it back and see what happens

drop table t2;

select  original_name, object_name, type from recyclebin;

ORIGINAL_NAME                    OBJECT_NAME                    TYPE
-------------------------------- ------------------------------ -------------------------
T2                               BIN$aIjHZIW4SIOhKx5Igx+FeQ==$0 TABLE
T2_TRG                           BIN$ZFZjH11RTbeQL8nMB+erDA==$0 TRIGGER
T2_UK_D2                         BIN$ryvaxcC6TLmjTj1Z+R/l7Q==$0 INDEX
SYS_C0015816                     BIN$PoLuxzQtRLqJqnA/8HWmnA==$0 INDEX
T2_FK_I                          BIN$DcRcwh2oSP6Uqc3eyI6ntA==$0 INDEX

flashback table t2 to before drop;

select constraint_name, constraint_type, r_constraint_name from user_constraints where table_name = 'T2';

CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
BIN$gteIpe7bTF2eWYi8/78uuw==$0 P
BIN$28+EDP0yTBKep354YKfaEQ==$0 C
BIN$xUFESH5oSLOICYctWxvcmQ==$0 U

The Foreign Key constraint has not been flashed back. It’s gone. Lost. The unique and primary constraint are back with that very bizarre name.

select index_name, table_name, uniqueness from user_indexes where table_name     = 'T2';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
BIN$DcRcwh2oSP6Uqc3eyI6ntA==$0 T2                             NONUNIQUE
BIN$ryvaxcC6TLmjTj1Z+R/l7Q==$0 T2                             UNIQUE
BIN$PoLuxzQtRLqJqnA/8HWmnA==$0 T2                             UNIQUE

All indexes are back with their not very commun name

select trigger_name, table_name from user_triggers where table_name    = 'T2';

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
BIN$ZFZjH11RTbeQL8nMB+erDA==$0 T2

And trigger is back also but always without its original name.

In this context of name change, I was wondering what if an application is hinting indexes with their original name. What happens when their corresponding table is droped/flashed back?

create table t2 as select rownum n1 from dual connect by level <= 1E6;

create index ind_t2 on t2(n1);

explain plan for
select /*+ index (t2 ind_t2) */ *
from t2
where n1 > 100;

select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   746K|  9482K|  2300   (1)| 00:00:28 |
|*  1 |  INDEX RANGE SCAN| IND_T2 |   746K|  9482K|  2300   (1)| 00:00:28 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N1">100)
 
drop table t2;

flashback table t2 to before drop;

explain plan for
  2  select /*+ index (t2 ind_t2) */ *
  3  from t2
  4  where n1 > 100;

Explained.

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   746K|  9482K|   440   (4)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T2   |   746K|  9482K|   440   (4)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("N1">100)

The hinted index is not anymore selected by the CBO. If you want to reproduce the explain plan before the drop then you need to hint again with the new bizarre index name or to rename the flashed back index name to the original name

Bottom Line: when you flash back a dropped table, do not be surprised to find that you are no longer protected by a foreign key constraint if it was the case before the drop. Be aware also that you will lose any naming standard you have used for the table depending objects such as triggers, indexes, constraints and so on. Due to this naming change, any hinted index could not be selected anymore by the CBO until you will explicitly give it back its original name. And in that case, where would you find this original name? Hmmm!!! Documentation rocks, documentation rocks.

Advertisements

January 24, 2014

SQL Plan Management and table flashback

Filed under: Oracle — hourim @ 11:17 am

Continuing my series on what makes a SPM plan not used anymore, today I will show the collateral effect a surgical drop/flashback table operation can have on the reproducibility (or on the use which is most adequat word in this case) of an enabled and accepted SQL baseline

Let’s see this in action

 create table rang_list_tab
( creation_date   date                not null,
  status_code     varchar2(30 char)   not null,
  n1              number              not null,
  v1              varchar2(1 char)    not null
)
partition by range (creation_date)
subpartition by list (status_code)
subpartition template
(subpartition sub_p1 values ('FULL') ,
 subpartition sub_p2 values ('EMPTY') ,
 subpartition sub_p3 values ('UNKNOWN') ,
 subpartition others  values (default)
)
(
partition p_20100131 values less than ( to_date('20100201','yyyymmdd') ),
partition p_20100201 values less than ( to_date('20100202','yyyymmdd') ),
partition p_20100202 values less than ( to_date('20100203','yyyymmdd') ),
partition p_20100203 values less than ( to_date('20100204','yyyymmdd') ),
partition p_20100204 values less than ( to_date('20100205','yyyymmdd')),
partition p_20100205 values less than (to_date('20100206','yyyymmdd')),
partition p_20100206 values less than ( to_date('20100207','yyyymmdd')),
partition p_20100207 values less than ( to_date('20100208','yyyymmdd'))
)
;

create index idx_n1 on rang_list_tab(n1);

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'FULL', 1000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100202','yyyymmdd'), 'FULL', 30000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100203','yyyymmdd'), 'EMPTY', 2000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'UNKNOWN', 1000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100205','yyyymmdd'), 'EMPTY', 2400, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'EMPTY', 500, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 700, 'P');
INSERT into rang_list_tab values (to_date('20100202','yyyymmdd'), 'UNKNOWN', 1200, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 400, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100206','yyyymmdd'), 'FULL', 600, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'FULL', 700, 'P');

commit;

exec dbms_stats.gather_table_stats(user, 'rang_list_tab');

The following select is covered by an already enabled and accepted stored Baseline as shown below:

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1798114219

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |       |       |     3 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| RANG_LIST_TAB |     1 |    19 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IDX_N1        |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

Note
-----
- SQL plan baseline SQL_PLAN_95rubw024zzjjed2b6efe used for this statement

What if by error someone drops the rang_list_tab table and then realizes that he needs to flash it back

SQL> -- drop the table
SQL> drop table rang_list_tab;

Table dropped.

SQL> -- damn!!! I need it back
SQL> flashback table rang_list_tab to before drop;

Flashback complete.

Well, all things are back and we are safe. Right? Until someone will start complaining about a baseline that used to be used and is not anymore constraining the CBO from selecting other paths

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

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

SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1757268378
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                |       |       |     3 (100)|          |       |      |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    19 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | BIN$8LTnuCCRXdfgQ20MIwp/NA==$0 |     1 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

The SQL plan baseline has not been used thanks to this table drop/flashback.

In fact, this not anymore than a collateral effect of the drop/flashback of the table which has the consequence of not flashing back the original index name baseline

If you want to put your baseline back to its initial behavior then rename the index as shown below

SQL> alter index  "BIN$8LTnuCCRXdfgQ20MIwp/NA==$0" rename to idx_n1;

Index altered.

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

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

---------
SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1798114219

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |       |       |     3 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| RANG_LIST_TAB |     1 |    19 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IDX_N1        |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

Note
-----
- SQL plan baseline SQL_PLAN_95rubw024zzjjed2b6efe used for this statement

Bottom line:  When you drop and flashback a table, then think about the following points

  1. the foreign key constraints are not flashed back
  2. the original index name, the trigger name and constraint name are not flashed back
  3. any SQL plan baseline based on an index created on a table that has been dropped and flashed back will not be reproducible until you give that index its original name

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)