ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 497|回复: 10

[PL/SQL] 有一个package,每天执行一次(用JOB调用),执行很慢

[复制链接]
认证徽章
论坛徽章:
15
ERP板块每日发贴之星
日期:2007-08-24 01:03:56狮子座
日期:2015-09-10 09:07:23处女座
日期:2015-09-11 10:28:03ITPUB14周年纪念章
日期:2015-10-26 17:23:44喜羊羊
日期:2015-06-24 14:33:00慢羊羊
日期:2015-06-15 14:22:442015年新春福章
日期:2015-03-12 17:22:46优秀写手
日期:2014-11-20 06:00:13茶鸡蛋
日期:2013-06-29 22:37:49蛋疼蛋
日期:2013-04-27 10:34:16
跳转到指定楼层
1#
发表于 2018-1-4 10:37 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 test_100 于 2018-1-4 10:40 编辑

如题,平时执行十几分就能执行完成,这两天却很慢(数小时还未跑完),想请各位帮看看:
1.经查,一直卡在这句SQL上,单独执行却很快:
  1. SELECT SUM(a.pur_amount) pur_amount
  2.   FROM xx_v a
  3. WHERE a.cst_id = :b3
  4.    AND a.pur_dt >= :b2
  5.    AND a.pur_dt < :b1
复制代码



2.查看sessions:
  1. select * from gv$session where sid = 392;
复制代码

结果如下:

   INST_IDSADDRSIDSERIAL#AUDSIDPADDRUSER#USERNAMECOMMANDOWNERIDTADDRLOCKWAITSTATUSSERVERSCHEMA#SCHEMANAMEOSUSERPROCESSMACHINEPORTTERMINALPROGRAMTYPESQL_ADDRESSSQL_HASH_VALUESQL_IDSQL_CHILD_NUMBERSQL_EXEC_STARTSQL_EXEC_IDPREV_SQL_ADDRPREV_HASH_VALUEPREV_SQL_IDPREV_CHILD_NUMBERPREV_EXEC_STARTPREV_EXEC_IDPLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_SUBPROGRAM_IDPLSQL_OBJECT_IDPLSQL_SUBPROGRAM_IDMODULEMODULE_HASHACTIONACTION_HASHCLIENT_INFOFIXED_TABLE_SEQUENCEROW_WAIT_OBJ#ROW_WAIT_FILE#ROW_WAIT_BLOCK#ROW_WAIT_ROW#TOP_LEVEL_CALL#LOGON_TIMELAST_CALL_ETPDML_ENABLEDFAILOVER_TYPEFAILOVER_METHODFAILED_OVERRESOURCE_CONSUMER_GROUPPDML_STATUSPDDL_STATUSPQ_STATUSCURRENT_QUEUE_DURATIONCLIENT_IDENTIFIERBLOCKING_SESSION_STATUSBLOCKING_INSTANCEBLOCKING_SESSIONFINAL_BLOCKING_SESSION_STATUSFINAL_BLOCKING_INSTANCEFINAL_BLOCKING_SESSIONSEQ#EVENT#EVENTP1TEXTP1P1RAWP2TEXTP2P2RAWP3TEXTP3P3RAWWAIT_CLASS_IDWAIT_CLASS#WAIT_CLASSWAIT_TIMESECONDS_IN_WAITSTATEWAIT_TIME_MICROTIME_REMAINING_MICROTIME_SINCE_LAST_WAIT_MICROSERVICE_NAMESQL_TRACESQL_TRACE_WAITSSQL_TRACE_BINDSSQL_TRACE_PLAN_STATSSESSION_EDITION_IDCREATOR_ADDRCREATOR_SERIAL#ECID
1
2
0000000C80B79B48
392
26077
20461111
0000000CA09C1900
102
CRM
3
2147483644
0000000C97591648 ACTIVEDEDICATED
102
CRMoracle56750crmdb2.daphne.com.cn
0
UNKNOWNoracle@crmdb2.daphne.com.cn  (J000)USER0000000CACEB0220
3691477886
g51basmf0fyvy
0
2018-1-4  10:11:22
33811783
0000000CADB82BC8
3947963296
00w4bfbpp28x0
0
2018-1-4  10:11:22
34205580
259850
1
   
0
 
0
 
100394384
-1
25
15297
0
59
2018-1-4  9:48:10
1399
NONONENONENO DISABLEDENABLEDENABLED
0
 NOT IN WAIT  NOT IN WAIT  
3833
147
db file sequential  readfile#
25
0000000000000019block#
15297
0000000000003BC1blocks
1
0000000000000001
1740759767
8
User I/O
-1
5
WAITED SHORT TIME
801
 
5121045
SYS$USERSENABLEDFALSEFALSEFIRST EXEC
100
0000000CA09C1900
238
 

认证徽章
论坛徽章:
15
ERP板块每日发贴之星
日期:2007-08-24 01:03:56狮子座
日期:2015-09-10 09:07:23处女座
日期:2015-09-11 10:28:03ITPUB14周年纪念章
日期:2015-10-26 17:23:44喜羊羊
日期:2015-06-24 14:33:00慢羊羊
日期:2015-06-15 14:22:442015年新春福章
日期:2015-03-12 17:22:46优秀写手
日期:2014-11-20 06:00:13茶鸡蛋
日期:2013-06-29 22:37:49蛋疼蛋
日期:2013-04-27 10:34:16
2#
发表于 2018-1-4 17:28 | 只看该作者
本帖最后由 test_100 于 2018-1-4 17:30 编辑

查看等待:
  1. select * from GV$SESSION_WAIT where sid = 392 ;
复制代码
结果:
   INST_IDSIDSEQ#EVENTP1TEXTP1P1RAWP2TEXTP2P2RAWP3TEXTP3P3RAWWAIT_CLASS_IDWAIT_CLASS#WAIT_CLASSWAIT_TIMESECONDS_IN_WAITSTATEWAIT_TIME_MICROTIME_REMAINING_MICROTIME_SINCE_LAST_WAIT_MICRO
1
2
392
28466
gc current requestfile#
25
0000000000000019block#
1444367
0000000000160A0Fid#
33554433
0000000002000001
3871361733
11
Cluster
-1
12
WAITED SHORT TIME
536
 
12404162
2
1
392
6
SQL*Net message from  clientdriver id
1413697536
0000000054435000#bytes
1
0000000000000001 
0
00
2723168908
6
Idle
0
341
WAITING
340707358
-1
0


使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
3#
发表于 2018-1-5 00:46 | 只看该作者
你所谓的“单独执行”是把里面的绑定变量替换成常量了吧?看一下慢的计划和快的计划分别是什么。(cst_id,pur_dt) 上面有索引吗?这个pur_dt 的查询条件的取值范围是不是变化很大?如果扫描范围忽大忽小,你可能要针对不同情况写不同的SQL。

使用道具 举报

回复
认证徽章
论坛徽章:
15
ERP板块每日发贴之星
日期:2007-08-24 01:03:56狮子座
日期:2015-09-10 09:07:23处女座
日期:2015-09-11 10:28:03ITPUB14周年纪念章
日期:2015-10-26 17:23:44喜羊羊
日期:2015-06-24 14:33:00慢羊羊
日期:2015-06-15 14:22:442015年新春福章
日期:2015-03-12 17:22:46优秀写手
日期:2014-11-20 06:00:13茶鸡蛋
日期:2013-06-29 22:37:49蛋疼蛋
日期:2013-04-27 10:34:16
4#
发表于 2018-1-5 16:28 | 只看该作者
newkid 发表于 2018-1-5 00:46
你所谓的“单独执行”是把里面的绑定变量替换成常量了吧?看一下慢的计划和快的计划分别是什么。(cst_id,pu ...

1.是把里面的绑定变量替换成常量。
2.取值范围是忽大忽小。
3.为什么之前10几分跑完,最近几天才慢。
4.正准备优化代码。

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
5#
发表于 2018-1-5 23:48 | 只看该作者
你没有回答索引的情况。典型的用例是,扫描范围大就别用索引,小范围用索引。假设你硬解析时是小范围扫描,产生了用索引的nested loop计划,你再用大范围的参数去执行就会很慢。

所以你“之前”跑得快是因为碰巧得到了正确的计划。后来发生了硬解析(计划过时了被逐出共享池)用到不恰当的绑定变量值,产生另一个计划,这个计划被沿用下来导致最近几天变慢。

所以你的代码可以这么写,强行为表取个不同的别名a1和a2来让它走不同分支,产生两次应解析,利用BIND PEEKING来生成不同计划。

if v_b1 - v_b2 >10 then -------- 随便写个10天的范围,具体的值应该测试后确定
  SELECT ... FROM xx_v a1 WHERE a.cst_id = v_b3 AND a.pur_dt >= v_b2 AND a.pur_dt < v_b1 ....
else
  SELECT ... FROM xx_v a2 WHERE a.cst_id = v_b3 AND a.pur_dt >= v_b2 AND a.pur_dt < v_b1 ....
end if;

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
6#
发表于 2018-1-6 12:01 | 只看该作者
1、获取慢时的执行计划,说别的也没用;
2、优化代码需要依据,盲目的提出优化代码的需求,既不能让研发信服,也不容易有很好的效果。

使用道具 举报

回复
论坛徽章:
20
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
7#
发表于 2018-1-8 15:11 | 只看该作者
SELECT SUM(a.pur_amount) pur_amount
  FROM xx_v a
WHERE a.cst_id = :b3
   AND a.pur_dt >= :b2
   AND a.pur_dt < :b1
It is a typical bind sensitive SQL or adaptive cursor sharing problem, final query plan will be changed upon the input bind values. if you think the performance is not reasonable, you can use hints to fix the plan such as using index of a.cst_id or index of a.pur_dt or even index_combine (if bitmap indexes are created).
Here is a product can help you to find out which one is better, I am sure it can help you to solve the problem.


使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
8#
发表于 2018-1-9 00:52 | 只看该作者
richto 发表于 2018-1-8 15:11
SELECT SUM(a.pur_amount) pur_amount
  FROM xx_v a
WHERE a.cst_id = :b3

我的猜测恰好相反,ACS没有发生,导致新传入的绑定变量用了旧的不合适的计划。由此可建议楼主加入一个 /*+ BIND_AWARE */ 提示来让CBO更加智能。这样就可以共享一个SQL来应付不同的绑定值了。
我们都在这猜,拥有第一手资料的楼主却跑没影了。

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
9#
发表于 2018-1-9 13:37 | 只看该作者
真相被揭露之前,一切皆有可能,计划变与不变,都可能导致问题的发生。

使用道具 举报

回复
认证徽章
论坛徽章:
15
ERP板块每日发贴之星
日期:2007-08-24 01:03:56狮子座
日期:2015-09-10 09:07:23处女座
日期:2015-09-11 10:28:03ITPUB14周年纪念章
日期:2015-10-26 17:23:44喜羊羊
日期:2015-06-24 14:33:00慢羊羊
日期:2015-06-15 14:22:442015年新春福章
日期:2015-03-12 17:22:46优秀写手
日期:2014-11-20 06:00:13茶鸡蛋
日期:2013-06-29 22:37:49蛋疼蛋
日期:2013-04-27 10:34:16
10#
发表于 2018-1-10 11:45 | 只看该作者
newkid 发表于 2018-1-5 23:48
你没有回答索引的情况。典型的用例是,扫描范围大就别用索引,小范围用索引。假设你硬解析时是小范围扫描, ...

1.之前cst_id 和pur_dt分别建了普通索引。
2.昨天,在cst_id上增加了pur_dt,变成了复合索引,速度已正常(用时几分钟)。

使用道具 举报

回复

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

本版积分规则

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