在实际情况中，我们经常发现驱动表选错，或者执行计划走错的情况。在这种情况下，我们该如何调整这个错误呢?就我所知道的,有如下几种方法:
1:重新收集表的收集统计,里边有两个比较重要的问题
第一个:用何种方式收集统计信息呢?比如收集不收集直方图的统计信息呢?
第二个:生产环境的话,对于动不动几十G的表,收集这个信息得花费多长的时间呢?
2:使用exec dbms_stats.set_table_stats修改表的统计信息,但是这样做也有风险,比如
当oracle 10g的自动统计信息开着的时候,第二天你会发现你修改的表的统计信息没了.
举例:
exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP',numrows => '10000000',numblks => '1000000');
numrows ，指定表上的行数
numblks ，指定表的块
修改test表的统计信息
SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'TEST',numrows => '100000',numblks => '40000');
执行如下语句:
select count(*) from test where object_id is not null
SQL> /
COUNT(*)
----------
51162
Execution Plan
----------------------------------------------------------
Plan hash value: 2611454708
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_TEST | 100K| 488K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
120 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------------------------------------------------------------------------------
再次修改test表的统计信息
SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'TEST',numrows => '10',numblks => '4');
执行计划如下:
SQL> list
1* select count(*) from test where object_id is not null
SQL> /
COUNT(*)
----------
51162
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TEST | 10 | 50 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
120 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
总结,可以看到修改统计信息可以改变该表的执行计划
3:使用cardinality hint来提示告知表的统计信息
继续上面的实验
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Commit complete.
执行计划如下:
SQL> /
COUNT(*)
----------
51162
Execution Plan
----------------------------------------------------------
Plan hash value: 2611454708
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_TEST | 51162 | 249K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
120 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
那么我们现在用/*+ cardinality(a,5) */ 来使它走全表扫描!,(a,5)表示a表的有5行,结果会是?
执行如下语句:
select /*+ cardinality(a,5) */ count(*) from test a where object_id is not null;
SQL> exec dbms_stats.delete_table_stats('SCOTT','TEST') ;
PL/SQL procedure successfully completed.
SQL> /
COUNT(*)
----------
51162
Execution Plan
----------------------------------------------------------
Plan hash value: 2611454708
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_TEST | 5 | 65 | 7 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
120 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
表的统计信息是改了,但是没有走全表扫描?什么原因呢?真是很怪异的问题,哪位兄弟知道告知一声?看来还是第二种方法,靠谱一点,呵呵!!
我们再看看多个表连接的时候,*+ cardinality(a,5) */是否生效
构造如下案例;
SQL> create table test1 as select * from dba_objects
Table created.
SQL> create table test2 as select * from dba_objects where rownum<1000;
Table created.
给test1建立个索引
create index ind_test1 on tes1t(object_id)
分析下:
SQL> exec dbms_stats.gather_table_stats('scott','TEST1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select table_name,NUM_ROWS,AVG_ROW_LEN from user_tables where table_name='TEST1';
TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
TEST1 51163 93
执行如下语句,然后查看执行计划
select test1.object_name,test1.object_type from test1,test2 where test1.object_id=test2.object_id and test2.object_id=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2880656267
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 52 | 7 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 39 | 2 (0)| 0
0:00:01 |
| 2 | NESTED LOOPS | | 1 | 52 | 7 (0)| 0
0:00:01 |
|* 3 | TABLE ACCESS FULL | TEST2 | 1 | 13 | 5 (0)| 0
0:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST1 | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TEST2"."OBJECT_ID"=1000)
4 - access("TEST1"."OBJECT_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这时候我想改变TEST1做为驱动表,那咋办呢
SQL> select count(*) from test2;
COUNT(*)
----------
999
我们如果欺骗oracle改表有100W行记录,那么他就应该让test1做为驱动表了吧,好我们采用hint的方法来测试下:
Execution Plan
----------------------------------------------------------
Plan hash value: 705875191
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1000K| 49M| 8 (13)|
00:00:01 |
| 1 | MERGE JOIN | | 1000K| 49M| 8 (13)|
00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST2 | 1000K| 12M| 5 (0)|
00:00:01 |
|* 3 | SORT JOIN | | 1 | 39 | 3 (34)|
00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 39 | 2 (0)|
00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_TEST1 | 1 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST2"."OBJECT_ID"=1000)
3 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
filter("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
5 - access("TEST1"."OBJECT_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
看到结果了嘛?不靠谱啊,再加个user_hash看看啊
执行如下语句:
select /*+ cardinality(test2,1000000) use_hash(test1,test2) */ test1.object_name,test1.object_type from test1,test2 where test1.object_id=test2.object_id and test2.object_id=1000
Execution Plan
----------------------------------------------------------
Plan hash value: 804702166
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1000K| 49M| 22 (69)|
00:00:01 |
|* 1 | HASH JOIN | | 1000K| 49M| 22 (69)|
00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 39 | 2 (0)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TEST1 | 1 | | 1 (0)|
00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST2 | 1000K| 12M| 5 (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
3 - access("TEST1"."OBJECT_ID"=1000)
4 - filter("TEST2"."OBJECT_ID"=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如何我们第二种采用修改统计信息的方法,有这么麻烦吗?最后测试一把
exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'TEST2',numrows => '10000000',numblks => '1000000');
验证结果
SQL> select table_name,NUM_ROWS,AVG_ROW_LEN from user_tables where table_name='TEST2';
TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
TEST2 10000000 100
ok,执行如下语句:
Execution Plan
----------------------------------------------------------
Plan hash value: 705875191
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 100K| 5078K| 220K (1)|
00:44:02 |
| 1 | MERGE JOIN | | 100K| 5078K| 220K (1)|
00:44:02 |
|* 2 | TABLE ACCESS FULL | TEST2 | 100K| 1269K| 220K (1)|
00:44:02 |
|* 3 | SORT JOIN | | 1 | 39 | 3 (34)|
00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 39 | 2 (0)|
00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_TEST1 | 1 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TEST2"."OBJECT_ID"=1000)
3 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
filter("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
5 - access("TEST1"."OBJECT_ID"=1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
也是一样的结果,最后我加上use_hash就可以了,看来我们想要改变oracle的执行计划,是个综合的工程,多种手段一起使用才可能达到最终的目的.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8410760/viewspace-732297/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Linux Hugepages(转载)
- CBO hint:no_unnest,push_subq,push_pred的用法(转载)
- oracle sql调优系列之8(局部前缀索引和局部非前缀索引)
- Oracle11gR2 RAC 使用scan IP无法连接数据库（ORA-12545）问题解决
- Physical and Logical Block Corruptions. All you wanted to know about it.
- CBO学习笔记系列6(转载)
- CBO学习笔记系列5(转载)
- oracle sql调优系列6(group by)
- 10G ocr损坏的故障的恢复
- Oracle临时表优化查询速度(转载)
