前言
之前和大家分享过oracle 11g下的一个新特性——收集多列统计信息()，今天和大家分享oracle 12c的一个新特性——自动检测有用列组信息。二者相得益彰，大家可以具体情况酌情使用。
言归正传，我们可以针对一个表，基于特定的工作负荷，通过使用dbms_stats.seed_col_usage和report_col_usage来确定我们需要哪些列组。当你不清除需要创建哪个扩展统计信息时，这个技术是非常有用的。需要注意的是，这种技术不适用于包含表达式列的统计工作。
接下来，我们通过例子来学习这个的新特性。
一、环境准备
首先，我们创建测试表customers_test，基于sh示例用户下的customers表。
sql> select banner from v$version; banner -------------------------------------------------------------------------------- oracle database 12c enterprise edition release 12.1.0.2.0 - 64bit production pl/sql release 12.1.0.2.0 - production core 12.1.0.2.0 production tns for linux: version 12.1.0.2.0 - production nlsrtl version 12.1.0.2.0 - production sql> sql> conn sh/sh@hoegh connected. sql> sql> drop table customers_test; drop table customers_test * error at line 1: ora-00942: table or view does not exist sql> create table customers_test as select * from customers; table created. sql> select count(*) from customers_test; count(*) ---------- 55500 sql>
二、收集统计信息
sql> sql> exec dbms_stats.gather_table_stats(user, 'customers_test'); pl/sql procedure successfully completed. sql>
三、开启负载监控
另外打开一个会话，通过sys用户登录，开启负载监控。其中，seed_col_usage的第三个参数表示监控的时间，单位是秒，300表示5分钟。
sql> show user user is “sys” sql> begin dbms_stats.seed_col_usage(null,null,300); end; / 2 3 4 pl/sql procedure successfully completed. sql>
四、使用explain plan for查询执行计划
sql>
sql> explain plan for
select *
from customers_test
where cust_city = 'los angeles'
and cust_state_province = 'ca'
and country_id = 52790; 2 3 4 5 6
explained.
sql>
sql> select plan_table_output
from table(dbms_xplan.display('plan_table', null,'basic rows')); 2
plan_table_output
--------------------------------------------------------------------------------
plan hash value: 2112738156
----------------------------------------------------
| id | operation | name | rows |
----------------------------------------------------
| 0 | select statement | | 1 |
| 1 | table access full| customers_test | 1 |
----------------------------------------------------
8 rows selected.
sql>
从执行计划来看，查询结果只有1列。我们暂且记下这个结果。
五、查看列使用信息
此时，我们可以通过report_col_usage来查看列的使用信息。
我们看到，oracle帮我们检测到了一个有用的列组信息，包括customers_test、cust_city和cust_state_province三列。
sql> sql> set long 100000 sql> set lines 120 sql> set pages 0 sql> select dbms_stats.report_col_usage(user, 'customers_test') 2 from dual; legend: ....... eq : used in single table equality predicate range : used in single table range predicate like : used in single table like predicate null : used in single table is (not) null predicate eq_join : used in equality join predicate noneq_join : used in non equality join predicate filter : used in single table filter predicate join : used in join predicate group_by : used in group by expression ............................................................................... ############################################################################### column usage report for sh.customers_test ......................................... 1. country_id : eq 2. cust_city : eq 3. cust_state_province : eq 4. (cust_city, cust_state_province, country_id) : filter ############################################################################### sql>
六、创建扩展统计信息
检测工作完成后，我们可以通过create_extended_stats方法来创建扩展统计信息。其中，黄色标注部分就是创建对象的名称。
sql> sql> select dbms_stats.create_extended_stats(user, 'customers_test') from dual; ############################################################################### extensions for sh.customers_test ................................ 1. (cust_city, cust_state_province, country_id) : sys_stumz$c3aihlpbroi#ska58h_n created ############################################################################### sql>
七、重新收集统计信息
sql> sql> exec dbms_stats.gather_table_stats(user,'customers_test'); pl/sql procedure successfully completed. sql>
八、查看user_tab_col_statistics，确认列统计信息
通过查询user_tab_col_statistics，我们可以获取到刚刚创建的列组对象，和第6步的输出结果是一致的。
sql> sql> col column_name for a30 sql> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'customers_test' order by 1; 2 3 4 country_id 19 frequency cust_city 620 hybrid cust_city_id 620 none cust_credit_limit 8 none cust_eff_from 1 none cust_eff_to 0 none cust_email 1699 none cust_first_name 1300 none cust_gender 2 none cust_id 55500 none cust_income_level 12 none cust_last_name 908 none cust_main_phone_number 51344 none cust_marital_status 11 none cust_postal_code 623 none cust_src_id 0 none cust_state_province 145 frequency cust_state_province_id 145 none cust_street_address 49900 none cust_total 1 none cust_total_id 1 none cust_valid 2 none cust_year_of_birth 75 none sys_stumz$c3aihlpbroi#ska58h_n 620 hybrid 24 rows selected. sql>
九、重新查询执行计划
我们看到，在第4步中查询执行计划中，rows为1；现在呢，是867。这差距也忒大了点儿。
sql>
sql> explain plan for
select *
from customers_test
where cust_city = 'los angeles'
and cust_state_province = 'ca'
and country_id = 52790; 2 3 4 5 6
explained.
sql>
sql> select plan_table_output
from table(dbms_xplan.display('plan_table', null,'basic rows')); 2
plan hash value: 2112738156
----------------------------------------------------
| id | operation | name | rows |
----------------------------------------------------
| 0 | select statement | | 867 |
| 1 | table access full| customers_test | 867 |
----------------------------------------------------
8 rows selected.
sql>
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对www.887551.com的支持。