[20160624]慎用nvarchar2数据类型.txt

--我以前的blog都写过谨慎使用nvarchar2类型,如果没有国际化需求,建议不要使用.
--而且这种类型可能导致另外的问题.正好最近优化一条sql语句,最后才发现问题所在.

1.环境:

SYSTEM@192.168.xx.yyy:1521/orcl> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
IBMPC/WIN_NT64-9.1.0           11.2.0.1.0     Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

UPDATE pacs_image
   SET DELFLAGS = '1'
WHERE insuid = '1.2.840.10008.1.2.1000510567.20160624.4765453.0';

--执行计划走的是全表扫描,如果检查可以发现索引是建立的.我一看insuid的查询值就大概知道可能是直方图导致的问题.

SYSTEM@192.168.xx.yyy:1521/orcl> select * from (select substr(INSUID,1,32),count(*)  from tjpacs.PACS_IMAGE group by  substr(INSUID,1,32) order by 2 desc) where rownum<=10;
SUBSTR(INSUID,1,32)                 COUNT(*)
--------------------------------- ----------
1.3.12.2.1107.5.4.4.10319.300000       40893
1.3.12.2.1107.5.4.4.10278.300000       26867
1.3.12.2.1107.5.3.58.40106.12.20       15343
1.2.840.10008.1.2.160400058.2016          25
1.2.840.10008.1.2.1000342479.201          16
1.2.840.10008.1.2.1000471555.201          16
1.2.840.10008.1.2.1000353888.201          15
1.2.840.10008.1.2.1000325399.201          15
1.2.840.10008.1.2.1000450679.201          15
1.2.840.10008.1.2.1000407902.201          14
10 rows selected.

SYSTEM@192.168.xx.yyy:1521/orcl> select count(*)   from tjpacs.PACS_IMAGE ;
  COUNT(*)
----------
    184870

--不管怎样,查询占14/184870应该可以很好的使用索引,但是当我查询直方图信息发现:

SYSTEM@192.168.xx.yyy:1521/orcl> select * from DBA_TAB_HISTOGRAMS where table_name='PACS_IMAGE' and column_name='INSUID';
OWNER  TABLE_NAME COLUMN_NAME                             ENDPOINT_NUMBER                     ENDPOINT_VALUE ENDPOINT_A
------ ---------- -------------------- ---------------------------------- ---------------------------------- ----------
TJPACS PACS_IMAGE INSUID                                             2862  993852307125505000000000000000000
TJPACS PACS_IMAGE INSUID                                             5416  993852307130228000000000000000000

--仅仅2个backet,有点奇怪了.

SYSTEM@192.168.xx.yyy:1521/orcl> @ &r/desc tjpacs.PACS_IMAGE
Name         Null?    Type
------------ -------- ----------------------------
FILENAME     NOT NULL NVARCHAR2(128)
HOSTNAME     NOT NULL NVARCHAR2(20)
VIRTUAL_DIR           NVARCHAR2(128)
STUDYID      NOT NULL NUMBER
GROUPNO      NOT NULL NUMBER
SERIESID     NOT NULL NUMBER
IMAGEID      NOT NULL NUMBER
INSUID                NVARCHAR2(128)
IMAGENO               NVARCHAR2(10)
IMAGEDATE             DATE
IMAGEDES              NVARCHAR2(128)
BODYPART              NVARCHAR2(128)
IMAGESTATUS           NVARCHAR2(1)
IMAGETYPE             NVARCHAR2(4)
FILESIZE              NUMBER
DELFLAGS              NVARCHAR2(1)
VIEWTAGPATH           NVARCHAR2(200)

--一看表定义很容易明白问题使用了nvarchar2类型.转储看看:

SYSTEM@192.168.xx.yyy:1521/orcl> select dump(INSUID,16) c70  ,insuid c60 from tjpacs.PACS_IMAGE where rownum<=1;
C70                                                                    C60
---------------------------------------------------------------------- ------------------------------------------------------------
Typ=1 Len=110: 0,31,0,2e,0,33,0,2e,0,31,0,32,0,2e,0,32,0,2e,0,31,0,31, 1.3.12.2.1107.5.4.4.10278.30000015111823402501500000000
0,30,0,37,0,2e,0,35,0,2e,0,34,0,2e,0,34,0,2e,0,31,0,30,0,32,0,37,0,38,
0,2e,0,33,0,30,0,30,0,30,0,30,0,30,0,31,0,35,0,31,0,31,0,31,0,38,0,32,
0,33,0,34,0,30,0,32,0,35,0,30,0,31,0,35,0,30,0,30,0,30,0,30,0,30,0,30,
0,30,0,30

--可以发现nvarchar2类型保存'1'需要2个字节.这样直方图仅仅分析16个字符.

SYSTEM@192.168.xx.yyy:1521/orcl> select * from (select substr(INSUID,1,16),count(*)  from tjpacs.PACS_IMAGE group by  substr(INSUID,1,16) order by 2 desc) where rownum<=10;
SUBSTR(INSUID,1,16)  COUNT(*)
-------------------- --------
1.2.840.10008.1.       101766
1.3.12.2.1107.5.        83110
1.2.840.113619.2            4

--可以发现这样查询建立的直方图就是鸡肋.毫无用处,导致oracle认为重复值很多.取消直方图定义.

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'TJPACS'
     ,TabName        => 'PACS_IMAGE'
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE auto for columns INSUID size 1 '
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/

SELECT * FROM tjpacs.pacs_image WHERE insuid = '1.2.840.10008.1.2.1000510567.20160624.4765453.0';

SYSTEM@192.168.xx.yyy:1521/orcl> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fmy1xha29r0dg, child number 0
-------------------------------------
SELECT * FROM tjpacs.pacs_image WHERE insuid =
'1.2.840.10008.1.2.1000510567.20160624.4765453.0'
Plan hash value: 3036732012
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PACS_IMAGE        |      1 |      1 |   487 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | PACS_IMAGE$INSUID |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / PACS_IMAGE@SEL$1
   2 - SEL$1 / PACS_IMAGE@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("INSUID"=U'1.2.840.10008.1.2.1000510567.20160624.4765453.0')

--OK现在可以正常使用索引了.当然如果oracle如果晚上自动分析问题就还原了.优化这个问题需要控制直方图建立.
exec dbms_stats.set_table_prefs('tjpacs','.pacs_image','method_opt','FOR ALL COLUMNS SIZE AUTO FOR columns  INSUID size 1');

SYSTEM@192.168.xx.yyy:1521/orcl> exec dbms_stats.set_table_prefs('tjpacs','pacs_image','method_opt','FOR ALL COLUMNS SIZE AUTO FOR columns  INSUID size 1');
PL/SQL procedure successfully completed.

SYSTEM@192.168.xx.yyy:1521/orcl> select * from SYS.OPTSTAT_USER_PREFS$;
OBJ# PNAME          VALNUM VALCHAR                                                      CHGTIME                           SPARE1
----- ---------- ---------- ------------------------------------------------------------ --------------------------------- ------
73788 METHOD_OPT            FOR ALL COLUMNS SIZE AUTO FOR COLUMNS  INSUID SIZE 1         2016-06-24 15:35:10.005000 +08:00

--另外这样建立的索引键值太长,索引占用空间也很大.

SYSTEM@192.168.xx.yyy:1521/orcl> validate index tjpacs.PACS_IMAGE$INSUID;
Index analyzed.

SYSTEM@192.168.xx.yyy:1521/orcl> @ &r/i
    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS35M
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
         3       4480 PACS_IMAGE     184892       4317    20799904       8000       4316         84      372844       8032           2             244        181921
                      $INSUID

MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
                3    35210688   21172748         61   1.01633126           4.00816563          0            0              0                0

--一个块仅仅保存4个键值,索引达到了35M,.而表104M.

总之:在生产系统要慎用Nvarchar2类型.不要打着什么国际化的幌子骗人,开发应该认真了解这种数据类型,实际上这样的程序就是垃圾.
再回头非常困难.