温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

oracle 11g 扩展统计信息extended_stats

发布时间:2020-08-09 06:15:20 来源:ITPUB博客 阅读:346 作者:kunlunzhiying 栏目:关系型数据库
<span style="font-size:16px;"><strong>oracle 11g在统计信息收集方面增加了扩展统计信息的特性,它可以收集一个表中相关列上的统计信息,也可以收集函数表达式上的</strong></span><br /> <span style="font-size:16px;"><strong>统计信息.使选择率,成本的估计更加准确,也更容易走正确的执行计划.在相关列上收集统计信息,好处还是很明显的.例如两列在逻辑</strong></span><br /> <span style="font-size:16px;"><strong>上有一定的关系,但如果只是对这两个列单独做统计信息的收集,根据多条件的选择率计算{(A AND B的选择率为:OPSEL[a]*OPSEL[b]);</strong></span><br /> <span style="font-size:16px;"><strong>(A OR B 的选择率为:OPSEL[a]+OPSEL[b]-OPSEL[a]OPSEL[b]);(NOT A的选择率为:1-OPSEL[a])},估算出来的选择率就可能偏差很大.</strong></span><br /> <br /> <span style="font-size:16px;"><strong>以下测试:</strong></span><br /> <strong>DB Version:11.2.0.4</strong><br /> <span style="font-size:16px;"><strong>----产生测试数据</strong></span><br /> <span style="font-size:16px;">drop table scott.test01 purge;</span><br /> <span style="font-size:16px;">create table scott.test01</span><br /> <span style="font-size:16px;">as select * from dba_objects;</span><br /> <br /> <span style="font-size:16px;"><strong>--把object_name 更新为和object_type一样,用于测试.</strong></span><br /> <span style="font-size:16px;">update scott.test01</span><br /> <span style="font-size:16px;">set object_name=object_type;</span><br /> <span style="font-size:16px;">commit;</span><br /> <br /> <span style="font-size:16px;"><strong>1.收集单列统计信息,查看执行计划</strong></span><br /> <span style="font-size:16px;"><strong>--收集单列统计信息</strong></span><br /> <span style="font-size:16px;">begin</span><br /> <span style="font-size:16px;">dbms_stats.gather_table_stats('scott','test01');</span><br /> <span style="font-size:16px;">end;</span><br /> <span style="font-size:16px;"><strong>--查看表的行数</strong></span><br /> <span style="font-size:16px;">select&nbsp; table_name,num_rows from dba_tables</span><br /> <span style="font-size:16px;">where owner = 'SCOTT' and table_name = 'TEST01';</span><br /> <span style="font-size:16px;">/*</span><br /> <span style="font-size:16px;">TABLE_NAME&nbsp; NUM_ROWS</span><br /> <span style="font-size:16px;">TEST01&nbsp; 87212</span><br /> <span style="font-size:16px;">*/</span><br /> <span style="font-size:16px;"><strong>--产生语句的执行计划</strong></span><br /> <span style="font-size:16px;">explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';</span><br /> <br /> <span style="font-size:16px;">SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; object_name,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cardinality,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bytes,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; io_cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu_cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; time</span><br /> <span style="font-size:16px;">&nbsp; FROM plan_table</span><br /> <span style="font-size:16px;">&nbsp;START WITH id = 0</span><br /> <span style="font-size:16px;">CONNECT BY PRIOR id = parent_id;</span><br /> <span style="font-size:16px;">/*</span><br /> <span style="font-size:16px;">OPERATION&nbsp; OPTIONS&nbsp; OBJECT_NAME&nbsp; CARDINALITY&nbsp; BYTES&nbsp; IO_COST&nbsp; CPU_COST&nbsp; COST&nbsp; TIME</span><br /> <span style="font-size:16px;">SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 41&nbsp; 3362&nbsp; 347&nbsp; 35338490&nbsp; 348&nbsp; 5</span><br /> <span style="font-size:16px;">&nbsp; TABLE ACCESS&nbsp; FULL&nbsp; TEST01&nbsp; 41&nbsp; 3362&nbsp; 347&nbsp; 35338490&nbsp; 348&nbsp; 5</span><br /> <span style="font-size:16px;">*/</span><br /> <span style="font-size:16px;"><strong>这里可以看到,估算的返回行数是41,显然和实际相差很远</strong></span><br /> <span style="font-size:16px;">rollback;</span><br /> <br /> <span style="font-size:16px;"><strong>--行数估算</strong></span><br /> <span style="font-size:16px;">&nbsp; select rpad(column_name, 30, ' ') column_name,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(num_distinct, 8, ' ') num_distinct,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(num_nulls, 8, ' ') num_nulls,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(avg_col_len, 6, ' ') avg_col_len,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad(density, 20, ' ') density,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; histogram</span><br /> <span style="font-size:16px;">&nbsp; from dba_tab_col_statistics</span><br /> <span style="font-size:16px;">&nbsp;where owner = 'SCOTT'</span><br /> <span style="font-size:16px;">&nbsp;&nbsp; and table_name = 'TEST01'</span><br /> <span style="font-size:16px;">&nbsp;&nbsp; and column_name&nbsp; in ('OBJECT_NAME','OBJECT_TYPE');</span><br /> <span style="font-size:16px;">/*&nbsp; &nbsp;</span><br /> <span style="font-size:16px;">COLUMN_NAME&nbsp; NUM_DISTINCT&nbsp; LOW_VALUE&nbsp; HIGH_VALUE&nbsp; NULLABLE&nbsp; NUM_NULLS&nbsp; AVG_COL_LEN&nbsp; DENSITY&nbsp; HISTOGRAM</span><br /> <span style="font-size:16px;">OBJECT_NAME&nbsp;&nbsp; 46&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLUSTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XML SCHEMA&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .0217391304347826&nbsp;&nbsp;&nbsp;&nbsp; NONE</span><br /> <span style="font-size:16px;">OBJECT_TYPE&nbsp;&nbsp; 46&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLUSTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XML SCHEMA&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .0217391304347826&nbsp;&nbsp;&nbsp;&nbsp; NONE</span><br /> <span style="font-size:16px;">*/</span><br /> <span style="font-size:16px;"><strong>估算的返回行数是41,是由两个列的density相乘再乘以表的行数得到,.0217391304347826*.0217391304347826*87212=41.2155009451796=41</strong></span><br /> <span style="font-size:16px;"><strong>&nbsp;</strong> &nbsp;</span><br /> <span style="font-size:16px;"><strong>2.收集多列扩展统计信息,查看执行计划</strong></span><br /> <span style="font-size:16px;"><strong>--收集多列扩展统计信息</strong></span><br /> <span style="font-size:16px;">&nbsp; begin</span><br /> <span style="font-size:16px;">&nbsp; dbms_stats.gather_table_stats('scott','test01',method_opt =&gt;'for columns (object_name,object_type)');</span><br /> <span style="font-size:16px;">&nbsp; end;</span><br /> <br /> <span style="font-size:16px;"><strong>--产生语句的执行计划 </strong>&nbsp;</span><br /> <span style="font-size:16px;">&nbsp; explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';</span><br /> <br /> <span style="font-size:16px;">SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; options,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; object_name,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cardinality,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bytes,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; io_cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu_cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cost,</span><br /> <span style="font-size:16px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; time</span><br /> <span style="font-size:16px;">&nbsp; FROM plan_table</span><br /> <span style="font-size:16px;">&nbsp;START WITH id = 0</span><br /> <span style="font-size:16px;">CONNECT BY PRIOR id = parent_id;</span><br /> <span style="font-size:16px;">/*</span><br /> <span style="font-size:16px;">OPERATION&nbsp; OPTIONS&nbsp; OBJECT_NAME&nbsp; CARDINALITY&nbsp; BYTES&nbsp; IO_COST&nbsp; CPU_COST&nbsp; COST&nbsp; TIME</span><br /> <span style="font-size:16px;">SELECT STATEMENT&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;5303&nbsp;&nbsp; &nbsp;498482&nbsp;&nbsp; &nbsp;347&nbsp;&nbsp; &nbsp;36285951&nbsp;&nbsp; &nbsp;348&nbsp;&nbsp; &nbsp;5</span><br /> <span style="font-size:16px;">&nbsp; TABLE ACCESS&nbsp;&nbsp; &nbsp;FULL&nbsp;&nbsp; &nbsp;TEST01&nbsp;&nbsp; &nbsp;5303&nbsp;&nbsp; &nbsp;498482&nbsp;&nbsp; &nbsp;347&nbsp;&nbsp; &nbsp;36285951&nbsp;&nbsp; &nbsp;348&nbsp;&nbsp; &nbsp;5</span><br /> <span style="font-size:16px;">*/</span><br /> <span style="font-size:16px;"><strong>这里可以看到,估算的返回行数是5303,已经基本上和实际返回行数相近.</strong></span><br /> <br /> <span style="font-size:16px;"><strong>PS:</strong></span><br /> <span style="font-size:16px;"><strong>1.扩展统计信息的收集,可以用select dbms_stats.create_extended_stats('scott','test01','(object_name,object_type)')from dual</strong></span><br /> <span style="font-size:16px;"><strong>创建扩展统计列,然后dbms_stats.gather_table_stats('scott','test01')收集统计信息,也可以直接在</strong></span><br /> <span style="font-size:16px;"><strong>dbms_stats.gather_table_stats中的method_opt属性同时建立扩展统计又收集统计数据.</strong></span><br /> <span style="font-size:16px;"><strong>2.oracle 11g不仅可以收集多列扩展统计信息,还可以收集函数和表达式的扩展统计信息.</strong></span><br /> <br />
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI