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

前言

之前和大家分享过Oracle 11g下的一个新特性——收集多列统计信息(http://www.jb51.net/article/109514.htm),今天和大家分享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>

总结

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

时间: 2017-03-24

Oracle 统计用户下表的数据量实现脚本

要想统计用户下所有表的数据量,可以查看user_tables,此表里面是统计信息,当然这个可能不太准,要想非常精确,需要直接count表.下面的脚本有异常不中断,可以重复执行的特点. create table bk_count_tables ( owner VARCHAR2(30), table_name VARCHAR2(30), part_col varchar2(100),--分区字段 row_s number, gather_time date ); create index ind_b

PDO取Oracle lob大字段,当数据量太大无法取出的问题的解决办法

首先,创建一个存储过程 get_clob: t_name:要查询的表名:f_name:要查询的字段名:u_id:表的主键,查询条件:l_pos:截取的开始位置: l_amount :截取长度: CREATE OR REPLACE PROCEDURE get_clob(t_name in varchar2, f_name in varchar, u_id in integer, l_pos in integer, l_amount in BINARY_INTEGER, ReturnValue ou

oracle数据库下统计专营店的男女数量的语句

Web_THBC 为表示层也就是页面(.aspx) BLL_THBC 为业务逻辑层 DAL_THBC 为数据库交互层 (向数据库执行SQL语句) Model_THBC 为实体类 DbHelper 为数据库连接类 统计个专营店的男女数量 复制代码 代码如下: select dua.UARTERS_ID,dua.UARTERS_NAME ,sum(case when sex = '1' then 1 else 0 end) as Man ,sum(case when sex = '0' then 1

oracle表空间中空表统计方法示例介绍

复制代码 代码如下: DECLARE v_table tabs.table_name%TYPE; v_sql VARCHAR2(888); v_q NUMBER; CURSOR c1 IS SELECT table_name tn FROM tabs; TYPE c IS REF CURSOR; c2 c; BEGIN DBMS_OUTPUT.PUT_LINE('以下为空数据表的表名:'); FOR r1 IN c1 LOOP v_table :=r1.tn; v_sql :='SELECT C

Oracle 11g收集多列统计信息详解

前言 通常,当我们将SQL语句提交给Oracle数据库时,Oracle会选择一种最优方式来执行,这是通过查询优化器Query Optimizer来实现的.CBO(Cost-Based Optimizer)是Oracle默认使用的查询优化器模式.在CBO中,SQL执行计划的生成,是以一种寻找成本(Cost)最优为目标导向的执行计划探索过程.所谓成本(Cost)就是将CPU和IO消耗整合起来的量化指标,每一个执行计划的成本就是经过优化器内部公式估算出的数字值. 我们在写SQL语句的时候,经常会碰到w

Oracle数据库按时间进行分组统计数据的方法

Oracle按不同时间分组统计的sql 如下表table1: 日期(exportDate) 数量(amount) -------------- ----------- 14-2月 -08 20 10-3月 -08 2 14-4月 -08 6 14-6月 -08 75 24-10月-09 23 14-11月-09 45 04-8月 -10 5 04-9月 -10 44 04-10月-10 88 注意:为了显示更直观,如下查询已皆按相应分组排序 1.按年份分组 select to_char(expo

Oracle Translate 统计字符出现的次数示例代码

特殊用法:统计字符串中 E 出现的次数: SELECT LENGTHB(TRANSLATE('ABCDEFGEFGDBE','E'||'ABCDEFGEFGDBE','E')) FROM DUAL; 等同于: SELECT LENGTHB('ABCDEFGEFGDBE')-LENGTHB(REPLACE('ABCDEFGEFGDBE','E','')) FROM DUAL; Translate 的用法 一.语法: TRANSLATE(string,from_str,to_str) 二.目的 返回

android 限制某个操作每天只能操作指定的次数(示例代码详解)

最近有个需求,要求启动页的拦截页每天只能显示3次,超过三次就显示别的页面,然后到第二天才可以再次显示,利用SharePreferences保存天数和每天的次数,大概是思路是:判断 如果是同一天,就去拿保存的次数,当次数小于3才执弹出拦截页,然后,每次弹出,次数就加1,并且保存次数和当天的时间:如果不是同一天,就把次数赋值为1,并且把当天赋值给最后访问的时间,然后保存当前的次数.具体实现如下: package com.example.demo1.test; import android.suppo

JavaScript实现的冒泡排序法及统计相邻数交换次数示例

本文实例讲述了JavaScript实现的冒泡排序法及统计相邻数交换次数.分享给大家供大家参考,具体如下: <html> <head>JS冒泡排序</head> <body> <script> var arr=[-1,-2,-30,-4,-5,-6]; var flag=false; //判断相邻两个数是否交换过 var n=0; //计算交换次数 for(var i=0;i<arr.length-1;i++){ //i表示的是每次找出来的最

JS实现区分中英文并统计字符个数的方法示例

本文实例讲述了JS实现区分中英文并统计字符个数的方法.分享给大家供大家参考,具体如下: <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> <title>js区分中英文统计字符个数</titl

Oracle 创建主键自增表示例代码

前言 本篇文章给大家主要介绍的是在oracle中如果创建自增长表的方法,这里要用到序列.下面话不多说,我们来看示例代码. 示例代码 create table tb_student ( id NUMBER(10) not null, createtime DATE not null, constraint PK_tb_student primary key (id) ); comment on table "tb_student" is '学生表'; comment on column

oracle实现一对多数据分页查询筛选示例代码

前言 今天项目测试运行的时候,遇到了一个奇怪的问题,这个问题说起来按sql语法的话是没有错误的 但是呢按照我们的业务来做区分就有些逻辑上的错误了, 下面请听我慢慢道来,在数据库中有两个数据, 先来看下第一次sql是如何写的 查询之后在外面做分页,很正常的逻辑,但是大家都发现了,这是一个多表查询,而且是一对多关系,这就有点问题了 先来看一个图 问题出现在哪呢? 1.需要对主表做分页数据查询, 如: limit 1,10 或 SELECT * FROM (SELECT A.* ,ROWNUM R F

php下用cookie统计用户访问网页次数的代码

如何创建 cookie? setcookie() 函数用于设置 cookie. 注释:setcookie() 函数必须位于 <html> 标签之前. 创建您的第一个PHP cookie 当您创建一个cookie,使用函数setcookie,你必须指定三个参数.这些参数是setcookie(名称,值,过期): 姓名:您的Cookie的名称.您将使用此名称以后检索你的cookie,所以不要忘记它! 价值:在您的cookie中存储的值.共同价值观是用户名(字符串)和最后的访问时间(日期). 到期:日

js统计页面的来访次数实现代码

脚本说明: 把如下代码加入<body>区域中 复制代码 代码如下: <SCRIPT language="JavaScript"><!-- function getCookieVal(offset) {var endstr=document.cookie.indexOf(";",offset);if(endstr==-1) endstr=document.cookie.length;return unescape(document.coo

Javascript中查找不以XX字符结尾的单词示例代码

首先,让我声明一下,我在写这篇文章之前花了2个多小时在弄正则表达式.悲~悲~悲~ 按照一般的思路,先来看看其他几个插找方式: 我以字符串 复制代码 代码如下: var str = "eattd gebcat gedat jadu geat beu"; 為例子. 1.以"ge"為开头的,结果应该是"gebcat, gedat, geat".因為单词以"ge"开头,则我可以放入一个新的数组供以后使用. 复制代码 代码如下: var

java实现的统计字符算法示例

本文实例讲述了java实现的统计字符算法.分享给大家供大家参考,具体如下: 统计字符: 概述:给定字符串,将它们进行分类,分别的去统计它们的个数及其字符 分类的有:字母 数字 中文 空格 等等 算法思路分析: 分别统计即可: 下面给出代码:(代码仅供参考) package javastudy; public class Testit6 { public static void main(String[] args) { String str = "...天2气 :[1] aA"; //