利用version_rpt3_24.sql指令碼來診斷sql遊標多版本

paulyibinyi發表於2016-04-18
1:指令碼下載 可以在metalink id 438755.1 中下載

2:執行方法:
     sqlplus / as sysdba
     @version_rpt3_24.sql
     會建立4個檢視

3: 查詢多版本大於100個

根據sql_id
set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;  

根據hash_value  
set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(NULL,a.hash_value)) b
WHERE loaded_versions>=100;  

根據sql_id:
set pages 2000 lines 100
SELECT * FROM TABLE(version_rpt('cyzznbykb509s'));
4:具體案例分析
以下是2個例子:
1:optime_mode 最佳化器模式不同 可以看detail部分
 Details for OPTIMIZER_MODE_MISMATCH :
 1 versions with ALL_ROWS
 1 versions with FIRST_ROWS
SQL> select * from table(version_rpt('7b2twsn8vgfsc'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 18-4? -16 11:56
RDBMS Version :11.2.0.4.0 Host: PAYI-CN Instance 1 : orcl
==================================================================
Addr: 000007FF36BEFE58  Hash_Value: 297253644  SQL_ID 7b2twsn8vgfsc
Sharable_Mem: 29074 bytes   Parses: 2   Execs:2
Stmt:
0 select count(*) from test
1
Versions Summary
----------------
OPTIMIZER_MODE_MISMATCH :1
Total Versions:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 1024 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     1950795681 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MODE_MISMATCH :
1 versions with ALL_ROWS
1 versions with FIRST_ROWS
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 297253644, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
 
37 rows selected
2:繫結變數長度不同,可以看
可以看detail部分 Details for BIND_MISMATCH
SQL> select * from table(version_rpt('3dz5hgntqn0am'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 18-4? -16 11:59
RDBMS Version :11.2.0.4.0 Host: PAYI-CN Instance 1 : orcl
==================================================================
Addr: 000007FF46FE1B88  Hash_Value: 862585171  SQL_ID 3dz5hgntqn0am
Sharable_Mem: 45571 bytes   Parses: 6   Execs:41
Stmt:
0 SELECT NVL(MAX(LAST_SAMPLE_DATE), SYSDATE-7) FROM GC$FU_STATISTI
1 CS WHERE FEATURE_ID = :B1
2
Versions Summary
----------------
BIND_MISMATCH :1
Total Versions:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 1024 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     1959252720 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
======== ======== =============== =============== ======== =============== =====
       2        1             128             128        1     No           (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
 
COLUMN_VALUE
--------------------------------------------------------------------------------
=========== ================= ============= ============
          0                 2             0            2
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 862585171, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
 
49 rows selected
 
SQL>


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-2083252/,如需轉載,請註明出處,否則將追究法律責任。

相關文章