SQL Profile – create manually

Symptom

Sql execution is too long

Cause

After automatic statistics collection on table, the execution plan changed. It is no more picking the index and doing full table scan.

solution

Step 1. Find the SQL ID that needs a profile,

               in this example: 50ux45v27k6ab

Step 2. Find the hint that introduces a good plan

               In this example: INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)

Step 3. Run following anonymous PLQSL block

DECLARE

cl_sql_text CLOB; 

BEGIN

SELECT sql_text  

INTO cl_sql_text  

FROM gv$sqlarea where sql_id = '50ux45v27k6ab' and rownum = 1; 

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text,  

profile => sqlprof_attr(‘INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)'),  

name => 'USE_PK_FOR_UPDATE',  

category => 'DEFAULT', 

force_match => TRUE); 

end; 

/

Hope this helps! Rupam

Flashback Cheatsheet

Enabling/disabling Logging for Flashback Database

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE FLASHBACK OFF;

Estimating Disk Space Requirements for Flashback Database Logs

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

By default, flashback logs are generated for all permanent tablespaces.

SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Backup database

RMAN> backup database plus archivelog;

Backup flash recovery area

RMAN> backup recovery area;

Flashback usage

sql> select * from v$recovery_file_dest;

sql> select * from v$flash_recovery_area_usage;

Create Restore Point

sql> create restore point rp01;

sql> select name from v$restore_point;

sql> drop restore point rp01;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

-- Determining the Current Window for Flashback Database

sql> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Restore to Restore Point using RMAN

RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;

RMAN> LIST RESTORE POINT ALL;

RMAN> LIST RESTORE POINT  RP03;

RMAN> flashback database  to  restore point rp01;

RMAN> alter database open resetlogs;

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; # VERIFY CHANGES

Restore to Restore Point using SQL

sql> shutdown immediate

sql> startup mount

sql> flashback database  to  restore point rp01;

sql> alter database open resetlogs;

Options After Flashback Database to the Wrong Time

RMAN> FLASHBACK DATABASE TO SCN 42963;  #earlier than current SCN

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

RMAN>  RECOVER DATABASE;

Performing Flashback Database to Undo an OPEN RESETLOGS

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

Flashback Database To The Right of Open Resetlogs

sql> select oldest_flashback_scn from v$flashback_database_log;

SQL> select prior_incarnation# from v$database_incarnation where status = 'CURRENT';

RMAN> LIST INCARNATION OF DATABASE trgt;

RMAN> RESET DATABASE TO INCARNATION 1;

RMAN> FLASHBACK DATABASE TO SCN 1500;

Flashback Table

   -- Prerequisites for Using Flashback Table

sql> ALTER TABLE table ENABLE ROW MOVEMENT;

sql> SELECT, INSERT, DELETE, and ALTER privileges on the table.

 Performing Flashback Table

sql> FLASHBACK TABLE EMP TO SCN 123456;

sql> FLASHBACK TABLE EMP TO TIMESTAMP

      TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Viewing, Querying and flashback  Objects in the Recycle Bin

SQL> show recyclebin;

SQL> SELECT object_name as recycle_name, original_name, type

     FROM recyclebin;

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

OR

sql> FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP

     RENAME TO hr.int2_admin_emp;

Hope this helps! Regards Rupam