Category: Query Tuning

How to Move SQL Profiles from One Database to Another

Solution
What is a SQL Profile?
Managing SQL Profiles
Steps to Create and Transfer Profile from One Database to Another
1. Create SQL Profile in SCOTT schema
2. Creating a staging table to store the SQL Profiles
3. Pack the SQL Profiles into the Staging Table
4. Export the Staging Table to the Target Database
4a. Export from Source Database
4b. Import into Target Database
5. Unpack the SQL Profiles
5a. Test before unpacking
5b. Unpack Staging Table
6. Check the SQL Profile is enabled in Target Database

Solution

What is a SQL Profile?

 

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

 

For information on SQL Profiles see:

 

Document 271196.1 Automatic SQL Tuning – SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

 

The following example illustrates the process of moving a SQL Profile from 10.2.0.4.0 to 11.2.0.3.0

1. Create SQL Profile in SCOTT schema

 

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

 

DECLARE 
my_task_name VARCHAR2(30);
my_sqltext CLOB; 
my_sqlprofile_name VARCHAR2(30); 

BEGIN 
  my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839'; 
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, 
        user_name => 'SCOTT', 
        scope => 'COMPREHENSIVE', 
        time_limit => 60, 
        task_name => 'my_sql_tuning_task', 
        description => 'Demo Task to tune a query'); 
  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); 

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>
             'my_sql_tuning_task', name => 'my_sql_profile'); 
END; 
/

PL/SQL procedure successfully completed.

 

 

 

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7839)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

 

 

 

Note: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information  that indicates that  “my_sql_profile” is used.

2. Creating a staging table to store the SQL Profiles

 

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

 

  • table_name => name of the table to store the SQL Profiles.
  • schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

 

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

 

  • staging_table_name => name of the table to store the SQL Profiles.
  • profile_name => name of the SQL Profile to be packed.

 

Note: The table_name and schema_name are case-sensitive.

 

SQL> desc STAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE_NAME                                       VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 SIGNATURE                                          NUMBER
 SQL_TEXT                                           CLOB
 DESCRIPTION                                        VARCHAR2(500)
 TYPE                                               VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 BOOLEAN_FLAGS                                      NUMBER
 ATTRIBUTES                                         SQLPROF_ATTR
 VERSION                                            NUMBER
 SPARE1                                             CLOB
 SPARE2                                             BLOB

4. Export the Staging Table to the Target Database

 

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

 

my_linux_1:~> exp scott/tiger tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          STAGE          1 rows exported
Export terminated successfully without warnings.

4b. Import into Target Database

 

my_linux_1:~> imp scott/tiger tables=STAGE

Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "STAGE"          1 rows imported
Import terminated successfully with warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

 

SQL> set lines 130
SQL> set autotrace on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

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

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

   1 - filter("EMPNO"=7839)

 

 

 

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

 

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');

PL/SQL procedure successfully completed.

 

 

6. Check the SQL Profile is enabled in Target Database

 

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7839)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

 

Advertisements

Catch the Latch on Oracle DB

Waits on the cache buffer chains latch, ie the wait event “latch: cache buffers chains” happen when there is extremely high and concurrent access to the same block in a database. Access to a block is normally a fast operation but if concurrent users access a block fast enough, repeatedly then simple access to the block can become an bottleneck. The most common occurance of cbc (cache buffer chains) latch contention happens when multiple users are running nest loop joins on a table and accessing the table driven into via an index. Since the NL  join is basically a
  For all rows in i
     look up a value in j  where j.field1 = i.val
  end loop
then table j’s index on field1 will get hit for every row returned from i. Now if the lookup on i returns a lot of rows and if multiple users are running this same query then the index root block is going to get hammered on the index j(field1).
In order to solve a CBC latch bottleneck we need to know what SQL is causing the bottleneck and what table or index that the SQL statement is using is causing the bottleneck.
From ASH data this is fairly easy:
select 

      count(*), 
      sql_id, 
      nvl(o.object_name,ash.current_obj#) objn,
      substr(o.object_type,0,10) otype,
      CURRENT_FILE# fn,
      CURRENT_BLOCK# blockn
from  v$active_session_history ash
    , all_objects o
where event like 'latch: cache buffers chains'
  and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
               current_block#, o.object_name,o.object_type
order by count(*)
/               
From the out put it looks like we have both the SQL (at least the id, we can get the text with the id) and the block:
CNT SQL_ID        OBJN     OTYPE   FN BLOCKN

---- ------------- -------- ------ --- ------
  84 a09r4dwjpv01q MYDUAL   TABLE    1  93170
But the block actually is probably left over from a recent IO and not actually the CBC hot block though it might be.
We can investigate further to get more information by looking at P1, P2 and P3 for the CBC latch wait. How can we find out what P1, P2 and P3 mean? by looking them up in V$EVENT_NAME:
select * from v$event_name

where name = 'latch: cache buffers chains'
EVENT#     NAME                         PARAMETER1 PARAMETER2 PARAMETER3 
---------- ---------------------------- ---------- ---------- ----------
        58 latch: cache buffers chains     address     number      tries 

So  P1 is the address of the latch for the cbc latch wait.

Now we can group the CBC latch waits by the address and find out what address had the most waits:
select

    count(*),
    lpad(replace(to_char(p1,'XXXXXXXXX'),' ','0'),16,0) laddr
from v$active_session_history
where event='latch: cache buffers chains'
group by p1
order by count(*);   
COUNT(*)  LADDR

---------- ----------------
      4933 00000004D8108330   
In this case, there is only one address that we had waits for, so now we can look up what blocks (headers actually) were at that address
select o.name, bh.dbarfil, bh.dbablk, bh.tch

from x$bh bh, obj$ o
where tch > 5
  and hladdr='00000004D8108330'
  and o.obj#=bh.obj
order by tch
NAME        DBARFIL DBABLK  TCH

----------- ------- ------ ----
EMP_CLUSTER       4    394  120        
We look for the block with the highest “TCH” or “touch count”. Touch count is a count of the times the block has been accesses. The count has some restrictions. The count is only incremented once every 3 seconds, so even if I access the block 1 million times a second, the count will only go up once every 3 seconds. Also, and unfortunately, the count gets zeroed out if the block cycles through the buffer cache, but probably the most unfortunate is that  this analysis only works when the problem is currently happening. Once the problem is over then the blocks will usually get pushed out of the buffer cache.
In the case where the CBC latch contention is happening right now we can run all of this analysis in one query
select 

        name, file#, dbablk, obj, tch, hladdr 
from x$bh bh
    , obj$ o
 where 
       o.obj#(+)=bh.obj and
      hladdr in 
(
    select ltrim(to_char(p1,'XXXXXXXXXX') )
    from v$active_session_history 
    where event like 'latch: cache buffers chains'
    group by p1 
    having count(*) > 5
)
   and tch > 5
order by tch   
example output
NAME          FILE# DBABLK    OBJ TCH HLADDR

------------- ----- ------ ------ --- --------
BBW_INDEX         1 110997  66051  17 6BD91180
IDL_UB1$          1  54837     73  18 6BDB8A80
VIEW$             1   6885     63  20 6BD91180
VIEW$             1   6886     63  24 6BDB8A80
DUAL              1   2082    258  32 6BDB8A80
DUAL              1   2081    258  32 6BD91180
MGMT_EMD_PING     3  26479  50312 272 6BDB8A80
This can be misleading, as TCH gets set to 0 every rap around the LRU and it only gets updated once every 3 seconds, so in this case DUAL was my problem table not MGMT_EMD_PING
Deeper Analysis from Tanel Poder
Using Tanel’s ideas here’s a script to get the objects that we have the most cbc latch waits on
col object_name for a35
col cnt for 99999
SELECT
  cnt, object_name, object_type,file#, dbablk, obj, tch, hladdr
FROM (
  select count(*) cnt, rfile, block from (
    SELECT /*+ ORDERED USE_NL(l.x$ksuprlat) */
      –l.laddr, u.laddr, u.laddrx, u.laddrr,
      dbms_utility.data_block_address_file(to_number(object,’XXXXXXXX’)) rfile,
      dbms_utility.data_block_address_block(to_number(object,’XXXXXXXX’)) block
    FROM
       (SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 100000) s,
       (SELECT ksuprlnm LNAME, ksuprsid sid, ksuprlat laddr,
       TO_CHAR(ksulawhy,’XXXXXXXXXXXXXXXX’) object
        FROM x$ksuprlat) l,
       (select  indx, kslednam from x$ksled ) e,
       (SELECT
                    indx
                  , ksusesqh     sqlhash
  , ksuseopc
  , ksusep1r laddr
             FROM x$ksuse) u
    WHERE LOWER(l.Lname) LIKE LOWER(‘%cache buffers chains%’)
     AND  u.laddr=l.laddr
     AND  u.ksuseopc=e.indx
     AND  e.kslednam like ‘%cache buffers chains%’
    )
   group by rfile, block
   ) objs,
     x$bh bh,
     dba_objects o
WHERE
      bh.file#=objs.rfile
 and  bh.dbablk=objs.block
 and  o.object_id=bh.obj
order by cnt
;
CNT  OBJECT_NAME       TYPE  FILE#  DBABLK    OBJ   TCH  HLADDR

---- ----------------- ----- ----- ------- ------ ----- --------
   1 WB_RETROPAY_EARNS TABLE     4   18427  52701  1129 335F7C00
   1 WB_RETROPAY_EARNS TABLE     4   18194  52701  1130 335F7C00
   3 PS_RETROPAY_RQST  TABLE     4   13253  52689  1143 33656D00
   3 PS_RETROPAY_RQST  INDEX     4   13486  52692   997 33656D00
   3 WB_JOB            TABLE     4   14443  52698   338 335B9080
   5 PS_RETROPAY_RQST  TABLE     4   13020  52689   997 33656D00
   5 WB_JOB            TABLE     4   14676  52698   338 335B9080
   5 WB_JOB            TABLE     4   13856  52698   338 335F7C00
   6 WB_JOB            TABLE     4   13623  52698   338 335F7C00
   7 WB_JOB            TABLE     4   14909  52698   338 335B9080
 141 WB_JOB            TABLE     4   15142  52698   338 335B9080
2513 WB_JOB            INDEX     4   13719  52699   997 33656D00
Why do we get cache buffers chains latch contention?
In order to understand why we get CBC latch contention we have to understand what the CBC latch protects. The CBC latch protects information controlling the buffer cache. Here is a schematic of computer memory and the Oracle processes, SGA and the main components of the SGA:
The buffer cache holds in memory versions of datablocks for faster access. Can you imagine though how we find a block we want in the buffer cache? The buffer cache doesn’t have a index of blocks it contains and we certainly don’t scan the whole cache looking for the block we want (though I have heard that as a concern when people increase the size of there buffer cache). The way we find a block in the buffer cache is by taking the block’s address, ie it’s file and block number and hashing it. What’s hashing? A simple example of hashing is  the “Modulo” function
1 mod 4 = 1

2 mod 4 = 2
3 mod 4 = 3
4 mod 4 = 0
5 mod 4 = 1
6 mod 4 = 2
7 mod 4 = 3
8 mod 4 = 0
Using “mod 4” as a hash funtion creates 4 possible results. These results are used by Oracle as “buckets” or identifiers of locations to store things. The things in this case will be block headers.
Block headers are meta data about data block including pointers to the actual datablock as well as pointers to the other headers in the same bucket.
The block headers in the hash buckets are connected via a doubly linked list. One link points forward the other points backwards
The resulting layout looks like
the steps to find a block in the cache are
If there are a lot of sessions concurrently accessing the same buffer header (or buffer headers in the same bucket) then the latch that protects that bucket will get hot and users will have to wait getting “latch: cache buffers chains” wait.
Two ways this can happen (among probably several others)
For the nested loops example, Oracle will in some (most?) cases try and pin the root block of the index because Oracle knows we will be using it over and over. When a block is pinned we don’t have to use the cbc latch. There seem to be cases (some I think might be bugs) where the root block doesn’t get pinned. (I want to look into this more – let me know if you have more info)
One thing that can make CBC latch contention worse is if a session is modifying the data block that users are reading because readers will clone a block with uncommitted changes and roll back the changes in the cloned block:
all these clone copies will go in the same bucket and be protected by the same latch:

How many copies of a block are in the cache?

select 

       count(*)
     , name
     , file#
     , dbablk
     , hladdr 
from   x$bh bh
          , obj$ o
where 
      o.obj#(+)=bh.obj and
      hladdr in 
(
    select ltrim(to_char(p1,'XXXXXXXXXX') )
    from v$active_session_history 
    where event like 'latch: cache%'
    group by p1 
)
group by name,file#, dbablk, hladdr
having count(*) > 1
order by count(*);
CNT NAME        FILE#  DBABLK HLADDR

--- ---------- ------ ------- --------
 14 MYDUAL          1   93170 2C9F4B20
Notice that the number of copies, 14, is higher the the max number of copies allowed set by “_db_block_max_cr_dba = 6” in 10g. The reason is this value is just a directive not a restriction. Oracle tries to limit the  number of copies.
Solutions

Find SQL ( Why is application hitting the block so hard? )

Possibly change application logic

Eliminate hot spots

Nested loops, possibly

Hash Partition the index with hot block

Use Hash Join instead of Nested loop join
Use Hash clusters

Look up tables (“select language from lang_table where …”)

Change application
Use plsql function
Spread data out to reduce contention, like set PCTFREE to 0 and recreate the table so that there is only one row per block

Select from dual

Possibly use x$dual
Note starting in 10g Oracle uses the “fast dual” table (ie x$dual) automatically when executing a query on dual as long as the column “dummy” is not accessed. Accessing dummy would be cases like
    select count(*) from dual;
    select * from dual;
    select dummy from dual;
an example of not accessing “dummy” would be:
    select 1 from dual;
    select sysdate from dual;

Updates, inserts , select for update on blocks while reading those blocks

Virtual Indexes

The process of tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Creating additional indexes is not without its problems. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system. This article presents a simple example of how virtual indexes are used.

First, we create and populate a table.

CREATE TABLE objects_tab AS SELECT * FROM all_objects;

ALTER TABLE objects_tab ADD (
  CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
);

EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    92 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB    |     1 |    92 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | OBJECTS_TAB_PK |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=10)

SQL>

If we query the table using a non-indexed column, we see a full table scan.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

SQL>

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

SQL>

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

SQL>

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

SQL>

If we repeat the query we can see that the virtual index is now used.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     2 |   184 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB                |     2 |   184 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | OBJECTS_TAB_OBJECT_NAME_VI |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='USER_TABLES')

SQL>

The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF
SQL> SELECT index_name FROM user_indexes;

INDEX_NAME
------------------------------
OBJECTS_TAB_PK

1 row selected.

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_PK
OBJECTS_TAB_OBJECT_NAME_VI

2 rows selected.

SQL>

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

SQL>

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;
CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
                                                        *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.

SQL>

SQL Plan Management -2-

1. Optimizer_capture_sql_plan_baselines              2. Optimizer_use_sql_plan_baseline 

As known “optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines.”

Does it mean it will not capture any plan for any sql if set to false ?

What if we want to capture baselines for only selected queries automatically.

The whole answer lies in values of “ENABLED,ACCEPTED AND FIXED” column if dba_sql_plan_baselines. How? Lets find out…

As usual, lets create a table and insert few records.

Session1>show parameter capture

NAME                                 TYPE        VALUE

———————————— ———– ——————————

optimizer_capture_sql_plan_baselines boolean     FALSE

create table test (name varchar2(10), id number(*));

begin

for i in 1..1000

loop

insert into test values (‘SUMIT’,i);

end loop;

commit;

end;

/

begin

for i in 1001..10000

loop

insert into test values (‘BHATIA’,i);

end loop;

commit;

end;

/

begin

for i in 10001..100000

loop

insert into test values (‘BASELINE’,i);

end loop;

commit;

end;

/

begin

for i in 100001..1000000

loop

insert into test values (‘SPM’,i);

end loop;

commit;

end;

/

SELECT COUNT(*),name from test group by name order by 1;

COUNT(*) NAME

———- ———-

1000 SUMIT

9000 BHATIA

90000 BASELINE

900000 SPM

4 rows selected.

Time to query table and load the plan into the baseline.

–Inserting hint to easily identify the query

Select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

select sql_id,exact_matching_signature,force_matching_signature from v$sql where sql_text like ‘%id=1000%’;

SQL_ID                EXACT_MATCHING_SIGNATURE         FORCE_MATCHING_SIGNATURE

————- ——————————– ——————————–

duk2ypk5fz9g6              7784548270786280511              4428329137525653294

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

———————————————————————-

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

———————————-

| Id  | Operation         | Name |

———————————-

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| TEST |

———————————-

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– rule based optimizer used (consider using cbo)

–Creating the baseline for the plan

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘duk2ypk5fz9g6’,plan_hash_value => 1357081020 );

select sql_handle, plan_name, enabled, accepted,fixed from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

—————————— —————————— — — —

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO

As we can see, If we load the plan manually, that will plan will be enabled and accepted but not fixed.  Its time create an index and run the query again.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

Oracle is still using SYS_SQL_PLAN_7118fc3f97bbe3d0 baseline.

However, if we query dba_sql_plan_baselines, we will see that oracle has started capturing baselines for this sql. (plan name SYS_SQL_PLAN_7118fc3f642e4a26)

PLAN_TABLE_OUTPUT

———————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

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

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

select sql_handle, plan_name, enabled, accepted,fixed,signature from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX             SIGNATURE

—————————— —————————— — — — ———————

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO NO   7784548270786280511

SYS

So even if capture baseline is set to false, oracle will still capture the new plans and baselines for that particular sql onwards. We can evolve the  new plan and can check if index is getting used or not.

/*Evolving new plan */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle:SYS_SQL_6c0845687118fc3f

Enter value for time:60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

Plan: SYS_SQL_PLAN_7118fc3f642e4a26

———————————–

Plan was verified: Time used .05 seconds.

Passed

performance criterion: Compound improvement ratio >= 504.88

Plan was changed to an accepted plan.

Baseline Plan      Test Plan     Improv. Ratio

————-      ———     ————-

Execution Status:        COMPLETE       COMPLETE

Rows Processed:                 1              1

Elapsed Time(ms):               26             0

CPU Time(ms):                  25              0

Buffer Gets:                 2017              4            504.25

Disk Reads:                     0              0

Direct Writes:                  0              0

Fetches:                        0              0

Executions:                     1              1

——————————————————————————-

Report

Summary

——————————————————————————-

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

As we can read in the report, Oracle has marked the new plan as accepted. Oracle keeps a tab if the plan was loaded manually or captured automatically. This can be verified from origin column of dba_sql_plan_baselines.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

So far, the story was pretty much straight. Here’s a quick recap what we have done
1. Created Table and Inserted records
2. Query the table and baseline the query
3. Created Index and verified new plan is loaded but not accepted.
4. Evolve the new plan and accepted=yes.
5. New plan(Index Range Scan) started getting used.

Its time to discuss the usage of enabled,accepted and fixed parameters.

TEST1:  ENABLED=NO.

For every sql_id, oracle checks if the baselines exists or not. If yes, then it will filter all the enabled plans. The plans for which enabled=NO will not be considered even if accepted and fixed are set to yes.

Other than this, if any plan is set enabled=yes for a query,oracle will start capturing new plans also.

Therefore, enabled is the first level. If enabled is set to NO,oracle will mark not to use and evolve that plan.

Lets have a test case. I will mark enabled =no for the plan that was using index and check which plan is used.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

/* Setting Enabled=NO for SYS_SQL_PLAN_7118fc3f642e4a26 */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ENABLED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO YES  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

So, plan SYS_SQL_PLAN_7118fc3f642e4a26 is accepted but not enabled. Lets execute the query again and see which plan is getting used.

PLAN_TABLE_OUTPUT

——————————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

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

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

Oracle choses SYS_SQL_PLAN_7118fc3f97bbe3, since that plan is still enabled and accepted.
Therefore, minimum requirement for oracle to use a plan as baseline is, it should be marked both as ENABLED and ACCEPTED.

The next level is Accepted. In this level oracle will pick all the plans that are accepted to the users and discard all “ACCEPTED=NO” plans. As we had already seen,there can be more than one plan for accepted=yes. All these plans are already evolved plans and are candidate for being  the final execution plan.

If no plan is marked “FIXED=YES”, then oracle will pick any of these plans based on the costing.

All those plans which are “ENABLED=YES” and “ACCEPTED=NO”, are candidates for evolution. We can also however marked then accepted=yes manually.

Next and the last level is Fixed. If any plan is fixed oracle will use that plan only. If more than one plan is fixed, oracle will use costing as criteria to select the plan among those.

TEST2:  Setting any one plan as FIXED=YES

Lets mark index plan as accepted again and FTS plan as fixed.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO  YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

/*Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to Enabled */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name =>’ENABLED’,attribute_value =>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/*Setting SYS_SQL_PLAN_7118fc3f97bbe3d0 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Executing the query and checking the plan again

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————————

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

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

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

As we can confirm oracle start using FTS as that plan is marked fixed.

TEST3: Setting FIXED=YES for more than one plan

Lets mark second plan “FIXED=YES” and re-execute the query

/*Setting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle=>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1699862855

—————————————————————————————

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

—————————————————————————————

|   0 | SELECT STATEMENT            |         |       |       |     1 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST_ID |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – access(“ID”=1000)

Note

—–

– dynamic sampling used for this statement

– SQL plan baseline SYS_SQL_PLAN_7118fc3f642e4a26 used for this statement

Test 4:

Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.
To simulate the test, lets again mark the plan(using index) as fixed=no and accepted=no.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to ACCEPTED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ACCEPTED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Lets try to evolve the index plan (SYS_SQL_PLAN_7118fc3f642e4a26) again, like we did previously. The only difference is fixed=yes this time for plan using FTS

/* TRYING TO EVOLVE SYS_SQL_PLAN_7118fc3f642e4a26 */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for time: 60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

As we can confirm, plan SYS_SQL_PLAN_7118fc3f642e4a26 is not evolved since SYS_SQL_PLAN_7118fc3f97bbe3d0 was already fixed.

TEST5:

Lets go back a step further and drop the baseline as well as index. We will try to simulate a test case to check if oracle captures new plan if any plan is marked as FIXED=YES

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped  PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => ‘SYS_SQL_6c0845687118fc3f’,

plan_name  => ‘SYS_SQL_PLAN_7118fc3f642e4a26’);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

drop index test_id;

Index dropped.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

So, we are back to square one from where we have started. Lets again create the same index and query the table to see if it still capture new plans.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

As we can see, oracle has stopped capturing new plans for this sql as SYS_SQL_PLAN_7118fc3f97bbe3d0 is already fixed.

Lets mark fixed=NO again and re-execute the query. Oracle should capturing the plan again for this query.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

Here’s the quick summary,

1.  ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.

2. Enabled=NO :-  Than plan won’t we used.

3.  Accepted=Yes (Any one Plan) :- That plan will be used for execution

4.  Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.

5. Accepted=No: Plan wont be used.

6.  FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.

7.  FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.

SPECIAL CASE :-ENABLED=YES,FIXED=YES BUT ACCEPTED=NO.

What if in our given scenario, we mark SYS_SQL_PLAN_7118fc3f97bbe3d0 as fixed and enabled but not accepted. Will oracle capture the new plan and will it evolve it.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

1 row selected.

Lets discuss the above scenario and try to figure out who takes precedence under what condition

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

Oracle has captured new plan. Lets try to evolve that one also.

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT =

60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

ENABLED takes precedence over FIXED in case of capturing the new plan and vice versa in case of evolving.

SQL Plan Management -1-

SQL Plan Management(SPM) provides a mechanism to prevent unwanted plan flips.

Whenever a query is hard parsed, oracle produces several execution plan and compares it with the accepted plan in a baseline. If a match is found, oracle picks that plan.

The behaviour of baseline is governed by 2 parameters.

  1. Optimizer_capture_sql_plan_baselines
  2. Optimizer_use_sql_plan_baseline

optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines. The default value is false

Optimizer_use_sql_plan_baseline, if set to true, force oracle to use the sql plan baseline (if present). The default is true.

Implementing HINTS without changing code

Environment Setting :-

  • Optimizer_capture_sql_plan_baselines (false)
  • Optimizer_use_sql_plan_baseline (true)

Session1>desc mytab

Name Null? Type

—————————————– ——– —————————-

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

Step 1> Query the mytab table without index.

select /*+woindex */ * from mytab where GENERATED=’FOR’;

no rows selected

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘0ytzpgnhg1g83′));

SQL_ID  0ytzpgnhg1g83, child number 0

————————————-

select /*+woindex */ * from mytab where GENERATED=’FOR’

Plan hash value: 96696846

—————————————————————————

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

—————————————————————————

|   0 | SELECT STATEMENT  |       |       |       |   124K(100)|          |

|*  1 |  TABLE ACCESS FULL| MYTAB |  4819K|   422M|   124K  (3)| 00:24:54 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter(“GENERATED”=’FOR’)

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

no rows selected

Step 2> No baseline yet exists for the current query. Lets baseline the plan to make sure it does not pick index automatically so that we can simulate the test.

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘0ytzpgnhg1g83′,plan_hash_value => 96696846 );

PL/SQL procedure successfully completed.

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

SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC

——————————————————————————– —————————— —————————— — —

select /*+woindex */ * from mytab where GENERATED=’FOR’                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

1 row selected.

Step 3> Lets create an index

create index mytab_idx2 on mytab(GENERATED) parallel 4;

Index created.

alter index mytab_idx2 noparallel;

Index altered.

Step4> Execute the query to confirm its not using the index since optimizer_use_sql_plan_baseline is set to true.

select * FROM TABLE(dbms_xplan.display_cursor(‘0ytzpgnhg1g83′,1));

PLAN_TABLE_OUTPUT

——————————————————————————————————————————————————

SQL_ID  0ytzpgnhg1g83, child number 1

————————————-

select /*+woindex */ * from mytab where GENERATED=’FOR’

Plan hash value: 96696846

—————————————————————————

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

—————————————————————————

|   0 | SELECT STATEMENT  |       |       |       |   124K(100)|          |

|*  1 |  TABLE ACCESS FULL| MYTAB |  4819K|   422M|   124K  (3)| 00:24:54 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter(“GENERATED”=’FOR’)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_fdd635aada00620d used for this statement

22 rows selected.

Step5>Execute the query with index hint and create the baseline.

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED=’FOR’;

no rows selected

Select * FROM TABLE(dbms_xplan.display_cursor(‘ay757nb7anm56′));

PLAN_TABLE_OUTPUT

———————————————————————————————-

SQL_ID  ay757nb7anm56, child number 0

————————————-

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED=’FOR’

Plan hash value: 3007699452

——————————————————————————————

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

——————————————————————————————

|   0 | SELECT STATEMENT            |            |       |       |   995 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTAB      |  4819K|   422M|   995   (1)| 00:00:12 |

|*  2 |   INDEX RANGE SCAN          | MYTAB_IDX2 |  4896K|       |    90   (2)| 00:00:02 |

——————————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – access(“GENERATED”=’FOR’)

19 rows selected.

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘ay757nb7anm56’,plan_hash_value => ‘3007699452’ );

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

SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC

——————————————————————————– —————————— —————————— — —

select /*+woindex */ * from mytab where GENERATED=’FOR’                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aa041dae64  YES NO

select /*+woindex */ * from mytab where GENERATED=’FOR’                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED=’FOR’          SYS_SQL_5229297eca7bb2c7       SYS_SQL_PLAN_ca7bb2c7041dae64  YES YES

3 rows selected.

Step6> Now here, you can see that plan(SYS_SQL_PLAN_fdd635aa041dae64 ) being automatically linked with sql_handle(SYS_SQL_50969e88fdd635aa).
You can accept it. In case, plan is not linked with sql_handle, use the below method to link plan manually.

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘ay757nb7anm56’,plan_hash_value => 3007699452 ,sql_handle => ‘SYS_SQL_50969e88fdd635aa’);

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

SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC

——————————————————————————– —————————— —————————— — —

select /*+woindex */ * from mytab where GENERATED=’FOR’                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aa041dae64  YES YES

select /*+woindex */ * from mytab where GENERATED=’FOR’                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED=’FOR’          SYS_SQL_5229297eca7bb2c7       SYS_SQL_PLAN_ca7bb2c7041dae64  YES YES

3 rows selected.

dbms_spm.load_plans_from_cursor_cache(sql_id => ‘<hinted_sqlid>’,plan_hash_value=><hinted_plan_value>,sql_handle=>’<sql handle of original query>’)