Oracle 无法从共享池中删除解释计划

你好，
我有一个查询的问题。基本上我有2个相同的查询与2个不同的SQL_IDs。一个正在使用快速执行计划，另一个正在使用非常慢的计划。因此，我决定从缓存中删除慢速计划，但DB告诉我对象不是共享池，即使它显示在SQLAREA中。

快速计划: 1837086266
缓慢的计划: 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.

另外，我不明白为什么它对同一查询使用不同的SQL_ID和不同的计划。问题是，当我从SQL开发人员运行查询时，它的性能很好，但是当从应用程序运行相同的查询时，它的性能非常糟糕。所有会话参数都是相同的，绑定中的值也是相同的。

这里有一件重要的事情要提到。查询是由应用程序生成的，所以我无法更改查询本身。我只能在DB级别上进行修改。

请帮忙。
彼得

您正在选择PLAN_HASH_VALUE。您需要使用语句哈希 (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.

那他们不是identical查询!

也就是说，SQL id是基于确切的SQL文本。空格，大小写等的差异将导致不同的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.

一定有something不同!

这可能只是格式差异。导致优化器在SQL Dev中硬解析您的查询。使它获得更好的计划。但是也有其他差异。

获得计划大纲可能有助于消除一些差异:

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                         
  */   

但也要检查NLS设置等。如果您在此处发布计划，我们可以查看是否可以发现任何内容。