Force the SQL to use a plan using SQL baseline

This is an example where we see how to force a particular execution plan.It is useful in the cases when there are multiple execution plans for the SQL and its not using optimal execution plan or we want to force a hint to be used to improve the execution plan but can't use it with the packaged application.

So here we go..Following are the steps that can be followed to test this out..



Step 1) Create the test table and load the data to create the test case.

SQL> create table shas ( id number  primary key,name varchar2(128) );

Table created.

SQL>
SQL> insert into shas select OBJECT_ID,OBJECT_NAME from dba_objects;
 72884 rows created.

SQL> commit;

Commit complete.

Step 2) For testing purpose I am going to use the following SQL which is doing an Index  unique scan and I want to force it to do a full table scan.

SQL>  select * from shas where id='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 1793678797

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

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

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

|   0 | SELECT STATEMENT            |              |     1 |    79 |     1   (0)
| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SHAS         |     1 |    79 |     1   (0)
| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0012285 |     1 |       |     1   (0)
| 00:00:01 |

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


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

   2 - access("ID"=75322)


Step 3) Suppose I want to force the full table scan hint(although not a very desired option in this case). I run the following hinted SQL

SQL> select /*+ FULL(shas) */ * from shas where id ='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    79 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SHAS |     1 |    79 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=75322)

Step 4) Get the SQL id from the v$sql_text.
 
SQL> select sql_text,sql_id from v$sql where sql_text like '%75322%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
 select * from shas where id='75322'
5zfadn00x105u

select /*+ FULL(shas) */ * from shas where id ='75322'
d8kdxq5baunx4


Step 6) Load the baseline doing Index unique scan from the cursor cache.

var res number ;
exec :res := dbms_spm.load_plans_from_cursor_cache(sql_id => '5zfadn00x105u', plan_hash_value => '1793678797' );

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
 select * from shas where id='75322'
SYS_SQL_9d09bd8936f5f4b0       SQL_PLAN_9u2dxj4vgbx5h230cac57 YES YES



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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d8kdxq5baunx4, child number 0
-------------------------------------
select /*+ FULL(shas) */ * from shas where id ='75322'

Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    95 (100)|          |
|*  1 |  TABLE ACCESS FULL| SHAS |   282 | 22278 |    95   (0)| 00:00:02 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

   1 - filter("ID"=75322)


18 rows selected.

Step 7) Force the SQL id to use the PHV 622179821(Full table scan)

var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => 'd8kdxq5baunx4', -
plan_hash_value => '622179821', -
sql_handle => 'SYS_SQL_9d09bd8936f5f4b0');

Step 8) Drop the original baseline with the "Index unique scan plan"

SQL> exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');
Enter value for original_sql_handle: SYS_SQL_9d09bd8936f5f4b0
Enter value for original_plan_name: SQL_PLAN_9u2dxj4vgbx5h230cac57

PL/SQL procedure successfully completed.
Step 7) Test that the baseline is being used and the SQL is doing a full table scan without a hint

SQL> set autotrace on explain
SQL> select * from shas where id ='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   282 | 22278 |    95   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| SHAS |   282 | 22278 |    95   (0)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=75322)

Note
-----
   - SQL plan baseline "SQL_PLAN_9u2dxj4vgbx5h55888a5b" used for this statement

Can you take the RMAN backup of catalog database using the same catalog database ??

This is very interesting question.Can you take the backup of rman catalog database by connecting to the same database.The document suggest that the catalog database is same as any other database and the restore and recovery operations are also the same .So I thought of running a test to confirm.

1) Create tablespace to keep the recovery catalog schema

SQL> create tablespace rman datafile  '/u01/app/oracle/oradata/fsdb/rman01.dbs' size 10m autoextend on ;

Tablespace created.

2) Create the recovery catalog schema

SQL> CREATE USER rman IDENTIFIED BY XXXXX  DEFAULT TABLESPACE rman TEMPORARY TABLESPACE temp  QUOTA UNLIMITED ON rman;

User created.

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

Grant succeeded.

3) Create catalog using rman command line

[oracle@localhost ~]$ rman catalog rman/password@fsdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 27 15:01:01 2014

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

4) Register the  same database to the catalog

[oracle@localhost ~]$ rman target / catalog rman/password@fsdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 27 15:02:59 2014

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

connected to target database: FSDB (DBID=1085674687)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

5)Run rman L0 backup to check

RMAN> backup incremental level 0 database;

Starting backup at 27-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

.

.

.

.

Finished backup at 27-AUG-14

Starting Control File and SPFILE Autobackup at 27-AUG-14

piece handle=/u01/app/oracle/flash_recovery_area/FSDB/autobackup/2014_08_27/o1_mf_s_856710457_9zwl4vls_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 27-AUG-14

Seems like its working but still I feel that we should backup the  control file and spfile seperately even though the spfile and control file autobackup is on.

Fine grained auditing (FGA) : Use AND,OR operators in the audit condition using funtions and avoid ORA-28138

The issue here is that we want to use the "and" operator  as the auditing condition to compare two values  of the columns with fine grained auditing or FGA. But If  I do something like the following.I get error ORA-28138.

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'C1=35 AND C2= 45',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/

 

SQL> insert into fg_test values('35','45');

insert into fg_test values('35','45')

            *

ERROR at line 1:

ORA-28138: Error in Policy Predicate

As per the oracle documentation you cannot use a complex condition in the FGA policies. See the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_fga.htm#CDEIECAG

"The audit_condition must be a Boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. The expression can also use functions, such as the USER or SYS_CONTEXT functions.

The expression must not combine conditions using operators such as AND and OR. audit_condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:"

So to fix the problem we need to create a function that returns a Boolean value .See the following test case.

   Environment:OEL 5 64 bit,DB 11.2.0.1 
1) Create Table

SQL> create table hr.fg_test(c1 number,c2 number);

Table created.

2) Create function

create or replace function hr.cond_fg(col1 number ,col2 number ) return number is
begin
if (col1 =35 and col2=45)
then
return 1;
else
return 0;
end if;
end;
/


3) Add Policy
exec DBMS_FGA.drop_policy ( -
       object_schema => 'hr', -
       object_name => 'fg_test', -
       policy_name => 'test_users_fga_policy');

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'hr.cond_fg(C1,C2)=1',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/


4)Enable Policy

 begin
   DBMS_FGA.ENABLE_POLICY (
   object_schema    =>  'HR',
   object_name      =>  'FG_TEST',
   policy_name      =>  'test_users_fga_policy',
   enable           =>   TRUE);
   end;
   /


SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL
from DBA_AUDIT_POLICIES ;  2    3

OBJECT_SCHEMA                  OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN                  ENA SEL INS UPD DEL
------------------------------ --- --- --- --- ---
HR                             FG_TEST
TEST_USERS_FGA_POLICY
hr.cond_fg(C1,C2)=1
C1                             YES YES YES YES YES

5) Run the test

As HR User
===========================================
SQL>  insert into fg_test values('&a','&b');
Enter value for a: 35
Enter value for b: 45
old   1:  insert into fg_test values('&a','&b')
new   1:  insert into fg_test values('35','45')

1 row created.

SQL> commit;

Commit complete.


As SYS User
==========================================================================================
SQL> select db_user,sql_text from dba_fga_audit_trail;

DB_USER
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
HR
 insert into fg_test values('35','45')
 



It seems to be working but as per the guidelines the function should not be too complex to avoid the performance issues.

Golden gate replication when the redo logs are in ASM

In order for the extract process to read from the redo logs located on ASM diskgroup you need to specify a connect string in the tnsnames.ora like the following.


ASM=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

To test the connection run a remote connection sqlplus session

[oracle@localhost admin]$ sqlplus sys@asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 21 12:59:43 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Automatic Storage Management option

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

+ASM             STARTED

In the extract parameter file make an entry like following.

TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD XXXXX

There is another option to read from ASM .Some versions support the following option.

TRANLOGOPTIONS DBLOGREADER

ORA-12705: Cannot access NLS data files or invalid environment specified

After upgrading CRS from 10.2.0.3 to 11.2.0.3 started seeing the following issue while starting the ASM or DB using the srvctl utilitty.ASM starts fine using sqlplus.


ORA-12705: Cannot access NLS data files or invalid environment specified

To resolve the issue we changed the following entries.


1)$GRID_HOME/crs/install/s_crsconfig__env.txt

Changed #NLS_LANG=CRS_LANGUAGE_ID

to following

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1



2)$GRID_HOME/crs/install/crsconfig_params, we commented

Changed

#LANGUAGE_ID=CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'

to following

LANGUAGE_ID=AMERICAN_AMERICA.WE8ISO8859P1
CRS_LANGUAGE_ID=AMERICAN_AMERICA.WE8ISO8859P1