ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql调优系列7(执行计划之走错)

oracle sql调优系列7(执行计划之走错)

原创 Linux操作系统 作者:alsrt 时间:2012-06-09 09:47:24 0 删除 编辑

在实际情况中,我们经常发现驱动表选错,或者执行计划走错的情况。在这种情况下,我们该如何调整这个错误呢?就我所知道的,有如下几种方法:

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博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-732297/,如需转载,请注明出处,否则将追究法律责任。

下一篇: .EVENT: ADJUST_SCN
请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    35561