30 积分	0 好友	1 主题

发消息

[RAC性能调优] 执行计划不走索引

1^#

发表于 2013-5-8 18:03:29 | 查看: 4638| 回复: 8

本帖最后由 sky13and23 于 2013-5-8 18:06 编辑

SQL> explain plan for select a.ICPCODE,a.ICPSERVID,ShortcutText from portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
| Id  | Operation          |  Name                | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                      | 34517 |  1078K|  5299 |
| 1 |  NESTED LOOPS       |                      | 34517 |  1078K|  5299 |
| 2 | TABLE ACCESS FULL  | SERVICE             |  1702K| 32M|  5299 |
|*  3 | INDEX UNIQUE SCAN  | PK_WAPSERVICE_SERVID  |    1 | 12 |    |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------

3 - access("A"."ICPCODE"="B"."ICPCODE" AND "A"."ICPSERVID"="B"."ICPSERVID")

Note: cpu costing is off

16 rows selected.

为什么service 表不走索引PK_SERVICE_SERVID

下面是一些统计信息：

SQL> select count(*) from portal.service ;

  COUNT(*)
----------
1702774
SQL> select count(*) from portal.wap_service ;

  COUNT(*)
----------
   34873

SQL> select a.last_analyzed,a.clustering_factor,a.blevel,a.leaf_blocks,a.distinct_keys,
  2  a.avg_leaf_blocks_per_key,a.avg_data_blocks_per_key,a.num_rows,a.table_name,a.index_name
  3 from dba_indexes a where table_name in ('SERVICE','WAP_SERVICE') and owner = 'PORTAL';

LAST_ANALYZED CLUSTERING_FACTOR    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS TABLE_NAME                   INDEX_NAME
------------- ----------------- ---------- ----------- ------------- ----------------------- ----------------------- ---------- ------------------------------ ------------------------------
2013-5-8 17:5          78750       2       4783       1144                      4                   68 1712433 SERVICE                      IDX_SERVATTR_ICPCODE
2013-5-8 17:5          1530193       2       6723    1719553                      1                      1 1719553 SERVICE                      PK_SERVICE_SERVID
2013-5-7 18:3          15415       1       127       34873                      1                      1    34873 WAP_SERVICE                   PK_WAPSERVICE_SERVID

SQL>
SQL>
SQL> select a.last_analyzed,a.blocks,a.num_rows,a.EMPTY_BLOCKS,a.avg_space,
  2  a.chain_cnt,a.avg_row_len,a.table_name
  3 from dba_tables a where table_name in ('SERVICE','WAP_SERVICE')and owner = 'PORTAL';

LAST_ANALYZED    BLOCKS NUM_ROWS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN TABLE_NAME
------------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------------
2013-5-8 17:5    55079 1702023          0       0       0       222 SERVICE
2013-5-7 18:3    1135    34873          0       0       0       100 WAP_SERVICE

分享0

收藏0 回复只看该作者道具举报

低调小马哥

2^#

发表于 2013-5-8 18:43:48

首先这个sql没有谓词过滤条件，使用的两个关联连接条件，因此使用portal.service表的索引也要全索引扫描，最后回表，所以代价应该更高，所以优化器选择了全表扫描。可以对比下用hash_join连接，试试哪个逻辑读更少。

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-5-8 19:54:39

没有条件只有关联， SERVICE 充当驱动表的时候怎么走索引？

回复只看该作者道具举报

sky13and23

4^#

发表于 2013-5-9 11:04:52

这个sql是业务所需，没有过滤条件也是和业务有关。

目前执行计划

SQL> select a.ICPCODE,a.ICPSERVID,ShortcutText from portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=5299 Card=34517 Bytes=1104544)
1 0 NESTED LOOPS (Cost=5299 Card=34517 Bytes=1104544)
2 1    TABLE ACCESS (FULL) OF 'SERVICE' (Cost=5299 Card=1702023 Bytes=34040460)
3 1    INDEX (UNIQUE SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
1762481  consistent gets
   53102  physical reads
      0  redo size
1162701  bytes sent via SQL*Net to client
   26220  bytes received via SQL*Net from client
   2326  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   34873  rows processed

修改驱动表后执行计划

SQL> select  /*+leading(b)*/a.ICPCODE,a.ICPSERVID,ShortcutText from portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.
Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=5666 Card=34517 Bytes=1104544)
1 0 HASH JOIN (Cost=5666 Card=34517 Bytes=1104544)
2 1    INDEX (FAST FULL SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE) (Cost=14 Card=34873 Bytes=418476)
3 1    TABLE ACCESS (FULL) OF 'SERVICE' (Cost=5299 Card=1702023 Bytes=34040460)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   57512  consistent gets
   53104  physical reads
      0  redo size
1162701  bytes sent via SQL*Net to client
   26220  bytes received via SQL*Net from client
   2326  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   34873  rows processed

我想要的执行计划
SQL> select  /*+index(a PK_SERVICE_SERVID)*/a.ICPCODE,a.ICPSERVID,ShortcutText from portal.wap_service b,portal.service a where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.
Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=34888 Card=34517 Bytes=1104544)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE' (Cost=2 Card=1 Bytes=20)
2 1    NESTED LOOPS (Cost=34888 Card=34517 Bytes=1104544)
3 2    INDEX (FAST FULL SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE) (Cost=14 Card=34873 Bytes=418476)
4 2    INDEX (RANGE SCAN) OF 'PK_SERVICE_SERVID' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   93933  consistent gets
      82  physical reads
      0  redo size
1162512  bytes sent via SQL*Net to client
   26220  bytes received via SQL*Net from client
   2326  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   34873  rows processed

不知道为什么数据库不走我想要的执行计划，有什么办法可以得到我想要的执行计划

回复只看该作者道具举报

foxhuntwang

5^#

发表于 2013-5-9 11:29:25

建议你先把2个表重新做一次统计信息分析再看看执行计划

回复只看该作者道具举报

sky13and23

6^#

发表于 2013-5-9 11:34:02

这两个表都是最新的统计分析

回复只看该作者道具举报

kevinlin.ora

7^#

发表于 2013-5-9 13:08:19

在正常情况下，数据库选择COST最小的执行计划，由于各种原因，其不一定就是我们想要的。
建立索引PK_SERVICE_SERVID(icpcode,icpservid,ShortcutText)，(前两列的顺序按实际排)，试一下，低调小马哥在2#楼提到需要回表，回表的代价有时也很高。

回复只看该作者道具举报

sky13and23

8^#

发表于 2013-5-9 14:27:33

这个做法也测试了，cost减小一些，但是consistent gets和physical reads还是很高，我主要是希望得到
/*+index(a PK_SERVICE_SERVID)*/这样的执行计划，这样可以降低不少consistent gets和physical reads。由于sql是JDBC发出，又不能加hint 所以想请叫一些有没有什么办法能够使执行计划和我想要强制索引的执行计划走一样的

回复只看该作者道具举报

Stone

9^#

发表于 2013-5-10 22:22:14

3 - access("A"."ICPCODE"="B"."ICPCODE" AND "A"."ICPSERVID"="B"."ICPSERVID")

Note: cpu costing is off

根据上面explain里的 "Note: cpu costing is off"，建议看看是不是系统信息没有收集，或者看看参数“_optimizer_cost_model”的设置。具体可以参考：
https://forums.oracle.com/forums/thread.jspa?threadID=1128379
http://www.itpub.net/thread-911829-1-1.html
http://yong321.freeshell.org/oranotes/SystemStats.txt

希望有所帮助。

另外，感觉最好说明一下Oracle的版本，运行的平台，这样可以帮助分析下问题。

如果还是感觉有问题，做一个10053的trace，可以更深入地看下问什么Oracle认为这样的计划跟好，毕竟Oracle是根据信息做判断嘛 :)

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册