[20170114]12c varchar2类型直方图.txt

[20170114]12c varchar2类型直方图.txt

--我曾经提到慎用nvarchar2数据类型,链接:http://blog.itpub.net/267265/viewspace-2120925/
--我那里提到数据类型nvarchar2类型,因为1个字符占用2个字节,这样如果前面16个字符重复很多,直方图的建立就是鸡肋,
--毫无用处(因为分析仅仅对前面32个字节有效),12c 直方图支持更多类型: 高度直方图,频率直方图.混和类型(HYBRID).

--看看12c关于直方图方面的变化,通过例子说明:

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table t (a varchar2(100));
insert into t select  '12345678901234567890123456789012'||level||'3456789123456789123456' a from dual connect by level < 5000;
commit ;

2.建立直方图:

SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2049'); END;

*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 2049
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 1
--2049 太大了,11g最大支持255.

SCOTT@test01p> exec DBMS_STATS.GATHER_TABLE_STATS(null,'T',method_opt => 'FOR ALL COLUMNS SIZE 2048');
PL/SQL procedure successfully completed.

--//12C可以支持2048.

SELECT COLUMN_NAME
      ,NUM_DISTINCT
      ,DENSITY
      ,NUM_BUCKETS
      ,HISTOGRAM
      ,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
      ,UTL_RAW.cast_to_varchar2 (low_value) L_A
      ,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
      ,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
  FROM USER_TAB_COL_STATISTICS
 WHERE table_name = 'T';

Record View
As of: 2017/1/14 21:25:12

COLUMN_NAME:   A
NUM_DISTINCT:  4999
DENSITY:       0.0002
NUM_BUCKETS:   2048
HISTOGRAM:     HYBRID
L_LENGTH:      58
L_A:           1234567890123456789012345678901210003456789123456789123456
H_LENGTH:      57
H_A:           123456789012345678901234567890129993456789123456789123456

--//你可以发现保存的长度发生了变量,而且bucket的数量增加到了2048.直方图类型HYBRID.
--//也可以查询:select * from DBA_TAB_HISTOGRAMS where owner=user and table_name='T' order by endpoint_number;
--//结果太长,大家可以自行检测.

3.再来看看查询的sql语句:

SCOTT@test01p> alter session set statistics_level=all;
Session altered.

SCOTT@test01p> select * from t where a='12345678901234567890123456789012'||1001||'3456789123456789123456';
A
----------------------------------------------------------
1234567890123456789012345678901210013456789123456789123456

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  c4asjnvdvyqhn, child number 0
-------------------------------------
select * from t where a='12345678901234567890123456789012'||1001||'34567
89123456789123456'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    15 (100)|          |      1 |00:00:00.01 |      54 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |    59 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |      54 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"='1234567890123456789012345678901210013456789123456789123456')

--//可以发现E-Rows与A-Rows一样.

4.也就是oracle 12c现在能很好的处理长字符串直方图问题,但是究竟支持多长,我继续测试:

create table t1 (a varchar2(1000));
insert into  t1 select  lpad('x',996,'x')||level a from dual connect by level < 5000;
commit ;

exec DBMS_STATS.GATHER_TABLE_STATS(null,'T1',method_opt => 'FOR ALL COLUMNS SIZE 2048');

SELECT COLUMN_NAME
      ,NUM_DISTINCT
      ,DENSITY
      ,NUM_BUCKETS
      ,HISTOGRAM
      ,LENGTH (UTL_RAW.cast_to_varchar2 (low_value)) L_length
      ,UTL_RAW.cast_to_varchar2 (low_value) L_A
      ,LENGTH (UTL_RAW.cast_to_varchar2 (HIGH_VALUE)) H_length
      ,UTL_RAW.cast_to_varchar2 (HIGH_VALUE) H_A
  FROM USER_TAB_COL_STATISTICS
 WHERE table_name = 'T1';

 Record View
As of: 2017/1/14 21:41:33

COLUMN_NAME:   A
NUM_DISTINCT:  4999
DENSITY:       0.0002
NUM_BUCKETS:   1
HISTOGRAM:     HYBRID
L_LENGTH:      64
L_A:           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
H_LENGTH:      64
H_A:           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--//也就是比原来增加了1倍,现在支持64字节.但是看看现在的情况NUM_BUCKETS=1,也就是仅仅1个桶.

SCOTT@test01p> select * from t1 where a=lpad('x',996,'x')||1001;

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2cua094wf06kf, child number 0
-------------------------------------
select * from t1 where a=lpad('x',996,'x')||1001
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   204 (100)|          |      1 |00:00:00.01 |     753 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |   4999 |  4886K|   204   (0)| 00:00:01 |      1 |00:00:00.01 |     753 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
               ...
              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1001')
32 rows selected.

--//现在E-Rows,A-Rows相差很远了.这样的直方图也无用.

时间： 2024-09-28 11:32:28

[20170114]12c varchar2类型直方图.txt的相关文章

[20170603]12c Top Frequency histogram.txt

[20170603]12c Top Frequency histogram.txt --//个人对直方图了解很少,以前2种直方图类型对于目前的许多应用来讲已经足够,或者讲遇到的问题很少. --//抽一点点时间,简单探究12c Top Frequency histogram. --//以前的频率直方图Frequency histogram,受限bucket(桶的大小),如果有255个不同值,oracle分析后不会建立频率直方图,而是建立高 --//度直方图.这样的情况会导致一些流行值的统计在显示执

[20170620]11G 12c expand sql text.txt

[20170620]11G 12c expand sql text.txt --//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来. --//讨论链接:http://www.itpub.net/thread-2088981-1-1.html --//再次感谢solomon_007的指点: set long 20000 set serveroutput on declare     L_sqltext clob := null;     l_version varc

[20120905]删除直方图.txt

[20120905]删除直方图.txt 维护直方图是一个成本很高的操作,9i的缺省模式Method_Opt=> 'FOR ALL COLUMNS SIZE REPEAT ',而10g的缺省模式 Method_Opt => 'FOR ALL COLUMNS SIZE AUTO ',这样会导致在许多不必要的字段建立直方图. 我自己在维护10g的数据库也存在许多sql执行计划不稳定的情况,我后来采取的方法就是把这些控制在自己的手中. 修改10g的缺省模式为Method_Opt=> 'FOR A

[20130809]12c Clustering Factor(2).txt

[20130809]12c Clustering Factor(2).txt 参考以下链接,自己重复测试: http://richardfoote.wordpress.com/2013/05/14/clustering-factor-calculation-improvement-part-ii-blocks-on-blocks/ 1.测试环境: SQL> @ver BANNER                                                           

oracle-Oracle中varchar2类型的id为自动增长

问题描述 Oracle中varchar2类型的id为自动增长 Oracle中id的类型为varchar2(10)主键,怎样创建自动增长的id 解决方案 可以的,使用序列 创建序列 create sequence SEQ_CEID minvalue 1 maxvalue 9999999999 start with 41 increment by 1 cache 20; 创建表,插入,查询 create table table_f ( id varchar(10) primary key, name

insert-spring jdbcTemplate怎么得到新插入数据的主键（varchar2类型）

问题描述 spring jdbcTemplate怎么得到新插入数据的主键(varchar2类型) 在网上看到用KeyHelper 可以得到数字类型的主键,但是表的主键guid是varchar类型的,用Spring的jdbcTemplate有什么方法可以得到新插入数据的主键? 用的是Hibernate 数据库是Oracle 求大神帮忙了啊! 不用先插入了,再查询的方法怎么破?

[20160806]12c 与 WMSYS.WM_CONCAT.txt

[20160806]12c 与 WMSYS.WM_CONCAT.txt --前几天有人问临时表空间消耗的问题,链接: http://www.itpub.net/thread-2065053-1-1.html --我提到11g的wmsys.wm_concat也会使用临时表空间. --链接:http://blog.itpub.net/267265/viewspace-1356256/ --但是在12c测试发现12c已经不存在WMSYS.WM_CONCAT,看来要建议开发不要在使用它连接字符串. --

[20131109]deferred segment creation与12c的exp命令.txt

[20131109]deferred segment creation与12c的exp命令.txt 参考链接:http://space.itpub.net/267265/viewspace-713311 昨天想导出一些数据在自己的12c测试环境,发现具有段延迟建立特性的表使用exp也能导出. 例子如下: SCOTT@test01p> @ver BANNER                                                                       

[20131128]12c的dbms_utility.expand_sql_text.txt

[20131128]12c的dbms_utility.expand_sql_text.txt SCOTT@ztest> @verBANNER                                                                              CON_ID-------------------------------------------------------------------------------- ----------Oracl