[20230413]慎用nvarchar2数据类型2.txt

来源：这里教程网时间：2026-03-03 18:39:02 作者：

[20230413]慎用nvarchar2数据类型2.txt --//生产系统不同数据库一套字符串使用varchar2类型,另外一套使用nvarchar2类型导致的问题.通过真实的例子说明问题. 1.环境: > @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 > @ ashtop sql_id sql_id='by086v1989xs8' &day Total Distinct Distinct Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps --------- ------- ------- ------------- ------------------- ------------------- ---------- -------- 24392 .3 100% | by086v1989xs8 2023-04-13 02:45:10 2023-04-13 03:29:20 3722 2571 > @ sqlhh by086v1989xs8 1 BEGIN_INTERVAL_TIME INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC ------------------- ------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2023-04-12 14:00:20 1 by086v1989xs8 701626669 1350 7162 6697 47.7 635680 635971 223 0.0 0 1 0 2023-04-12 15:00:16 1 by086v1989xs8 701626669 770 7576 7136 45.4 640502 640710 215 0.0 0 1 0 2023-04-13 02:00:20 1 by086v1989xs8 701626669 1299 6486 6031 47.2 625700 626143 246 0.0 0 1 0 2023-04-13 03:00:16 1 by086v1989xs8 701626669 2429 6660 6143 45.8 633385 633582 249 0.0 0 1 0 2.分析: > @ sql_id by086v1989xs8 --SQL_ID = by086v1989xs8 SELECT b.id as report_id,/*检验报告ID*/ to_char(a.zyh) as patient_id,/*病人ID*/ c.examinaim as report_name,/*报告名称*/ c.requesttime as pms_oder_date,/*开检验单日期*/ c.receivetime as accept_date,/*送检日期*/ b.audit_time as report_date,/*报告日期*/ b.sample_type_name as sample,/*样本*/ c.stayhospitalmode as zyflag,/*检验数据类型（0 普通、1 急诊、2 疼痛中心、3、血透、4 早癌筛查）*/ b.RESULT_FINISH_TIME as pms_result_date,/*出结果日期*/ :"SYS_B_00" as patient_id1,/*病人ID1*/ :"SYS_B_01" as patient_id2,/*病人ID2*/ :"SYS_B_02" as patient_id3,/*病人ID3*/ :"SYS_B_03" as report_id1,/*检验报告ID1*/ :"SYS_B_04" as report_id2,/*检验报告ID2*/ :"SYS_B_05" as report_id3,/*检验报告ID3*/ :"SYS_B_06" as pms_location,/*采样地点*/ c.requesttime as pms_sample_date,/*样本收集日期*/ c.requestmode as pms_exam_type,/*检查类型*/ :"SYS_B_07" as pms_result_loc,/*检验科还是即时检测*/ :"SYS_B_08" as pms_battery,/*一组检验项目编码*/ b.depart_name as pms_dept/*科室*/ FROM zy_brry a inner join lis_test@HLXJY b on a.mzhm = b.pat_id inner join l_lis_sqd c on b.barcode = c.tmbh where :"SYS_B_09"=:"SYS_B_10" and a.zyh = :Paitent_id; --//仔细检查发现各表对应索引都建立,nested连接应该没有问题. > @ dpcawr by086v1989xs8 '' Plan hash value: 701626669 ----------------------------------------------------------------------------------------------- |Id|Operation |Name |E-Rows|E-Bytes|Cost(%CPU)|E-Time |Inst |IN-OUT| ----------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | | | 173K(100)| | | | | 1| FILTER | | | | | | | | | 2| HASH JOIN | | 16 | 3648 | 173K (1)|00:34:48| | | | 3| NESTED LOOPS | | 6 | 1032 | 6 (0)|00:00:01| | | | 4| TABLE ACCESS BY INDEX ROWID|ZY_BRRY | 1 | 21 | 3 (0)|00:00:01| | | | 5| INDEX UNIQUE SCAN |PK_ZY_BRRY| 1 | | 2 (0)|00:00:01| | | | 6| REMOTE |LIS_TEST | 6 | 906 | 3 (0)|00:00:01|HLXJY| R->S | | 7| TABLE ACCESS STORAGE FULL |L_LIS_SQD | 24M| 1307M| 173K (1)|00:34:47| | | ----------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$9E43CB6E 4 - SEL$9E43CB6E / A@SEL$1 5 - SEL$9E43CB6E / A@SEL$1 6 - SEL$9E43CB6E / B@SEL$1 7 - SEL$9E43CB6E / C@SEL$2 Peeked Binds (identified by position): -------------------------------------- 12 - :PAITENT_ID (CHAR(30), CSID=852): '20041093' Remote SQL Information (identified by operation id): ---------------------------------------------------- 6 - SELECT "ID","PAT_ID","DEPART_NAME","AUDIT_TIME","BARCODE","RESULT_FINISH_TIME","SAMPLE_TYPE_NAM E" FROM "LIS_TEST" "B" WHERE "PAT_ID"=:1 (accessing 'HLXJY' ) --//使用dpc.sql看不到执行计划(说明已经不再共享池),只能使用dbms_xplan.display_awr查看.许多信息不全. --//id=7全表全表扫描,为什么? --//我自己生成脚本执行得到的执行计划如下: Plan hash value: 701626669 ------------------------------------------------------------------------------------------------------------------------------------------------------------ |Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |Inst |IN-OUT|A-Rows| A-Time |Buffers|Reads|OMem |1Mem |Used-Mem| ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0|SELECT STATEMENT | | 1| | | 173K(100)| | | | 240 |00:00:11.76| 647K| 647K| | | | |*1| FILTER | | 1| | | | | | | 240 |00:00:11.76| 647K| 647K| | | | |*2| HASH JOIN | | 1| 16 | 3648 | 173K (1)|00:34:48| | | 240 |00:00:11.76| 647K| 647K|1968K|1968K|1237K(0)| | 3| NESTED LOOPS | | 1| 6 | 1032 | 6 (0)|00:00:01| | | 261 |00:00:00.01| 4 | 0 | | | | | 4| TABLE ACCESS BY INDEX ROWID|ZY_BRRY | 1| 1 | 21 | 3 (0)|00:00:01| | | 1 |00:00:00.01| 4 | 0 | | | | |*5| INDEX UNIQUE SCAN |PK_ZY_BRRY| 1| 1 | | 2 (0)|00:00:01| | | 1 |00:00:00.01| 3 | 0 |1025K|1025K| | | 6| REMOTE |LIS_TEST | 1| 6 | 906 | 3 (0)|00:00:01|HLXJY| R->S | 261 |00:00:00.01| 0 | 0 | | | | | 7| TABLE ACCESS STORAGE FULL |L_LIS_SQD | 1| 24M| 1307M| 173K (1)|00:34:47| | | 24M|00:00:02.99| 647K| 647K|1025K|1025K|3085K(0)| ------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$9E43CB6E 4 - SEL$9E43CB6E / A@SEL$1 5 - SEL$9E43CB6E / A@SEL$1 6 - SEL$9E43CB6E / B@SEL$1 7 - SEL$9E43CB6E / C@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$9E43CB6E") MERGE(@"SEL$58A6D7F6") OUTLINE(@"SEL$3") OUTLINE(@"SEL$58A6D7F6") MERGE(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") INDEX_RS_ASC(@"SEL$9E43CB6E" "A"@"SEL$1" ("ZY_BRRY"."ZYH")) FULL(@"SEL$9E43CB6E" "B"@"SEL$1") FULL(@"SEL$9E43CB6E" "C"@"SEL$2") LEADING(@"SEL$9E43CB6E" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$2") USE_NL(@"SEL$9E43CB6E" "B"@"SEL$1") USE_HASH(@"SEL$9E43CB6E" "C"@"SEL$2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (VARCHAR2(30), CSID=852): '861636' Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:SYS_B_09=:SYS_B_10) 2 - access("B"."BARCODE"=SYS_OP_C2C("C"."TMBH")) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 5 - access("A"."ZYH"=TO_NUMBER(:PAITENT_ID)) Remote SQL Information (identified by operation id): ---------------------------------------------------- 6 - SELECT "ID","PAT_ID","DEPART_NAME","AUDIT_TIME","BARCODE","RESULT_FINISH_TIME","SAMPLE_TYPE_NAME" FROM "LIS_TEST" "B" WHERE "PAT_ID"=:1 (accessing 'HLXJY' ) --//问题在于id=7,选择全表扫描.为什么? > @seg2 PPPPPP_HHH.L_LIS_SQD SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ------ ---------- ------------ ------------- ------------ ------------------- ------ ------ ------- 5070 PPPPPP_HHH L_LIS_SQD TABLE PPPPPP_HHH 648960 35 1147425 --//噢5G,开发应该好好感谢exadata,它才是真正的先进工作者!! --//仔细看id=2的Predicate Information 信息,连接条件存在一个函数是SYS_OP_C2C. 2 - access("B"."BARCODE"=SYS_OP_C2C("C"."TMBH")) SYS@192.168.100.235:1521/orcl> @ desczz lis.lis_test barcode eXtended describe of lis.lis_test DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- -------- ------------- ------------ ------------ --------- --------- ----------- ---------- ---------- LLL LIS_TEST 8755 2022-11-01 09:10:12 55 BARCODE NOT NULL NVARCHAR2(28) 9530368 .00000000000 0 HYBRID 254 0018153755 _ > @ desczz PPPPPP_HHH.l_lis_sqd tmbh eXtended describe of PPPPPP_HHH.l_lis_sqd DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ---------- ---------- ----------- ------------------- ---- ----------- ----- ------------ ------------ ------------ ---------- --------- ----------- --------- ---------- PPPPPP_HHH L_LIS_SQD 23112823 2023-03-20 00:00:22 30 TMBH VARCHAR2(30) 23164928 .00000004317 1360529 1 00160721 9500000111 --//仔细看两个连接字段的类型不一致,导致出现SYS_OP_C2C转换,出现全表扫描.也就是存在隐式转换,修改如下: $ cat by086v1989xs8.sql9d_0 variable SYS_B_09 NUMBER variable SYS_B_10 NUMBER variable PAITENT_ID VARCHAR2(32) begin :SYS_B_09 := 1; :SYS_B_10 := 1; :PAITENT_ID := '861636'; null; end; / set termout off set sqlblanklines on alter session set current_schema=PPPPPP_HHH; alter session set statistics_level=all; SELECT b.id as report_id,/*检验报告ID*/ to_char(a.zyh) as patient_id,/*病人ID*/ c.examinaim as report_name,/*报告名称*/ c.requesttime as pms_oder_date,/*开检验单日期*/ c.receivetime as accept_date,/*送检日期*/ b.audit_time as report_date,/*报告日期*/ b.sample_type_name as sample,/*样本*/ c.stayhospitalmode as zyflag,/*检验数据类型（0 普通、1 急诊、2 疼痛中心、3、血透、4 早癌筛查）*/ b.RESULT_FINISH_TIME as pms_result_date,/*出结果日期*/ --:"SYS_B_00" as patient_id1,/*病人ID1*/ --:"SYS_B_01" as patient_id2,/*病人ID2*/ --:"SYS_B_02" as patient_id3,/*病人ID3*/ --:"SYS_B_03" as report_id1,/*检验报告ID1*/ --:"SYS_B_04" as report_id2,/*检验报告ID2*/ --:"SYS_B_05" as report_id3,/*检验报告ID3*/ --:"SYS_B_06" as pms_location,/*采样地点*/ c.requesttime as pms_sample_date,/*样本收集日期*/ c.requestmode as pms_exam_type,/*检查类型*/ --:"SYS_B_07" as pms_result_loc,/*检验科还是即时检测*/ --:"SYS_B_08" as pms_battery,/*一组检验项目编码*/ b.depart_name as pms_dept/*科室*/ FROM zy_brry a inner join lis_test@HLXJY b on a.mzhm = b.pat_id inner join l_lis_sqd c on cast(b.barcode as varchar2(30)) = c.tmbh ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ where :"SYS_B_09"=:"SYS_B_10" and a.zyh = :Paitent_id; set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' @dpc '' outline rollback; alter session set current_schema=SYS ; --//当然还有1个思路就是建立SYS_OP_C2C("TMBH")的函数索引. --//补充说明,还可以加入如下条件: inner join l_lis_sqd c on cast(b.barcode as varchar2(30)) = c.tmbh and b.barcode = cast (c.tmbh as nvarchar2(28)) Plan hash value: 3494871177 -------------------------------------------------------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time |Inst |IN-OUT|A-Rows| A-Time |Buffers|OMem |1Mem |Used-Mem| -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | | 24 (100)| | | | 240|00:00:00.01| 606| | | | |*1| FILTER | | 1| | | | | | | 240|00:00:00.01| 606| | | | | 2| NESTED LOOPS | | 1| 7| 1596 | 24 (0)|00:00:01| | | 240|00:00:00.01| 606| | | | | 3| NESTED LOOPS | | 1| 7| 1596 | 24 (0)|00:00:01| | | 240|00:00:00.01| 536| | | | | 4| NESTED LOOPS | | 1| 6| 1032 | 6 (0)|00:00:01| | | 261|00:00:00.01| 4| | | | | 5| TABLE ACCESS BY INDEX ROWID|ZY_BRRY | 1| 1| 21 | 3 (0)|00:00:01| | | 1|00:00:00.01| 4| | | | |*6| INDEX UNIQUE SCAN |PK_ZY_BRRY | 1| 1| | 2 (0)|00:00:01| | | 1|00:00:00.01| 3|1025K|1025K| | | 7| REMOTE |LIS_TEST | 1| 6| 906 | 3 (0)|00:00:01|HLXJY| R->S | 261|00:00:00.01| 0| | | | |*8| INDEX RANGE SCAN |I_L_LIS_SQD_TMBH| 261| 1| | 2 (0)|00:00:01| | | 240|00:00:00.01| 532|1025K|1025K| | | 9| TABLE ACCESS BY INDEX ROWID |L_LIS_SQD | 240| 1| 56 | 3 (0)|00:00:01| | | 240|00:00:00.01| 70| | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- 3.再次提醒: --//建议开发不要拿着怎么国际化需求吓唬人,其实根本不了解这种类型的存储模式,也就是不要乱用数据类型.许多老系统(我相信大部分还是使用varchar2类型). --//那套新数据库只要字符类型都是nvarchar2类型,实际上我看到仅仅是冰山一角,应用中涉及到这样dblink的语句基本都有问题. > @ ashtable '' @HLXJY 1=1 &day @ tpt/ashtop username,sql_id,module "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%@HLXJY%'))" sysdate-1 sysdate Total Distinct Distinct Seconds AAS %This USERNAME SQL_ID MODULE FIRST_SEEN LAST_SEEN Execs Seen Tstamps --------- ------- ------- ---------- ------------- ---------- ------------------- ------------------- ---------- -------- 78556 .9 49% | PPPPPP_HHH by086v1989xs8 w3wp.exe 2023-04-17 02:45:33 2023-04-17 05:08:04 12017 8303 69744 .8 43% | PPPPPP_HHH 1h1b8ccmaxpx1 w3wp.exe 2023-04-17 05:31:44 2023-04-17 07:37:52 24982 7340 12426 .1 8% | PPPPPP_HHH 5dxqqv937z5a7 w3wp.exe 2023-04-17 05:08:27 2023-04-17 05:31:24 9068 1339 1 .0 0% | PPPPPP_HHH 00rgs3nb3czcw PPPPPP.EXE 2023-04-17 08:01:23 2023-04-17 08:01:23 1 1 --//前面3条基本都是一样的问题. $ cat ashtable.sql column module format a30 prompt prompt @ tpt/&&1.ashtop username,sql_id,module "&&3 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%&&2%'))" &&4 &&5 prompt @ tpt/&&1.ashtop username,sql_id,module "&&3 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%&&2%'))" &&4 &&5 --//简单说明: --//参数1 '' d ,''表示执行ashtop,d 表示执行dashtop. --//参数2 查询相关的表或者特定字符串 --//参数3 过滤查询条件 --//参数4,5 查询时间范围.我一般选择查询trunc(sysdate-1) sysdate,这样简单一些. --//查询使用DBA_HIST_SQLTEXT视图呢? > @ tpt/dashtop username,sql_id,module "1=1 and sql_id in (select sql_id from DBA_HIST_SQLTEXT where lower(sql_text) like lower('%@HLXJY%'))" sysdate-1 sysdate Total Seconds AAS %This USERNAME SQL_ID MODULE FIRST_SEEN LAST_SEEN --------- ------- ------- ---------- ------------- -------- ------------------- ------------------- 78700 .9 49% PPPPPP_HHH by086v1989xs8 w3wp.exe 2023-04-17 02:45:36 2023-04-17 05:07:55 70020 .8 43% PPPPPP_HHH 1h1b8ccmaxpx1 w3wp.exe 2023-04-17 05:31:45 2023-04-17 07:37:49 12700 .1 8% PPPPPP_HHH 5dxqqv937z5a7 w3wp.exe 2023-04-17 05:08:36 2023-04-17 05:31:15 --//我曾经帮别人优化过一个ms sqlsever的项目,对方讲分开执行很快,而连接在一起执行很慢,仔细检查发现也是类似的问题. --//后来我给他讲了问题出在字符类型的定义上,实际上在于开发管理的混乱,一部分人使用varchar2类型,另外一部分人使用nvarchar2类型. --//后来对方花了一个晚上的时间统一到nvarchar2类型,实际上这套新系统我猜测也是类似的情况,一部分人使用varchar2类型,另外一部分人使用nvarchar2类型. --//等到上线发现性能问题,全部修改为nvarchar2类型,从程序带入的绑定变量参数类型就可以知道确定,两者是混用的.只不过这时转换发生绑定变量端.

编辑推荐：

返回列表

[20230413]慎用nvarchar2数据类型2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推