Hello Oracle Masters, I need your help, like everyone :-)
I am testing on my database baselines and I asked myself if it is possible do put an execution plan of SQL_ID 2 in a baseline for SQl_ID 1.
Here is my theorical test : an external software do a SELECT with an ORDER BY. My client say, we don't need this ORDER BY, so remove it. The vendor said no, no patch before six months. OK, I am a DBA, I have to find a solution :-)
My test case : a table with nine peoples named MARTIN and one named DUPONT.
SQL> CREATE TABLE zztest(id NUMBER(10) CONSTRAINT zztest_pk_id PRIMARY KEY, nom VARCHAR2(50 CHAR), prenom VARCHAR2(50 CHAR) );
Table created.
SQL> Begin
For i in 1..9
Loop
INSERT INTO zztest VALUES(i, 'MARTIN', 'TOM');
End loop ;
End ;
/
PL/SQL procedure successfully completed.
SQL> INSERT INTO zztest VALUES(10, 'DUPONT', 'David');
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname=>'ZZTEST', cascade=>TRUE);
I execute the SELECT without the ORDER BY : SQL_ID akk1mjcvrdz1q, Plan hash value: 3582063246.
SQL> SELECT * FROM zztest;
ID NOM PRENOM
---------- ------------------------------
1 MARTIN TOM
2 MARTIN TOM
3 MARTIN TOM
4 MARTIN TOM
5 MARTIN TOM
6 MARTIN TOM
7 MARTIN TOM
8 MARTIN TOM
9 MARTIN TOM
10 DUPONT David
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID akk1mjcvrdz1q, child number 0
-------------------------------------
SELECT * FROM zztest
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| ZZTEST | 10 | 140 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
13 rows selected.
Now with the ORDER BY : SQL_ID a1k6qugb0hdz1, Plan hash value: 4012859079.
The execution plan is different, it is OK, there is a SORT.
SQL> SELECT * FROM zztest ORDER BY nom;
ID NOM PRENOM
---------- -------------------------------
10 DUPONT David
2 MARTIN TOM
3 MARTIN TOM
4 MARTIN TOM
6 MARTIN TOM
7 MARTIN TOM
8 MARTIN TOM
9 MARTIN TOM
1 MARTIN TOM
5 MARTIN TOM
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a1k6qugb0hdz1, child number 0
-------------------------------------
SELECT * FROM zztest ORDER BY nom
Plan hash value: 4012859079
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 10 | 140 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ZZTEST | 10 | 140 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
14 rows selected.
Now I want to associate the plan 3582063246 to the SQL_ID a1k6qugb0hdz1 : an plan without SORT for a SELECT with an ORDER BY.
For that, I create a baseline for the order with the ORDER BY.
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'a1k6qugb0hdz1');
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENA ACC FIX
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5.1359E+18 SQL_PLAN_4fjk2k2pjac2k192f0eef 28-SEP-19 09.52.12.000000000 AM YES YES NO
Now I want to add to this baseline the execution plan of the SQL_ID akk1mjcvrdz1q.
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_47464290ab153052', sql_id => 'akk1mjcvrdz1q', plan_hash_value => '3582063246');
Oh problem, only one plan in the baseline, why the other is not here? I know there is a match with the signature of the order and the execution plan in the shared pool, is it why it failed?
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENA ACC FIX
--------------------------------------------------------------------------- --- --- ---------------
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5.1359E+18 SQL_PLAN_4fjk2k2pjac2k192f0eef 28-SEP-19 10.42.42.000000 AM YES YES NO
Can you tell me how to do that? Thank you very very much for your answer!
My client say, we don't need this ORDER BY, so remove itAre they really
sure about that?
Most selects without an order by => bug waiting to happen!
Anyway I'm not sure why you're not seeing the second plan loaded as a baseline.
"It works for me":
CREATE TABLE zztest(id NUMBER(10) CONSTRAINT zztest_pk_id PRIMARY KEY, nom VARCHAR2(50 CHAR), prenom VARCHAR2(50 CHAR) );
Begin
For i in 1..9
Loop
INSERT INTO zztest VALUES(i, 'MARTIN', 'TOM');
End loop ;
End ;
/
INSERT INTO zztest VALUES(10, 'DUPONT', 'David');
commit;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>user, tabname=>'ZZTEST', cascade=>TRUE);
SELECT * FROM zztest;
SELECT * FROM zztest ORDER BY nom;
variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'a1k6qugb0hdz1');
select sql_text,
sql_handle,
signature,
plan_name,
created,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where upper (sql_text) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENABLED ACCEPTED FIXED
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2k192f0eef 03-OCT-2019 10.17.57.000000000 YES YES NO
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_47464290ab153052', sql_id => 'akk1mjcvrdz1q', plan_hash_value => '3582063246');
select sql_text,
sql_handle,
signature,
plan_name,
created,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where upper (sql_text) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENABLED ACCEPTED FIXED
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2k192f0eef 03-OCT-2019 10.17.57.000000000 YES YES NO
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2kb63e4ae3 03-OCT-2019 10.17.57.000000000 YES YES NO In any case, this approach won't help. The plan without the sort changes the behaviour of the query. So the database won't use it:
exec :cnt := dbms_spm.drop_sql_plan_baseline('SQL_47464290ab153052','SQL_PLAN_4fjk2k2pjac2k192f0eef');
set serveroutput off
SELECT * FROM zztest ORDER BY nom;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM zztest ORDER BY nom
Plan hash value: 4012859079
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS FULL| ZZTEST |
-------------------------------------
Note
-----
- Failed to use SQL plan baseline for this statement As the note says, it can't use this baseline.
If you must remove the order by (which I'd push back heavily on), the SQL translation framework will let you intercept the query and rewrite it.
https://docs.oracle.com/en/database/oracle/oracle-database/19/drdaa/SQL-translation-framework-overview.html#GUID-C7814DA0-96BA-4D15-A348-6F0960D4475F