查看: 4285|回复: 18

每天执行15万次的一条sql语句求优化方案

[复制链接]
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
发表于 2011-1-30 09:59 | 显示全部楼层 |阅读模式
SELECT COUNT(1)
             FROM TB_LOTTERY_WIN_DETAIL A, TB_BSK_WIN_SEESION_DETAIL B
             WHERE (B.INTERNAL_SESSION_ID = :B1) AND
                          (A.ID = B.ID) AND
                          (A.COMMIT_FLAG = 0)

执行计划走的是 IDX_BSK_WIN_SEESION_DETAIL_003,IDX_TB_LOTTERY_WIN_DETAIL_05

A表360万行,索引如下:

IDX_TB_LOTTERY_WIN_DETAIL_01        TB_LOTTERY_WIN_DETAIL        ISSUE
IDX_TB_LOTTERY_WIN_DETAIL_01        TB_LOTTERY_WIN_DETAIL        LOTTERY_ID
IDX_TB_LOTTERY_WIN_DETAIL_02        TB_LOTTERY_WIN_DETAIL        ISSUE
IDX_TB_LOTTERY_WIN_DETAIL_02        TB_LOTTERY_WIN_DETAIL        USER_ID
IDX_TB_LOTTERY_WIN_DETAIL_02        TB_LOTTERY_WIN_DETAIL        LOTTERY_ID
IDX_TB_LOTTERY_WIN_DETAIL_03        TB_LOTTERY_WIN_DETAIL        STR_DAY
IDX_TB_LOTTERY_WIN_DETAIL_04        TB_LOTTERY_WIN_DETAIL        WIN_VALUE
IDX_TB_LOTTERY_WIN_DETAIL_05        TB_LOTTERY_WIN_DETAIL        ID
PK_LOTTERY_WIN_DETAIL                     TB_LOTTERY_WIN_DETAIL        ORDER_ID


B表50万行,索引如下:

IDX_BSK_WIN_SEESION_DETAIL_001        TB_BSK_WIN_SEESION_DETAIL        LOTTERY_ID
IDX_BSK_WIN_SEESION_DETAIL_001        TB_BSK_WIN_SEESION_DETAIL        INTERNAL_SESSION_ID
IDX_BSK_WIN_SEESION_DETAIL_002        TB_BSK_WIN_SEESION_DETAIL        USER_ID
IDX_BSK_WIN_SEESION_DETAIL_002        TB_BSK_WIN_SEESION_DETAIL        BATCH_ID
IDX_BSK_WIN_SEESION_DETAIL_003        TB_BSK_WIN_SEESION_DETAIL        INTERNAL_SESSION_ID
IDX_BSK_WIN_SEESION_DETAIL_003        TB_BSK_WIN_SEESION_DETAIL        USER_ID
PK_BSK_WIN_SEESION_DETAIL                  TB_BSK_WIN_SEESION_DETAIL        ID
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2011-1-30 10:02 | 显示全部楼层
如果ab不太变化,用物化视图

使用道具 举报

回复
论坛徽章:
51
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22铁扇公主
日期:2012-02-21 15:03:13最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-05-22 15:10:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:13:332013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36itpub13周年纪念徽章
日期:2014-09-28 10:55:55
发表于 2011-1-30 10:07 | 显示全部楼层

回复 #1 zhanglincon 的帖子

SQL写的应该没啥可优化的了
下面也就是索引了
执行计划贴出来看看吧

使用道具 举报

回复
论坛徽章:
15
生肖徽章2007版:羊
日期:2009-02-10 14:52:362013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2012-12-17 17:16:242011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-30 14:20:16ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522010年世界杯参赛球队:尼日利亚
日期:2010-07-15 14:53:432010年世界杯参赛球队:美国
日期:2010-04-29 22:55:002010新春纪念徽章
日期:2010-03-01 11:08:27生肖徽章2007版:龙
日期:2009-07-30 10:45:52
发表于 2011-1-30 10:08 | 显示全部楼层
组合索引TB_LOTTERY_WIN_DETAIL(COMMIT_FLAG,ID)  TB_BSK_WIN_SEESION_DETAIL(INTERNAL_SESSION_ID,ID)

使用道具 举报

回复
论坛徽章:
68
2012新春纪念徽章
日期:2012-01-04 11:51:22奥运会纪念徽章:举重
日期:2012-08-02 22:17:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:312013年新春福章
日期:2013-02-25 14:51:24慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:312015年新春福章
日期:2015-06-11 12:54:06
发表于 2011-1-30 10:16 | 显示全部楼层
A表走的索引应该问题不大
B表在这2个字段上建个索引(INTERNAL_SESSION_ID,ID)
另外最好把执行计划和统计信息部分贴全。

[ 本帖最后由 howard_zhang 于 2011-1-30 10:17 编辑 ]

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
 楼主| 发表于 2011-1-30 10:18 | 显示全部楼层
篮彩的兑奖查询,判断是否兑奖完成,每天开奖兑奖次数不确定,不能使用物化视图

使用道具 举报

回复
论坛徽章:
15
生肖徽章2007版:羊
日期:2009-02-10 14:52:362013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2012-12-17 17:16:242011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-30 14:20:16ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522010年世界杯参赛球队:尼日利亚
日期:2010-07-15 14:53:432010年世界杯参赛球队:美国
日期:2010-04-29 22:55:002010新春纪念徽章
日期:2010-03-01 11:08:27生肖徽章2007版:龙
日期:2009-07-30 10:45:52
发表于 2011-1-30 10:18 | 显示全部楼层
原帖由 howard_zhang 于 2011-1-30 10:16 发表
A表走的索引应该问题不大
B表在这2个字段上建个索引(INTERNAL_SESSION_ID,ID)
另外最好把执行计划和统计信息部分贴全。


这样A表还要去读表

组合索引TB_LOTTERY_WIN_DETAIL(COMMIT_FLAG,ID)  TB_BSK_WIN_SEESION_DETAIL(INTERNAL_SESSION_ID,ID)
这样的话两个索引HASH JOIN一下就可以,应该是最快滴~

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
 楼主| 发表于 2011-1-30 10:23 | 显示全部楼层
我们用的RBO的优化器

SQL> SELECT COUNT(1)
  2    FROM TB_LOTTERY_WIN_DETAIL A, TB_BSK_WIN_SEESION_DETAIL B
  3   WHERE (B.INTERNAL_SESSION_ID = '1_20110129_6_067')
  4     AND (A.ID = B.ID)
  5     AND (A.COMMIT_FLAG = 0);

  COUNT(1)
----------
        51

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2398083150

-------------------------------------------------------------------------
| Id  | Operation                      | Name                           |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                                |
|   1 |  SORT AGGREGATE                |                                |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | TB_LOTTERY_WIN_DETAIL          |
|   3 |    NESTED LOOPS                |                                |
|   4 |     TABLE ACCESS BY INDEX ROWID| TB_BSK_WIN_SEESION_DETAIL      |
|*  5 |      INDEX RANGE SCAN          | IDX_BSK_WIN_SEESION_DETAIL_003 |
|*  6 |     INDEX RANGE SCAN           | IDX_TB_LOTTERY_WIN_DETAIL_05   |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."COMMIT_FLAG"=0)
   5 - access("B"."INTERNAL_SESSION_ID"='1_20110129_6_067')
   6 - access("A"."ID"="B"."ID")

Note
-----
   - 'PLAN_TABLE' is old version
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
 楼主| 发表于 2011-1-30 10:26 | 显示全部楼层
我自己改写了一下这个语句,大家看看性能是否有提高呢

SQL> SELECT Count(*)
  2    From dual
  3    Where Exists(Select Null
  4                        FROM TB_LOTTERY_WIN_DETAIL A, TB_BSK_WIN_SEESION_DETAIL B
  5                        WHERE (B.INTERNAL_SESSION_ID = '1_20110129_6_067')
  6                        AND (A.ID = B.ID)
  7                        AND (A.COMMIT_FLAG = 0));

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1925888899

--------------------------------------------------------------------------
| Id  | Operation                       | Name                           |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |
|   1 |  SORT AGGREGATE                 |                                |
|*  2 |   FILTER                        |                                |
|   3 |    FAST DUAL                    |                                |
|*  4 |    TABLE ACCESS BY INDEX ROWID  | TB_LOTTERY_WIN_DETAIL          |
|   5 |     NESTED LOOPS                |                                |
|   6 |      TABLE ACCESS BY INDEX ROWID| TB_BSK_WIN_SEESION_DETAIL      |
|*  7 |       INDEX RANGE SCAN          | IDX_BSK_WIN_SEESION_DETAIL_003 |
|*  8 |      INDEX RANGE SCAN           | IDX_TB_LOTTERY_WIN_DETAIL_05   |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "TB_BSK_WIN_SEESION_DETAIL"
              "B","TB_LOTTERY_WIN_DETAIL" "A" WHERE "A"."ID"="B"."ID" AND
              "A"."COMMIT_FLAG"=0 AND "B"."INTERNAL_SESSION_ID"='1_20110129_6_06
7'))

   4 - filter("A"."COMMIT_FLAG"=0)
   7 - access("B"."INTERNAL_SESSION_ID"='1_20110129_6_067')
   8 - access("A"."ID"="B"."ID")

Note
-----
   - 'PLAN_TABLE' is old version
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
 楼主| 发表于 2011-1-30 10:38 | 显示全部楼层
使用CBO时的执行计划

SQL> analyze table TB_LOTTERY_WIN_DETAIL compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:03:13.25
SQL> analyze table TB_BSK_WIN_SEESION_DETAIL compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:01:39.47
SQL>
SQL>
SQL> SELECT /*+ all_rows */ Count(*)
  2    From dual
  3    Where Exists(Select Null
  4                        FROM TB_LOTTERY_WIN_DETAIL A, TB_BSK_WIN_SEESION_DETAIL B
  5                        WHERE (B.INTERNAL_SESSION_ID = '1_20110129_6_067')
  6                        AND (A.ID = B.ID)
  7                        AND (A.COMMIT_FLAG = 0));

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1925888899

--------------------------------------------------------------------------------
----------------------------------

| Id  | Operation                       | Name                           | Rows
| Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
----------------------------------

|   0 | SELECT STATEMENT                |                                |     1
|       |   739   (0)| 00:00:14 |

|   1 |  SORT AGGREGATE                 |                                |     1
|       |            |          |

|*  2 |   FILTER                        |                                |
|       |            |          |

|   3 |    FAST DUAL                    |                                |     1
|       |     2   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS BY INDEX ROWID  | TB_LOTTERY_WIN_DETAIL          |     1
|    42 |     3   (0)| 00:00:01 |

|   5 |     NESTED LOOPS                |                                |   247
| 26182 |   737   (0)| 00:00:14 |

|   6 |      TABLE ACCESS BY INDEX ROWID| TB_BSK_WIN_SEESION_DETAIL      |   244
| 15616 |     5   (0)| 00:00:01 |

|*  7 |       INDEX RANGE SCAN          | IDX_BSK_WIN_SEESION_DETAIL_003 |   244
|       |     3   (0)| 00:00:01 |

|*  8 |      INDEX RANGE SCAN           | IDX_TB_LOTTERY_WIN_DETAIL_05   |     1
|       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "TB_BSK_WIN_SEESION_DETAIL" "B","TB_LOTTERY
_WIN_DETAIL" "A" WHERE

              "A"."ID"="B"."ID" AND "A"."COMMIT_FLAG"=0 AND "B"."INTERNAL_SESSIO
N_ID"='1_20110129_6_067'))

   4 - filter("A"."COMMIT_FLAG"=0)
   7 - access("B"."INTERNAL_SESSION_ID"='1_20110129_6_067')
   8 - access("A"."ID"="B"."ID")

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表