Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: November 25, 2014 - 6:45 am UTC

Last updated: November 27, 2019 - 1:42 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Tom,

We have an application(situation), where one of the query is performing poor which is written without analytics, when used analytics it was far better.
we dont have time to make this code change right now immediately, so thought of using SPM to seed analytics plan into baselines for this non-analytical query.
so that when non-analytical query goes into execution, it will grap the plan (analytics plan) available in baseline for real execution.
but this approach doesn't work, can you please help us to understand why it doesn't work?

rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select a.empno, a.ename, a.sal, sum(b.sal)
  2  from emp a, emp b
  3  WHERE a.sal > b.sal
  4  OR
  5  ( a.sal= b.sal
  6  and a.rowid >= b.rowid )
  7  group by a.empno, a.ename, a.rowid,a.sal
  8  order by a.sal, a.rowid  ;

     EMPNO ENAME             SAL SUM(B.SAL)
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7900 JAMES             950       1750
      7876 ADAMS            1100       2850
      7521 WARD             1250       4100
      7654 MARTIN           1250       5350
      7934 MILLER           1300       6650
      7844 TURNER           1500       8150
      7499 ALLEN            1600       9750
      7782 CLARK            2450      12200
      7698 BLAKE            2850      15050
      7566 JONES            2975      18025
      7788 SCOTT            3000      21025
      7902 FORD             3000      24025
      7839 KING             5000      29025

14 rows selected.

rajesh@ORA11G> select * from table( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  0vrjsuh9qf524, child number 0
-------------------------------------
select a.empno, a.ename, a.sal, sum(b.sal) from emp a, emp b WHERE
a.sal > b.sal OR ( a.sal= b.sal and a.rowid >= b.rowid ) group by
a.empno, a.ename, a.rowid,a.sal order by a.sal, a.rowid

Plan hash value: 2853333416

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |  2719M(100)|          |
|   1 |  SORT GROUP BY      |      |    50G|  3259G|  2719M  (1)|999:59:59 |
|   2 |   NESTED LOOPS      |      |    50G|  3259G|  2717M  (1)|999:59:59 |
|   3 |    TABLE ACCESS FULL| EMP  |  1000K|    42M|  2719   (1)| 00:00:33 |
|*  4 |    TABLE ACCESS FULL| EMP  | 50000 |  1220K|  2718   (1)| 00:00:33 |
----------------------------------------------------------------------------

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

   4 - filter(("A"."SAL">"B"."SAL" OR ("A"."SAL"="B"."SAL" AND
              "A".ROWID>="B".ROWID)))


24 rows selected.

rajesh@ORA11G> select empno,ename,sal,
  2  sum(sal) over(order by sal,rowid) as new_sal
  3  from emp ;

     EMPNO ENAME             SAL    NEW_SAL
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7900 JAMES             950       1750
      7876 ADAMS            1100       2850
      7521 WARD             1250       4100
      7654 MARTIN           1250       5350
      7934 MILLER           1300       6650
      7844 TURNER           1500       8150
      7499 ALLEN            1600       9750
      7782 CLARK            2450      12200
      7698 BLAKE            2850      15050
      7566 JONES            2975      18025
      7788 SCOTT            3000      21025
      7902 FORD             3000      24025
      7839 KING             5000      29025

14 rows selected.

rajesh@ORA11G> select * from table( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  80bkghaa93gyp, child number 0
-------------------------------------
select empno,ename,sal, sum(sal) over(order by sal,rowid) as new_sal
from emp

Plan hash value: 3145491563

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       | 14077 (100)|          |
|   1 |  WINDOW SORT       |      |  1000K|    42M|    57M| 14077   (1)| 00:02:49 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|    42M|       |  2719   (1)| 00:00:33 |
-----------------------------------------------------------------------------------


15 rows selected.

rajesh@ORA11G> set serveroutput on
rajesh@ORA11G> declare
  2     l_sql varchar2(1000);
  3     l_rows int;
  4  begin
  5     l_sql := 'select a.empno, a.ename, a.sal, sum(b.sal) '||
  6     'from emp a, emp b '||
  7     'WHERE a.sal > b.sal '||
  8     'OR '||
  9     '( a.sal= b.sal '||
 10     'and a.rowid >= b.rowid ) '||
 11     'group by a.empno, a.ename, a.rowid,a.sal '||
 12     'order by a.sal, a.rowid  ' ;
 13     l_rows := dbms_spm.load_plans_from_cursor_cache
 14     (sql_id=>'80bkghaa93gyp',
 15      plan_hash_value=>3145491563,
 16      sql_text=>l_sql);
 17
 18     dbms_output.put_line('plans loaded ='||l_rows);
 19  end;
 20  /
plans loaded =1

PL/SQL procedure successfully completed.

rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> select a.empno, a.ename, a.sal, sum(b.sal)
  2  from emp a, emp b
  3  WHERE a.sal > b.sal
  4  OR
  5  ( a.sal= b.sal
  6  and a.rowid >= b.rowid )
  7  group by a.empno, a.ename, a.rowid,a.sal
  8  order by a.sal, a.rowid  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2853333416

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    50G|  3259G|  2719M  (1)|999:59:59 |
|   1 |  SORT GROUP BY      |      |    50G|  3259G|  2719M  (1)|999:59:59 |
|   2 |   NESTED LOOPS      |      |    50G|  3259G|  2717M  (1)|999:59:59 |
|   3 |    TABLE ACCESS FULL| EMP  |  1000K|    42M|  2719   (1)| 00:00:33 |
|*  4 |    TABLE ACCESS FULL| EMP  | 50000 |  1220K|  2718   (1)| 00:00:33 |
----------------------------------------------------------------------------

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

   4 - filter("A"."SAL">"B"."SAL" OR "A"."SAL"="B"."SAL" AND
              "A".ROWID>="B".ROWID)

rajesh@ORA11G> set autotrace off
rajesh@ORA11G>
rajesh@ORA11G> select a.empno, a.ename, a.sal, sum(b.sal)
  2  from emp a, emp b
  3  WHERE a.sal > b.sal
  4  OR
  5  ( a.sal= b.sal
  6  and a.rowid >= b.rowid )
  7  group by a.empno, a.ename, a.rowid,a.sal
  8  order by a.sal, a.rowid  ;

     EMPNO ENAME             SAL SUM(B.SAL)
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7900 JAMES             950       1750
      7876 ADAMS            1100       2850
      7521 WARD             1250       4100
      7654 MARTIN           1250       5350
      7934 MILLER           1300       6650
      7844 TURNER           1500       8150
      7499 ALLEN            1600       9750
      7782 CLARK            2450      12200
      7698 BLAKE            2850      15050
      7566 JONES            2975      18025
      7788 SCOTT            3000      21025
      7902 FORD             3000      24025
      7839 KING             5000      29025

14 rows selected.

rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> select * from table( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  0vrjsuh9qf524, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 0vrjsuh9qf524, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

rajesh@ORA11G>

and Tom said...

you can only use the "swap the plan for a query using baselines" if you take the plan from what is fundamentally the same query.

that is, you can take the plan for

select /*+ full(t) */ * from t where x = 5;

and associate it with the query

select * from t where x = 5;


but you cannot take a single table query plan and associate it with a multiple table query. The queries just are not compatible.


You have to be able to take the original query, and just by adding hints, fixing up an optimizer setting, playing around with statistics (but having the query remain pretty much the "same" otherwise) get the desired plan.


It is just like you cannot take the plan for "select * from dept" and apply it to "select * from emp"...


for those that are not familiar with the technique attempted to be used here, see:

http://www.oracle.com/technetwork/issue-archive/2014/14-jul/o44asktom-2196080.html


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Try DBMS_ADVANCED_REWRITE

Mike Tefft, December 02, 2014 - 8:29 pm UTC

Also known as the 'SQL Swap'.

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_advrwr.htm#i999507

You don't substitute a plan - you substitute one SQL statement for another.

Tom Kyte
December 02, 2014 - 8:41 pm UTC

will not work - there is an order by involved.... You cannot currently swap "order bys"

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8730199800346896388

on SQL Profiles to SQL Plan baselines

Rajeshwaran Jeyabal, November 26, 2019 - 4:11 pm UTC

Team,

Is it possible to convert a SQL Profiles to SQL Plan baselines?

any inputs/directions please.
Chris Saxon
November 27, 2019 - 1:42 pm UTC

They're separate concepts. A SQL profile contains information to help the optimizer choose a better plan. But it's still possible for it to use different plans.

A SQL plan baseline is a set of possible plans for a statement. With the baseline in place, the optimizer can only use one of the accepted plans.

One SQL statement can have both a profile and a baseline. If there's more than one plan in the baseline, the profile helps the optimizer choose the best one.

Maria discusses this further at https://blogs.oracle.com/optimizer/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines

So you can't "migrate" the profile to a baseline.

If you already have a profile, and want to lock the statement to the plan you've got, use the normal process to create a baseline.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library