ORACLE10g柱状图信息对执行计划的影响
来源:互联网 发布:rete算法实现 java 编辑:程序博客网 时间:2024/05/16 10:28
我们知道,在Oracle Database 10g中,Oracle优化器的缺
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
查看NAME列数据分布情况:
SQL> select name,count(*) from test group by name;
NAME COUNT(*)
-------------------- ----------
msptest 1
tsptest 1034
在TEST表NAME列上有一个索引:
SQL> select INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------------------
TT NAME
没有做表分析的情况,我们看一下执行计划:
SQL> set autotrace traceonly
SQL> select * from test where name='msptest';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=4 Bytes=84)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Byt
es=84)
2 1 INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where name='tsptest';
1034 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=4 Bytes=84)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Byt
es=84)
2 1 INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
154 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL*Net to client
1399 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1034 rows processed
NAME列上tsptest总共有1034条记录,走全表的效率要优于索引扫描,但这里却走了索引扫描,显然优化器做出了错误的选择.
只分析一下表,试试看:
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select * from test where name='msptest';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=518 Bytes=7
252)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=518 Bytes=7252)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
只需返回一条数据,但做了全表扫描,这是不合理的执行计划。因为,它只是知道name列有两个不同的值,但oracle不知道每个不同的name分别有多少记录,oracle默认为这些数据的分布是完全均匀的,所以,当用name作条件时,oracle会认为会返回总记录的二分之一。
SQL> select * from test where name='tsptest';
1034 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=518 Bytes=7
252)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=518 Bytes=7252)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL*Net to client
1399 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1034 rows processed
删除统计信息,对表TEST生成柱状图后在做同样的查询:
SQL> analyze table test delete statistics;
Table analyzed.
SQL> analyze table test compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select * from test where name='msptest';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=16)
2 1 INDEX (RANGE SCAN) OF 'TT' (NON-UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where name='tsptest';
1034 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1034 Bytes=
16544)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1034 Bytes=1654
4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL*Net to client
1399 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1034 rows processed
可见,生成了柱状图后,oracle会根据数据的实际分布情况选择合适的执行计划,提高性能。
- ORACLE10g柱状图信息对执行计划的影响
- 有关成本统计数据新旧对执行计划的影响
- Oracle中CLUSTER_FACTOR对SQL执行计划的影响
- 数据分布对MySQL执行计划的影响
- 集簇因子对执行计划影响和统计信息拷贝
- Oracle10g里sql语句的执行计划
- 应用程序对Sqlserver进行SQL查询使用与不使用参数对执行计划的影响
- Oracle 对表设置并行度对执行计划的影响
- sql优化(查询条件的变化对执行计划的影响)
- toad 分析执行计划产生的影响
- 隐式转换在执行计划中对Access predicates 和Filter predicates 的影响
- 从Peeked Binds看DDL和DCL语句对执行计划的影响
- 测试6——观察Optimizer_index_cost_adj 对执行计划的影响
- ORACLE柱状图与执行计划
- 版本计划对版本质量的影响
- 对执行计划的解释
- Oracle10g获取sql语句的执行计划详解!
- 执行计划、索引 是否会影响SQL的执行结果
- log4j使用步骤
- 如何在屏幕上画虚线矩形框?
- Excel应用评论
- jstl
- Direct3D9 教程03
- ORACLE10g柱状图信息对执行计划的影响
- JExecl的使用
- python bookmark
- IP Multicasting - Using I/O completion ports with UDP
- (二)边做边发表--产销系统之詳細設計------更新时间2007/8/1----------之數據庫設計
- Log4j简明手册(一)
- spring学习笔记一
- 单元测试之道-JAVA
- FeatureIdentifyObject Example