Mohamed Houri’s Oracle Notes

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
About these ads

1 Comment »

  1. Test case: What happens if we purged recycle bin and imported fresh copy of table? I think it must use plan base line. ?

    Comment by Nassyam Basha — January 24, 2014 @ 6:16 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

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.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 84 other followers

%d bloggers like this: