How to catalog backups / archivelogs / datafile copies / control file copies

RMAN Catalog:It is mainly used for following:

Can store database backup scripts and backup information to safe place(RMAN Repository)apart from target database control file.
Can add backup pieces and image copies of backup on disk to the RMAN repository.
Can record a data file copy as a level 0 incremental backup in the RMAN repository and that can be used foran incremental backup.
Can record latest user-managed datafile copies to RMAN repository made after the clean shutdown of database.

Limitations and Advantages of Catalog:

To connect to target database using RMAN target database must be mounted or open.

1. Catalog database must be OPEN if RMAN is connected to catalog database.
2. RMAN can only catalog a datafile copy, control file copy, archived log or backup piece if these areuser-managed copy (OS level copy).
3. Catalogis accessible on disk.
4. File belongs to another database can not be cataloged using CATALOG.
5. All user managed (File system) backups considered as an IMAGE COPIES by RMAN while cataloged.
6. RMAN does not check whether the file was correctly copied by the operating system utility; it just reads and checks the header during cataloging but file should be clean(consistent) while copying through OS utilities so .

Cataloging Backups Examples:

1. Cataloging Archived Logs:Here we assumed that archive logs have been moved to other location using operating system utilities (CP/MV/COPY) and we cataloging those moved Archiveloged to RMAN REPOSITORY.

RMAN > CATALOG ARCHIVELOG ‘?/oradata/backup/arch_1.dbf’, ‘?/oradata/backup/arch_9.dbf’;

If you need to catalog multiple archive files then use below simple script which will generate .rcv file and run newly created .rcv file.

[oracle@test ~] echo > catalog.rcv for archivelog in /u01/test/arch/* ; do echo "catalog archivelog '$archivelog';" >> catalog.rcv done

Now connect to rman promt and run .rcv using below command.

[oracle@test ~] Rman target / cmdfile=catalog.rcv

2.Cataloging consistent copy of datafile as an incremental backup:Here we are cataloging datafile copy data01.dbfas an incremental level 0 backup to RMAN repository,

This datafile copy must be consistent and can be either taken by OS copy commands or by RAMN backup as copy command:

CATALOG DATAFILECOPY ‘?/oracle/oradata/data01.dbf’ LEVEL 0;

3.Cataloging controlfile copy in the RMAN Repository tken by user:

a. Take backup of controle file by using below query:

SQL> alter database backup controlfile to ‘/u01/bkp/controlfile01.dbf’;

b. Catalog above controlfile copy to RMAN REPOSITORY using blow query:

RMAN> catalog controlfilecopy ‘oracle/oradata/control01.ctl’;

4.Cataloging all the database file copies present in one Directory:

Below example will catalog all the archivelog copied using OS command to directory /u01/bkp/archlog:

RMAN> CATALOG START WITH ‘/u01/bkp/archlog’;

5.Cataloging Files in the Flash Recovery Area:Below will catalog Flash Recovery Area without prompting to user:

CATALOG RECOVERY AREA NOPROMPT;

6. Cataloging RMAN Backup Pieces:BELOW WILL CATALOG a RMAN backup piece to RMAN Repository which is manually copied using OS commands to default backup location:

CATALOG BACKUPPIECE ‘/u01/bkp/rman/KOKTSV_ORCL_RMAN_ONL_ST’;

7.Below command will catalog the Backup Pieces present in the Location set for DB_RECOVERY_FILE_DEST Parameter:

RMAN> catalog db_recovery_file_dest;

Note:catalog backuppiece, catalog recovery area, catalog start with, catalog db_recovery_file_dest commands are not available in 9i.

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

Oracle Baseline/ SQL plan migration to its DEV/QA/Clone

In this article I have explained step by step how to copy/migrate SQL baseline from source database to target database.

On Source database

============ 

Check the list of SQL plan baselines on Source

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;


SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

Create the staging table to hold the SQL baseline

SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name > 'spm_staging_tab',
table_owner > 'BIASDBA',
tablespace_name > 'TOOLS');
END;


PL/SQL procedure successfully completed.

Check the baseline staging table

SQL> desc biasdba.spm_staging_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
VERSION NUMBER
SIGNATURE NUMBER
SQL_HANDLE VARCHAR2(30)
OBJ_NAME VARCHAR2(30)
OBJ_TYPE VARCHAR2(30)
PLAN_ID NUMBER
SQL_TEXT CLOB
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
DB_VERSION VARCHAR2(64)
CREATED TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
STATUS NUMBER
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
CATEGORY VARCHAR2(30)
SQLFLAGS NUMBER
TASK_ID NUMBER
TASK_EXEC_NAME VARCHAR2(30)
TASK_OBJ_ID NUMBER
TASK_FND_ID NUMBER
TASK_REC_ID NUMBER
INUSE_FEATURES NUMBER
PARSE_CPU_TIME NUMBER
PRIORITY NUMBER
OPTIMIZER_ENV RAW(2000)
BIND_DATA RAW(2000)
PARSING_SCHEMA_NAME VARCHAR2(30)
COMP_DATA CLOB

Now pack the baselines / load the baselines to the staging table created above

SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
SQL> 2 3 BEGIN
4 l_plans_packed : DBMS_SPM.pack_stgtab_baseline(
table_name > 'spm_staging_tab',
5 6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
9 10 /

Plans Packed: 3
PL/SQL procedure successfully completed.

Check the baseline table after loading

SQL> select sql_handle from BIASDBA.spm_staging_tab;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db


On target database
============

Copy required table to target database.

Create the DB link to the source database ( If the DBlink not an option then use export / import to copy the baseline staging table data to target).

SQL> create public database link efipfqa1 connect to biasdba identified by biasdba using 'efipfqa1';

Check the DB link is working or not

SQL> select SQL_HANDLE from biasdba.spm_staging_tab@efipfqa1;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db

Create the baseline staging table using the source database baseline staging table data

SQL> create table biasdba.spm_staging_tab as select * from biasdba.spm_staging_tab@efipfqa1;Table created.

Now Unpack / unload the baseline to the database dictionary
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_unpacked PLS_INTEGER;
3 BEGIN
l_plans_unpacked : DBMS_SPM.unpack_stgtab_baseline(
4 5 table_name > 'spm_staging_tab',
6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9 END;
10 /

Plans Unpacked: 3
PL/SQL procedure successfully completed.

Check the baselines created from the unload operation above

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!

Steps To Migrate a Database from Non-ASM to ASM

You can use these steps to migrate a NON-ASM database to ASM database in Oracle 11gR2.

Prerequisite - ASM instance should be created and up and running.

1) Check the database version and status

   SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;

    INSTANCE_NAME    VERSION           DATABASE_STATUS
    ---------------- ----------------- -----------------
    learndb          11.2.0.3.0        ACTIVE

2) We should be able to check the ASM disk available from the instance which we created earlier

   SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;


     NAME                           STATE      TOTAL_MB PATH
   ------------------------------ -------- ---------- ----------
   DISK1                          NORMAL         4777 ORCL:DISK1


Migrating the SPFILE from Non-asm to ASM


1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk

   RMAN>BACKUP AS BACKUPSET SPFILE;

2) Now shutdown the database and start it in Mount stage
   SQL> Shutdown immediate

   SQL> STARTUP MOUNT;

3) Now restore the spfile from the backup to ASM disk as below

  RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';

4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    spfile                               string      /opt/oracle/product/11.2.0/dbh
                                                        ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance

    SQL> shutdown immediate

6) Delete "spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora with the following line of content and start the instance,

    vi initlearndb.ora
   SPFILE='+DATA/spfilelearndb.ora'

   SQL> Startup (  first it will search for spfile<sid>.ora which we deleted and next it will look for      init<sid>.ora which we have moified with the above content  )

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   spfile                               string      +DATA/spfilelearndb.ora


Migrating Control files from Non-ASM to ASM


1) These are the current control files in non-asm

SQL>show parameter contol_files
         control_files                        string      /u01/data/learndb/control01.ctl,
                                                                 /u01/data/learndb/control02.ctl

2) Start the instance in nomount state

SQL> startup nomount

3) You can move these control files to ASM using RMAN

    [oracle@coltdb04 ~]$ rman target /

    Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: LEARNDB (not mounted)


    RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';


4) You can check whether the control file are created

   ASMCMD> find -t CONTROLFILE +DATA *
   WARNING:option 't' is deprecated for 'find'
   please use 'type'

   +DATA/LEARNDB/CONTROLFILE/current.256.787200593

5) Update the spfile with new control file location

   SQL> alter system set control_files='+DATA/LEARNDB/CONTROLFILE/current.256.787200593' scope=spfile;

6) Now shutdown the instance and startup in mount stage and check for the control file location
  
   SQL> shutdown immediate

   SQL> startup mount

   SQL> show parameter control_files

       NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
     control_files                        string      +DATA/learndb/controlfile/curr
                                                      ent.256.787200593


Migrating the Datafiles from Non-ASM to ASM

1) Keep the database is in mount stage

2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM

   [oracle@coltdb04 ~]$ rman target /

   Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012

   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

   connected to target database: LEARNDB (DBID=3704858930, not open)
                                            

   RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

3)  Switch the database to the copy created using the following RMAN command

   RMAN> SWITCH DATABASE TO COPY;

   datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
   datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
   datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
   datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"

4) Migrate the tempfile to ASM using RMAN

   RMAN> run
   2> {
   3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
   4> switch tempfile all;
   5> }

   executing command: SET NEWNAME

   using target database control file instead of recovery catalog

   renamed tempfile 1 to +DATA in control file

5) Now open the Database

   RMAN> ALTER DATABASE OPEN;

   database opened

6) You can now check the datafiles created in ASM
  
   SQL> select FILE_NAME from dba_data_files;

   FILE_NAME
   --------------------------------------------------------------------------------
   +DATA/learndb/datafile/users.261.787201725
   +DATA/learndb/datafile/undotbs1.259.787201713
   +DATA/learndb/datafile/sysaux.257.787201553
   +DATA/learndb/datafile/system.258.787201633


Migrating the Redo log files from Non-ASM to ASM

1) Identify the currently available redo log files using the following command

   SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP#           MEMBER                STATUS
   ----------       ---------              -----------
  
        3    /u01/data/learndb/redo03.log INACTIVE

        2    /u01/data/learndb/redo02.log CURRENT

        1    /u01/data/learndb/redo01.log INACTIVE

2) Add the new logfiles to ASM using following command

   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1; 
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.

   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';

4) You can check the logfiles created now in ASM

     GROUP#           MEMBER                STATUS
   ----------       ---------              -----------
         1    +DATA/learndb/onlinelog/group_1.264.787205393   CURRENT

         2    +DATA/learndb/onlinelog/group_2.265.787205405   INACTIVE

         3    +DATA/learndb/onlinelog/group_3.266.787205417   INACTIVE

Now your database is migrated to ASM

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!

Recovery of database using RMAN backup when system datafile is lost

DB Name: TESTDB

[oracle@test dbf]$   ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@test dbf]$  rm system01.dbf

SQL> select * from sys.aud$;
select * from sys.aud$
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Note: Since the normal shutdown is not possible in this case we shall use shut abort

SQL> shut abort
ORACLE instance shut down
[oracle@test dbf]$ sqlplus / as sysdba
SQL> startup mount


[oracle@test dbf]$ rman target/

RMAN> restore datafile 1;

Starting restore at 25-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/TESTDB/oradata/dbf/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck
channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-SEP-14


RMAN> recover datafile 1;

Starting recover at 25-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_32_859142042.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_33_859142042.arc
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc thread=1 sequence=29
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-SEP-14

sql>alter database open;

It is suggested to take RMAN backup immediately after the activity is over

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

Obtaining Execution Plan in Oracle

Obtaining Execution Plan in Oracle

What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.

Sample Execution Plan:

1. Access Method & Join type of data access.

2. Name of the objects on which operation is going to perform.

3. Cardinality: Estimated number of rows.

4. Bytes: How much byte of data needs to process for each operation.

5. Cost: Cost require to perform operation.

6. Time: Estimated time require to complete the operation.

Displaying Execution plan:

Below are the two mostly used methods to display an execution plan.

1.      EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.

2.      V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.

 DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.

• Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:

Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.

SQL> explain plan for select * from test1 a,test2 b where a.emp_no=b.emp_no;

Explained.

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 497311279

----------------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |     1 |    40 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN         |       |     1 |    40 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST1 |     1 |    20 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST2 |     1 |    20 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."EMP_NO"="B"."EMP_NO")

Note

-----

   - dynamic sampling used for this statement (level=2)

19 rows selected.

• Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:

Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  0kkhhb2w93cx0, child number 0

-------------------------------------

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

 ----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT      |                |       |       |     3 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    72 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)

• Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function

Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0kkhhb2w93cx0

--------------------

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

----------------------------------------------------------------------------------------

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT      |                |       |       |     2 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    71 |     2   (0)| 00:00:01 |

|   3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

19 rows selected.

Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.