[20161219]关于LANGUAGE_MISMATCH.txt

[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,但是这个是单个会话的,不是全部的.

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

转载于:http://blog.itpub.net/267265/viewspace-2130947/