[20161219]关于LANGUAGE_MISMATCH.txt

简介: [20161219]为什么光标不共享(LANGUAGE_MISMATCH).txt --生产系统看看那种情况出现比较多,写一个脚本: select sum(decode(UNBOUND_CURSOR,'Y',1,0))    UNBOUND_CURSOR...

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

目录
相关文章
|
23天前
|
应用服务中间件
The valid characters are defined in RFC XXXX
The valid characters are defined in RFC XXXX
12 0
Invalid character found in the request target. The valid characters are defined in RFC 7230 and RFC
Invalid character found in the request target. The valid characters are defined in RFC 7230 and RFC
|
4月前
|
JSON 数据格式
Error:Comments are not permitted in JSON
Error:Comments are not permitted in JSON
|
JSON 数据格式
遇到【Unexpected character (‘“‘ (code 34)): was expecting comma to separate Object entries】的解决办法
遇到【Unexpected character (‘“‘ (code 34)): was expecting comma to separate Object entries】的解决办法
遇到【Unexpected character (‘“‘ (code 34)): was expecting comma to separate Object entries】的解决办法
(standard input): No keywords in input file
(standard input): No keywords in input file
85 0
How to analyze the dump MESSAGE_TYPE_X when modifying an attachment
Created by Jerry Wang, last modified on Mar 04, 2014 The real problem could be reproduced by following the four steps below:
113 0
How to analyze the dump MESSAGE_TYPE_X when modifying an attachment
|
开发工具 Android开发 Go
RN Exception: Before building your project, you need to accept the license agreements and comp le...
异常 * What went wrong: A problem occurred configuring project ':app'. > You have not accepted the license agreements of the fol...
1617 0
|
关系型数据库 测试技术 Oracle
[20180102]statistics_level=BASIC.txt
[20180102]statistics_level=BASIC.txt --//一个测试环境不知道谁设置statistics_level=BASIC,导致重启出现错误,自己在测试环境模拟看看: SYS@book> create pfile='/tmp/@.
1195 0
Error saving your changes: Description control characters are not allowed
在修改 GitHub 上的仓库描述时出现此提示信息:Error saving your changes: Description control characters are not allowed 开始以为是 Fork 来的没有修改权限,但之前没有遇到这样的情况,提示信息说的也不是这个意思。
2342 0