MIGRATE OUTLINES TO SQL MANAGEMENT BASE

                      MIGRATE OUTLINES TO SQL MANAGEMENT BASE

In this post, we will learn about outlines and more importantly in 11g Release 2 sometimes we need to migrate outlines from older versions to 11G... so thats what we will see .
_____________________________________________________-

Outlines can be created automatically by Oracle or manually for specific statements. The automatic creation of outlines is controlled using the create_stored_outlines parameter than can be set at session or instance level using the following commands.

ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;

Outlines can be generated for specific statements using the CREATE OUTLINE statement or the DBMS_OUTLN.CREATE_OUTLINE procedure and also we need to give privileges to the user who is doing this if he is not a DBA. The privileges are CREATE ANY OUTLINE and EXECUTE_CATALOG_ROLE .

Now let me show you a small demo

SQL> create outline outline_karan for category sales on select * from emp;
SQL> SELECT name, category, sql_text FROM user_outlines WHERE category = 'SALES';


Note that outline stores the information in the form of hints





SQL> SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'OUTLINE_KARAN'

      NODE      STAGE   JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------------------------------------
         1          1          1 FULL(@"SEL$1" "EMP"@"SEL$1")
         1          1          0 OUTLINE_LEAF(@"SEL$1")
         1          1          0 ALL_ROWS
         1          1          0 DB_VERSION('11.2.0.1')
         1          1          0 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
         1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

## Now you see below the outline is unused because the query associated with outline is not fired.
]
SQL>  SELECT name, category, used FROM user_outlines where name='OUTLINE_KARAN';

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
OUTLINE_KARAN                  SALES                          UNUSED

###Lets fire the query see

SQL> select * from emp;

## I have removed the 14 rows of the output to reduce the size of demo

### Note that outline is used when the query is executed (USED = USED)

SQL>  SELECT name, category, used FROM user_outlines where name='OUTLINE_KARAN';

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
OUTLINE_KARAN                  SALES                          USED

## Now we will migrate information in the outlilne to SQL management base

-- Fine out the names of the outlines --



SQL> select name,sql_text from user_outlines ;

-- Migrate the stored outline

SQL>variable v1 clob;

SQL> begin
:v1 := dbms_spm.migrate_stored_outline(
attribute_name=>'outline_name',
attribute_value=>'OUTLINE_KARAN',fixed=>'NO');
end;
/

### NOTE := I had already give extra special privileges to scott who is doing this work, the privileges are CREATE ANY OUTLINE, ALTER ANY OUTLINE
ADMINISTER SQL MANAGEMENT OBJECT

###Now how can we determine whether stored outline was migrated or not.. See the following
SQL> select name,sql_text,migrated FROM user_outlines where name='OUTLINE_KARAN';

NAME                           SQL_TEXT                                                                         MIGRATED
------------------------------ -------------------------------------------------------------------------------- ------------
OUTLINE_KARAN                  select * from emp                                                                MIGRATED

## Login as SYSDBA and see the below dictionary query to check that outline has been stored
     as baseline in SQL Management Base
SQL> select SQL_HANDLE,ORIGIN,ENABLED,ACCEPTED from dba_sql_plan_baselines where SQL_TEXT like 'select * from em%';

SQL_HANDLE                     ORIGIN         ENA ACC
------------------------------ -------------- --- ---
SYS_SQL_612b4fadc881121e       STORED-OUTLINE YES YES

#### Some more methods to show you how to create and work on outlines, but first lets say i deleted my previous outline

SQL> drop outline OUTLINE_KARAN;
drop outline OUTLINE_KARAN
*
ERROR at line 1:
ORA-18006: DROP ANY OUTLINE privilege is required for this operation

SQL> conn / as sysdba
Connected.
SQL> grant DROP ANY OUTLINE to scott;

SQL> conn scott/tiger

SQL> drop outline OUTLINE_KARAN;



-- Create a outline by setting create_stored_outlines=true at instance level --

SQL> conn / as sysdba

SQL> ALTER SYSTEM SET create_stored_outlines=TRUE;

System altered.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> ALTER SYSTEM SET create_stored_outlines=FALSE;

-- Check that outline has been created --

SQL>  select name,owner,SQL_TEXT from  dba_outlines;

NAME                           OWNER                          SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_12092715271822003  SYS                            select * from scott.dept



-- Create outline by using DBMS_OUTLN.create_outline procedure 

  
-- Issue the statement for which outline is to be stored
 
SQL> conn scott/tiger

SQL>select dname,loc from dept where deptno=10;

SQL> conn / as sysdba
Connected.

-- Find out sql_id of the statement --

SQL> select sql_id,HASH_VALUE,CHILD_NUMBER from v$sql where sql_text like 'select dname,loc from dept where dept%';

SQL_ID        HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
6kd9zwrdtur6h 3684523216            0


-- Create outline --

SQL> BEGIN
      DBMS_OUTLN.create_outline(
        hash_value    =>3684523216,
     child_number  => 0,
      category      => 'PAYABLES_OUTLINES');
   END;
 /

PL/SQL procedure successfully completed.

-- check that outline has been craeted --  

SQL> select name,owner,SQL_TEXT from  dba_outlines;

NAME                           OWNER                          SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_12092715305717804  SCOTT                          select dname,loc from dept where deptno=10

********THANKS FOR YOUR TIME ******************************


Related links:

Home
 http://koenigocm.blogspot.in/

SQL PLAN MANAGEMENT PART-I
 http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1
 
SQL PLAN MANAGEMENT PART-II
http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%20%E2%80%93%20ORACLE%2011g%20%3A%20Part-2
--