Drop the SQL PLAN BASELINE

Drop the SQL_BASELINES

This post explains to Drop the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select  sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

Now we have the package available in oracle to drop the SQL PLAN BASELINE -

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => '&SQL_HANDLE_NAME',  
 plan_name => '&SQL_BASELINE_PLAN_NAME'); 
 dbms_output.put_line(drop_result);    
 end; 
/

Fetch the SQL BASELINE evolve Report without actually change the ACCEPTED flag

In this blog we will explain the details of how to fetch the SQL BASELINE Report.

first of the the method we use for SQL PLAN BASELINE is the EVOLVE and the feature name used in Oracle is SQL PLAN MANAGER.  Oracle has provided the DBMS package for this is SPM. 

Now in the SPM Package ,we evolve the SQL PLAN BASELINE manually. Once we run the evolve this will evaluate the benefit of SQL PLAN BASELINE plans and accept them. Once we do not need to make changes and only to fetch the report of the SQLPLAB BASELINE, the cause COMMIT=NO is used. 

Sample example - 

set lines 200 pages 20000

set serveroutput on
  declare evolve_out CLOB;
  begin
  evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', 

  COMMIT => 'NO' );
  dbms_output.put_line(evolve_out);
  end; 

/

Explain plan for SQL_BASELINES using DBMS package in ORACLE

Explain plan for SQL_BASELINES

This post explains to find out the explain plan used by the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select profile, sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

If you have only SQLPLAN_HANDLE name -


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE')); 

If you want to check the explain plan of specific SQL_PLAN NAME, then you can add the second argument in the above command.


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE','&PLAN_NAME');

Recover data of table from FLASHBACK

In this post , we are going to explain how can you recover the data of a table from the past timestamp with FLASHBACK on

Top "ForeGround Wait Events" from AWR history

This post describe the SQL to fetch "Top 5 Fore-Ground Wait Events" from history of Oracle database which can be used to analyze the performance of database while a defined time interval.

Top SQL by "Physical Reads" from AWR history

This post describe the SQL to fetch TOP SQL by Physical Reads from Historical per AWR Snapshots from history of Oracle database which can be used to analyse the performance of database while a defined time interval.

Top SQL by "Elapsed Time" reads from AWR history

This post describe the SQL to fetch "TOP SQL by Elapsed Time" from history of Oracle database which can be used to analyse the performance of database while a defined time interval.

Database Archive Generation report

Segmentation Fault During Duplicate From Active Database

Scope:
This post explains the solution to resolve the SEGMENT FAULT error message while Duplicate database from source using RMAN Duplicate method.

Symptoms:
channel ORA_DISK_1: starting datafile copy
copying current control file
Oracle instance started

Drop PDB (Pluggable database) from RAC CDB (Container Database) in Oracle 12c

Scope:
This post explains the easy steps to remove the Plugabble database from RAC Container database.

How to restart Oracle 12c Database with Pluggable Database (PDBs)

Purpose: This post explains the method to shutdown and restart the Oracle 12c Database with PDB

Steps:

1. Connect to root container CDB$root
2. Stop (all/one by one) pluggable database/s

Apply PSU patch in RAC database with physical standby database in place

Scope:

This post explains steps to "Apply PSU patch" on database with Physical standby.

INSTALL TIMESTEN IN WINDOWS:

Scope:

This post explains the steps to install "Times Ten" on windows.

Install TimesTen on Windows

How to find bind variable name and value of a SQL Id

Scope:

Some times it is required by a DBA to find explain plan of a query which might be responsible for bad performance of your database.

Top SQL by Logical reads from AWR history

Scope: 

This blog shared method to fetch TOP sqls from history of Oracle database which can be used to analyse the performance of database while a defined time interval.

ORA-01110: data file 1 while duplicate database from physical standby

Scope: While duplicating database from standby database, you might be getting below error message as recovery is required for datafile 1. 

ORA 2020 too many database links in use

Scope: While selecting data with DBLink (Database Link) produces error  ORA-02020 instead providing output from source database.

Duplicate database from Physical standby database as target

How to enable a OSuser to connect with sqlplus / without login as SYSDBA

HOW TO RENAME DIRECTORY IN ASM