开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
关于 通过jlink使用jtag(或swd)下载程序成功后,keil4 uversion停止运行 的解决方法
关于 通过jlink使用jtag(或swd)下载程序成功后,keil4 uversion停止运行 的解决方法
79 0
CUUG第十六期PostgreSQL公开课:冷冻TXIDS内核剖析
CUUG第十六期PostgreSQL公开课:冷冻TXIDS内核剖析
186 0
斯坦福 CS183 Peter Thiel 创业课资料整理
原始课程笔记(英文): http://blakemasters.com/peter-thiels-cs183-startup 36kr 的笔记翻译(前六章): http://get.
944 0
关于base64编码的原理及实现
我们的图片大部分都是可以转换成base64编码的data:image。 这个在将canvas保存为img的时候尤其有用。虽然除ie外,大部分现代浏览器都已经支持原生的基于base64的encode和decode,例如btoa和atob。
834 0
关于base64编码的原理及实现
我们的图片大部分都是可以转换成base64编码的data:image。 这个在将canvas保存为img的时候尤其有用。虽然除ie外,大部分现代浏览器都已经支持原生的基于base64的encode和decode,例如btoa和atob。
1013 0
How to write own add-in for SSMS 2012 (Final release version)
原文 How to write own add-in for SSMS 2012 (Final release version) Reading internet forums I have noticed that some developers want to experiment and w...
983 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
KR^X- Comprehensive- Kernel-Protection-Against-Just-In-Time-Code-Reuse
立即下载
40 Must know Questions to test
立即下载
40 Must Know Questions to test
立即下载