[20161208]11g直方图与char数据类型.txt
--以前看tom大师的书提到过不要使用char数据类型,哪怕是char(1)也不要使用,最近看了几篇blob里面都提到了11g升级后会出现char数
--据类型直方图统计发生了变化,我重复别人的例子来说明.再次强调不要生产环境使用char类型.
--参考链接:http://blog.dbi-services.com/histograms-on-character-strings-between-11-2-0-3-and-11-2-0-4/
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
SCOTT@book> create table DEMO ( flag char);
Table created.
SCOTT@book> @ &r/desc demo
Name Null? Type
----- -------- --------
FLAG CHAR(1)
--不指定长度,长度为1.
SCOTT@book> insert into DEMO select 'Y' from xmltable('1 to 100000');
100000 rows created.
SCOTT@book> insert into DEMO select 'N' from xmltable('1 to 1000');
1000 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select flag,to_char(ascii(flag),'XX'),count(*) from DEMO group by flag;
F TO_ COUNT(*)
- --- ----------
Y 59 100000
N 4E 1000
SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false,Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 5');
PL/SQL procedure successfully completed.
select endpoint_number,endpoint_value
,to_char(endpoint_value,rpad('FM',65,'X'))
,utl_raw.cast_to_varchar2(hextoraw(to_char(endpoint_value,rpad('FM',65,'X')))) c20
from user_histograms h where table_name='DEMO';
ENDPOINT_NUMBER ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X')) C20
--------------- -------------- ---------------------------------------------------------------- --------------------
101000 4.6211E+35 590000000000127D2B51B120000000 Y }+Q?
1000 4.0500E+35 4E0000000000181F436C7BBB200000 N Cl{?
--你可以注意实际参加运算的Y/N后面的字符是0x00.}
--而如果你设置参数optimizer_features_enable.
SCOTT@book> alter session set optimizer_features_enable='11.2.0.3';
Session altered.
SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false,Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 5');
PL/SQL procedure successfully completed.
select endpoint_number,endpoint_value
,to_char(endpoint_value,rpad('FM',65,'X'))
,utl_raw.cast_to_varchar2(hextoraw(to_char(endpoint_value,rpad('FM',65,'X')))) c20
4 from user_histograms h where table_name='DEMO';
ENDPOINT_NUMBER ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X')) C20
--------------- -------------- ---------------------------------------------------------------- --------------------
1000 4.0565E+35 4E20202020203A7BB119D5F6000000 N :{闭?
101000 4.6277E+35 59202020202034D998FF0B5AE00000 Y 4贅? Z?
--注意实际参加运算的Y/N后面的字符是0x20.} 也就是使用空格.
2.测试:
--这个时候退出,在登录执行如下:
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> show parameter optimizer_features_enable
NAME TYPE VALUE
------------------------- ------ ---------
optimizer_features_enable string 11.2.0.4
--恢复原来设置.
SCOTT@book> select count(*) from demo where flag='Y';
COUNT(*)
----------
100000
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 8var563j3tg8g, child number 0
-------------------------------------
select count(*) from demo where flag='Y'
Plan hash value: 2180342005
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 69 (100)| | 1 |00:00:00.03 | 219 |
| 1 | SORT AGGREGATE | | 1 | 1 | 2 | | | 1 |00:00:00.03 | 219 |
|* 2 | TABLE ACCESS FULL| DEMO | 1 | 500 | 1000 | 69 (2)| 00:00:01 | 100K|00:00:00.02 | 219 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEMO@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='Y')
--你可以发现这时E-Rows=500.
SCOTT@book> select * from DBA_TAB_HISTOGRAMS where owner=user and table_name='DEMO';
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
------ ---------- -------------------- --------------- -------------- ----------
SCOTT DEMO FLAG 1000 4.0565E+35
SCOTT DEMO FLAG 101000 4.6277E+35
select * from DBA_TAB_cols where owner=user and table_name='DEMO';
Record View
As of: 2016/12/8 9:24:56
OWNER: SCOTT
TABLE_NAME: DEMO
COLUMN_NAME: FLAG
DATA_TYPE: CHAR
DATA_TYPE_MOD:
DATA_TYPE_OWNER:
DATA_LENGTH: 1
DATA_PRECISION:
DATA_SCALE:
NULLABLE: Y
COLUMN_ID: 1
DEFAULT_LENGTH:
DATA_DEFAULT:
NUM_DISTINCT: 2
LOW_VALUE: 4E
HIGH_VALUE: 59
DENSITY: 0.00000495049504950495
NUM_NULLS: 0
NUM_BUCKETS: 2
LAST_ANALYZED: 2016/12/8 9:19:24
SAMPLE_SIZE: 101000
CHARACTER_SET_NAME: CHAR_CS
CHAR_COL_DECL_LENGTH: 1
GLOBAL_STATS: YES
USER_STATS: NO
AVG_COL_LEN: 2
CHAR_LENGTH: 1
CHAR_USED: B
V80_FMT_IMAGE: NO
DATA_UPGRADED: YES
HIDDEN_COLUMN: NO
VIRTUAL_COLUMN: NO
SEGMENT_COLUMN_ID: 1
INTERNAL_COLUMN_ID: 1
HISTOGRAM: FREQUENCY
QUALIFIED_COL_NAME: FLAG
-- 实际上0x5920 与 0x5900 存在很大差异,运算并没有按照直方图来计算,而使用DENSITY来计算.0.00000495049504950495 * 1000* 101000 =499.99999999999995.
-- 作者做10053测试
Column (#1):
NewDensity:0.004950, OldDensity:0.000005 BktCnt:101000.000000, PopBktCnt:101000.000000, PopValCnt:2, NDV:2
--这个新旧Density,为什么相差1000,我不知道?
--如果你重新分析就ok了.
SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false,Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 5');
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from demo where flag='Y';
COUNT(*)
----------
100000
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8var563j3tg8g, child number 0
-------------------------------------
select count(*) from demo where flag='Y'
Plan hash value: 2180342005
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 69 (100)| | 1 |00:00:00.03 | 219 |
| 1 | SORT AGGREGATE | | 1 | 1 | 2 | | | 1 |00:00:00.03 | 219 |
|* 2 | TABLE ACCESS FULL| DEMO | 1 | 100K| 195K| 69 (2)| 00:00:01 | 100K|00:00:00.02 | 219 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEMO@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='Y')
--这样E-Rows正确了.所以作者建议升级后要重新分析.
3.另外我上面的测试使用数据类型char(1),改变长度呢?重复测试:
--drop table demo purge ;
create table DEMO ( flag char(20));
insert into DEMO select 'Y' from xmltable('1 to 100000');
insert into DEMO select 'N' from xmltable('1 to 1000');
commit ;
--实际上这个保存在数据库的信息是
SCOTT@book> select dump(flag,16) from demo where rownum=1;
DUMP(FLAG,16)
------------------------------------------------------------------------------
Typ=96 Len=20: 59,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20
--有19个空格.
SCOTT@book> select flag,to_char(ascii(flag),'XX'),count(*) from DEMO group by flag;
F TO_ COUNT(*)
- --- ----------
Y 59 100000
N 4E 1000
SCOTT@book> exec dbms_stats.gather_table_stats(user,'DEMO',no_invalidate=>false,Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 5');
PL/SQL procedure successfully completed.
select endpoint_number,endpoint_value
,to_char(endpoint_value,rpad('FM',65,'X'))
,utl_raw.cast_to_varchar2(hextoraw(to_char(endpoint_value,rpad('FM',65,'X')))) c40
from user_histograms h where table_name='DEMO';
ENDPOINT_NUMBER ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,RPAD('FM',65,'X')) C40
--------------- -------------- ------------------------------------------- ----------------
1000 4.0500E+35 4E0000000000181F436C7BBB200000 N Cl{?
101000 4.6211E+35 590000000000127D2B51B120000000 Y }+Q?
--你可以发现oracle 11.2.0.4 改变了算法,取消了后面的空格,而使用0x00 参加运算.
--从我个人的建议不要在生产系统使用任何char类型,哪怕是char(1),而统一使用varchar2类型,即使是varchar2(1).
--jonathanlewis大师也写了一篇blog,链接https://jonathanlewis.wordpress.com/2016/12/02/histogram-upgrade/
--另外在链接里面还有一个链接指向直方图如何运算,链接:https://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/
--我摘要主要部分:
In an earlier note on interpreting the content of frequency histograms I made a throwaway comment about the extra
complexity of interpreting frequency histograms on character-based columns. This note starts to examine some of the
complications.
The driving problem behind character columns is that they can get quite large – up to 4,000 bytes – so the content of
an "accurate histogram" could become quite large, and Oracle seems to have taken a strategic decision (at some point in
history) to minimise this storage. As a result we can see an algorithm that works roughly as follows:
Take the first 15 bytes of the string (after padding the string with nulls (for varchar2) or spaces (for char))
Treat the result as a hexadecimal number, and convert to decimal
Round to 15 significant digits and store as the endpoint_value
If duplicate rows appear, store the first 32 characters (increased to 64 for 12c) of each string as the endpoint_actual_value
Given this algorithm, we can do an approximate reversal (which will only be needed when the endpoint_actual_value is not
available) by formatting the endpoint_value into a hex string, extracting the first six pairs of digits, converting to
numeric and applying the chr() function to get a character value. (You'll have to fiddle with this bit of code to handle
multibyte character sets, of course).
With a nice friendly single-byte character code, the first 5 characters will be extracted correctly (assuming there are
at least 5 characters in the string), and the sixth will be pretty close to the original. Here's an example (which also
includes the logic to convert the endpoint_number into a frequency):
--哎,许多还是不懂,放弃..