有无直方图，性能的差距
来源：互联网 发布：反淘宝联盟事件 编辑：程序博客网 时间：2024/05/16 09:05
#################################
###有无直方图，性能的差距###
#################################
实验的环境在redhat平台下的11gr2单实例环境
1、在自己定义的用户下创建表及索引
CREATE TABLE customers AS SELECT * FROM sh.customers;
CREATE INDEX customers_country_ix ON customers(country_id);
2、收集表的收集信息
BEGIN
SYS.DBMS_STATS.gather_table_stats (ownname =>'TRSEN',
tabname => 'CUSTOMERS');
END;
/
3、查看一下统计信息(列的密度、列的空值、列的不同值)
SQL> SELECT density, num_nulls, num_distinct
2 FROM user_tab_col_statistics
3 WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';
DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
.052631579 0 19
4、执行两条语句
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 | 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52787)
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 | 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
4、收集索引列直方图信息
SYS.DBMS_STATS.gather_table_stats (ownname => 'TRSEN',
3 tabname => 'CUSTOMERS',
4 method_opt => 'FOR ALL INDEXED COLUMNS'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
5、查看统计信息，发现列的密度发生改变了
SQL> SELECT density, num_nulls, num_distinct
2 FROM user_tab_col_statistics
3 WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';
DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
9.1437E-06 0 19
6、此时查询查看执行计划
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 |23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 102 | 2652 |23 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 102 | |1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52787)
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 18230 | 462K| 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
7、强制走索引查看执行计划
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 18230 | 462K| 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
SQL> SELECT /*+ INDEX(customers) */
2 MAX (cust_income_level)
3 FROM customers
4 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3
872 (1)| 00:00:47 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 18230 | 462K| 3872 (1)| 00:00:47 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 18230 | |41 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52790)
SQL> SELECT /*+ INDEX(customers) */
2 MAX (cust_income_level)
3 FROM customers
4 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 |23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 102 | 2652 |23 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 102 | |1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52787)
8、这张表的数据分布及实验结果分析
select country_id,count(country_id) as total from customers group by country_id order by total;
从这张统计表可以看出一些信息有19个country_id值，有30%左右的数据在52790列上，如果我们要搜索一个特定的country_id=52787，优化器也会选择全表扫描
9、清除收集的直方图信息语句
BEGIN
SYS.DBMS_STATS.gather_table_stats ('TRSEN','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS size 1');
END;
/
10、针对consistent gets分析结果图
实验分析：列的直方图创建，使得oracle能够在其他值不具备选择性的列中识别出那些有选择性的值。优化能够为有选择性的值选择使用索引，为没有选择性的列值选择全表扫描。即便强制没有选择性的列走索引，但其性能会更差。
###有无直方图，性能的差距###
#################################
实验的环境在redhat平台下的11gr2单实例环境
1、在自己定义的用户下创建表及索引
CREATE TABLE customers AS SELECT * FROM sh.customers;
CREATE INDEX customers_country_ix ON customers(country_id);
2、收集表的收集信息
BEGIN
SYS.DBMS_STATS.gather_table_stats (ownname =>'TRSEN',
tabname => 'CUSTOMERS');
END;
/
3、查看一下统计信息(列的密度、列的空值、列的不同值)
SQL> SELECT density, num_nulls, num_distinct
2 FROM user_tab_col_statistics
3 WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';
DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
.052631579 0 19
4、执行两条语句
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 | 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52787)
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 | 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
4、收集索引列直方图信息
SYS.DBMS_STATS.gather_table_stats (ownname => 'TRSEN',
3 tabname => 'CUSTOMERS',
4 method_opt => 'FOR ALL INDEXED COLUMNS'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
5、查看统计信息，发现列的密度发生改变了
SQL> SELECT density, num_nulls, num_distinct
2 FROM user_tab_col_statistics
3 WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';
DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
9.1437E-06 0 19
6、此时查询查看执行计划
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 |23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 102 | 2652 |23 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 102 | |1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52787)
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 18230 | 462K| 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
7、强制走索引查看执行计划
SQL> SELECT MAX (cust_income_level)
2 FROM customers
3 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 406 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 18230 | 462K| 406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY_ID"=52790)
SQL> SELECT /*+ INDEX(customers) */
2 MAX (cust_income_level)
3 FROM customers
4 WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3
872 (1)| 00:00:47 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 18230 | 462K| 3872 (1)| 00:00:47 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 18230 | |41 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52790)
SQL> SELECT /*+ INDEX(customers) */
2 MAX (cust_income_level)
3 FROM customers
4 WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 |23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 102 | 2652 |23 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_COUNTRY_IX | 102 | |1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"=52787)
8、这张表的数据分布及实验结果分析
select country_id,count(country_id) as total from customers group by country_id order by total;
从这张统计表可以看出一些信息有19个country_id值，有30%左右的数据在52790列上，如果我们要搜索一个特定的country_id=52787，优化器也会选择全表扫描
9、清除收集的直方图信息语句
BEGIN
SYS.DBMS_STATS.gather_table_stats ('TRSEN','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS size 1');
END;
/
10、针对consistent gets分析结果图
实验分析：列的直方图创建，使得oracle能够在其他值不具备选择性的列中识别出那些有选择性的值。优化能够为有选择性的值选择使用索引，为没有选择性的列值选择全表扫描。即便强制没有选择性的列走索引，但其性能会更差。
0 0
- 有无直方图，性能的差距
- Eclipse的性能分析差距TPTP
- 冒泡排序和改进的冒泡排序的性能差距
- C风格文件操作 与 fstream的性能差距
- memcpy和for循环复制的性能差距
- 性能比较误人不浅（理论与现实的次数差距）
- 控制台窗口的有无
- var 有无的区别
- 有无
- 测试性能：JAVA行优先与列优先的性能差距
- vc8 与 vc6性能有无差异？
- LwIP有无操作系统的区别
- 质的差距~~
- 实践的差距
- 实战的差距
- 做人的“差距”
- 中日两国的差距
- 差距的产生
- 数据结构之线性表一：
- hdu 1081 To The Max
- ReportStudio入门教程(二十六) - 列表的悬浮提示信息-显示自定义信息
- 第五周作业
- 笔记本电脑变WiFi和WiFi共享精灵的应用教程比较
- 有无直方图，性能的差距
- 七大经典排序（中）
- 青春代码
- 软考历程(1)——操作系统
- 第八周 项目一复数类的运算符重载（3）
- sizeof
- IOS开发之——如何引入外部项目
- Oracle表分区
- brk/sbrk
