http://www.itpub.net/thread-1263154-1-1.html
SQL version 版本过高。
生产系统里,并非所有SQL的version_count过高,而是个别某一条使用绑定变量的SQL语句,其version_count高达2500多,
此SQL写法上,非未发现有什么特殊之外,与其它SQL相比,只多用了两个nvl函数。从绑定变量值的长度范围也中可控的。
v$sql里有同一条SQL的多个版本,但如何来去分版本之间存在那些差异呢,能否从版本之间的差异分析出导致多版本的原因来?
请高人,指点一二,我应该如何入手?
此SQL写法上,非未发现有什么特殊之外,与其它SQL相比,只多用了两个nvl函数。从绑定变量值的长度范围也中可控的。
v$sql里有同一条SQL的多个版本,但如何来去分版本之间存在那些差异呢,能否从版本之间的差异分析出导致多版本的原因来?
请高人,指点一二,我应该如何入手?
环境:
HP-UX +ORACLE 10G
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
HP-UX +ORACLE 10G
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
根据sql_id查这个试图
看看到底因为什么子游标,不能共享
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
看看到底因为什么子游标,不能共享
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
感谢,已经查出来了,以下两个原因导致SQL不可共享。
bind_mismatch; 绑定变量不匹配现在子游标
OPTIMIZER_MISMATCH; 优化环境不匹配现有的子游标
15:57:01 SQL> show parameter OPTIMIZER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 20
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
bind_mismatch可以理解。但OPTIMIZER_MISMATCH我们的优化环境参数都是默认的,再者这个值也不会频繁的改动,SQL语句本岙也没做强制提示,但为什么会这样呢。
OPTIMIZER_MISMATCH应该如何理解呢?
bind_mismatch; 绑定变量不匹配现在子游标
OPTIMIZER_MISMATCH; 优化环境不匹配现有的子游标
15:57:01 SQL> show parameter OPTIMIZER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 20
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
bind_mismatch可以理解。但OPTIMIZER_MISMATCH我们的优化环境参数都是默认的,再者这个值也不会频繁的改动,SQL语句本岙也没做强制提示,但为什么会这样呢。
OPTIMIZER_MISMATCH应该如何理解呢?
select sql_id, address, child_address, child_number, OPTIMIZER_MISMATCH
from gv$sql_shared_cursor
where sql_id = '2wfjtn76993g1'
----------------------------------------------------------------------------------------------------------------------
SQL_ID address child_address child_number OPTIMIZER_MISMATCH
1 2wfjtn76993g1 C000000F4761EFC0 C000000ED9765770 174 N
2 2wfjtn76993g1 C000000F4761EFC0 C000000DD4CBA768 487 Y
3 2wfjtn76993g1 C000000F4761EFC0 C000000EC938A698 528 Y
4 2wfjtn76993g1 C000000F4761EFC0 C000000DA3CD7950 610 N
5 2wfjtn76993g1 C000000F4761EFC0 C000000E986935C8 614 N
6 2wfjtn76993g1 C000000F4761EFC0 C000000E6AADFA60 619 N
7 2wfjtn76993g1 C000000F4761EFC0 C000000E542E3150 638 Y
8 2wfjtn76993g1 C000000F4761EFC0 C000000EA74B3AD8 652 Y
15:57:11 SQL> show parameter OPTIMIZER;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 20
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
同一台机子,相同的optimizer_mode 为什么会出现不同的优化环境呢?
from gv$sql_shared_cursor
where sql_id = '2wfjtn76993g1'
----------------------------------------------------------------------------------------------------------------------
SQL_ID address child_address child_number OPTIMIZER_MISMATCH
1 2wfjtn76993g1 C000000F4761EFC0 C000000ED9765770 174 N
2 2wfjtn76993g1 C000000F4761EFC0 C000000DD4CBA768 487 Y
3 2wfjtn76993g1 C000000F4761EFC0 C000000EC938A698 528 Y
4 2wfjtn76993g1 C000000F4761EFC0 C000000DA3CD7950 610 N
5 2wfjtn76993g1 C000000F4761EFC0 C000000E986935C8 614 N
6 2wfjtn76993g1 C000000F4761EFC0 C000000E6AADFA60 619 N
7 2wfjtn76993g1 C000000F4761EFC0 C000000E542E3150 638 Y
8 2wfjtn76993g1 C000000F4761EFC0 C000000EA74B3AD8 652 Y
15:57:11 SQL> show parameter OPTIMIZER;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 20
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
同一台机子,相同的optimizer_mode 为什么会出现不同的优化环境呢?
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/230160/viewspace-625838/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/230160/viewspace-625838/