[20161219]为什么光标不共享(LANGUAGE_MISMATCH).txt
--生产系统看看那种情况出现比较多,写一个脚本:
select
sum(decode(UNBOUND_CURSOR,'Y',1,0)) UNBOUND_CURSOR,
sum(decode(SQL_TYPE_MISMATCH,'Y',1,0)) SQL_TYPE_MISMATCH,
sum(decode(OPTIMIZER_MISMATCH,'Y',1,0)) OPTIMIZER_MISMATCH,
sum(decode(OUTLINE_MISMATCH,'Y',1,0)) OUTLINE_MISMATCH,
sum(decode(STATS_ROW_MISMATCH,'Y',1,0)) STATS_ROW_MISMATCH,
sum(decode(LITERAL_MISMATCH,'Y',1,0)) LITERAL_MISMATCH,
sum(decode(FORCE_HARD_PARSE,'Y',1,0)) FORCE_HARD_PARSE,
sum(decode(EXPLAIN_PLAN_CURSOR,'Y',1,0)) EXPLAIN_PLAN_CURSOR,
sum(decode(BUFFERED_DML_MISMATCH,'Y',1,0)) BUFFERED_DML_MISMATCH,
sum(decode(PDML_ENV_MISMATCH,'Y',1,0)) PDML_ENV_MISMATCH,
sum(decode(INST_DRTLD_MISMATCH,'Y',1,0)) INST_DRTLD_MISMATCH,
sum(decode(SLAVE_QC_MISMATCH,'Y',1,0)) SLAVE_QC_MISMATCH,
sum(decode(TYPECHECK_MISMATCH,'Y',1,0)) TYPECHECK_MISMATCH,
sum(decode(AUTH_CHECK_MISMATCH,'Y',1,0)) AUTH_CHECK_MISMATCH,
sum(decode(BIND_MISMATCH,'Y',1,0)) BIND_MISMATCH,
sum(decode(DESCRIBE_MISMATCH,'Y',1,0)) DESCRIBE_MISMATCH,
sum(decode(LANGUAGE_MISMATCH,'Y',1,0)) LANGUAGE_MISMATCH,
sum(decode(TRANSLATION_MISMATCH,'Y',1,0)) TRANSLATION_MISMATCH,
sum(decode(BIND_EQUIV_FAILURE,'Y',1,0)) BIND_EQUIV_FAILURE,
sum(decode(INSUFF_PRIVS,'Y',1,0)) INSUFF_PRIVS,
sum(decode(INSUFF_PRIVS_REM,'Y',1,0)) INSUFF_PRIVS_REM,
sum(decode(REMOTE_TRANS_MISMATCH,'Y',1,0)) REMOTE_TRANS_MISMATCH,
sum(decode(LOGMINER_SESSION_MISMATCH,'Y',1,0)) LOGMINER_SESSION_MISMATCH,
sum(decode(INCOMP_LTRL_MISMATCH,'Y',1,0)) INCOMP_LTRL_MISMATCH,
sum(decode(OVERLAP_TIME_MISMATCH,'Y',1,0)) OVERLAP_TIME_MISMATCH,
sum(decode(EDITION_MISMATCH,'Y',1,0)) EDITION_MISMATCH,
sum(decode(MV_QUERY_GEN_MISMATCH,'Y',1,0)) MV_QUERY_GEN_MISMATCH,
sum(decode(USER_BIND_PEEK_MISMATCH,'Y',1,0)) USER_BIND_PEEK_MISMATCH,
sum(decode(TYPCHK_DEP_MISMATCH,'Y',1,0)) TYPCHK_DEP_MISMATCH,
sum(decode(NO_TRIGGER_MISMATCH,'Y',1,0)) NO_TRIGGER_MISMATCH,
sum(decode(FLASHBACK_CURSOR,'Y',1,0)) FLASHBACK_CURSOR,
sum(decode(ANYDATA_TRANSFORMATION,'Y',1,0)) ANYDATA_TRANSFORMATION,
sum(decode(PDDL_ENV_MISMATCH,'Y',1,0)) PDDL_ENV_MISMATCH,
sum(decode(TOP_LEVEL_RPI_CURSOR,'Y',1,0)) TOP_LEVEL_RPI_CURSOR,
sum(decode(DIFFERENT_LONG_LENGTH,'Y',1,0)) DIFFERENT_LONG_LENGTH,
sum(decode(LOGICAL_STANDBY_APPLY,'Y',1,0)) LOGICAL_STANDBY_APPLY,
sum(decode(DIFF_CALL_DURN,'Y',1,0)) DIFF_CALL_DURN,
sum(decode(BIND_UACS_DIFF,'Y',1,0)) BIND_UACS_DIFF,
sum(decode(PLSQL_CMP_SWITCHS_DIFF,'Y',1,0)) PLSQL_CMP_SWITCHS_DIFF,
sum(decode(CURSOR_PARTS_MISMATCH,'Y',1,0)) CURSOR_PARTS_MISMATCH,
sum(decode(STB_OBJECT_MISMATCH,'Y',1,0)) STB_OBJECT_MISMATCH,
sum(decode(CROSSEDITION_TRIGGER_MISMATCH,'Y',1,0)) CROSSEDITION_TRIGGER_MISMATCH,
sum(decode(PQ_SLAVE_MISMATCH,'Y',1,0)) PQ_SLAVE_MISMATCH,
sum(decode(TOP_LEVEL_DDL_MISMATCH,'Y',1,0)) TOP_LEVEL_DDL_MISMATCH,
sum(decode(MULTI_PX_MISMATCH,'Y',1,0)) MULTI_PX_MISMATCH,
sum(decode(BIND_PEEKED_PQ_MISMATCH,'Y',1,0)) BIND_PEEKED_PQ_MISMATCH,
sum(decode(MV_REWRITE_MISMATCH,'Y',1,0)) MV_REWRITE_MISMATCH,
sum(decode(ROLL_INVALID_MISMATCH,'Y',1,0)) ROLL_INVALID_MISMATCH,
sum(decode(OPTIMIZER_MODE_MISMATCH,'Y',1,0)) OPTIMIZER_MODE_MISMATCH,
sum(decode(PX_MISMATCH,'Y',1,0)) PX_MISMATCH,
sum(decode(MV_STALEOBJ_MISMATCH,'Y',1,0)) MV_STALEOBJ_MISMATCH,
sum(decode(FLASHBACK_TABLE_MISMATCH,'Y',1,0)) FLASHBACK_TABLE_MISMATCH,
sum(decode(LITREP_COMP_MISMATCH,'Y',1,0)) LITREP_COMP_MISMATCH,
sum(decode(PLSQL_DEBUG,'Y',1,0)) PLSQL_DEBUG,
sum(decode(LOAD_OPTIMIZER_STATS,'Y',1,0)) LOAD_OPTIMIZER_STATS,
sum(decode(ACL_MISMATCH,'Y',1,0)) ACL_MISMATCH,
sum(decode(FLASHBACK_ARCHIVE_MISMATCH,'Y',1,0)) FLASHBACK_ARCHIVE_MISMATCH,
sum(decode(LOCK_USER_SCHEMA_FAILED,'Y',1,0)) LOCK_USER_SCHEMA_FAILED,
sum(decode(REMOTE_MAPPING_MISMATCH,'Y',1,0)) REMOTE_MAPPING_MISMATCH,
sum(decode(LOAD_RUNTIME_HEAP_FAILED,'Y',1,0)) LOAD_RUNTIME_HEAP_FAILED,
sum(decode(HASH_MATCH_FAILED,'Y',1,0)) HASH_MATCH_FAILED,
sum(decode(PURGED_CURSOR,'Y',1,0)) PURGED_CURSOR,
sum(decode(BIND_LENGTH_UPGRADEABLE,'Y',1,0)) BIND_LENGTH_UPGRADEABLE,
sum(decode(USE_FEEDBACK_STATS,'Y',1,0)) USE_FEEDBACK_STATS
from v$sql_shared_cursor ;
--结果如下:
Record View
As of: 2016/12/19 15:06:48
OPTIMIZER_MISMATCH: 12
STATS_ROW_MISMATCH: 1
AUTH_CHECK_MISMATCH: 63
BIND_MISMATCH: 422
LANGUAGE_MISMATCH: 5642
TRANSLATION_MISMATCH: 11
BIND_EQUIV_FAILURE: 235
INSUFF_PRIVS_REM: 52
INCOMP_LTRL_MISMATCH: 10
USER_BIND_PEEK_MISMATCH: 9
TOP_LEVEL_RPI_CURSOR: 9
BIND_UACS_DIFF: 153
PLSQL_CMP_SWITCHS_DIFF: 1
TOP_LEVEL_DDL_MISMATCH: 9
MULTI_PX_MISMATCH: 2
ROLL_INVALID_MISMATCH: 1
OPTIMIZER_MODE_MISMATCH: 13
LOAD_OPTIMIZER_STATS: 73
HASH_MATCH_FAILED: 26
PURGED_CURSOR: 25
BIND_LENGTH_UPGRADEABLE: 1657
USE_FEEDBACK_STATS: 1340
--//注:删除等于0的行,不然太长了.可以发现主要问题集中在LANGUAGE_MISMATCH.测一下这个产生的原因.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--从LANGUAGE_MISMATCH看应该与语言环境设置有关.
2.设置2种情况下环境变量:
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
alter system flush shared_pool;
alter system flush shared_pool;
--分别登录,然后执行:select * from dept where deptno=10;确定sql_id='4xamnunv51w9j'.
SCOTT@book> select executions,sql_id,child_number from v$sql where sql_id='4xamnunv51w9j';
EXECUTIONS SQL_ID CHILD_NUMBER
---------- ------------- ------------
2 4xamnunv51w9j 0
--奇怪可以发现光标是共享的.为什么?
3.换成字符参数看看:
--分别登录,然后执行:Select * from dept where DNAME='ACCOUNTING';确定sql_id='727p30dc2pq3z'
SCOTT@book> select executions,sql_id,child_number from v$sql where sql_id='727p30dc2pq3z';
EXECUTIONS SQL_ID CHILD_NUMBER
---------- ------------- ------------
1 727p30dc2pq3z 0
1 727p30dc2pq3z 1
SCOTT@book> @ &r/share 727p30dc2pq3z
SQL_TEXT = Select * from dept where DNAME='ACCOUNTING'
SQL_ID = 727p30dc2pq3z
ADDRESS = 000000007D691210
CHILD_ADDRESS = 000000007DB178A0
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS Settings(0)</reason><size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengthSemantics>0</CursorLengthSemantics></ChildNode>
--------------------------------------------------
SQL_TEXT = Select * from dept where DNAME='ACCOUNTING'
SQL_ID = 727p30dc2pq3z
ADDRESS = 000000007D691210
CHILD_ADDRESS = 000000007D1A6D70
CHILD_NUMBER = 1
LANGUAGE_MISMATCH = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
--可以发现如果字符字段不能共享就出现了.
4.很明显我们系统在实施安装时存在2种设置环境,各个安装人员的安装方式不统一,现在要纠正困难重重.
--当然也与我们的以前的旧系统有关,以前我们使用字符集是AMERICAN_AMERICA.US7ASCII.
--我能否有视图查询环境呢?我跟一下视图定义,最后x$nls_parameters,但是这个是单个会话的,不是全部的.