col_usage$与直方图的收集

最新推荐文章于 2023-02-13 17:45:04 发布
最新推荐文章于 2023-02-13 17:45:04 发布
阅读量97 收藏
点赞数

DB：11202，两节点RAC

 

建表脚本，id列分布极度不均

create table t1(id number(10), name varchar2(20)) nologging tablespace IBMDBA；

 begin

  for i in 1.. 99999 loop

    insert /*+ append */ into t1 values(999,lpad('I',20));

  end loop;

  for i in 1.. 100 loop

    insert /*+ append */into t1 values(i,lpad('I',20));

  end loop;

  commit;

end;

/

 

收集统计信息脚本，如无特殊说明，以下试验中均用此参数收集统计信息

exec dbms_stats.gather_table_stats('SYS','T1');

SQL> select dbms_stats.get_prefs('method_opt','SYS','T1') as pref from dual;

 

PREF

--------------------------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

SQL> select dbms_stats.get_prefs('estimate_percent','SYS','T1') from dual;

 

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','SYS','T1')

--------------------------------------------------------------------------------

DBMS_STATS.AUTO_SAMPLE_SIZE

 

新建表后的信息，其统计信息和直方图均无

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE HISTOGRAM

----- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- --------- ----------- ----------

NAME                                                                                                                                              NONE

ID                                                                                                                                                NONE

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

---------- ----------- -------------------

 

 

以上是创建后的基本信息，统计信息为空；

 

 

1

没有执行任何查询，收集统计信息

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

---------- ----------- -------------------

100099      100099 2013-03-21 23:22:14

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

----- --------------- -------------- --------------------

ID                  1            999

ID                  0              1

NAME                1     1.6681E+35

NAME                0     1.6681E+35

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

----- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049          1           1 2013-03-21 23:22:14      100099 NONE

ID             101 C102                                     C20A64                                    .00990099           1 2013-03-21 23:22:14      100099 NONE

 

SQL> select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

no rows selected

 

没有收集直方图，col_usage$也没有记录任何信息

 

 

2

执行以表列为where条件的sql(必须为硬解析)，再次收集统计信息

 

SQL>  select count(*) from t1 where name='I';

 

  COUNT(*)

----------

         0

收集统计信息

SQL>  select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------

    132928          2              1              0                 0           0          0          0 2013-03-21 23:24:54

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

---------- ----------- -------------------

100099      100099 2013-03-21 23:24:54

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

----- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 5.1103E-06           1 2013-03-21 23:24:54        5376 FREQUENCY

ID             101 C102                                     C20A64                                    .00990099           1 2013-03-21 23:24:54      100099 NONE

 

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

----- --------------- -------------- --------------------

ID                  0              1

ID                  1            999

NAME             5376     1.6681E+35

 

 

针对id列进行查询

SQL> select count(*) from t1 where id=999;

 

  COUNT(*)

----------

     99999

收集统计信息

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

---------- ----------- -------------------

    100099      100099 2013-03-21 23:28:02

 

SQL> select * from col_usage$ where OBJ#=132928;

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------

    132928          1              1              0                 0           0          0          0 2013-03-21 23:28:02

    132928          2              1              0                 0           0          0          0 2013-03-21 23:24:54

 

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

----- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9842E-06           1 2013-03-21 23:28:02        5512 FREQUENCY

ID             101 C102                                     C20A64                                   4.9842E-06           3 2013-03-21 23:28:02        5512 FREQUENCY

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

----- --------------- -------------- --------------------

ID                  1             15

ID                  2             31

ID               5512            999

NAME             5512     1.6681E+35

 

对id列也收集了直方图，bucket为3个；

结论：

当column出现在where子句时，其会被col_usage$记录并在下次收集统计信息时尝试收集其直方图；

 

 

3

Col_usage$的作用

修改一下案例2

执行select count(*) from t1 where id=999;

收集统计信息

删除col_usage$中的记录

重新收集统计信息

SQL>  select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

---------- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049          1           1 2013-03-22 02:34:17      100099 NONE

ID                  101 C102                                     C20A64                                    .00990099           1 2013-03-22 02:34:17      100099 NONE

此时id列没有直方图，因为col_usage$没有相应记录的缘故

 

注：不要手工更改col_usage$，否则很容易产生不可预知的问题

 

 

4

收集直方图类型

重建该表，其id列有101个distinct值，在不执行任何sql的情况下收集统计信息

SQL> select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

no rows selected

 

exec dbms_stats.gather_table_stats('SYS','T1',method_opt => 'for all columns size 100');

其显示如下

 

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

---------- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9969E-06           1 2013-03-22 03:05:44        5498 FREQUENCY

ID                  101 C102                                     C20A64                                   9.9938E-06         100 2013-03-22 03:05:44        5498 HEIGHT BAL

                                                                                                                                                                 ANCED

 

将bucket调大至101

exec dbms_stats.gather_table_stats('SYS','T1',method_opt => 'for all columns size 101');

反复执行多次，id的bucket数量会波动

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

---------- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9208E-06           1 2013-03-22 03:06:13        5583 FREQUENCY

ID                  101 C102                                     C20A64                                   4.9208E-06           9 2013-03-22 03:06:13        5583 FREQUENCY

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

---------- ------------ ---------------------------------------- ---------------------------------------- ---------- ----------- ------------------- ----------- ----------

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9923E-06           1 2013-03-22 03:05:21        5503 FREQUENCY

ID                  101 C102                                     C20A64                                   4.9923E-06           7 2013-03-22 03:05:21        5503 FREQUENCY

 

 

当列的distinct数量小于或等于指定的bucket数目时，会采用等频直方图，否则采用使用高度直方图

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-763737/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-763737/

congdiku5262
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
tc_1910_preproduction_src-r10238(docs)_intel_PXEboot_telemetry_t
10-01
Intel Solution for Transparent Computing (Documentation)Look for my other upload with the source and binary package for this.A login and system image server and client with an EFI hypervisor using PXE...
【DB笔试面试640】在Oracle中，基表COL_USAGE$的作用是什么？
小麦苗DBA宝典
09-13 476
♣题目部分在Oracle中，基表COL_USAGE$的作用是什么？ ♣答案部分从Oracle 9i开始引入了SYS.COL_USAGE$表用来跟踪列的使用情况，该...
数据字典基表---COL_USAGE$
ezbit
12-18 2204
从oracle9i开始，oracle为了监控column的使用情况，引入了col_usage$基表。col_usage$会记录数据库运行期间column作为谓词被使用的情况，这些记录信息会指导oracle如何生成column的直方图。 首先来看一看col_usage$的表结构： SQL> select * from v$version; BANNER --------------------
【数据库优化系列】优化需要用的系统表和视图
Axlsss的博客
02-13 481
【数据库优化】优化需要用的系统表和视图
CBO中SMON进程与col_usage$的维护
weixin_33955681的博客
07-12 139
从Oracle9i开始，为了监控column的使用信息，引入了一个对象col_usage$，用于记录运行时的COLUMN使用信息。这部分信息由SMON维护，所以当看到SMON报出相关的死锁、错误时不要惊讶，SMON做的工作是越来越杂了。在Oracle10g中，这个表的结构如下：create table col_usage$( obj# number...
在matlab中实现了自适应直方图均衡（AHE
06-14
自适应直方图均衡（Adaptive Histogram Equalization, AHE）是一种图像增强技术，它通过改变像素值分布来改善图像的对比度，特别是在图像局部区域。相比于传统的直方图均衡化，AHE考虑了图像的局部特性，可以更有效...
gtk4_col_view_test
03-22
标题“gtk4_col_view_test”和描述中的信息表明这是一个与GTK4库相关的测试项目，可能是用Rust编程语言编写的。GTK4是Gnome桌面环境的一部分，是一个用于创建图形用户界面（GUI）的跨平台工具包。Rust是一种系统级...
COL_Dashboard
03-21
"COL_Dashboard" 是一个基于Python开发的项目，旨在提供一个综合的数据可视化仪表板。从标题和描述中我们可以推测，这个项目可能是一个用于展示、分析和交互数据的工具，可能是用Python的库如matplotlib, seaborn, ...
col_mnage_code.zip
12-14
7. **测试与评估**：在完成白平衡的编程实现后，需要对结果进行测试，以确保在各种光照条件下都能获得准确的色彩还原。这可能涉及到创建不同光源条件的模拟图像，或者使用真实拍摄的图像进行测试。 总的来说，"col_...
col_usage$用途
crjd3851的博客
03-07 227
从oracle9i开始引入了col_usage$表用来跟踪表列的使用情况，该功能通过隐含参数_column_tracking_level(动态参数)控制，如_column_tracking_level=0取消该功能，_colum...
了解你所不知道的SMON功能(四):维护col_usage$字典基表
weixin_33728708的博客
06-12 124
SMON的作用还包括维护col_usage$列监控统计信息基表。 最早在9i中引入了col_usage$字典基表，其目的在于监控column在SQL语句作为predicate的情况，col_usage$的出现完善了CBO中柱状图自动收集的机制。 create table col_usage$ ( obj# number, ...
[统计信息系列4] 列的统计信息
weixin_30895723的博客
05-30 311
（一）列的统计信息查看列的统计信息，主要包含了列的distinct值的数量、列的null值数量、列的最小值、列的最大值等，这些统计信息存储在SYS.HIST_HEAD$中，可以通过数据字典查看：DBA_TAB_COL_STATISTICS：查看表的列统计信息 DBA_PART_COL_STATISTICS：查看分区表的分区列的统计信息 DBA_SUBPART_COL_STATISTICS...
sql col_usage$
bjchangxiao的专栏
03-20 413
 the AVG_ROW_LEN statistic for a table partition indicates the average size of a row in the table partition. The BLEVEL statistic of an index partition indicates how many blocks have to be visit
ora-08102 and col_usage$
aocu40057610的博客
09-27 85
详见原文博客链接 http://www.killdb.com/2012/07/26/ora-08102-and-col_usage-2.html 转载于:https://www.cnblogs.com/lovewifelovelife/archive/2012/07/26/3295043.html
CBO中 SMON 进程与 col_usage$ 的维护
weixin_33725515的博客
01-30 113
为什么80%的码农都做不了架构师？>>> ...
Oracle 数据字典表 -- SYS.COL$
tianshibaijia
09-28 288
一.数据字典表SYS.COL$说明 Oracle的数据字典分两类，一个数据字典表，另一个是数据字典视图。 数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。 数据字典表由$ORACLE_HOME/rdbms/admin/sql.bsq脚本创建。 这些数据字典表名称不好记，所以Or...
[20111215]sys.col_usage$和intcol# = 1001的问题.txt
12-15 132
[20111215]sys.col_usage$和intcol# = 1001的问题.txt讨论链接:http://www.itpub.net/thread-1454515-1-1.htmlcol_usage$字典基表，其目的在...
foreach clk_gate_name $all_crg_gates { set clk_source_col [all_fanin -to ${clk_gate_name}/cp -flat -startpoints_only] set clk_source [get_object_name $clk_source_col] create_generated_clock [get_pins ${clk_gate_name}/hand_latch/Q -source $clk_source -divide_by 1 if {$clk_source == "core_clk"} { set all_core_clks [add_to_collection $all_core_clks [get_clock ${clk_gate_name}/hand_latch/Q] ] } } foreach clk_gate_name $all_crm_gates { set clk_source_crg_col [all_fanin -to ${clk_gate_name}/cp -flat -only_cells -startpoints_only ] set clk_source_crg [get_object_name $clk_source_crg_col] set clk_source [get_object_name [remove_from_collection -intersect [all_fanin -to ${clk_source_crg}/CP -flat -startpoints_only] $clk_ports_list]] create_gennerated_clock [get_pins $clk_gate_name/hand_latch/Q] -source ${clk_source_crg}/Q -divide_by 1 if {$clk_source == "core_clk"} { set_all_core_clks [add_to_collection $all_core_clks [get_clock ${clk_gate_name}/hand_latch/Q] ] } }，这两段tcl有什么相同之处和不同之处
最新发布
06-03
这两段 TCL 脚本的相同之处是它们都含有一个类似的循环结构，都是对一个变量进行遍历操作，然后对这个变量进行一系列操作。 它们的不同之处在于循环中对变量的操作不同，第一段脚本的循环中涉及到了获取对象名称、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值