[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类型,从程序带入的绑定变量参数类型就可以知道确定,两者是混用的.只不过这时转换发生绑定变量端.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2946379/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
