RESULT_CACHE – can not be baselined

RESULT_CACHE Oracle hint alows to use the essential Oracle feature – the caching of low-volatile data gives the win in the performance. And it would be very usefull to baseline correspondent execution plan for existing queries.
Let’s check if we can do it.

From the beginning – let’s create the baseline and ensure we can use it.
Our first query should use INDEX FULL SCAN access path:

SQL> select empno from emp order by empno;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 27kbchb2hj15r, child number 0
-------------------------------------
select empno from emp order by empno

Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |

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

13 rows selected.

OK, now let’s force FULL SCAN usage:

SQL> select /*+ FULL(emp) */ empno from emp order by empno;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gfm9uc4k95qct, child number 0
-------------------------------------
select /*+ FULL(emp) */ empno from emp order by empno

Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 14 | 56 | 4 (25)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

14 rows selected.

Let’s make plan of the last query baselined and assigned to the first query.

SQL> set serveroutput on
SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
  5          sql_id=>'27kbchb2hj15r'
  6      );
  7      dbms_output.put_line(cnt);
  8  end;
  9  /
1
PL/SQL procedure successfully completed.

Elapsed: 00:00:01.71
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a YES

Now let’s disable existing baseline

SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  5      SQL_HANDLE => 'SYS_SQL_c9d708f33f36449b',
  6      PLAN_NAME=> 'SQL_PLAN_cmps8yczmcj4v57c0790a',
  7      ATTRIBUTE_NAME => 'enabled',
  8      ATTRIBUTE_VALUE => 'NO');
  9  end;
10  /
PL/SQL procedure successfully completed.

And now let’s tailor new ineffective plan to our sql:

SQL> declare
  2      cnt number;
  3  begin
  4      cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  5      SQL_HANDLE => 'SYS_SQL_c9d708f33f36449b',
  6      PLAN_NAME=> 'SQL_PLAN_cmps8yczmcj4v57c0790a',
  7      ATTRIBUTE_NAME => 'enabled',
  8      ATTRIBUTE_VALUE => 'NO');
  9  end;
10  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a NO

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt:=dbms_spm.load_plans_from_cursor_cache(
5 sql_id => 'gfm9uc4k95qct',
6 plan_hash_value => 150391907,
7 sql_handle => 'SYS_SQL_c9d708f33f36449b',
8 fixed=>'YES');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4v57c0790a NO

SYS_SQL_c9d708f33f36449b
select empno from emp order by empno
SQL_PLAN_cmps8yczmcj4vc6f9334c YES

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---

Elapsed: 00:00:00.01
SQL> set autotrace on
SQL> select empno from emp order by empno;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

Elapsed: 00:00:01.39

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 56 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
36 recursive calls
14 db block gets
26 consistent gets
2 physical reads
2976 redo size
566 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

Well, OK, we did it.🙂
Now let’s do the same for RESULT_CACHE

SQL> select * from dept;

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

Elapsed: 00:00:00.01
SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3154rqzb8xudy, child number 0
-------------------------------------
select * from dept

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |

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

13 rows selected.

Elapsed: 00:00:00.34
SQL> select /*+ RESULT_CACHE */ * from dept;

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

Elapsed: 00:00:00.06
SQL> select /*+ RESULT_CACHE */ * from dept;

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

Elapsed: 00:00:00.00
SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2km3uu3ddcqan, child number 0
-------------------------------------
select /*+ RESULT_CACHE */ * from dept

Plan hash value: 3383998547

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

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

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

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

| 0 | SELECT STATEMENT | | | | 3
(100)| |

| 1 | RESULT CACHE | 8r96gypwgu6ng1gwzmznkkp626 | | |
| |

| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
(0)| 00:00:01 |

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

Result Cache Information (identified by operation id):
------------------------------------------------------

1 -

19 rows selected.

Please notice interesting thing – dispite we have quite different SQL_IDs for these two queries above we have similar plan hash values.
OK, let’s try now to make it baselined:

SQL> declare
  2      cnt number;
  3  begin
  4   FOR c IN (SELECT * FROM dba_sql_plan_baselines) LOOP
  5      cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE
  6          (c.sql_handle, c.plan_name);
  7   END LOOP;
  8  END;
9  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

no rows selected

Elapsed: 00:00:00.00
SQL> set serveroutput on
SQL> declare
2 cnt number;
3 begin
4 cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5 sql_id=>'3154rqzb8xudy'
6 );
7 dbms_output.put_line(cnt);
8 end;
9 /
1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 YES

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
5 SQL_HANDLE => 'SYS_SQL_8e73ec4c599bebb6',
6 PLAN_NAME=> 'SQL_PLAN_8wwzc9jctruxq0e23be79',
7 ATTRIBUTE_NAME => 'enabled',
8 ATTRIBUTE_VALUE => 'NO');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 NO

Elapsed: 00:00:00.00
SQL> declare
2 cnt number;
3 begin
4 cnt:=dbms_spm.load_plans_from_cursor_cache(
5 sql_id => '2km3uu3ddcqan',
6 plan_hash_value => 3383998547,
7 sql_handle => 'SYS_SQL_8e73ec4c599bebb6',
8 fixed=>'YES');
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
2 dba_sql_plan_baselines;

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ENA
------------------------------ ---
SYS_SQL_8e73ec4c599bebb6
select * from dept
SQL_PLAN_8wwzc9jctruxq0e23be79 YES

Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select * from dept;

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

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

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

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

Statistics
----------------------------------------------------------
15 recursive calls
14 db block gets
15 consistent gets
0 physical reads
2924 redo size
666 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

Seems this does not work. As we can see Oracle does not create new baseline in this particular case but
re-enables the existing one.
I have no idea now is it a bug or a feature. Because the procedure LOAD_PLAN_FROM_CURSOR_CACHE uses both sql_id and plan_hash_value all this looks to me as a bug.

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to RESULT_CACHE – can not be baselined

  1. Anonymous says:

    you be useful to try the same with old fashioned sql profiles ?

  2. tonyhasler says:

    Hi Dimitri,

    If you run this query:

    select * from v$sql_hint where version_outline is null;

    You will see a large number of hints. I think that none make it into the outline hints section of an explain plan statement and similarly don’t make it into a SQL baseline or stored outline!

    • dnikiforov says:

      Hi Tony,
      thank you, that’s quite correct remark.
      Really I’m a bit disappinted what Oracle did not include this hint into baseline/outline support.
      Sometimes it would be useful to get stable results of frequent queries cached without code change.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s