Skip to Main Content
  • Questions
  • Cannot remove explain plan from shared pool

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: March 06, 2020 - 8:11 am UTC

Last updated: June 30, 2021 - 3:03 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello,
I have a problem with a query. Basically I have 2 identical queries with 2 different SQL_IDs. One is using a fast execution plan and the other one is using the very slow one. Hence I decided to remove the slow plan from cache but DB tells me the object is not is shared pool even when it is displayed in SQLAREA.

The fast plan: 1837086266
The slow plan: 634079431

select sql_id,address,PLAN_HASH_VALUE from gv$sqlarea where sql_id in ('8wk9vg1c32z0m','5uu888g8j1m7b'); 

SQL_ID        ADDRESS                                  PLAN_HASH_VALUE
------------- ---------------- ---------------------------------------
5uu888g8j1m7b 00000000CFCB2CD8                              1837086266
8wk9vg1c32z0m 000000007596CB38                               634079431


exec DBMS_SHARED_POOL.PURGE ('000000007596CB38,634079431', 'C');

Error starting at line : 157 in command -
exec DBMS_SHARED_POOL.PURGE ('000000007596CB38,634079431', 'C')
Error report -
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 120
ORA-06512: at line 1
06570. 00000 -  "shared pool object does not exist, cannot be pinned/purged"
*Cause:    The specified shared pool shared cursor could not be found,
           therefore it cannot be pinned/purged.
*Action:   Make sure that a correct shared cursor name is given.  Names
           are a string of the form 'HHHHHHHH,SDDDDDDDDDD' where the H's
           are an 8 digit hex number from the 'address' column of v$sqlarea,
           and the D's are a 1 to 10 digit decimal number with an optional
           leading sign (from the 'hash_value' column)
*Action:   Remove the procedure from the calling stored procedure.


Also, I don't understand why it is using different SQL_ID with a different plan for the same query. Problem is that when I ran the query from SQL Developer it performs great but when the same query is running from application it performs terribly. All session parameters are the same also values in BINDs are the same.

One important thing to mention here. The query is generated by the application so I cannot change the query itself. I can only do modifications on the DB level.

Please help.
Peter

and Chris said...

You're selecting the PLAN_HASH_VALUE. You need to use the statement hash (HASH_VALUE)

select * from t;

select sql_id, address, hash_value 
from   v$sqlarea
where  sql_text = 'select * from t';

SQL_ID           ADDRESS             HASH_VALUE   
89km4qj1thh13    00000000849BECE8       1134051363 

exec sys.dbms_shared_pool.purge ('00000000849BECE8, 1134051363', 'C');

select sql_id, address, hash_value 
from   v$sqlarea
where  sql_text = 'select * from t';

no rows selected


Also, I don't understand why it is using different SQL_ID with a different plan for the same query.

Then they're not identical queries!

That said, the SQL id is based on the exact SQL text. Differences in whitespace, case, etc. will lead to a different SQL id:

select * from t;
SELECT * FROM T;

select sql_text, sql_id
from   v$sqlarea
where  lower ( sql_text ) = 'select * from t';

SQL_TEXT           SQL_ID          
select * from t    89km4qj1thh13    
SELECT * FROM T    6k1fc899x4ud2    


Problem is that when I ran the query from SQL Developer it performs great but when the same query is running from application it performs terribly. All session parameters are the same also values in BINDs are the same.

There must be something different!

This could just be formatting differences. Causing the optimizer to hard parse your query in SQL Dev. Enabling it to get a better plan. But chances are there other differences too.

Getting the plan outline may help throw up some of the differences:

set serveroutput off
select * from t;
select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));

Plan hash value: 1601196873                    
                                               
----------------------------------             
| Id  | Operation         | Name |             
----------------------------------             
|   0 | SELECT STATEMENT  |      |             
|   1 |  TABLE ACCESS FULL| T    |             
----------------------------------             
                                               
Outline Data                                   
-------------                                  
                                               
  /*+                                          
      BEGIN_OUTLINE_DATA                       
      IGNORE_OPTIM_EMBEDDED_HINTS              
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')    
      DB_VERSION('12.2.0.1')                   
      ALL_ROWS                                 
      OUTLINE_LEAF(@"SEL$1")                   
      FULL(@"SEL$1" "T"@"SEL$1")               
      END_OUTLINE_DATA                         
  */   


But check NLS settings, etc. too. If you post the plans here we can see if there's anything we can spot.

Rating

  (5 ratings)

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

Comments

Peter, March 09, 2020 - 3:49 pm UTC

Thank you very much for your thoughts.
You are right with different SQL_IDs. I realized that I omitted one bind variable in "the fast select" which then appears in v$session as CLIENT_IDENTIFIER (in the query it is this part 'SWToolsObjMgr_enu,SES0,41943060,SADMIN,000000fe5e5f0697:0,,Repository Applet,WT Repository Applet List View').

Anyway, my issue still persists but now only with one SQL_ID. I tried to display the plan as you suggested but there is apparently something wrong (don't know what).
Also when I checked the SQL_ID of running SQL it is different (this time it must be the same I checked it several times). When I execute the query from SQL Developer or from the application both are running slow and both give me the same SQL_ID which is '8wk9vg1c32z0m'. When I ran the explain plan for the query it shows different SQL_ID as you can see.

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE;
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE;
Session altered.

SQL>
Session altered.

SQL>

Session altered.

SQL>
SQL> set serveroutput off
SQL> explain plan for
SELECT
      T8.CONFLICT_ID,
      T8.LAST_UPD,
      T8.CREATED,
      T8.LAST_UPD_BY,
      T8.CREATED_BY,
      T8.MODIFICATION_NUM,
      T8.WS_SRC_ID,
      T8.ASSOC_APPLET_NAME,
      T8.AUTO_QUERYMODE,
      T8.BGND_BMAP_NAME,
      T8.BGND_BMAP_STYLE,
      T8.BROWSER_CLASS,
      T8.BUSCOMP_NAME,
      T8.CLASS_NAME,
      T8.COMMENTS,
      T8.HTML_NUM_ROWS,
      T8.HTML_POPUP_DM,
      T8.HEIGHT,
      T8.HELP_ID,
      T8.UPG_ICL_PATH_CD,
      T8.INACTIVE_FLG,
      T8.INS_APPLET_NAME,
      T8.INSERT_POSITION,
      T5.NAME,
      T8.MAIL_ADD_FLD_NAME,
      T8.MAIL_TEMPLATE,
      T8.MODULE_NAME,
      T8.NAME,
      T8.NODELETE,
      T8.NOINSERT,
      T8.NOMERGE,
      T8.NOUPDATE,
      T8.OBJ_LOCKED_LANG,
      T8.OBJ_LOCKED_FLG,
      T8.OBJ_LOCKED_BY,
      T6.LOGIN,
      T8.OBJ_LOCKED_DATE,
      T8.POPUP_DIMENSION,
      T8.PROJECT_ID,
      T7.LOCKED_FLG,
      T7.LOCKED_BY,
      T7.NAME,
      T7.UIFREEZE_FLG,
      T7.UIFREEZE_BY,
      T8.REPOSITORY_ID,
      T8.SCRIPTED_FLG,
      T8.SRCHSPEC,
      T8.TEXT_STYLE_NAME,
      T2.STRING_VALUE,
      T4.STRING_VALUE,
      T1.TITLE,
      T8.TITLE_REF,
      T8.TYPE,
      T8.UPGD_ANCESTOR,
      T8.UPG_BEHAVIOR, 48
      T8.WIDTH,
      T8.ROW_ID,
      T8.WS_ID,
      T8.WS_OBJ_VER,
      T8.WS_DELETE_FLG,
      T3.WS_SRC_ID,
      T3.PAR_ROW_ID,
      T3.ROW_ID,
      T3.WS_ID,
      T3.WS_OBJ_VER,
      T3.WS_DELETE_FLG,
     'SWToolsObjMgr_enu,SES0,41943060,SADMIN,000000fe5e5f0697:0,,Repository Applet,WT Repository Applet List View'
   FROM
       (SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_APPLET_INTL WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T1,
       (SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHE 70  RE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T2,
       (SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_APPLET_RX WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET_RX WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SR 71  C_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T3,
       (SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_SYM_STR_INTL WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T4,
       (SELECT TBL1.* FROM (SELECT * FROM SIEBEL.S_LIST WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_LIST WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T5,
       SIEBEL.S_USER T6,
       SIEBEL.S_PROJECT T7,
       (SELECT TBL1.* FROM (SELECT * FROM SIEB 72  EL.S_APPLET WHERE ((WS_ID = '1@21'))) TBL1 LEFT OUTER JOIN (SELECT * FROM SIEBEL.S_APPLET WHERE ((WS_ID = '1@21'))) TBL2 ON (TBL1.WS_SRC_ID = TBL2.WS_SRC_ID AND TBL1.WS_OBJ_VER < TBL2.WS_OBJ_VER) WHERE TBL2.WS_SRC_ID IS NULL AND TBL1.WS_DELETE_FLG='N') T8
   WHERE
      T8.WS_SRC_ID = T1.APPLET_ID (+) AND T1.APP_CD (+) = 'STD' AND T1.INACTIVE_FLG (+) = 'N' AND T1.LANG_CD (+) = 'ENU' AND
      T8.OBJ_LOCKED_BY = T6.PAR_ROW_ID (+) AND
      T8.PROJECT_ID = T7.ROW_ID (+) AND
      T8.WS_SRC_ID = T5.APPLET_ID (+) AND
      T8.REPOSITORY_ID = T2.REPOSITORY_ID (+) AND T8.TITLE_REF = T2.SYM_STR_KEY (+) AND T2.APP_CD (+) = 'STD' AND T2.INACTIVE_FLG (+) = 'N' AND T2.LANG_CD (+) = 'ENU' AND
      T8.REPOSITORY_ID = T4.REPOSITORY_ID (+) AND T8.TITLE_REF  73  = T4.SYM_STR_KEY (+) AND T4.APP_CD (+) = 'STD' AND T4.INACTIVE_FLG (+) = 'N' AND T4.LANG_CD (+) = 'ENU' AND
      T8.WS_SRC_ID = T3.PAR_ROW_ID (+) AND
      (T8.INACTIVE_FLG = 'N')
   ORDER BY
      T8.NAME /*+ opt_param('_hash_join_enabled', 'true') */
      /*+ opt_param('_optimizer_sortmerge_join_enabled', 'true') */; 

Explained.

SQL> select * from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6dmr45mry95hb, child number 0

explain plan for SELECT       T8.CONFLICT_ID,       T8.LAST_UPD,
T8.CREATED,       T8.LAST_UPD_BY,       T8.CREATED_BY,
T8.MODIFICATION_NUM,       T8.WS_SRC_ID,       T8.ASSOC_APPLET_NAME,
   T8.AUTO_QUERYMODE,       T8.BGND_BMAP_NAME,
T8.BGND_BMAP_STYLE,       T8.BROWSER_CLASS,       T8.BUSCOMP_NAME,
 T8.CLASS_NAME,       T8.COMMENTS,       T8.HTML_NUM_ROWS,
T8.HTML_POPUP_DM,       T8.HEIGHT,       T8.HELP_ID,
T8.UPG_ICL_PATH_CD,       T8.INACTIVE_FLG,       T8.INS_APPLET_NAME,
   T8.INSERT_POSITION,       T5.NAME,       T8.MAIL_ADD_FLD_NAME,

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
T8.MAIL_TEMPLATE,       T8.MODULE_NAME,       T8.NAME,
T8.NODELETE,       T8.NOINSERT,       T8.NOMERGE,       T8.NOUPDATE,
   T8.OBJ_LOCKED_LANG,       T8.OBJ_LOCKED_FLG,       T8.OBJ_LOCKED_BY,
      T6.LOGIN,       T8.OBJ_LOCKED_DATE,       T8.POPUP_DIMENSION,
  T8.PROJECT_ID,       T7.LOCKED_FLG,       T7.LOCKED_BY,
T7.NAME,       T7.UIFREEZE_FLG,       T7.UIFREEZE_BY,       T8

NOTE: cannot fetch plan for SQL_ID: 6dmr45mry95hb, 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_p
lan)

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


22 rows selected.


I ran explain for the query once again but then I used "format all" and then I got the plan. I give the output into the next comment as it doesn't fit into this frame.


Chris Saxon
March 10, 2020 - 9:08 am UTC

Hmmm, do you need to set those parameters at the start?

And when you use "explain plan for..." you're running a different statement. So you'll have a different SQL_ID!

set feed on sql_id
select count(*) from t;

  COUNT(*)
----------
     10000

1 row selected.

SQL_ID: cyzznbykb509s

explain plan for
select count(*) from t;

Explained.

SQL_ID: 4858bq67u4kjg


Really what you want to do is something like:

set serveroutput off

select /*+ gather_plan_statistics */* from ...

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

Peter, March 09, 2020 - 4:06 pm UTC

Hmm, it doesn't fit even here. It would be easier if there would be an option to attach a file. Anyway, the problem is that the plain expect that overall query time will be "00:00:01" but when I did statistics on the query the real time appears.
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.09       2.11          0        183         13           0
Fetch       10    719.62     722.19          0  282592240          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12    721.72     724.31          0  282592423         13          10


I collected statistics on all involved tables but still, it uses the very slow plan. Don't know how to tell DB to use the fast one.

Thanks Peter
Chris Saxon
March 10, 2020 - 9:10 am UTC

How big is the plan?!

Don't know how to tell DB to use the fast one.

Ultimately it's because there's a mistake in the row/costing estimates somewhere. You can look into tweaking the stats to get this. Or check SQL Plan Management to force the optimizer to use the "right" plan:

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

supply SQL with different plan

PeterP, March 12, 2020 - 10:02 am UTC

Yes, those parameters are needed as it simulates application behavior.

Yes, I know that SQL_ID change and that is actually the problem...

Even this is not working and it gives me the same "NOTE" as before.
Really what you want to do is something like:

set serveroutput off

select /*+ gather_plan_statistics */* from ...

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));


I was trying the steps in PDF provided and actually again it fails on that I have 2 SQL_IDs. So SQL_HANDLE is different. When I tried ti alter sql_plan it tells me that there is no such plan for given handel (which is true of course).

SQL_HANDLE PLAN_NAME
------------------------------ ----------------------------------
SQL_0394051517fbcf86 SQL_PLAN_075052nbzrmw6b7abe75a
SQL_ff1ef7a9a751e020 SQL_PLAN_gy7rrp6mp3s10bd18a36a

So what I basically need to achieve is to tell DB to use this plan_name "SQL_PLAN_075052nbzrmw6b7abe75a" for this SQL_handle "SQL_ff1ef7a9a751e020"
Chris Saxon
March 12, 2020 - 10:38 am UTC

You can create a baseline using a plan from a "different" SQL statment. Tom describes how to do this at:

https://blogs.oracle.com/oraclemagazine/on-table-updates-and-sql-plan-baselines

Niko, June 23, 2021 - 11:55 am UTC

Thanks for the answers!

ASK TOM Team:
Can you tell us if DBMS_SHARED_POOL.PURGE will work also for currently executed cursors?

We're facing performance issues on Prod. Systems from time to time due to execution plan regressions and want to apply a SQL Plan Baseline or a SQL Patch adhoc without interrupting the process or even kill sessions. But that seems not to work as the currently executed cursors...
Can you clarify that?

Thanks.
Chris Saxon
June 23, 2021 - 1:37 pm UTC

You can't change the plan a statement uses part-way through execution. Once it starts it uses that plan until it finishes. A new baseline will only be available the next time the statement runs. If you want to change the plan, you need to abort the process & restart it.

DBMS_SHARED_POOL.PURGE also leaves the cursor in the cache while it's executing. Think about what it would mean to purge the cursor while the statement is running - this is the information the database needs to process the statement. Remove this and the query would have to stop!

MOS notes 2214994.1 & 2655262.1 discuss this in more detail.

Niko, June 28, 2021 - 4:48 pm UTC

Thanks Chris for your clarification!

BR
Niko
Connor McDonald
June 30, 2021 - 3:03 am UTC

glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.