请教数据库优化,目前表中仅6万数据,一个查询居然要3m左右!

pfengk 2011-05-07 04:01:27
请教一个数据库优化,实在受不了了,表中仅11万数据,查询时间花费3分钟左右时间,SQL我感觉已最优了。


表结构和数据库配置:
-- 此 CLP 文件是使用 DB2LOOK 版本创建的 9.1
-- 时间戳记: 2011-5-7 15:51:41
-- 数据库名称: MESANS1
-- 数据库管理器版本: DB2/NT Version 9.1.3
-- 数据库代码页: 1386
-- 数据库整理顺序为: UNIQUE
-- 省略了 COMMIT。在执行脚本之后,需要显式地进行落实。


-- 模拟表空间
ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;


------------------------------------------------
-- 表的 DDL 语句 "MESANS "."T_PB_FACT_H"
------------------------------------------------


CREATE TABLE "MESANS "."T_PB_FACT_H" (
"F_ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +9223372036854775807
NO CYCLE
CACHE 512
NO ORDER ) ,
"F_DAY" DATE NOT NULL ,
"F_ORDER_CODE" VARCHAR(50) ,
"F_ORG_CODE" VARCHAR(50) ,
"F_SHIFT_CODE" VARCHAR(50) ,
"F_PROD_CODE" VARCHAR(50) ,
"F_MACHINE_CODE" VARCHAR(50) ,
"F_NOTE" VARCHAR(512) ,
"F_MODIFY_TIME" TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP ,
"F_MODIFY_ID" BIGINT ,
"F_CREATE_TIME" TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP ,
"F_CREATE_ID" BIGINT ,
"F_PRT_000" DECIMAL(30,6) ,
"F_PRT_001" DECIMAL(30,6) ,
"F_PRT_002" DECIMAL(30,6) ,
"F_PRT_003" DECIMAL(30,6) ,
"F_PRT_004" DECIMAL(30,6) ,
"F_PRT_005" DECIMAL(30,6) ,
"F_PRT_006" DECIMAL(30,6) ,
"F_PRT_007" DECIMAL(30,6) ,
"F_PRT_008" DECIMAL(30,6) ,
"F_PRT_009" DECIMAL(30,6) ,
"F_PRT_010" DECIMAL(30,6) ,
"F_PRT_011" DECIMAL(30,6) ,
"F_PRT_012" DECIMAL(30,6) ,
"F_PRT_013" DECIMAL(30,6) ,
"F_PRT_014" DECIMAL(30,6) ,
"F_PRT_015" DECIMAL(30,6) ,
"F_PRT_016" DECIMAL(30,6) ,
"F_PRT_017" DECIMAL(30,6) ,
"F_PRT_018" DECIMAL(30,6) ,
"F_PRT_019" DECIMAL(30,6) ,
"F_PRT_020" DECIMAL(30,6) ,
"F_PRT_021" DECIMAL(30,6) ,
"F_PRT_022" DECIMAL(30,6) ,
"F_PRT_023" DECIMAL(30,6) ,
"F_PRT_024" DECIMAL(30,6) ,
"F_PRT_025" DECIMAL(30,6) ,
"F_PRT_026" DECIMAL(30,6) ,
"F_PRT_027" DECIMAL(30,6) ,
"F_PRT_028" DECIMAL(30,6) ,
"F_PRT_029" DECIMAL(30,6) ,
"F_PRT_030" DECIMAL(30,6) ,
"F_PRT_031" DECIMAL(30,6) ,
"F_PRT_032" DECIMAL(30,6) ,
"F_PRT_033" DECIMAL(30,6) ,
"F_PRT_034" DECIMAL(30,6) ,
"F_PRT_035" DECIMAL(30,6) ,
"F_PRT_036" DECIMAL(30,6) ,
"F_PRT_037" DECIMAL(30,6) ,
"F_PRT_038" DECIMAL(30,6) ,
"F_PRT_039" DECIMAL(30,6) ,
"F_PRT_040" DECIMAL(30,6) ,
"F_PRT_041" DECIMAL(30,6) ,
"F_PRT_042" DECIMAL(30,6) ,
"F_PRT_043" DECIMAL(30,6) ,
"F_PRT_044" DECIMAL(30,6) ,
"F_PRT_045" DECIMAL(30,6) ,
"F_PRT_046" DECIMAL(30,6) ,
"F_PRT_047" DECIMAL(30,6) ,
"F_PRT_048" DECIMAL(30,6) ,
"F_PRT_049" DECIMAL(30,6) ,
"F_PRT_050" DECIMAL(30,6) ,
"F_PRT_051" DECIMAL(30,6) ,
"F_PRT_052" DECIMAL(30,6) ,
"F_PRT_053" DECIMAL(30,6) ,
"F_PRT_054" DECIMAL(30,6) ,
"F_PRT_055" DECIMAL(30,6) ,
"F_PRT_056" DECIMAL(30,6) ,
"F_PRT_057" DECIMAL(30,6) ,
"F_PRT_058" DECIMAL(30,6) ,
"F_PRT_059" DECIMAL(30,6) ,
"F_PRT_060" DECIMAL(30,6) ,
"F_PRT_061" DECIMAL(30,6) ,
"F_PRT_062" DECIMAL(30,6) ,
"F_PRT_063" DECIMAL(30,6) ,
"F_PRT_064" DECIMAL(30,6) ,
"F_PRT_065" DECIMAL(30,6) ,
"F_PRT_066" DECIMAL(30,6) ,
"F_PRT_067" DECIMAL(30,6) ,
"F_PRT_068" DECIMAL(30,6) ,
"F_PRT_069" DECIMAL(30,6) ,
"F_PRT_070" DECIMAL(30,6) ,
"F_PRT_071" DECIMAL(30,6) ,
"F_PRT_072" DECIMAL(30,6) ,
"F_PRT_073" DECIMAL(30,6) ,
"F_PRT_074" DECIMAL(30,6) ,
"F_PRT_075" DECIMAL(30,6) ,
"F_PRT_076" DECIMAL(30,6) ,
"F_PRT_077" DECIMAL(30,6) ,
"F_PRT_078" DECIMAL(30,6) ,
"F_PRT_079" DECIMAL(30,6) ,
"F_PRT_080" DECIMAL(30,6) ,
"F_PRT_081" DECIMAL(30,6) ,
"F_PRT_082" DECIMAL(30,6) ,
"F_PRT_083" DECIMAL(30,6) ,
"F_PRT_084" DECIMAL(30,6) ,
"F_PRT_085" DECIMAL(30,6) ,
"F_PRT_086" DECIMAL(30,6) ,
"F_PRT_087" DECIMAL(30,6) ,
"F_PRT_088" DECIMAL(30,6) ,
"F_PRT_089" DECIMAL(30,6) ,
"F_PRT_090" DECIMAL(30,6) ,
"F_PRT_091" DECIMAL(30,6) ,
"F_PRT_092" DECIMAL(30,6) ,
"F_PRT_093" DECIMAL(30,6) ,
"F_PRT_094" DECIMAL(30,6) ,
"F_PRT_095" DECIMAL(30,6) ,
"F_PRT_096" DECIMAL(30,6) ,
"F_PRT_097" DECIMAL(30,6) ,
"F_PRT_098" DECIMAL(30,6) ,
"F_PRT_099" DECIMAL(30,6) ,
"F_PRT_100" DECIMAL(30,6) ,
"F_PRT_101" DECIMAL(30,6) ,
"F_PRT_102" DECIMAL(30,6) ,
"F_PRT_103" DECIMAL(30,6) ,
"F_PRT_104" DECIMAL(30,6) ,
"F_PRT_105" DECIMAL(30,6) ,
"F_PRT_106" DECIMAL(30,6) ,
"F_PRT_107" DECIMAL(30,6) ,
"F_PRT_108" DECIMAL(30,6) ,
"F_PRT_109" DECIMAL(30,6) ,
"F_PRT_110" DECIMAL(30,6) ,
"F_PRT_111" DECIMAL(30,6) ,
"F_PRT_112" DECIMAL(30,6) ,
"F_PRT_113" DECIMAL(30,6) ,
"F_PRT_114" DECIMAL(30,6) ,
"F_PRT_115" DECIMAL(30,6) ,
"F_PRT_116" DECIMAL(30,6) ,
"F_PRT_117" DECIMAL(30,6) ,
"F_PRT_118" DECIMAL(30,6) ,
"F_PRT_119" DECIMAL(30,6) ,
"F_PRT_120" DECIMAL(30,6) ,
"F_PRT_121" DECIMAL(30,6) ,
"F_PRT_122" DECIMAL(30,6) ,
"F_PRT_123" DECIMAL(30,6) ,
"F_PRT_124" DECIMAL(30,6) ,
"F_PRT_125" DECIMAL(30,6) ,
"F_PRT_126" DECIMAL(30,6) ,
"F_PRT_127" DECIMAL(30,6) ,
"F_PRT_128" DECIMAL(30,6) ,
"F_PRT_129" DECIMAL(30,6) ,
"F_PRT_130" DECIMAL(30,6) ,
"F_PRT_131" DECIMAL(30,6) ,
"F_PRT_132" DECIMAL(30,6) ,
"F_PRT_133" DECIMAL(30,6) ,
"F_PRT_134" DECIMAL(30,6) ,
"F_PRT_135" DECIMAL(30,6) ,
"F_PRT_136" DECIMAL(30,6) ,
"F_PRT_137" DECIMAL(30,6) ,
"F_PRT_138" DECIMAL(30,6) ,
"F_PRT_139" DECIMAL(30,6) ,
"F_PRT_140" DECIMAL(30,6) ,
"F_PRT_141" DECIMAL(30,6) ,
"F_PRT_142" DECIMAL(30,6) ,
"F_PRT_143" DECIMAL(30,6) ,
"F_PRT_144" DECIMAL(30,6) ,
"F_PRT_145" DECIMAL(30,6) ,
"F_PRT_146" DECIMAL(30,6) ,
"F_PRT_147" DECIMAL(30,6) ,
"F_PRT_148" DECIMAL(30,6) ,
"F_PRT_149" DECIMAL(30,6) ,
"F_PRT_150" DECIMAL(30,6) ,
"F_PRT_151" DECIMAL(30,6) ,
"F_PRT_152" DECIMAL(30,6) ,
"F_PRT_153" DECIMAL(30,6) ,
"F_PRT_154" DECIMAL(30,6) ,
"F_PRT_155" DECIMAL(30,6) ,
"F_PRT_156" DECIMAL(30,6) ,
"F_PRT_157" DECIMAL(30,6) ,
"F_PRT_158" DECIMAL(30,6) ,
"F_PRT_159" DECIMAL(30,6) ,
"F_PRT_160" DECIMAL(30,6) ,
"F_PRT_161" DECIMAL(30,6) ,
"F_PRT_162" DECIMAL(30,6) ,
"F_PRT_163" DECIMAL(30,6) ,
"F_PRT_164" DECIMAL(30,6) ,
"F_PRT_165" DECIMAL(30,6) ,
"F_PRT_166" DECIMAL(30,6) ,
"F_PRT_167" DECIMAL(30,6) ,
"F_PRT_168" DECIMAL(30,6) ,
"F_PRT_169" DECIMAL(30,6) ,
"F_PRT_170" DECIMAL(30,6) ,
"F_PRT_171" DECIMAL(30,6) ,
"F_PRT_172" DECIMAL(30,6) ,
"F_PRT_173" DECIMAL(30,6) ,
"F_PRT_174" DECIMAL(30,6) ,
"F_PRT_175" DECIMAL(30,6) ,
"F_PRT_176" DECIMAL(30,6) ,
"F_PRT_177" DECIMAL(30,6) ,
"F_PRT_178" DECIMAL(30,6) ,
"F_PRT_179" DECIMAL(30,6) ,
"F_PRT_180" DECIMAL(30,6) ,
"F_PRT_181" DECIMAL(30,6) ,
"F_PRT_182" DECIMAL(30,6) ,
"F_PRT_183" DECIMAL(30,6) ,
"F_PRT_184" DECIMAL(30,6) ,
"F_PRT_185" DECIMAL(30,6) ,
"F_PRT_186" DECIMAL(30,6) ,
"F_PRT_187" DECIMAL(30,6) ,
"F_PRT_188" DECIMAL(30,6) ,
"F_PRT_189" DECIMAL(30,6) ,
"F_PRT_190" DECIMAL(30,6) ,
"F_PRT_191" DECIMAL(30,6) ,
"F_PRT_192" DECIMAL(30,6) ,
"F_PRT_193" DECIMAL(30,6) ,
"F_PRT_194" DECIMAL(30,6) ,
"F_PRT_195" DECIMAL(30,6) ,
"F_PRT_196" DECIMAL(30,6) ,
"F_PRT_197" DECIMAL(30,6) ,
"F_PRT_198" DECIMAL(30,6) ,
"F_PRT_199" DECIMAL(30,6) )
INDEX IN "USERSPACE1" PARTITION BY RANGE("F_DAY" NULLS FIRST)
(PART "P_FACT_2008" STARTING(MINVALUE) EXCLUSIVE ENDING('2008-12-31') IN "USERSPACE1",
PART "P_FACT_2009" STARTING('2009-01-01') ENDING('2009-12-31') IN "USERSPACE1",
PART "P_FACT_2010" STARTING('2010-01-01') ENDING('2010-12-31') IN "USERSPACE1",
PART "P_FACT_2011" STARTING('2011-01-01') ENDING('2011-12-31') IN "USERSPACE1",
PART "P_FACT_2012" STARTING('2012-01-01') ENDING('2012-12-31') IN "USERSPACE1",
PART "P_FACT_2013" STARTING('2013-01-01') ENDING('2013-12-31') IN "USERSPACE1",
PART "P_FACT_2014" STARTING('2014-01-01') ENDING('2014-12-31') EXCLUSIVE IN "USERSPACE1",
PART "P_FACT_2015" STARTING('2015-01-01') ENDING('2015-12-31') IN "USERSPACE1",
PART "P_FACT_2016" STARTING('2016-01-01') ENDING(MAXVALUE) EXCLUSIVE IN "USERSPACE1")
ORGANIZE BY (
( "F_DAY" ) ,
( "F_SHIFT_CODE" ) ,
( "F_ORDER_CODE" ) ,
( "F_ORG_CODE" ) ,
( "F_MACHINE_CODE" ) )
;

ALTER TABLE "MESANS "."T_PB_FACT_H" PCTFREE 30;


-- 表上主键的 DDL 语句 "MESANS "."T_PB_FACT_H"

ALTER TABLE "MESANS "."T_PB_FACT_H"
ADD CONSTRAINT "T_PB_FACT_H" PRIMARY KEY
("F_ID");



ALTER TABLE "MESANS "."T_PB_FACT_H" ALTER COLUMN "F_ID" RESTART WITH 116735;

--------------------------------------------------------
-- 数据库和“数据库管理器”配置参数
--------------------------------------------------------

UPDATE DBM CFG USING cpuspeed 2.519169e-007;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING comm_bandwidth 100.000000;
UPDATE DBM CFG USING federated YES;
UPDATE DBM CFG USING fed_noauth NO;

UPDATE DB CFG FOR MESANS1 USING locklist 4384;
UPDATE DB CFG FOR MESANS1 USING dft_degree -1;
UPDATE DB CFG FOR MESANS1 USING maxlocks 98;
UPDATE DB CFG FOR MESANS1 USING avg_appls 1;
UPDATE DB CFG FOR MESANS1 USING stmtheap 2048;
UPDATE DB CFG FOR MESANS1 USING dft_queryopt 5;
...全文
211 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
pfengk 2011-05-14
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 wangdehao 的回复:]
调整sql语句,先将T_PB_FACT_H汇总,然后再跟V_PB_EQUI连接。类似这样:
select ..... from V_PB_EQUI H left join
(
select ...
from T_PB_FACT_H
group by ...
)H
on ...
[/Quote]

尝试过你的方法,结果一样!
wangdehao 2011-05-13
  • 打赏
  • 举报
回复
另外,对于这种sql语句,大的buffer pool和sort heap配置对于执行速度是非常有帮助的
wangdehao 2011-05-13
  • 打赏
  • 举报
回复
调整sql语句,先将T_PB_FACT_H汇总,然后再跟V_PB_EQUI连接。类似这样:
select ..... from V_PB_EQUI H left join
(
select ...
from T_PB_FACT_H
group by ...
)H
on ...


pfengk 2011-05-13
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 zhaojianmi1 的回复:]
用中间字段,或者接近于真实值的计算来优化吧,做到完全精确,计算量有点大
[/Quote]

不懂怎么做,能详细点吗?
pfengk 2011-05-13
  • 打赏
  • 举报
回复
1、runstats已经做过了,而且定时执行
2、prefetch已设置为20
3、通过SQL执行分析,耗费最多的地方为数据抓取,耗用成本60%左右
zwbhanye 2011-05-12
  • 打赏
  • 举报
回复
1.先runstats一下该表
2.如果是集群数据库,你设置好分区键。要不然数据就会落在单一节点上面。
Mr_Bean 2011-05-11
  • 打赏
  • 举报
回复
第一 先runstats一下该表
第二 尝试调整下prefetch(我想应该有点效果)
第三 看看该语句的access plan
zhaojianmi1 2011-05-10
  • 打赏
  • 举报
回复
用中间字段,或者接近于真实值的计算来优化吧,做到完全精确,计算量有点大
yangxiao_jiang 2011-05-09
  • 打赏
  • 举报
回复
看看存取路径,看是否可以增加索引来提速,不过这么多的计算,估计是快不到那里去
qhdjm 2011-05-09
  • 打赏
  • 举报
回复
用TOAD 分析一下,看哪里耗费最多
cspamway 2011-05-08
  • 打赏
  • 举报
回复
这个很正常。你知道的
pfengk 2011-05-08
  • 打赏
  • 举报
回复
不能用物化视图,查询字段在运行时确定!
zhaojianmi1 2011-05-07
  • 打赏
  • 举报
回复
这个语句计算量比较大啊,看看能不能预处理,或者物化视图
pfengk 2011-05-07
  • 打赏
  • 举报
回复
通过SQL分析
数据抓取用60%成本。
pfengk 2011-05-07
  • 打赏
  • 举报
回复
这个SQL用时3分钟。
SELECT F_MACHINE_NAME,AVG(COL_01000001),AVG(COL_01000002),AVG(COL_01000004),AVG(COL_01000007),
AVG(COL_03000005),AVG(COL_03000006),AVG(COL_02300003),AVG(COL_02400003),AVG(COL_02400002),AVG(COL_05100021)
FROM (SELECT M.F_MACHINE_NAME,M.F_MACHINE_CODE,
DECIMAL(SUM(F_PRT_000),14,2) AS COL_01000001,DECIMAL(sum ( F_PRT_001 ),14,2) AS COL_01000002,
DECIMAL(sum ( F_PRT_002 ) ,14,2) AS COL_01000004,DECIMAL(sum ( F_PRT_129 ) ,20,4) AS COL_01000007,
DECIMAL(avg ( F_PRT_006 ) ,5,2) AS COL_03000005, DECIMAL( sum ( F_PRT_000 ) * 1.00 / CASE WHEN DECIMAL( sum ( F_PRT_129 ),20,4) = 0 THEN NULL ELSE DECIMAL( sum(F_PRT_129),20,4) END * 100 ,20,4) AS COL_03000006,
DECIMAL((AVG(F_PRT_003) + AVG(F_PRT_005)) / 2.0,14,2) AS COL_02300003,
DECIMAL(avg ( F_PRT_128 ) ,5,2) AS COL_02400003,
DECIMAL(98 ,6,2) AS COL_02400002,DECIMAL(sum ( F_PRT_039 ) ,30,2) AS COL_05100021
FROM V_PB_EQUI M
LEFT JOIN T_PB_FACT_H H ON H.F_MACHINE_CODE=M.F_MACHINE_CODE
WHERE 1=1 AND H.F_DAY BETWEEN '2011-01-01' AND '2011-05-07'
GROUP BY M.F_MACHINE_NAME,M.F_MACHINE_CODE) ZZZ
GROUP BY F_MACHINE_NAME,F_MACHINE_CODE
ORDER BY F_MACHINE_CODE

5,889

社区成员

发帖
与我相关
我的任务
社区描述
IBM DB2 是美国IBM公司开发的一套关系型数据库管理系统,它主要的运行环境为UNIX(包括IBM自家的AIX)、Linux、IBM i(旧称OS/400)、z/OS,以及Windows服务器版本
社区管理员
  • DB2
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧