1

Topic: Cursor pin:s

During a certain time frame all basis starts to hang in these waitings.
All waitings judging by ASH have on one request:
[spoiler Pictures]
[img=http://dl4.joxi.net/drive/2018/03/05/0018/1912/1206136/36/2cf1a917e7.jpg]
[img=http://dl4.joxi.net/drive/2018/03/05/0018/1912/1206136/36/2d0b5bc1f1.jpg]
[/spoiler]
Request by sight the extremely simple, problems with parsing should not be

function fId (sCode in varchar2) return integer as
nRet C_USR.ID%TYPE;
begin
select ID into nRet from C_USR where CODE = sCode;
return nRet;
exception
when NO_DATA_FOUND then
return null;
end;

With the plan the request like too should not have problems

 SQL_ID 5x3hrqrr4pnvx, child number 0
-------------------------------------
SELECT ID FROM C_USR WHERE CODE =:B1
Plan hash value: 163455779
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | C_USR |
|* 2 | INDEX UNIQUE SCAN | AK_C_USR_ISP |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access ("CODE" =:B1)
Note
-----
- rule based optimizer used (consider using cbo)

In what there can be a reason?

2

Re: Cursor pin:s

Still there is a suspicion that probably problem that the request permanently takes off from shared_poola

3

Re: Cursor pin:s

;
What is the version?

 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

And why RBO it is included?

 So it was historically added 

Parameter cursor_sharing?

 cursor_sharing = EXACT 

The reason can be in a considerable quantity hard parse.
use?
We try to use.

select regexp_replace (sql_text, ' (\d +) | ("*?") ', ' * ') str, count (1)
from v$sqlarea t
group by regexp_replace (sql_text, ' (\d +) | ("*?") ', ' * ')
having count (1)> 10
order by count (1) desc

  gives about the following
[img=http://joxi.ru/5mdoN8McvzVOLr.jpg]
I do not consider result of this request as something that could lead to it

4

Re: Cursor pin:s

;
[img=http://joxi.ru/82QMpd5C1xvE52.jpg]

5

Re: Cursor pin:s

feagor;

SELECT / * + OPT_PARAM (' _fix_control ' ' 9088510:0 ') NO_XML_QUERY_REWRITE cursor_sharing_exact */count (*) FROM SYS.DUAL WHERE 1=1
SELECT / * + OPT_PARAM (' _fix_control ' ' 9088510:0 ') NO_XML_QUERY_REWRITE cursor_sharing_exact */count (*) FROM SYS.DUAL WHERE 1 = 1

6

Re: Cursor pin:s

;
Thanks, something similar to truth