Friday, December 21, 2012

Use Sqltxplain or DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to change execution plans

This demonstration is based on Metalink Note 1487302.1 "Using Sqltxplain to create a 'SQL Profile' to consistently reproduce a good plan".

First of all, we need to install the Sqltxplain tool. Go to Metalink Note 215187.1 "SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results", download sqlt.zip and install it under your ORACLE_HOME.

Let's test it. First we create 2 tables, based on DBA_OBJECTS and DBA_SEGMENTS and indexes on OBJECT_NAME and SEGMENT_NAME columns respectively.
sqlplus helpdesk

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 21 11:55:47 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE HELPDESK.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.

SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.

SQL> CREATE TABLE HELPDESK.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.

SQL> CREATE INDEX HELPDESK.SEG_NAME ON HELPDESK.SEGMENTS(SEGMENT_NAME);
Index created.

We will use a query that joins those tables using the indexed columns.
SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT A.OBJECT_ID,
         A.OBJECT_NAME,
         B.SEGMENT_TYPE,
         B.TABLESPACE_NAME
  FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 917395572

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     2 |   298 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |          |       |       |            |          |
|   2 |   NESTED LOOPS                |          |     2 |   298 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| OBJECTS  |     2 |   158 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | OBJ_NAME |     2 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | SEG_NAME |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | SEGMENTS |     1 |    70 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
       filter("A"."OBJECT_NAME" LIKE 'HELP%')
   5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
       filter("B"."SEGMENT_NAME" LIKE 'HELP%')

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


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     12  consistent gets
      0  physical reads
      0  redo size
    855  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

In this execution plan with plan hash value 917395572, both indexes are used.
Let's find out our query's SQL_ID. Run as SYSDBA:
SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT A.OBJECT_ID%';

SQL_IDSQL_TEXT
8qu7mmcwnygtvSELECT A.OBJECT_ID,    A.OBJECT_NAME,    B.SEGMENT_TYPE,    B.TABLESPACE_NAME   FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B  WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%'

Now, we will drop index HELPDESK.OBJ_NAME, in order to get a new execution plan for our query, where a full table scan will be used on table HELPDESK.OBJECTS.
SQL> DROP INDEX HELPDESK.OBJ_NAME;
Index dropped.

SQL> SELECT A.OBJECT_ID,
         A.OBJECT_NAME,
         B.SEGMENT_TYPE,
         B.TABLESPACE_NAME
  FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     2 |   298 |    83   (2)| 00:00:01 |
|*  1 |  HASH JOIN                   |          |     2 |   298 |    83   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SEGMENTS |     2 |   140 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SEG_NAME |     2 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | OBJECTS  |     3 |   237 |    79   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
   3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
       filter("B"."SEGMENT_NAME" LIKE 'HELP%')
   4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')

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


Statistics
----------------------------------------------------------
     54  recursive calls
      0  db block gets
    424  consistent gets
      0  physical reads
      0  redo size
    855  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      4  sorts (memory)
      0  sorts (disk)
      2  rows processed

A new plan was created with a full table scan on HELPDESK.OBJECTS and plan hash value 2874684176.
We recreate the index and we check the old plan is being used again.
SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.

SQL> SELECT A.OBJECT_ID,
         A.OBJECT_NAME,
         B.SEGMENT_TYPE,
         B.TABLESPACE_NAME
  FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 917395572

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     2 |   298 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |          |       |       |            |          |
|   2 |   NESTED LOOPS                |          |     2 |   298 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| OBJECTS  |     2 |   158 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | OBJ_NAME |     2 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | SEG_NAME |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | SEGMENTS |     1 |    70 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
       filter("A"."OBJECT_NAME" LIKE 'HELP%')
   5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
       filter("B"."SEGMENT_NAME" LIKE 'HELP%')

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


Statistics
----------------------------------------------------------
     15  recursive calls
      0  db block gets
    139  consistent gets
      1  physical reads
      0  redo size
    855  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

Now, we have 2 execution plans for our query.
We create an AWR snapshot as SYSDBA and we will use a query from Document 1487302.1 to compare those execution plans.
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.

WITH
P AS (
SELECT PLAN_HASH_VALUE
  FROM GV$SQL_PLAN
 WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
   AND OTHER_XML IS NOT NULL
 UNION
SELECT PLAN_HASH_VALUE
  FROM DBA_HIST_SQL_PLAN
 WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
   AND OTHER_XML IS NOT NULL ),
M AS (
SELECT PLAN_HASH_VALUE,
       SUM(ELAPSED_TIME)/SUM(EXECUTIONS) AVG_ET_SECS
  FROM GV$SQL
 WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
   AND EXECUTIONS > 0
 GROUP BY
       PLAN_HASH_VALUE ),
A AS (
SELECT PLAN_HASH_VALUE,
       SUM(ELAPSED_TIME_TOTAL)/SUM(EXECUTIONS_TOTAL) AVG_ET_SECS
  FROM DBA_HIST_SQLSTAT
 WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
   AND EXECUTIONS_TOTAL > 0
 GROUP BY
       PLAN_HASH_VALUE )
SELECT P.PLAN_HASH_VALUE,
       ROUND(NVL(M.AVG_ET_SECS, A.AVG_ET_SECS)/1E6, 3) AVG_ET_SECS
  FROM P, M, A
 WHERE P.PLAN_HASH_VALUE = M.PLAN_HASH_VALUE(+)
   AND P.PLAN_HASH_VALUE = A.PLAN_HASH_VALUE(+)
 ORDER BY
       AVG_ET_SECS NULLS LAST;

PLAN_HASH_VALUEAVG_ET_SECS
9173955720,008
28746841760,019

Obviously, the execution plan with the 2 index range scans (917395572) is faster, but for the sake of our scenario we want to force plan 2874684176 to be used, when both indexes exist.
We will use Sqltxplain's coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE] as SYSDBA.
SQL> @$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile.sql '8qu7mmcwnygtv' '2874684176';

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      917395572        .008
     2874684176        .019

Parameter 2:
PLAN_HASH_VALUE (required)


Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID           : "8qu7mmcwnygtv"
PLAN_HASH_VALUE: "2874684176"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3       RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
on TARGET system in order to create a custom SQL Profile
with plan 2874684176 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

Now, we need to execute as SYSDBA the generated coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql.
SQL>@$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql 11.4.4.4 2012/12/21 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID 8qu7mmcwnygtv based on plan hash
SQL>REM   value 2874684176.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM     EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_8qu7mmcwnygtv_2874684176');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM     for the Oracle Tuning Pack.
SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM     Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM     By doing so you can create a custom SQL Profile for the original
SQL>REM     SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h         SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[SELECT A.OBJECT_ID,
 15                A.OBJECT_NAME,
 16                B.SEGMENT_TYPE,
 17                B.TA]');
 18  wa(q'[BLESPACE_NAME
 19    FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 20   WH]');
 21  wa(q'[ERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP]');
 22  wa(q'[%']');
 23  DBMS_LOB.CLOSE(sql_txt);
 24  h := SYS.SQLPROF_ATTR(
 25  q'[BEGIN_OUTLINE_DATA]',
 26  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 27  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
 28  q'[DB_VERSION('11.2.0.3')]',
 29  q'[ALL_ROWS]',
 30  q'[OUTLINE_LEAF(@"SEL$1")]',
 31  q'[INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("SEGMENTS"."SEGMENT_NAME"))]',
 32  q'[FULL(@"SEL$1" "A"@"SEL$1")]',
 33  q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]',
 34  q'[USE_HASH(@"SEL$1" "A"@"SEL$1")]',
 35  q'[END_OUTLINE_DATA]');
 36  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 37  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 38  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 39  sql_text     => sql_txt,
 40  profile     => h,
 41  name     => 'coe_8qu7mmcwnygtv_2874684176',
 42  description => 'coe 8qu7mmcwnygtv 2874684176 '||:signature||' '||:signaturef||'',
 43  category     => 'DEFAULT',
 44  validate     => TRUE,
 45  replace     => TRUE,
 46  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 47  DBMS_LOB.FREETEMPORARY(sql_txt);
 48  END;
 49  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

        SIGNATURE
---------------------
  5858216496569750688


       SIGNATUREF
---------------------
  1896484766659468535


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_8qu7mmcwnygtv_2874684176 completed

Now, the plan  2874684176 will be used, every time our query is run.
SQL> SELECT A.OBJECT_ID,
         A.OBJECT_NAME,
         B.SEGMENT_TYPE,
         B.TABLESPACE_NAME
  FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%'; 
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   436 | 64964 |    98   (2)| 00:00:02 |
|*  1 |  HASH JOIN                   |          |   436 | 64964 |    98   (2)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SEGMENTS |   417 | 29190 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SEG_NAME |    75 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | OBJECTS  |  1176 | 92904 |    79   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
   3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
       filter("B"."SEGMENT_NAME" LIKE 'HELP%')
   4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')

Note
-----
   - SQL profile "coe_8qu7mmcwnygtv_2874684176" used for this statement <-- This is the SQL profile created by coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql


Statistics
----------------------------------------------------------
      6  recursive calls
      0  db block gets
    288  consistent gets
      1  physical reads
      0  redo size
    855  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

In 11g databases, it's recommended to use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function, instead:
SQL> SET SERVEROUTPUT ON
VAR N NUMBER
BEGIN
:N:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'8qu7mmcwnygtv', PLAN_HASH_VALUE=>2874684176, FIXED =>'NO', ENABLED=>'YES');
DBMS_OUTPUT.PUT_LINE('Number of plans loaded: '||:N);
END;
/
Number of plans loaded: 1

PL/SQL procedure successfully completed.

SQL> SELECT A.OBJECT_ID,
         A.OBJECT_NAME,
         B.SEGMENT_TYPE,
         B.TABLESPACE_NAME
  FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
 WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%'; 
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   436 | 64964 |    98   (2)| 00:00:02 |
|*  1 |  HASH JOIN                   |          |   436 | 64964 |    98   (2)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SEGMENTS |   417 | 29190 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SEG_NAME |    75 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | OBJECTS  |  1176 | 92904 |    79   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
   3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
       filter("B"."SEGMENT_NAME" LIKE 'HELP%')
   4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')

Note
-----
   - SQL plan baseline "SQL_PLAN_52m4hvq7pz550a442f8ad" used for this statement <-- This is the SQL plan baseline we have just created

Statistics
----------------------------------------------------------
      6  recursive calls
      0  db block gets
    288  consistent gets
      1  physical reads
      0  redo size
    855  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

Thursday, December 20, 2012

RAC: How data blocks are loaded into the buffer cache

In this scenario, we will demonstrate how buffer cache is populated by data blocks in a RAC environment.
The following information was copied by Oracle9i Real Application Clusters Concepts Guide and describes the states of a block, when loaded into the buffer cache. It's strange, this information was removed from 10g/11g documentation:

Block Access Modes and Buffer States
An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance.
The buffer state of a block relates to the access mode of the block.
For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.
To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view.
The V$BH view provides information about the block access mode and their buffer state names as follows:

* With a block access mode of NULL the buffer state name is CR:
An instance can perform a consistent read of the block.
That is, if the instance holds an older version of the data.
* With a block access mode of S the buffer state name is SCUR:
An instance has shared access to the block and can only perform reads.
* With a block access mode of X the buffer state name is XCUR:
An instance has exclusive access to the block and can modify it.
* With a block access mode of NULL the buffer state name is PI:
An instance has made changes to the block but retains copies of it as past images to record its state before changes.

Only the SCUR and PI buffer states are Real Application Clusters-specific.
There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time.
To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.
For example, if another instance requests read access to the most current version of the same block,
then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance
and keeps a PI buffer if the buffer contained a dirty block.
At this point, the first instance has the current block and the requesting instance also has the current block in shared mode.
Therefore, the role of the resource becomes global.
There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.

First, we create a new LMT/ASSM tablespace:
SQL> CREATE TABLESPACE HELPDESK2 DATAFILE 
  '/oradb/helpdesk2_01.dbf' SIZE 5M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Tablespace created.

SQL> ALTER USER HELPDESK QUOTA UNLIMITED ON HELPDESK2;
User altered.

We connect to Instance 1 and create a new table:
1> CREATE TABLE HELPDESK.TESTTBL (ID NUMBER, TEXT VARCHAR2(100)) TABLESPACE HELPDESK2;
Table created.

We query DBA_EXTENTS:
SELECT *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME='TESTTBL'
AND OWNER='HELPDESK';
   
OWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPETABLESPACE_NAMEEXTENT_IDFILE_IDBLOCK_IDBYTESBLOCKSRELATIVE_FNO
HELPDESKTESTTBLTABLEHELPDESK2024839655368437

LMT/ASSM tablespaces have a minimum extent size of 64K, that's why table's initial size is 64K.
Since tablespace's block size is 8K, table's initial extent occupies 8 blocks and since the starting block's ID is 9, it occupies blocks 9 to 16.
Are all 8 blocks free?
Let's find out:
SQL> SET SERVEROUTPUT ON

DECLARE
        V_OWNER  DBA_SEGMENTS.OWNER%TYPE;
        V_NAME   DBA_SEGMENTS.SEGMENT_NAME%TYPE;
        V_TYPE   DBA_SEGMENTS.SEGMENT_TYPE%TYPE;
        V_TBL    NUMBER;
        V_TBB    NUMBER;
        V_UBL    NUMBER;
        V_UBB    NUMBER;
        V_LUFID  NUMBER;
        V_LUBID  NUMBER;
        V_LUB    NUMBER;
BEGIN
        V_OWNER := 'HELPDESK';
        V_NAME := 'TESTTBL';
        V_TYPE := 'TABLE';
        DBMS_SPACE.UNUSED_SPACE (V_OWNER, V_NAME, V_TYPE, V_TBL, V_TBB, V_UBL, V_UBB, V_LUFID, V_LUBID, V_LUB);
        DBMS_OUTPUT.PUT_LINE ('OWNER: ' || V_OWNER);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT NAME: ' || V_NAME);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT TYPE: ' || V_TYPE);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BLOCKS: ' || V_TBL);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BYTES: ' || V_TBB);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BLOCKS: ' || V_UBL);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BYTES: ' || V_UBB);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT FILE ID: ' || V_LUFID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT BLOCK ID: ' || V_LUBID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED BLOCK: ' || V_LUB);
END;
/
 
OWNER: HELPDESK
SEGMENT NAME: TESTTBL
SEGMENT TYPE: TABLE
TOTAL BLOCKS: 8
TOTAL BYTES: 65536
UNUSED BLOCKS: 5
UNUSED BYTES: 40960
LAST USED EXTENT FILE ID: 2483
LAST USED EXTENT BLOCK ID: 9
LAST USED BLOCK: 3

PL/SQL procedure successfully completed.

No, only 5 blocks are unused.
Now, let's see what was loaded into the buffer caches, when we created the table:
SELECT A.INST_ID,
         C.TABLESPACE_NAME,
         C.FILE_NAME,
         A.BLOCK#,
         B.OWNER,
         B.OBJECT_NAME,
         B.SUBOBJECT_NAME,
         B.OBJECT_TYPE,
         DECODE (A.CLASS#,
                    1, 'data block',
                    2, 'sort block',
                    3, 'save undo block',
                    4, 'segment header',
                    5, 'save undo header',
                    6, 'free list',
                    7, 'extent map',
                    8, '1st level bmb',
                    9, '2nd level bmb',
                    10, '3rd level bmb',
                    11, 'bitmap block',
                    12, 'bitmap index block',
                    13, 'file header block',
                    14, 'unused',
                    15, 'system undo header',
                    16, 'system undo block',
                    17, 'undo header',
                    18, 'undo block',
                    A.CLASS#)
             CLASS,
         DECODE (A.STATUS,
                    'free', 'Not currently in use',
                    'xcur', 'Exclusive',
                    'scur', 'Shared current',
                    'cr', 'Consistent read',
                    'read', 'Being read from disk',
                    'mrec', 'In media recovery mode',
                    'irec', 'In instance recovery mode',
                    A.STATUS)
             STATUS
  FROM GV$BH A, DBA_OBJECTS B, DBA_DATA_FILES C
 WHERE A.OBJD = B.DATA_OBJECT_ID
AND A.FILE# = C.FILE_ID
AND B.OWNER='HELPDESK'
AND B.OBJECT_NAME='TESTTBL'
ORDER BY 4, 1;
   
INST_IDTABLESPACE_NAMEFILE_NAMEBLOCK#OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPECLASSSTATUS
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerExclusive

So, TESTTBL's blocks 9 (1st level bitmap block), 10 (2nd level bitmap block) and 11(segment header) were loaded into Instance 1's buffer cache.
These are the 3 used blocks. Let's take a segment header's (11) dump to verify it:
SQL> EXEC DBMS_SPACE_ADMIN.SEGMENT_DUMP('HELPDESK2','437','11');

PL/SQL procedure successfully completed.

We read in the generated trace file:
Segment Dump: segment dba = 644 : 0x6d40000b <--Block 11
Segment Type - Pagetable Segment
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x6d40000c <-- Highwater Mark at block 12   ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x6d400009
  Level 1 BMB for Low HWM block: 0x6d400009
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001438
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x6d40000a
  Last Level 1 BMB:  0x6d400009 <-- Block 9
  Last Level II BMB:  0x6d40000a <-- Block 10
  Last Level III BMB:  0x00000000 <-- There is no 3rd level bitmap block
     Map Header:: next  0x00000000  #extents: 1    obj#: 1667683 flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x6d400009  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x6d400009 Data dba:  0x6d40000c
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x6d40000a

Dump of Second Level Bitmap Block
   number: 1       nfree: 1       ffree: 0      pdba:     0x6d40000b
   Inc #: 0 Objd: 1667683 <-- TESTTBL's data_object_id
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x6d400009  Free: 5 Inst: 1

  --------------------------------------------------------
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x6d40000a   poffset: 0
   unformatted: 5       total: 8         first useful block: 3 <-- 8 blocks total, 5 blocks unused
   owning instance : 1 <-- Instance 1 has the ownership
   instance ownership changed at
   Last successful Search
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 3
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 1667683
  HWM Flag: HWM Set
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x6d400009  Length: 8      Offset: 0

   0:Metadata   1:Metadata   2:Metadata   3:unformatted
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
  --------------------------------------------------------

We insert 1 row from Instance 1:
1> INSERT INTO HELPDESK.TESTTBL VALUES(1, 'TEXT1');

1 row created.

All TESTTBL's blocks were loaded into Instance 1's buffer cache:   
INST_IDTABLESPACE_NAMEFILE_NAMEBLOCK#OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPECLASSSTATUS
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbShared current
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerConsistent read
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf13HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf14HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf15HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf16HELPDESKTESTTBLTABLEdata blockExclusive

We commit:
1> COMMIT;
Commit complete.

Let's find out the block in which this row was stored:
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(A.ROWID) BLOCK#, A.*
FROM HELPDESK.TESTTBL A;
   
BLOCK#IDTEXT
161TEXT1

The block where the row was stored (16) remained in exclusive (xcur) state and all other data blocks changed their state to shared current (scur):   
INST_IDTABLESPACE_NAMEFILE_NAMEBLOCK#OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPECLASSSTATUS
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbShared current
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerConsistent read
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerShared current
1HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockShared current
1HELPDESK2/oradb/helpdesk2_01.dbf13HELPDESKTESTTBLTABLEdata blockShared current
1HELPDESK2/oradb/helpdesk2_01.dbf14HELPDESKTESTTBLTABLEdata blockShared current
1HELPDESK2/oradb/helpdesk2_01.dbf15HELPDESKTESTTBLTABLEdata blockShared current
1HELPDESK2/oradb/helpdesk2_01.dbf16HELPDESKTESTTBLTABLEdata blockExclusive

We insert one more row from Intance 2, this time, and we commit:
2> INSERT INTO HELPDESK.TESTTBL VALUES(2, 'TEXT2');
1 row created.

2> commit;
Commit complete.

This row was placed in block 12:
BLOCK#IDTEXT
122TEXT2
161TEXT1

Instance 1 kept an image of 1st level bmb (pi), Instance 2 got exclusive access (xcur) to 1st level bmb and 2nd level bmb, read (cr) the segment header and got exclusive access (xcur) to block 12, where the new row was stored:   
INST_IDTABLESPACE_NAMEFILE_NAMEBLOCK#OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPECLASSSTATUS
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbpi
2HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbExclusive
2HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbConsistent read
2HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbConsistent read
2HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerShared current
2HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerConsistent read
2HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerShared current
2HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf16HELPDESKTESTTBLTABLEdata blockExclusive

We, now, delete the 2nd row from Instance 1:
1> DELETE FROM HELPDESK.TESTTBL WHERE ID=2;
1 row deleted.

1> COMMIT;
Commit complete.

Instance 1 got block 12 in exclusive (xcur) state:   
INST_IDTABLESPACE_NAMEFILE_NAMEBLOCK#OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPECLASSSTATUS
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbpi
1HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbExclusive
2HELPDESK2/oradb/helpdesk2_01.dbf9HELPDESKTESTTBLTABLE1st level bmbpi
1HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbpi
2HELPDESK2/oradb/helpdesk2_01.dbf10HELPDESKTESTTBLTABLE2nd level bmbExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerShared current
1HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerConsistent read
2HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerShared current
2HELPDESK2/oradb/helpdesk2_01.dbf11HELPDESKTESTTBLTABLEsegment headerConsistent read
1HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockExclusive
1HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockConsistent read
1HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockConsistent read
2HELPDESK2/oradb/helpdesk2_01.dbf12HELPDESKTESTTBLTABLEdata blockpi
1HELPDESK2/oradb/helpdesk2_01.dbf16HELPDESKTESTTBLTABLEdata blockExclusive

Wednesday, December 5, 2012

OMS 11g: Copy agent binaries to another server and reconfigure

System admins cloned a server, including OMS agent's binaries, and I did not want to reinstall it.
At start, agent should be down.
Edit $OMS_HOME/sysman/config/emd.properties and change EMD_URL entry to your new server's hostname:
EMD_URL=http://newserver:3872/emd/main/

Delete a few files:
cd $OMS_HOME/sysman/emd
rm -rf state
rm -rf upload
rm lastupld.xml
rm agntstmp.txt
rm protocol.ini
rm blackouts.xml
rm targets.xml

Create a new targets.xml:
<?xml version = "1.0"?>
<Targets>
</Targets>

Start agent:
emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ...... started.

Upload:
emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Get its status and check "Agent URL" is pointing to new server and if upload was successful:
emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /oragrid/agent11g
Agent binaries    : /oragrid/agent11g
Agent Process ID  : 8061080
Parent Process ID : 5701676
Agent URL         : http://newserver:3872/emd/main/
Repository URL    : http://OMSserver:4889/em/upload/
Started at        : 2012-12-05 13:01:57
Started by user   : oragrid
Last Reload       : 2012-12-05 13:03:06
Last successful upload                       : 2012-12-05 13:03:09
Total Megabytes of XML files uploaded so far :     3.09
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    44.41%
Last successful heartbeat to OMS             : 2012-12-05 13:03:00
---------------------------------------------------------------
Agent is Running and Ready

Now, if you login to your OMS, you will see your new agent running, but it will not monitor the host installed in. OMS will not even know in which host this agent runs.

Stop agent:
emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

Edit $OMS_HOME/sysman/emd/targets.xml and add the host target:
<Targets AGENT_TOKEN="23e6c87aa252cb56f70800785be11d6071944de7">
        <Target TYPE="oracle_emd" NAME="newserver:3872"/>
        <Target TYPE="host" NAME="newserver"/>
</Targets>

Start, upload and status once more:
emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ..... started.

emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /oragrid/agent11g
Agent binaries    : /oragrid/agent11g
Agent Process ID  : 5308612
Parent Process ID : 9633920
Agent URL         : http://newserver:3872/emd/main/
Repository URL    : http://OMSserver:4889/em/upload/
Started at        : 2012-12-05 14:04:03
Started by user   : oragrid
Last Reload       : 2012-12-05 14:04:03
Last successful upload                       : 2012-12-05 14:04:15
Total Megabytes of XML files uploaded so far :     0.51
Number of XML files pending upload           :        1
Size of XML files pending upload(MB)         :     1.15
Available disk space on upload filesystem    :    44.40%
Last successful heartbeat to OMS             : 2012-12-05 14:04:05
---------------------------------------------------------------
Agent is Running and Ready

Now, login to your OMS add under Targets tab, All Targets subtab search for type Agent and your new server's hostname.
You will find your new agent with only one monitoring target, your host.