繫結變數分級導致共享池佔用過大
select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
INSERT INTO BPM_PROCINST ( ID, GMT_MODIFIED, MODIFIER, IS_DELETED, VERSION, PROCDEF, STATE, PROCSTART_TIME, PROCEND_TIME, OLD_PROCINST, SUPER_PROCINST, SUPER_NODENAME, SAVE_SIGN, LINE_NOTE, REQUEST_PERSONID, REQUEST_PERSONNAME, REQUEST_ROLE, REQUEST_ORGID, REQUEST_ORGPATH, DOC_NO, SYSTEM_TYPE, DOC_TYPE, SUB_DOCTYPE, DOC_OBJTYPE, DOC_OBJID, CUST_MEMBER, CUST_ID, CUST_NAME, FLAG, COL1, COL2, COL3 ) VALUES ( :1, sysdate, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31 )
select BIND_MISMATCH,count(*) from v$sql_shared_cursor where sql_id='576c1s91gua19' group by BIND_MISMATCH;
BIND_MISMATCH COUNT(*)
-------------------- ----------
Y 2635
N 1
select sum(SHARABLE_MEM)/1024/1024 M from v$sql where sql_id='576c1s91gua19';
M
----------
120.423182
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
INSERT INTO BPM_PROCINST ( ID, GMT_MODIFIED, MODIFIER, IS_DELETED, VERSION, PROCDEF, STATE, PROCSTART_TIME, PROCEND_TIME, OLD_PROCINST, SUPER_PROCINST, SUPER_NODENAME, SAVE_SIGN, LINE_NOTE, REQUEST_PERSONID, REQUEST_PERSONNAME, REQUEST_ROLE, REQUEST_ORGID, REQUEST_ORGPATH, DOC_NO, SYSTEM_TYPE, DOC_TYPE, SUB_DOCTYPE, DOC_OBJTYPE, DOC_OBJID, CUST_MEMBER, CUST_ID, CUST_NAME, FLAG, COL1, COL2, COL3 ) VALUES ( :1, sysdate, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31 )
select BIND_MISMATCH,count(*) from v$sql_shared_cursor where sql_id='576c1s91gua19' group by BIND_MISMATCH;
BIND_MISMATCH COUNT(*)
-------------------- ----------
Y 2635
N 1
select sum(SHARABLE_MEM)/1024/1024 M from v$sql where sql_id='576c1s91gua19';
M
----------
120.423182
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-701489/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 共享池之九:繫結變數與session_cached_cursors變數Session
- Oracle繫結變數分級(Bind Graduation)Oracle變數
- [20191213]共享池繫結變數的值在哪裡.txt變數
- Oracle資料傾斜導致的問題-有繫結變數Oracle變數
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 繫結變數,組合查詢方式,導致CBO錯誤一例變數
- 繫結變數變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- 單個分割槽索引失效導致繫結變數查詢無法使用索引索引變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數Oracle變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 【優化】使用繫結變數 OR 不使用繫結變數,這不是問題!優化變數
- ORACLE 繫結變數用法總結Oracle變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數,這不是問題!變數
- 繫結變數窺測的演變變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數(bind variable)Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 通過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 在php中使用繫結變數的方法(Oracle SQL共享的機制)(轉)PHP變數OracleSQL
- 異常程式導致大量資源佔用
- MySQL高階特性——繫結變數MySql變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- 繫結變數之基本概念變數
- ORACLE 獲取繫結變數值Oracle變數
- 繫結變數及其優缺點變數