一执行数据库就慢下来的sql，请问如何解决？
SELECT apa_file.*,apb_file.*,rvv03,apo02, apr02,pmm22, pmn31, pmn04,pmn041, azi03, azi04, azi05FROM apa_file,OUTER (apb_file,OUTER
(pmn_file,pmm_file),OUTER rvv_file),OUTER azi_file,OUTER apo_file,
OUTER apr_file WHERE apa01 = apb01 AND azi01 = apa13 AND apo01 = apa19
AND apr01 = apa36 AND pmn01 = apb06 AND pmn02 = apb07 AND rvv01 =
apb21 AND rvv02 = apb22 AND pmm01 = pmn01 AND apo01 = apa19 AND
apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01；
这个sql一执行，其它用户就进不了数据库了，请高手帮忙看看如何优化，谢谢
回复 #1 chiqieqie 的帖子
进不了数据库有什么报错信息没? 查看/u/informix/tmp/af.2e352469文件，里相关信息如下：15:41:24邏輯登錄 517547 - 備份完成
15:44:41斷言失敗: Exception Caught. Type: MT_EX_OS, Context: mem
15:41:24邏輯登錄 517547 - 備份完成
15:44:41斷言失敗: Exception Caught. Type: MT_EX_OS, Context: mem
15:44:41IBM Informix Dynamic Server Version 9.40.FC7
15:44:41
使用者:Session(705317, zh16879@zhdb01, 31689, c00000018a5c61a8)
Thread(731725, sqlexec, c000000185aaa838, 1)
File: mtex.c Line: 377
15:44:41 動作: 請通知 Informix 技術支援部門.
15:44:41stack trace for pid 4936 written to /u/inf7.3/tmp/af.2e352469
15:44:42 請參閱: /u/informix/tmp/af.2e352469, shmem.2e352469.0
查看onstat -g ses 705317：
/u/inf7.3/bin/onstat -g ses 705317:
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 33 days 03:10:15 -- 8305372 Kbytes
session #RSAM total used dynamic
id user tty pid hostname threadsmemory memory explain
705317 zh16879 142 31689 zhdb01 1 344064 317800 off
tid name rstcb flags curstk status
731725 sqlexecc000000185aaa838 ---P---61456 running
Memory pools count 7
name class addr totalsizefreesize #allocfrag #freefrag
705317 V c00000018bc05040 229376 10984 224 23
705317*O0 V c000000194fb2040 32768 3912 26 2
705317*O1 V c00000019b8b3040 12288 840 9 1
705317*O2 V c00000019ba1f040 20480 1864 16 2
705317*O3 V c00000019bb6d040 20480 840 17 1
705317*O4 V c00000019c25f040 16384 3912 10 2
705317*O5 V c00000019c3ea040 12288 3912 6 2
name free used name free used
overhead 0 22792 scb 0 208
opentable 0 5752 filetable 0 1016
log 0 2184 temprec 0 10104
keys 0 416 ralloc 0 240200
gentcb 0 1840 ostcb 0 3416
sqscb 0 22344 sql 0 72
rdahead 0 224 hashfiletab 0 552
osenv 0 2520 sqtcb 0 3808
fragman 0 352
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompinddirectives
c00000018997e818 c0000001bccbd028 0 0 0 2 1
SessSQL Current Iso Lock SQLISAM F.E.
Id Stmt type Database Lvl Mode ERRERRVers Explain
705317 SELECT xyz DRWait 1 0 0 7.20 Off
Current SQL statement :
SELECT apa_file.*,apb_file.*,rvv03,apo02, apr02,pmm22, pmn31, pmn04,
pmn041, azi03, azi04, azi05FROM apa_file,OUTER (apb_file,OUTER
(pmn_file,pmm_file),OUTER rvv_file),OUTER azi_file,OUTER apo_file,
OUTER apr_file WHERE apa01 = apb01 AND azi01 = apa13 AND apo01 = apa19
AND apr01 = apa36 AND pmn01 = apb06 AND pmn02 = apb07 AND rvv01 =
apb21 AND rvv02 = apb22 AND pmm01 = pmn01 AND apo01 = apa19 AND
apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01
这个语句执行后，基本系统就hang住了，dbaccess进数据库都不行，整个机器相当慢，后来用onstat -g seg看发现共享内存虚拟段多了很多。
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 71 days 01:42:40 -- 14654172 Kbytes
Segment Summary:
id key addr size ovhd class blkusedblkfree
87047 1388660737 c00000000041d000 4294705152 554920 R* 10485120
11 1388660741 c0000001003dd000 3125768192 96064 V 761686 1441
15 1388660745 c0000001ba8d4000 4456448 808 M 1062 26
16 1388660746 c0000001bad14000 4456448 808 M 1054 34
17 1388660747 c0000001bb154000 4456448 808 M 1054 34
18 1388660748 c0000001bb594000 4456448 808 M 1054 34
19 1388660749 c0000001bb9d4000 4456448 808 M 1054 34
20 1388660750 c0000001bbe14000 4456448 808 M 1054 34
21 1388660751 c0000001bc254000 4456448 808 M 1054 34
22 1388660752 c0000001bc694000 4456448 808 M 1054 34
15134951388660753 c0000001bcad4000 209715200 7072 V 48471 2729
6168 1388660754 c0000001c92d4000 209715200 7072 V 48858 2342
6169 1388660755 c0000001d5ad4000 209715200 7072 V 48134 3066
359962 1388660756 c0000001e22d4000 209715200 7072 V 48125 3075
7707 1388660757 c0000001eead4000 209715200 7072 V 49424 1776
7196 1388660758 c0000001fb2d4000 209715200 7072 V 48608 2592
360477 1388660759 c000000207ad4000 209715200 7072 V 48230 2970
8734 1388660760 c0000002142d4000 209715200 7072 V 48437 2763
8735 1388660761 c000000220ad4000 209715200 7072 V 48545 2655
8224 1388660762 c00000022d2d4000 209715200 7072 V 49736 1464
7201 1388660763 c000000239ad4000 209715200 7072 V 48572 2628
359970 1388660764 c0000002462d4000 209715200 7072 V 48263 2937
7715 1388660765 c000000252ad4000 209715200 7072 V 48323 2877
7204 1388660766 c00000025f2d4000 209715200 7072 V 48899 2301
6181 1388660767 c00000026bad4000 209715200 7072 V 49487 1713
4646 1388660768 c0000002782d4000 209715200 7072 V 48371 2829
357927 1388660769 c000000284ad4000 209715200 7072 V 48623 2577
5160 1388660770 c0000002912d4000 209715200 7072 V 48389 2811
4649 1388660771 c00000029dad4000 209715200 7072 V 48389 2811
4650 1388660772 c0000002aa2d4000 209715200 7072 V 48832 2368
4139 1388660773 c0000002b6ad4000 209715200 7072 V 49391 1809
3628 1388660774 c0000002c32d4000 209715200 7072 V 48127 3073
365101 1388660775 c0000002cfad4000 209715200 7072 V 48194 3006
4142 1388660776 c0000002dc2d4000 209715200 7072 V 48126 3074
4143 1388660777 c0000002e8ad4000 209715200 7072 V 48172 3028
3632 1388660778 c0000002f52d4000 209715200 7072 V 48545 2655
3633 1388660779 c000000301ad4000 209715200 7072 V 49252 1948
3122 1388660780 c00000030e2d4000 209715200 7072 V 48426 2774
364083 1388660781 c00000031aad4000 209715200 7072 V 48253 2947
4148 1388660782 c0000003272d4000 209715200 7072 V 48006 3194
4149 1388660783 c000000333ad4000 209715200 7072 V 48378 2822
3638 1388660784 c0000003402d4000 209715200 7072 V 48304 2896
3639 1388660785 c00000034cad4000 209715200 7072 V 48065 3135
2104 1388660786 c0000003592d4000 209715200 7072 V 48186 3014
2105 1388660787 c000000365ad4000 209715200 7072 V 48954 2246
2106 1388660788 c0000003722d4000 209715200 7072 V 36490 14710
Total: - - 15005872128 - - 3554223109320
[ 本帖最后由 chiqieqie 于 2009-11-23 14:22 编辑 ] 帖下/u/informix/tmp/af.2e352469 文件前面的stack部分~
做下explain计划~
dbaccess dbname -
set explain avoid_execute;
SELECT apa_file.*,apb_file.*,rvv03,apo02, apr02,pmm22, pmn31, pmn04,
pmn041, azi03, azi04, azi05FROM apa_file,OUTER (apb_file,OUTER
(pmn_file,pmm_file),OUTER rvv_file),OUTER azi_file,OUTER apo_file,
OUTER apr_file WHERE apa01 = apb01 AND azi01 = apa13 AND apo01 = apa19
AND apr01 = apa36 AND pmn01 = apb06 AND pmn02 = apb07 AND rvv01 =
apb21 AND rvv02 = apb22 AND pmm01 = pmn01 AND apo01 = apa19 AND
apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01; 15:44:41Stack for thread: 731725 sqlexec
base: 0xc0000001ec542000
len: 69632
pc: 0x0000000000000000
tos: 0xc0000001ec544640
state: running
vp: 1
( 0)0x400000000089fc08 legacy_hp_afstack + 0x258
( 1)0x400000000089f25c afstack + 0x5c
( 2)0x400000000089e4ec afhandler + 0x984
( 3)0x400000000089da98 affail_interface + 0x50
( 4)0x40000000008a448c mt_ex_throw_sig + 0x19c
( 5)0x4000000000867a48 afsig_handler + 0xf8
( 6)0xc0000000003f99d0 _sigreturn
( 7)0x40000000003f774c op_2phase_prune + 0x3dc
( 8)0x40000000003f5014 opprune + 0x19c
( 9)0x40000000003f6368 op_join + 0x358
(10)0x40000000003f7be0 op_phasejoin + 0x360
(11)0x4000000000357144 sqoptim + 0x14d4
(12)0x400000000048f2b8 bldstructs + 0x148
(13)0x400000000048f070 sqcmd + 0x250
(14)0x400000000048eb4c sq_cmnd + 0x94
(15)0x400000000048ec70 sq_prepare + 0x40
(16)0x4000000000521080 sqmain + 0x128
(17)0x4000000000876a74 startup + 0xd4
(18)0x400000000087685c resume + 0x10c
base: 0xc0000001ec542000
len: 69632
pc: 0x0000000000000000
tos: 0xc0000001ec544640
state: running
vp: 1
15:44:42 请参阅: /u/inf7.3/tmp/af.2e352469, shmem.2e352469.0
---------------------------------
Begin System Alarm Program Output
---------------------------------
Assertion Failure Type: FAILURE
Host Name: chnyts01
Database Server Name: on_tcp111
Time of failure: Fri Oct 16 15:44:42 EAT 2009
AF file: /u/inf7.3/tmp/af.2e352469
Shared memory file: None
System Blocking: OFF
回复 #1 chiqieqie 的帖子
哇，好多外连接，条件里apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'相关的记录多吗？考虑先过滤成临时表再进行外连接？另外建议先看看连接项是否都有索引支持，包括其他条件，红色的V段是执行后产生的吗？如果限制共享内存的大小，则不至于把系统拖垮，不过这个任务就更慢。 外联接太多了，真不知哪个破强人设计出这样的数据库来了，SQL建议改为如下试试：select pmn01, pmn02, pmm22, pmn31, pmn04, pmn041
from pmn_file, pmm_file
where pmm01 = pmn01
into temp tmp1 with no log;
select apb_file.*, rvv_file.rvv03, tmp1.pmm22, tmp1.pmn31, tmp1.pmn04, tmp1.pmn041
from apb_file, outer tmp1, outer rvv_file
where apb_file.apb06 = tmp1.pmn01 and apb_file.apb07 = tmp1.pmn02
and apb_file.apb21 = rvv_file.rvv01 and apb_file.apb22 = rvv_file.rvv02
into temp tmp2 with no log;
select apa_file.*, tmp2.*, apo02, apr02, azi03, azi04, azi05
from apa_file, outer tmp2, outer azi_file, outer apo_file, outer apr_file
where apa01 = tmp2.apb01 AND azi01 = apa13 AND apo01 = apa19
AND apr01 = apa36 AND apo01 = apa19
AND apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01;
另外，如果apa_file记录数很多，可以考虑使用并行查询。
页:
[1]