欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle 12c新特性之如何检测有用的多列统计信息详解

程序员文章站 2022-09-06 15:32:06
前言 之前和大家分享过oracle 11g下的一个新特性——收集多列统计信息(),今天和大家分享oracle 12c的一个新特性——自动检测有用列组信息。二者相得益彰,大...

前言

之前和大家分享过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>

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对的支持。