查看: 3298|回复: 8

江湖救急,SQL语句执行优化相关问题

[复制链接]
论坛徽章:
1
2010新春纪念徽章
日期:2010-03-01 11:08:24
发表于 2010-10-12 12:58 | 显示全部楼层 |阅读模式
现在有一个java+tomcat+oracle开发的应用,在启动tomcat的时候需要执行一个sql语句把数据库的数据加载到内存中,之前虽然这个过程比较慢,但是启动20来分钟也就可以了,但是最近发现启动一下子慢到需要花4-5小时,太夸张了。应用咨询过开发人员没有改过,数据库除了增加空间重建索引和统计数据收集最近也没有做过调整,当然数据量是每天在增加的。现在不知道问题出在哪,怎么查找调整,想请各位帮忙分析下。数据库是oracle9.2.0.5,hp-unix。原来的SGA只有500M,因为之前出现过temp空间满的情况,所以我把temp加大的同时SGA也加到了1.5G,db_cache加到了1G,只是情况还是照旧。sql语句如下:select a.acct_id,a.txn_dt,a.txn_at,a.txn_cd from acct_dtl a,custmng_acc b where a.acct_id=b.acct_id  and a.txn_dt>='20100501'  order by a.acct_id,a.txn_dt,下图是toad里显示的执行计划,不知道有问题吗?
QQ截图未命名.jpg 一执行就卡住了,unix的disk利用率基本就保持在100%。
两张表的定义索引的定义分别如下,字段都很少,acct_dtl是按txn_dt分区的表,总数大概4300万条,20100501以后的数据大概有2200万,custmng_acc大概有108万的数据,分区表每天增加的数据是十几万条,隔一个季度增加一个分区,再把一年前的分区删除掉。因为数据一直在增长,速度会变慢可以理解,但是一下子慢成这样肯定是哪有问题了。请教一下我要怎么定位我的问题,或者有没有什么好的对该语句的优化措施
CREATE TABLE ACCT_DTL
(
  SEQ_ID       NUMBER(12)                       NOT NULL,
  ACCT_ID      CHAR(19 BYTE)                    NOT NULL,
  TXN_BRH_ID   CHAR(9 BYTE)                     NOT NULL,
  TXN_DT       CHAR(8 BYTE)                     NOT NULL,
  CURR_BAL_AT  NUMBER(12),
  TXN_AT       NUMBER(12),
  TXN_CD       CHAR(1 BYTE),
  TXN_PRT_CD   CHAR(30 BYTE)
)

PARTITION BY RANGE (TXN_DT)
(  
  PARTITION ACCT_DTL20 VALUES LESS THAN ('20100101')
   PARTITION ACCT_DTL21 VALUES LESS THAN ('20100401')
   PARTITION ACCT_DTL22 VALUES LESS THAN ('20100701')
   PARTITION ACCT_DTL23 VALUES LESS THAN ('20101001')
   PARTITION ACCT_DTL24 VALUES LESS THAN ('20110101')
    )
CREATE UNIQUE INDEX ACCT_DTL_IDX1 ON ACCT_DTL
(SEQ_ID)
CREATE INDEX ACCT_DTL_IDX3 ON ACCT_DTL
(ACCT_ID, TXN_DT)
CREATE INDEX ACCT_DTL_IDX2 ON ACCT_DTL
(TXN_DT, ACCT_ID)
CREATE INDEX ACCT_DTL_IDX4 ON ACCT_DTL
(ACCT_ID)

CREATE TABLE CUSTMNG_ACC
(
  CUSTMNG_ID   CHAR(8 BYTE)                     NOT NULL,
  ACCT_ID      CHAR(19 BYTE)                    NOT NULL,
  OPEN_BRH_ID  CHAR(9 BYTE)                     NOT NULL,
  RELA_DT      CHAR(8 BYTE),
  STA_CD       CHAR(1 BYTE)
)

CREATE UNIQUE INDEX CUSTMNG_ACC_IDX2 ON CUSTMNG_ACC
(ACCT_ID)

CREATE UNIQUE INDEX CUSTMNG_ACC_IDX1 ON CUSTMNG_ACC
(CUSTMNG_ID, ACCT_ID)
论坛徽章:
1
2010新春纪念徽章
日期:2010-03-01 11:08:24
 楼主| 发表于 2010-10-12 13:02 | 显示全部楼层
我忽然发觉一个问题,我这个关联语句是不是用的索引不对?它的计划是用idx4,因为语句里出现了 acct_id和txn_dt,是不是应该用idx3?还是说表进行关联的时候idx3是用不上的

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-10-12 13:56 | 显示全部楼层
merge join,数据量还那么大,排序起来也很慢
statics呢???

使用道具 举报

回复
论坛徽章:
70
ITPUB元老
日期:2007-07-19 08:57:15乌索普
日期:2016-06-24 14:29:16沸羊羊
日期:2015-02-12 09:15:562014年世界杯参赛球队:喀麦隆
日期:2014-05-20 16:06:36马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11迷宫蛋
日期:2013-04-24 13:52:55茶鸡蛋
日期:2013-04-19 13:54:282013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-02-19 14:05:00
发表于 2010-10-12 14:02 | 显示全部楼层
select /*+ USE_HASH(a b)*/
a.acct_id,a.txn_dt,a.txn_at,a.txn_cd from acct_dtl a,custmng_acc b where a.acct_id=b.acct_id  and a.txn_dt>='20100501'  order by a.acct_id,a.txn_dt
用hash 看看!

使用道具 举报

回复
论坛徽章:
1
2010新春纪念徽章
日期:2010-03-01 11:08:24
 楼主| 发表于 2010-10-12 14:34 | 显示全部楼层

回复 #3 dingjun123 的帖子

sqlplus 里得到的具体信息如下
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=25051 Card=104123 By
          tes=5726765)

   1    0   SORT* (ORDER BY) (Cost=25051 Card=104123 Bytes=5726765)    :Q347700
                                                                       2

   2    1     MERGE JOIN* (Cost=21880 Card=104123 Bytes=5726765)       :Q347700
                                                                       1

   3    2       TABLE ACCESS* (BY GLOBAL INDEX ROWID) OF 'UNIPOST_ACCT :Q347700
          _DTL' (Cost=826 Card=104123 Bytes=3644305)                   0

   4    3         INDEX (FULL SCAN) OF 'UNIPOST_ACCT_DTL_IDX4' (NON-UN
          IQUE) (Cost=26 Card=36335740)

   5    2       SORT* (JOIN) (Cost=21054 Card=1056570 Bytes=21131400)  :Q347700
                                                                       1

   6    5         INDEX* (FAST FULL SCAN) OF 'UNIPOST_CUSTMNG_ACC_IDX2 :Q347700
          ' (UNIQUE) (Cost=354 Card=1056570 Bytes=21131400)            1



   1 PARALLEL_TO_SERIAL            SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3 F
                                   ROM :Q3477001 A1 ORDER BY A1.C0,A1.C

   2 PARALLEL_TO_PARALLEL          SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
                                   / A1.C0 C0,A1.C3 C1,A1.C2 C2,A1.C4 C

   3 PARALLEL_FROM_SERIAL
   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
          0  recursive calls
        161  db block gets
   22616588  consistent gets
   12973663  physical reads
      20648  redo size
   63153808  bytes sent via SQL*Net to client
    1515026  bytes received via SQL*Net from client
     137672  SQL*Net roundtrips to/from client
          1  sorts (memory)
          2  sorts (disk)
    2065063  rows processed

使用道具 举报

回复
论坛徽章:
1
2010新春纪念徽章
日期:2010-03-01 11:08:24
 楼主| 发表于 2010-10-12 17:51 | 显示全部楼层
结贴。应该还是执行计划的问题,重新分析后执行计划使用全表扫描就好

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2010-10-12 22:37 | 显示全部楼层
2200 万, 就应该走全表扫描了, 走索引根本跑不出来.

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
发表于 2010-10-12 22:49 | 显示全部楼层
4300选2200,很明显啊

使用道具 举报

回复
论坛徽章:
0
发表于 2010-10-13 17:12 | 显示全部楼层

回复 #1 marchyc 的帖子

从应用角度看,SQL语句可能的优化方案如下:
1、查询结果中没有custmng_acc b的列,从表定义看,这两张表是基本档和明细档的关系,如果可以由应用程序保证acct_dtl表中的accnt_id均在custmng_acc 表中,则语句简化如下即可:
select a.acct_id,a.txn_dt,a.txn_at,a.txn_cd from acct_dtl a where a.txn_dt>='20100501'  order by a.acct_id,a.txn_dt;

2、等价优化方案可以试试如下:
select a.acct_id,a.txn_dt,a.txn_at,a.txn_cd from acct_dtl a
where exists (select 1 from custmng_acc b where b.acct_id=a.acct_id)
    and a.txn_dt>='20100501'
order by a.acct_id,a.txn_dt

使用道具 举报

回复

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

本版积分规则 发表回复

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