ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > high VERSION_COUNT案例1

high VERSION_COUNT案例1

原创 Oracle 作者：monkeybron 时间：2015-03-30 16:57:44 0 删除 编辑

点击(此处)折叠或打开

  1. SQL> set pages 2000 lines 100
  2. SQL> SELECT b.*
  3.   2 FROM v$sqlarea a ,
  4.   3 TABLE(version_rpt(a.sql_id)) b
  5.   4 WHERE loaded_versions >=100;

  7. COLUMN_VALUE
  8. ----------------------------------------------------------------------------------------------------
  9. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:38
  10. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  11. ==================================================================
  12. Addr: 000000085BE266C8 Hash_Value: 1258960866 SQL_ID 4v3xxq15hndz2
  13. Sharable_Mem: 5552316 bytes Parses: 61210 Execs:61209
  14. Stmt:
  15. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  16. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  17. 2 _TIME\",\"REPLACED\",\"STATUS\",\"MESSAGE_ID\",\"REQUEST\",\"RESPONSE\") va
  18. 3 lues (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ) RETURN
  19. 4 ING ROWID INTO :13
  20. 5

  22. Versions Summary
  23. ----------------
  24. ROLL_INVALID_MISMATCH :1
  25. PURGED_CURSOR :153 --->cursor marked for purging,the cursor has been marked for puring with dbms_shared_pool.purge
  26. BIND_LENGTH_UPGRADEABLE :166 --->BIND LENGTH UPGRADEABLE and could not be shared because a bind variable size smaller than the
  27.                                        new value beging inserted(marked as bind_mismatch in earlier versions)
  28. Total Versions:168

  30. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  31. cursor_sharing = EXACT
  32. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  34. Plan Hash Value Summary
  35. -----------------------
  36. Plan Hash Value Count
  37. =============== =====
  38.               0 169

  40. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  41. Details for ROLL_INVALID_MISMATCH :

  43. No details available
  44. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  45. Details for PURGED_CURSOR :

  47. No details available
  48. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  49. Details for BIND_LENGTH_UPGRADEABLE :

  51. Consolidated details for BIND* columns:
  52. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  53. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  55. from v$sql_bind_capture
  56. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  57. ======== ======== =============== =============== ======== =============== =================
  58.      169 1 2000 2000 1 No (,)
  59.      169 2 32 128 1 Yes (,)
  60.      169 3 32 128 1 Yes (,)
  61.      169 4 32 2000 1 Yes (,)
  62.      169 5 32 2000 1 Yes (,)
  63.      169 6 32 128 1 Yes (,)
  64.      169 7 22 22 2 No (,)
  65.      169 8 22 22 2 No (,)
  66.      169 9 128 128 1 No (,)
  67.      169 10 32 32 1 No (,)
  68.      169 11 32 32 1 No (,)
  69.      169 12 32 32 1 No (,)
  70.      169 13 2000 2000 1 No (,)

  72. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  73. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  74. =========== ================= ============= ============
  75.           0 0 0 2
  76. ####
  77. To further debug Ask Oracle Support for the appropiate level LLL.
  78. alter session set events
  79.  \'immediate trace name cursortrace address 1258960866, level LLL\';
  80. To turn it off do use address 1, level 2147483648
  81. ================================================================
  82. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:38
  83. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  84. ==================================================================
  85. Addr: 0000000843AA43D0 Hash_Value: 4235483012 SQL_ID 8xwrpdby78pw4
  86. Sharable_Mem: 13499679 bytes Parses: 37193 Execs:37180
  87. Stmt:
  88. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  89. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  90. 2 _TIME\",\"PARTICIPANT\",\"REPLACED\",\"END_TIME\",\"STATUS\",\"MESSAGE_ID\"
  91. 3 ,\"REQUEST\") values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11
  92. 4 ,:12 ,:13 ) RETURNING ROWID INTO :14
  93. 5

  95. Versions Summary
  96. ----------------
  97. BIND_MISMATCH :407
  98. ROLL_INVALID_MISMATCH :5
  99. PURGED_CURSOR :401
  100. BIND_LENGTH_UPGRADEABLE :406

  102. Total Versions:410

  104. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  105. cursor_sharing = EXACT
  106. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  108. Plan Hash Value Summary
  109. -----------------------
  110. Plan Hash Value Count
  111. =============== =====
  112.               0 411

  114. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  115. Details for BIND_MISMATCH :

  117. Consolidated details for BIND* columns:
  118. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  119. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  121. from v$sql_bind_capture
  122. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  123. ======== ======== =============== =============== ======== =============== =================
  124.      411 1 2000 2000 1 No (,)
  125.      411 2 32 32 1 No (,)
  126.      411 3 32 128 1 Yes (,)
  127.      411 4 128 2000 1 Yes (,)
  128.      411 5 32 128 1 Yes (,)
  129.      411 6 32 128 1 Yes (,)
  130.      411 7 22 22 2 No (,)
  131.      411 8 4000 4001 1 Yes (,)
  132.      411 9 22 22 2 No (,)
  133.      411 10 22 22 2 No (,)
  134.      411 11 128 128 1 No (,)
  135.      411 12 2000 2000 1 No (,)
  136.      411 13 4000 4001 1 Yes (,)
  137.      411 14 2000 2000 1 No (,)

  139. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  140. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  141. =========== ================= ============= ============
  142.           0 0 0 8
  143. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  144. Details for ROLL_INVALID_MISMATCH :

  146. No details available
  147. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  148. Details for PURGED_CURSOR :

  150. No details available
  151. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  152. Details for BIND_LENGTH_UPGRADEABLE :

  154. Details shown already.
  155. ####
  156. To further debug Ask Oracle Support for the appropiate level LLL.
  157. alter session set events
  158.  \'immediate trace name cursortrace address 4235483012, level LLL\';
  159. To turn it off do use address 1, level 2147483648
  160. ================================================================
  161. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:38
  162. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  163. ==================================================================
  164. Addr: 000000084BE1D040 Hash_Value: 2045618218 SQL_ID f9x5fujwyva1a
  165. Sharable_Mem: 92587701 bytes Parses: 15455 Execs:15459
  166. Stmt:
  167. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  168. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  169. 2 _TIME\",\"REPLACED\",\"STATUS\",\"MESSAGE_ID\") values (:1 ,:2 ,:3 ,:4
  170. 3 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ) RETURNING ROWID INTO :11
  171. 4

  173. Versions Summary
  174. ----------------
  175. BIND_MISMATCH :1
  176. PURGED_CURSOR :3724
  177. BIND_LENGTH_UPGRADEABLE :3724

  179. Total Versions:3724

  181. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  182. cursor_sharing = EXACT
  183. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  185. Plan Hash Value Summary
  186. -----------------------
  187. Plan Hash Value Count
  188. =============== =====
  189.               0 3725

  191. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  192. Details for BIND_MISMATCH :

  194. Consolidated details for BIND* columns:
  195. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  196. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  198. from v$sql_bind_capture
  199. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  200. ======== ======== =============== =============== ======== =============== =================
  201.     3725 1 2000 2000 1 No (,)
  202.     3725 2 128 128 1 No (,)
  203.     3725 3 32 128 1 Yes (,)
  204.     3725 4 32 2000 1 Yes (,)
  205.     3725 5 32 128 1 Yes (,)
  206.     3725 6 32 128 1 Yes (,)
  207.     3725 7 22 22 2 No (,)
  208.     3725 8 22 22 2 No (,)
  209.     3725 9 128 128 1 No (,)
  210.     3725 10 32 32 1 No (,)
  211.     3725 11 2000 2000 1 No (,)

  213. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  214. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  215. =========== ================= ============= ============
  216.           0 0 0 1
  217. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  218. Details for PURGED_CURSOR :

  220. No details available
  221. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  222. Details for BIND_LENGTH_UPGRADEABLE :

  224. Details shown already.
  225. ####
  226. To further debug Ask Oracle Support for the appropiate level LLL.
  227. alter session set events
  228.  \'immediate trace name cursortrace address 2045618218, level LLL\';
  229. To turn it off do use address 1, level 2147483648
  230. ================================================================
  231. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:39
  232. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  233. ==================================================================
  234. Addr: 000000084BE1FAA8 Hash_Value: 885503481 SQL_ID 2wrgpnwucgdgt
  235. Sharable_Mem: 26579460 bytes Parses: 39019 Execs:39016
  236. Stmt:
  237. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  238. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  239. 2 _TIME\",\"REPLACED\",\"END_TIME\",\"STATUS\",\"MESSAGE_ID\",\"REQUEST\",\"RE
  240. 3 SPONSE\") values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:
  241. 4 12 ,:13 ) RETURNING ROWID INTO :14
  242. 5

  244. Versions Summary
  245. ----------------
  246. AUTH_CHECK_MISMATCH :386
  247. BIND_MISMATCH :449
  248. TRANSLATION_MISMATCH :386
  249. ROLL_INVALID_MISMATCH :447
  250. PURGED_CURSOR :377
  251. BIND_LENGTH_UPGRADEABLE :823

  253. Total Versions:829

  255. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  256. cursor_sharing = EXACT
  257. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  259. Plan Hash Value Summary
  260. -----------------------
  261. Plan Hash Value Count
  262. =============== =====
  263.               0 830

  265. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  266. Details for AUTH_CHECK_MISMATCH :

  268.   # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
  269. ========== =============== ================= ===================
  270.         99 87 87 CORDYSDX
  271.        731 85 85 CORDYSBPM
  272. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  273. Details for BIND_MISMATCH :

  275. Consolidated details for BIND* columns:
  276. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  277. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  279. from v$sql_bind_capture
  280. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  281. ======== ======== =============== =============== ======== =============== =================
  282.      830 1 2000 2000 1 No (,)
  283.      830 2 32 128 1 Yes (,)
  284.      830 3 32 32 1 No (,)
  285.      830 4 32 2000 1 Yes (,)
  286.      830 5 32 128 1 Yes (,)
  287.      830 6 32 128 1 Yes (,)
  288.      830 7 22 22 2 No (,)
  289.      830 8 22 22 2 No (,)
  290.      830 9 22 22 2 No (,)
  291.      830 10 32 128 1 Yes (,)
  292.      830 11 32 2000 1 Yes (,)
  293.      830 12 32 32 1 No (,)
  294.      830 13 32 32 1 No (,)
  295.      731 14 2000 2000 1 No (,)
  296.       99 14 16 16 11 No (,)

  298. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  299. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  300. =========== ================= ============= ============
  301.           0 0 0 5
  302. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  303. Details for TRANSLATION_MISMATCH :

  305. No objects in the plans with same name and different owner were found.
  306. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  307. Details for ROLL_INVALID_MISMATCH :

  309. No details available
  310. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  311. Details for PURGED_CURSOR :

  313. No details available
  314. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  315. Details for BIND_LENGTH_UPGRADEABLE :

  317. Details shown already.
  318. ####
  319. To further debug Ask Oracle Support for the appropiate level LLL.
  320. alter session set events
  321.  \'immediate trace name cursortrace address 885503481, level LLL\';
  322. To turn it off do use address 1, level 2147483648
  323. ================================================================
  324. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:39
  325. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  326. ==================================================================
  327. Addr: 0000000853F8B9B8 Hash_Value: 1335082476 SQL_ID 91pgqq97t7ggc
  328. Sharable_Mem: 19173188 bytes Parses: 37449 Execs:36872
  329. Stmt:
  330. 0 insert into CORDYS_LOG (COUNTER, PRIORITY, CATEGORY, HOP_COUNT,
  331. 1 CORRELATION_ID, APPLICATION, CORDYS_USER, LOCALIZABLE_MESSAGE_ID
  332. 2 , SENDER_COMPONENT, PROCESSOR_DN, THREAD, MESSAGE, LOCALIZABLE_M
  333. 3 ESSAGE, THROWABLE, NDC, MDC) values (:1 , :2 , :3 , :4 , :5
  334. 4 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :1
  335. 5 5 , :16 )
  336. 6

  338. Versions Summary
  339. ----------------
  340. AUTH_CHECK_MISMATCH :464
  341. BIND_MISMATCH :462
  342. TRANSLATION_MISMATCH :464
  343. ROLL_INVALID_MISMATCH :461
  344. BIND_LENGTH_UPGRADEABLE :454

  346. Total Versions:468

  348. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  349. cursor_sharing = EXACT
  350. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  352. Plan Hash Value Summary
  353. -----------------------
  354. Plan Hash Value Count
  355. =============== =====
  356.               0 469

  358. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  359. Details for AUTH_CHECK_MISMATCH :

  361.   # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
  362. ========== =============== ================= ===================
  363.        251 87 87 CORDYSDX
  364.        218 85 85 CORDYSBPM
  365. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  366. Details for BIND_MISMATCH :

  368. Consolidated details for BIND* columns:
  369. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  370. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  372. from v$sql_bind_capture
  373. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  374. ======== ======== =============== =============== ======== =============== =================
  375.      469 1 22 22 2 No (,)
  376.      469 2 32 32 1 No (,)
  377.      469 3 128 2000 1 Yes (,)
  378.      270 4 32 32 1 No (,)
  379.      199 4 22 22 2 No (,)
  380.      469 5 32 2000 1 Yes (,)
  381.      469 6 32 2000 1 Yes (,)
  382.      469 7 32 2000 1 Yes (,)
  383.      469 8 32 2000 1 Yes (,)
  384.      469 9 32 2000 1 Yes (,)
  385.      469 10 32 2000 1 Yes (,)
  386.      469 11 128 2000 1 Yes (,)
  387.      469 12 32 4001 1 Yes (,)
  388.      469 13 32 4001 1 Yes (,)
  389.      469 14 32 4001 1 Yes (,)
  390.      469 15 4000 4000 112 No (,)
  391.      469 16 2000 2000 1 No (,)

  393. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  394. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  395. =========== ================= ============= ============
  396.           0 0 0 17
  397. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  398. Details for TRANSLATION_MISMATCH :

  400. No objects in the plans with same name and different owner were found.
  401. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  402. Details for ROLL_INVALID_MISMATCH :

  404. No details available
  405. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  406. Details for BIND_LENGTH_UPGRADEABLE :

  408. Details shown already.
  409. ####
  410. To further debug Ask Oracle Support for the appropiate level LLL.
  411. alter session set events
  412.  \'immediate trace name cursortrace address 1335082476, level LLL\';
  413. To turn it off do use address 1, level 2147483648
  414. ================================================================
  415. Note:438755.1 Version Count Report Version 3.2.3 -- Today\'s Date 30-mar-15 10:39
  416. RDBMS Version :11.2.0.1.0 Host: cordys01 Instance 1 : cordys01
  417. ==================================================================
  418. Addr: 0000000843A907B8 Hash_Value: 551541981 SQL_ID 4m1fc6hhdzr6x
  419. Sharable_Mem: 67362228 bytes Parses: 24221 Execs:24223
  420. Stmt:
  421. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  422. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  423. 2 _TIME\",\"REPLACED\",\"END_TIME\",\"STATUS\",\"MESSAGE_ID\",\"REQUEST\") va
  424. 3 lues (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ) RETURN
  425. 4 ING ROWID INTO :13
  426. 5

  428. Versions Summary
  429. ----------------
  430. AUTH_CHECK_MISMATCH :2047
  431. BIND_MISMATCH :399
  432. TRANSLATION_MISMATCH :2047
  433. ROLL_INVALID_MISMATCH :4
  434. PURGED_CURSOR :2044
  435. BIND_LENGTH_UPGRADEABLE :2048

  437. Total Versions:2051

  439. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  440. cursor_sharing = EXACT
  441. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  443. Plan Hash Value Summary
  444. -----------------------
  445. Plan Hash Value Count
  446. =============== =====
  447.               0 2052

  449. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  450. Details for AUTH_CHECK_MISMATCH :

  452.   # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
  453. ========== =============== ================= ===================
  454.          2 87 87 CORDYSDX
  455.       2050 85 85 CORDYSBPM
  456. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  457. Details for BIND_MISMATCH :

  459. Consolidated details for BIND* columns:
  460. BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
  461. BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

  463. from v$sql_bind_capture
  464. COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
  465. ======== ======== =============== =============== ======== =============== =================
  466.     2052 1 2000 2000 1 No (,)
  467.     2052 2 32 128 1 Yes (,)
  468.     2052 3 32 32 1 No (,)
  469.     2052 4 32 2000 1 Yes (,)
  470.     2052 5 32 128 1 Yes (,)
  471.     2052 6 32 128 1 Yes (,)
  472.     2052 7 22 22 2 No (,)
  473.     2052 8 22 22 2 No (,)
  474.     2052 9 22 22 2 No (,)
  475.     2052 10 32 128 1 Yes (,)
  476.     2052 11 32 2000 1 Yes (,)
  477.     2052 12 2000 4001 1 Yes (,)
  478.     2050 13 2000 2000 1 No (,)
  479.        2 13 16 16 11 No (,)

  481. SUM(DECODE(column,Y, 1, 0) FROM V$SQL
  482. IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
  483. =========== ================= ============= ============
  484.           0 0 0 4
  485. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  486. Details for TRANSLATION_MISMATCH :

  488. No objects in the plans with same name and different owner were found.
  489. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  490. Details for ROLL_INVALID_MISMATCH :

  492. No details available
  493. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  494. Details for PURGED_CURSOR :

  496. No details available
  497. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  498. Details for BIND_LENGTH_UPGRADEABLE :

  500. Details shown already.
  501. ####
  502. To further debug Ask Oracle Support for the appropiate level LLL.
  503. alter session set events
  504.  \'immediate trace name cursortrace address 551541981, level LLL\';
  505. To turn it off do use address 1, level 2147483648
  506. ================================================================

  508. 498 rows selected.

根据前面转载blog的启发，我们对上面的案例进行分析：
其中主要不能共享的原因大概三种：BIND_LENGTH_UPGRADEABLE 和PURGED_CURSOR和BIND_MISMATCH
分析应用的情况：

点击(此处)折叠或打开

  1. Name Null? Type
  2.  ----------------------------------------- -------- ----------------------------
  3.  INSTANCE_ID NOT NULL VARCHAR2(50)
  4.  ACTIVITY_ID NOT NULL VARCHAR2(100)
  5.  ITERATION_COUNT NOT NULL VARCHAR2(100)
  6.  REPLACED NOT NULL NUMBER(3)
  7.  PARENT_ID VARCHAR2(100)
  8.  ACTIVITY_NAME VARCHAR2(250)
  9.  ACTIVITY_TYPE VARCHAR2(50)
  10.  STATUS VARCHAR2(50)
  11.  START_TIME NUMBER(16)
  12.  END_TIME NUMBER(16)
  13.  PARTICIPANT CLOB
  14.  REQUEST CLOB
  15.  RESPONSE CLOB
  16.  MESSAGE_ID VARCHAR2(50)
  17.  EXECUTED_BY VARCHAR2(500)

  19. 0 insert into \"PROCESS_ACTIVITY\" (\"INSTANCE_ID\",\"ACTIVITY_ID\",\"ITE
  20. 1 RATION_COUNT\",\"PARENT_ID\",\"ACTIVITY_NAME\",\"ACTIVITY_TYPE\",\"START
  21. 2 _TIME\",\"REPLACED\",\"END_TIME\",\"STATUS\",\"MESSAGE_ID\",\"REQUEST\") va
  22. 3 lues (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ) RETURN
  23. 4 ING ROWID INTO :13
  24. 5
可以看到应用是一条insert into return 语句，而且绑定变量中有个clob 的字段。绑定变量传入32bytes-2000bytes不等。
数据库参数：

点击(此处)折叠或打开

  1. NAME VALUE SESMOD SYSMOD DESCRIPTION
  2. ----------------------------------- -------------------- --------------- --------------- ----------------------------------------------------------------------
  3. _optimizer_adaptive_cursor_sharing TRUE Y N optimizer adaptive cursor sharing


建议的调整方案：1：ACS 特性关闭
                       2：patch Bug 9689310
                          （9689310 和PSU 11.2.0.1.6冲突）
                     



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30182853/viewspace-1479095/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 了解Oracle补丁知识
请登录后发表评论 登录
全部评论
monkeybron

注册时间：2015-03-30

  • 博文量
    70
  • 访问量
    94588

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593