1:脚本下载 可以在metalink id
438755.1 中下载
2:运行方法:
sqlplus / as sysdba
@version_rpt3_24.sql
会创建4个视图
3: 查找多版本大于100个
根据sql_id
根据sql_id:
4:具体案例分析
以下是2个例子:
1:optime_mode 优化器模式不同 可以看detail部分
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:绑定变量长度不同,可以看
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>
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'));
以下是2个例子:
1:optime_mode 优化器模式不同 可以看detail部分
Details for OPTIMIZER_MODE_MISMATCH : 1 versions with ALL_ROWS 1 versions with FIRST_ROWSSQL> 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_MISMATCHSQL> 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7199859/viewspace-2083252/