Logo MurCode
Oracle Новое сообщение

Разое время выполнение запроса

Relic Hunter
Дата: 04.12.2009 01:42:28
Приветствую!

Две тестовых машины Sun T2000, прицеплены к стореджу Netapp. Базы А и Б - онинаковые, клонированные. Сегодня утром база Б начала жестко тормозить. vmstat, iostat никакиx аномалий не обнаружили. Базы тестовые - юзеров нет. Прилагаю результаты трасировки на базас А и Б. При тех-же планах, скорость выполнения отличается в десятки раз. Уверен, что после перегруза Б все придет в норму. Но хотелось-бы понять причину.

ПС
Oracle 10.2.0.2 SE on Solaris 10.

База А (Быстрая)

PARSING IN CURSOR #8 len=46 dep=0 uid=96 oct=3 lid=96 tim=16720487638800 hv=1348261075 ad='fedd7838'
SELECT count(*) FROM V_COVERAGECATEGORY_LOOKUP
END OF STMT
PARSE #8:c=4120000,e=8810515,p=16,cr=4479,cu=0,mis=1,r=0,dep=0,og=1,tim=16720487638785
BINDS #8:
EXEC #8:c=0,e=911,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16720487641795
WAIT #8: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16720487642288
FETCH #8:c=70000,e=61089,p=0,cr=44,cu=0,mis=0,r=1,dep=0,og=1,tim=16720487703870
WAIT #8: nam='SQL*Net message from client' ela= 628 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16720487710779
FETCH #8:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=16720487711345
WAIT #8: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16720487711811
WAIT #8: nam='SQL*Net message from client' ela= 48598 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16720487760896
STAT #8 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=44 pr=0 pw=0 time=61164 us)'
STAT #8 id=2 cnt=82 pid=1 pos=1 obj=70375 op='VIEW  V_COVERAGECATEGORY_LOOKUP (cr=44 pr=0 pw=0 time=61168 us)'
STAT #8 id=3 cnt=82 pid=2 pos=1 obj=0 op='SORT UNIQUE (cr=44 pr=0 pw=0 time=61002 us)'
STAT #8 id=4 cnt=113 pid=3 pos=1 obj=0 op='UNION-ALL  (cr=44 pr=0 pw=0 time=25810 us)'
STAT #8 id=5 cnt=36 pid=4 pos=1 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=24774 us)'
STAT #8 id=6 cnt=36 pid=5 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=17707 us)'
STAT #8 id=7 cnt=9 pid=6 pos=1 obj=0 op='VIEW  index$_join$_020 (cr=6 pr=0 pw=0 time=9422 us)'
STAT #8 id=8 cnt=9 pid=7 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=9409 us)'
STAT #8 id=9 cnt=9 pid=8 pos=1 obj=67913 op='INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_AK (cr=3 pr=0 pw=0 time=334 us)'
STAT #8 id=10 cnt=9 pid=8 pos=2 obj=67912 op='INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_PK (cr=3 pr=0 pw=0 time=102 us)'
STAT #8 id=11 cnt=36 pid=6 pos=2 obj=67915 op='INDEX FULL SCAN BENEFIT_DENTAL_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=191 us)'
STAT #8 id=12 cnt=13 pid=5 pos=2 obj=0 op='VIEW  index$_join$_018 (cr=6 pr=0 pw=0 time=3465 us)'
STAT #8 id=13 cnt=13 pid=12 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=3461 us)'
STAT #8 id=14 cnt=13 pid=13 pos=1 obj=68423 op='INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_AK (cr=3 pr=0 pw=0 time=116 us)'
STAT #8 id=15 cnt=13 pid=13 pos=2 obj=68422 op='INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_PK (cr=3 pr=0 pw=0 time=121 us)'
STAT #8 id=16 cnt=13 pid=4 pos=2 obj=68423 op='INDEX FULL SCAN DENTAL_COV_CATEGORY_AK (cr=1 pr=0 pw=0 time=52 us)'
STAT #8 id=17 cnt=8 pid=4 pos=3 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=17787 us)'
STAT #8 id=18 cnt=8 pid=17 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=7453 us)'
STAT #8 id=19 cnt=8 pid=18 pos=1 obj=67962 op='INDEX FULL SCAN BENEFIT_VISION_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=57 us)'
STAT #8 id=20 cnt=9 pid=18 pos=2 obj=0 op='VIEW  index$_join$_024 (cr=6 pr=0 pw=0 time=4443 us)'
STAT #8 id=21 cnt=9 pid=20 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=4431 us)'
STAT #8 id=22 cnt=9 pid=21 pos=1 obj=67960 op='INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_AK (cr=3 pr=0 pw=0 time=102 us)'
STAT #8 id=23 cnt=9 pid=21 pos=2 obj=67959 op='INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_PK (cr=3 pr=0 pw=0 time=123 us)'
STAT #8 id=24 cnt=9 pid=17 pos=2 obj=0 op='VIEW  index$_join$_022 (cr=6 pr=0 pw=0 time=3118 us)'
STAT #8 id=25 cnt=9 pid=24 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=3099 us)'
STAT #8 id=26 cnt=9 pid=25 pos=1 obj=69559 op='INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=96 us)'
STAT #8 id=27 cnt=9 pid=25 pos=2 obj=69558 op='INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=91 us)'
STAT #8 id=28 cnt=9 pid=4 pos=4 obj=69559 op='INDEX FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=51 us)'
STAT #8 id=29 cnt=23 pid=4 pos=5 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=16248 us)'
STAT #8 id=30 cnt=23 pid=29 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=8361 us)'
STAT #8 id=31 cnt=23 pid=30 pos=1 obj=0 op='VIEW  index$_join$_028 (cr=6 pr=0 pw=0 time=4281 us)'
STAT #8 id=32 cnt=23 pid=31 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=4232 us)'
STAT #8 id=33 cnt=23 pid=32 pos=1 obj=67942 op='INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_AK (cr=3 pr=0 pw=0 time=132 us)'
STAT #8 id=34 cnt=23 pid=32 pos=2 obj=67941 op='INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_PK (cr=3 pr=0 pw=0 time=162 us)'
STAT #8 id=35 cnt=23 pid=30 pos=2 obj=67944 op='INDEX FULL SCAN BENEFIT_HEALTH_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=102 us)'
STAT #8 id=36 cnt=24 pid=29 pos=2 obj=0 op='VIEW  index$_join$_026 (cr=6 pr=0 pw=0 time=4184 us)'
STAT #8 id=37 cnt=24 pid=36 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=4156 us)'
STAT #8 id=38 cnt=24 pid=37 pos=1 obj=68919 op='INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=145 us)'
STAT #8 id=39 cnt=24 pid=37 pos=2 obj=68918 op='INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=120 us)'
STAT #8 id=40 cnt=24 pid=4 pos=6 obj=68919 op='INDEX FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=76 us)'
STAT #8 id=41 cnt=0 pid=4 pos=7 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=37 us)'
STAT #8 id=42 cnt=0 pid=41 pos=1 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=32 us)'
STAT #8 id=43 cnt=0 pid=42 pos=1 obj=67928 op='INDEX FULL SCAN BENEFIT_DRUG_COV_CAT_ASSGN_PK (cr=1 pr=0 pw=0 time=28 us)'
STAT #8 id=44 cnt=0 pid=42 pos=2 obj=67924 op='TABLE ACCESS BY INDEX ROWID BENEFIT_DRUG_COV_CAT (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=45 cnt=0 pid=44 pos=1 obj=67925 op='INDEX UNIQUE SCAN BENEFIT_DRUG_COV_CAT_PK (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=46 cnt=0 pid=41 pos=2 obj=68696 op='TABLE ACCESS BY INDEX ROWID DRUG_COVERAGE_CATEGORY (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=47 cnt=0 pid=46 pos=1 obj=68697 op='INDEX UNIQUE SCAN DRUG_COVERAGE_CATEGORY_PK (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=48 cnt=0 pid=4 pos=8 obj=68698 op='INDEX FULL SCAN DRUG_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=25 us)'
=====================

База Б (Тормоз)

PARSING IN CURSOR #1 len=46 dep=0 uid=96 oct=3 lid=96 tim=2978276119227 hv=1348261075 ad='24b6fff0'
SELECT count(*) FROM V_COVERAGECATEGORY_LOOKUP
END OF STMT
PARSE #1:c=3900000,e=3845990,p=4,cr=4736,cu=0,mis=1,r=0,dep=0,og=1,tim=2978276119211
BINDS #1:
EXEC #1:c=0,e=316,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2978276119790
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2978276119892
FETCH #1:c=6320000,e=6174832,p=0,cr=44,cu=0,mis=0,r=1,dep=0,og=1,tim=2978282294845
WAIT #1: nam='SQL*Net message from client' ela= 3642 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2978282303738
FETCH #1:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=2978282303901
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2978282303994
WAIT #1: nam='SQL*Net message from client' ela= 68497 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2978282372592
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=44 pr=0 pw=0 time=6174866 us)'
STAT #1 id=2 cnt=82 pid=1 pos=1 obj=70375 op='VIEW  V_COVERAGECATEGORY_LOOKUP (cr=44 pr=0 pw=0 time=6174892 us)'
STAT #1 id=3 cnt=82 pid=2 pos=1 obj=0 op='SORT UNIQUE (cr=44 pr=0 pw=0 time=6174725 us)'
STAT #1 id=4 cnt=113 pid=3 pos=1 obj=0 op='UNION-ALL  (cr=44 pr=0 pw=0 time=4382005 us)'
STAT #1 id=5 cnt=36 pid=4 pos=1 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=4380813 us)'
STAT #1 id=6 cnt=36 pid=5 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=4042890 us)'
STAT #1 id=7 cnt=9 pid=6 pos=1 obj=0 op='VIEW  index$_join$_020 (cr=6 pr=0 pw=0 time=2462039 us)'
STAT #1 id=8 cnt=9 pid=7 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=2462009 us)'
STAT #1 id=9 cnt=9 pid=8 pos=1 obj=67913 op='INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_AK (cr=3 pr=0 pw=0 time=39845 us)'
STAT #1 id=10 cnt=9 pid=8 pos=2 obj=67912 op='INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_PK (cr=3 pr=0 pw=0 time=166 us)'
STAT #1 id=11 cnt=36 pid=6 pos=2 obj=67915 op='INDEX FULL SCAN BENEFIT_DENTAL_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=315 us)'
STAT #1 id=12 cnt=13 pid=5 pos=2 obj=0 op='VIEW  index$_join$_018 (cr=6 pr=0 pw=0 time=122429 us)'
STAT #1 id=13 cnt=13 pid=12 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=122412 us)'
STAT #1 id=14 cnt=13 pid=13 pos=1 obj=68423 op='INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_AK (cr=3 pr=0 pw=0 time=132 us)'
STAT #1 id=15 cnt=13 pid=13 pos=2 obj=68422 op='INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_PK (cr=3 pr=0 pw=0 time=130 us)'
STAT #1 id=16 cnt=13 pid=4 pos=2 obj=68423 op='INDEX FULL SCAN DENTAL_COV_CATEGORY_AK (cr=1 pr=0 pw=0 time=63 us)'
STAT #1 id=17 cnt=8 pid=4 pos=3 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=1703452 us)'
STAT #1 id=18 cnt=8 pid=17 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=266876 us)'
STAT #1 id=19 cnt=8 pid=18 pos=1 obj=67962 op='INDEX FULL SCAN BENEFIT_VISION_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=65 us)'
STAT #1 id=20 cnt=9 pid=18 pos=2 obj=0 op='VIEW  index$_join$_024 (cr=6 pr=0 pw=0 time=236323 us)'
STAT #1 id=21 cnt=9 pid=20 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=236311 us)'
STAT #1 id=22 cnt=9 pid=21 pos=1 obj=67960 op='INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_AK (cr=3 pr=0 pw=0 time=109 us)'
STAT #1 id=23 cnt=9 pid=21 pos=2 obj=67959 op='INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_PK (cr=3 pr=0 pw=0 time=165 us)'
STAT #1 id=24 cnt=9 pid=17 pos=2 obj=0 op='VIEW  index$_join$_022 (cr=6 pr=0 pw=0 time=1295 us)'
STAT #1 id=25 cnt=9 pid=24 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=1275 us)'
STAT #1 id=26 cnt=9 pid=25 pos=1 obj=69559 op='INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=132 us)'
STAT #1 id=27 cnt=9 pid=25 pos=2 obj=69558 op='INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=86 us)'
STAT #1 id=28 cnt=9 pid=4 pos=4 obj=69559 op='INDEX FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=53 us)'
STAT #1 id=29 cnt=23 pid=4 pos=5 obj=0 op='HASH JOIN  (cr=13 pr=0 pw=0 time=43648 us)'
STAT #1 id=30 cnt=23 pid=29 pos=1 obj=0 op='HASH JOIN  (cr=7 pr=0 pw=0 time=40697 us)'
STAT #1 id=31 cnt=23 pid=30 pos=1 obj=0 op='VIEW  index$_join$_028 (cr=6 pr=0 pw=0 time=1695 us)'
STAT #1 id=32 cnt=23 pid=31 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=1647 us)'
STAT #1 id=33 cnt=23 pid=32 pos=1 obj=67942 op='INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_AK (cr=3 pr=0 pw=0 time=128 us)'
STAT #1 id=34 cnt=23 pid=32 pos=2 obj=67941 op='INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_PK (cr=3 pr=0 pw=0 time=156 us)'
STAT #1 id=35 cnt=23 pid=30 pos=2 obj=67944 op='INDEX FULL SCAN BENEFIT_HEALTH_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=132 us)'
STAT #1 id=36 cnt=24 pid=29 pos=2 obj=0 op='VIEW  index$_join$_026 (cr=6 pr=0 pw=0 time=1714 us)'
STAT #1 id=37 cnt=24 pid=36 pos=1 obj=0 op='HASH JOIN  (cr=6 pr=0 pw=0 time=1686 us)'
STAT #1 id=38 cnt=24 pid=37 pos=1 obj=68919 op='INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=166 us)'
STAT #1 id=39 cnt=24 pid=37 pos=2 obj=68918 op='INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=117 us)'
STAT #1 id=40 cnt=24 pid=4 pos=6 obj=68919 op='INDEX FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=75 us)'
STAT #1 id=41 cnt=0 pid=4 pos=7 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=39 us)'
STAT #1 id=42 cnt=0 pid=41 pos=1 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=34 us)'
STAT #1 id=43 cnt=0 pid=42 pos=1 obj=67928 op='INDEX FULL SCAN BENEFIT_DRUG_COV_CAT_ASSGN_PK (cr=1 pr=0 pw=0 time=29 us)'
STAT #1 id=44 cnt=0 pid=42 pos=2 obj=67924 op='TABLE ACCESS BY INDEX ROWID BENEFIT_DRUG_COV_CAT (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=45 cnt=0 pid=44 pos=1 obj=67925 op='INDEX UNIQUE SCAN BENEFIT_DRUG_COV_CAT_PK (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=46 cnt=0 pid=41 pos=2 obj=68696 op='TABLE ACCESS BY INDEX ROWID DRUG_COVERAGE_CATEGORY (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=47 cnt=0 pid=46 pos=1 obj=68697 op='INDEX UNIQUE SCAN DRUG_COVERAGE_CATEGORY_PK (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=48 cnt=0 pid=4 pos=8 obj=68698 op='INDEX FULL SCAN DRUG_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=24 us)'
=====================
Вячеслав Любомудров
Дата: 04.12.2009 02:19:14
Настройки PGA одинаковые?
Relic Hunter
Дата: 04.12.2009 02:32:30
Вячеслав Любомудров,

Да, по дефолту.

Дело в том, что сервер Б сам по-себе тормозит, хотя никаких активностех не обнаружено.

База А

   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 29622 root     1936K 1928K cpu8    59    0   0:00:00 0.0% prstat/1
 29259 500      2421M 2147M sleep   59    0   0:00:06 0.0% oracle/1
 25916 500      1397M 1123M sleep   59    0   0:00:52 0.0% oracle/1
 13427 500      1396M 1110M sleep   59    0   3:45:52 0.0% oracle/1
 13263 500      1396M 1102M sleep   59    0   3:48:00 0.0% oracle/1
 25926 500      2421M 2147M sleep   59    0   0:00:52 0.0% oracle/1
  3408 500      2420M 2126M sleep   59    0   1:35:36 0.0% oracle/1
 22868 500      2421M 2118M sleep   59    0   0:14:15 0.0% oracle/1
 22885 500      2422M 2137M sleep   59    0   0:23:17 0.0% oracle/1
 11937 root      224M   70M sleep   59    0   6:11:58 0.0% java/44
 13243 500      1397M 1094M sleep   59    0   1:39:47 0.0% oracle/1
  3404 500      2422M 2136M sleep   59    0   1:19:43 0.0% oracle/1
 13423 500      1398M 1119M sleep   59    0   2:54:22 0.0% oracle/1
 13259 500      1398M 1111M sleep   59    0   2:58:20 0.0% oracle/1
  3378 500      2421M 2118M sleep   59    0   0:51:08 0.0% oracle/1
 22889 500      2420M 2126M sleep   59    0   0:30:33 0.0% oracle/1
   215 root       11M 4576K sleep   59    0   0:04:24 0.0% inetd/4
   217 root     2336K  720K sleep   59    0   0:00:09 0.0% ttymon/1
   201 root     2392K 1128K sleep  100    -   0:24:45 0.0% xntpd/1
   195 daemon   2840K 1176K sleep   59    0   0:00:00 0.0% rpcbind/1
   204 daemon    458M 2720K sleep   59    0   0:15:28 0.0% nfsmapid/4
Total: 156 processes, 1320 lwps, load averages: 0.04, 0.04, 0.04

[root@sunsol03 ~]# time date
Thu Dec  3 16:30:09 MST 2009

real    0m0.008s
user    0m0.002s
sys     0m0.007s

База Б

   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 18831 root     1936K 1624K cpu12   40    0   0:00:01 0.3% prstat/1
 18826 root     3160K 2640K sleep   59    0   0:00:01 0.2% bash/1
 11969 500      3445M 3178M sleep   59    0   0:11:50 0.1% oracle/1
 18821 root     8976K 5672K sleep   59    0   0:00:01 0.1% sshd/1
 18824 root     8640K 2760K sleep   59    0   0:00:00 0.1% sshd/1
   867 noaccess  178M   51M sleep   59    0   1:26:37 0.0% java/39
   277 root     2584K 1312K sleep   59    0   3:34:09 0.0% in.routed/1
   553 root      223M   42M sleep   59    0   1:29:23 0.0% java/36
  1727 500      3188M  239M sleep   59    0   0:06:50 0.0% oracle/1
 14075 500      3189M 1374M sleep   59    0   0:06:08 0.0% oracle/1
  2667 500      3444M 3172M sleep   59    0   0:35:37 0.0% oracle/1
  2663 500      3446M 3175M sleep   59    0   0:31:16 0.0% oracle/1
 10751 500      1397M 1099M sleep   53    2   0:01:08 0.0% oracle/1
 22728 noaccess  182M   55M sleep   59    0   1:09:04 0.0% java/39
  1723 500      3190M  949M sleep   59    0   0:05:51 0.0% oracle/1
  2094 noaccess  216M   74M sleep   59    0   1:12:08 0.0% java/39
  1717 500      3188M  759M sleep   59    0   0:02:38 0.0% oracle/1
  1701 500      3189M  179M sleep   59    0   0:03:31 0.0% oracle/1
  2657 500      3445M 3168M sleep   59    0   0:14:01 0.0% oracle/1
  2641 500      3445M 3168M sleep   59    0   0:17:46 0.0% oracle/1
   144 root     2424K 1008K sleep   59    0   0:25:47 0.0% in.mpathd/1
Total: 185 processes, 1314 lwps, load averages: 0.13, 0.25, 0.44

[root@sunsol06 ~]# time date
Thu Dec  3 16:30:51 MST 2009

real    0m0.100s
user    0m0.002s
sys     0m0.120s
[root@sunsol06 ~]#
Тынц.
Дата: 04.12.2009 02:33:06
Relic Hunter,

В приведённых примерах "в разы" отличается только Fetch, если же учитывать время Parse, то почти одинаково. В Fetch разница набегает за счёт SORT и HASH JOIN => сравните pga_aggregate_target/v$pgastat, ну и причины отличий ещё в нескольких проблемных запросах, а то судить по одному, да ещё с hard parse...

SELECT count(*) 
FROM
 V_COVERAGECATEGORY_LOOKUP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      4.12       8.81         16       4479          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.07       0.06          0         44          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      4.19       8.87         16       4523          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 96  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=44 pr=0 pw=0 time=61164 us)
     82   VIEW  V_COVERAGECATEGORY_LOOKUP (cr=44 pr=0 pw=0 time=61168 us)
     82    SORT UNIQUE (cr=44 pr=0 pw=0 time=61002 us)
    113     UNION-ALL  (cr=44 pr=0 pw=0 time=25810 us)
     36      HASH JOIN  (cr=13 pr=0 pw=0 time=24774 us)
     36       HASH JOIN  (cr=7 pr=0 pw=0 time=17707 us)
      9        VIEW  index$_join$_020 (cr=6 pr=0 pw=0 time=9422 us)
      9         HASH JOIN  (cr=6 pr=0 pw=0 time=9409 us)
      9          INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_AK (cr=3 pr=0 pw=0 time=334 us)(object id 67913)
      9          INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_PK (cr=3 pr=0 pw=0 time=102 us)(object id 67912)
     36        INDEX FULL SCAN BENEFIT_DENTAL_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=191 us)(object id 67915)
     13       VIEW  index$_join$_018 (cr=6 pr=0 pw=0 time=3465 us)
     13        HASH JOIN  (cr=6 pr=0 pw=0 time=3461 us)
     13         INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_AK (cr=3 pr=0 pw=0 time=116 us)(object id 68423)
     13         INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_PK (cr=3 pr=0 pw=0 time=121 us)(object id 68422)
     13      INDEX FULL SCAN DENTAL_COV_CATEGORY_AK (cr=1 pr=0 pw=0 time=52 us)(object id 68423)
      8      HASH JOIN  (cr=13 pr=0 pw=0 time=17787 us)
      8       HASH JOIN  (cr=7 pr=0 pw=0 time=7453 us)
      8        INDEX FULL SCAN BENEFIT_VISION_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=57 us)(object id 67962)
      9        VIEW  index$_join$_024 (cr=6 pr=0 pw=0 time=4443 us)
      9         HASH JOIN  (cr=6 pr=0 pw=0 time=4431 us)
      9          INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_AK (cr=3 pr=0 pw=0 time=102 us)(object id 67960)
      9          INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_PK (cr=3 pr=0 pw=0 time=123 us)(object id 67959)
      9       VIEW  index$_join$_022 (cr=6 pr=0 pw=0 time=3118 us)
      9        HASH JOIN  (cr=6 pr=0 pw=0 time=3099 us)
      9         INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=96 us)(object id 69559)
      9         INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=91 us)(object id 69558)
      9      INDEX FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=51 us)(object id 69559)
     23      HASH JOIN  (cr=13 pr=0 pw=0 time=16248 us)
     23       HASH JOIN  (cr=7 pr=0 pw=0 time=8361 us)
     23        VIEW  index$_join$_028 (cr=6 pr=0 pw=0 time=4281 us)
     23         HASH JOIN  (cr=6 pr=0 pw=0 time=4232 us)
     23          INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_AK (cr=3 pr=0 pw=0 time=132 us)(object id 67942)
     23          INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_PK (cr=3 pr=0 pw=0 time=162 us)(object id 67941)
     23        INDEX FULL SCAN BENEFIT_HEALTH_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=102 us)(object id 67944)
     24       VIEW  index$_join$_026 (cr=6 pr=0 pw=0 time=4184 us)
     24        HASH JOIN  (cr=6 pr=0 pw=0 time=4156 us)
     24         INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=145 us)(object id 68919)
     24         INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=120 us)(object id 68918)
     24      INDEX FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=76 us)(object id 68919)
      0      NESTED LOOPS  (cr=1 pr=0 pw=0 time=37 us)
      0       NESTED LOOPS  (cr=1 pr=0 pw=0 time=32 us)
      0        INDEX FULL SCAN BENEFIT_DRUG_COV_CAT_ASSGN_PK (cr=1 pr=0 pw=0 time=28 us)(object id 67928)
      0        TABLE ACCESS BY INDEX ROWID BENEFIT_DRUG_COV_CAT (cr=0 pr=0 pw=0 time=0 us)
      0         INDEX UNIQUE SCAN BENEFIT_DRUG_COV_CAT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 67925)
      0       TABLE ACCESS BY INDEX ROWID DRUG_COVERAGE_CATEGORY (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX UNIQUE SCAN DRUG_COVERAGE_CATEGORY_PK (cr=0 pr=0 pw=0 time=0 us)(object id 68697)
      0      INDEX FULL SCAN DRUG_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=25 us)(object id 68698)


SELECT count(*) 
FROM
 V_COVERAGECATEGORY_LOOKUP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      3.90       3.84          4       4736          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.32       6.17          0         44          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.22      10.02          4       4780          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 96  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=44 pr=0 pw=0 time=6174866 us)
     82   VIEW  V_COVERAGECATEGORY_LOOKUP (cr=44 pr=0 pw=0 time=6174892 us)
     82    SORT UNIQUE (cr=44 pr=0 pw=0 time=6174725 us)
    113     UNION-ALL  (cr=44 pr=0 pw=0 time=4382005 us)
     36      HASH JOIN  (cr=13 pr=0 pw=0 time=4380813 us)
     36       HASH JOIN  (cr=7 pr=0 pw=0 time=4042890 us)
      9        VIEW  index$_join$_020 (cr=6 pr=0 pw=0 time=2462039 us)
      9         HASH JOIN  (cr=6 pr=0 pw=0 time=2462009 us)
      9          INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_AK (cr=3 pr=0 pw=0 time=39845 us)(object id 67913)
      9          INDEX FAST FULL SCAN BENEFIT_DENTAL_COV_CAT_PK (cr=3 pr=0 pw=0 time=166 us)(object id 67912)
     36        INDEX FULL SCAN BENEFIT_DENTAL_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=315 us)(object id 67915)
     13       VIEW  index$_join$_018 (cr=6 pr=0 pw=0 time=122429 us)
     13        HASH JOIN  (cr=6 pr=0 pw=0 time=122412 us)
     13         INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_AK (cr=3 pr=0 pw=0 time=132 us)(object id 68423)
     13         INDEX FAST FULL SCAN DENTAL_COV_CATEGORY_PK (cr=3 pr=0 pw=0 time=130 us)(object id 68422)
     13      INDEX FULL SCAN DENTAL_COV_CATEGORY_AK (cr=1 pr=0 pw=0 time=63 us)(object id 68423)
      8      HASH JOIN  (cr=13 pr=0 pw=0 time=1703452 us)
      8       HASH JOIN  (cr=7 pr=0 pw=0 time=266876 us)
      8        INDEX FULL SCAN BENEFIT_VISION_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=65 us)(object id 67962)
      9        VIEW  index$_join$_024 (cr=6 pr=0 pw=0 time=236323 us)
      9         HASH JOIN  (cr=6 pr=0 pw=0 time=236311 us)
      9          INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_AK (cr=3 pr=0 pw=0 time=109 us)(object id 67960)
      9          INDEX FAST FULL SCAN BENEFIT_VISION_COV_CAT_PK (cr=3 pr=0 pw=0 time=165 us)(object id 67959)
      9       VIEW  index$_join$_022 (cr=6 pr=0 pw=0 time=1295 us)
      9        HASH JOIN  (cr=6 pr=0 pw=0 time=1275 us)
      9         INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=132 us)(object id 69559)
      9         INDEX FAST FULL SCAN VISION_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=86 us)(object id 69558)
      9      INDEX FULL SCAN VISION_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=53 us)(object id 69559)
     23      HASH JOIN  (cr=13 pr=0 pw=0 time=43648 us)
     23       HASH JOIN  (cr=7 pr=0 pw=0 time=40697 us)
     23        VIEW  index$_join$_028 (cr=6 pr=0 pw=0 time=1695 us)
     23         HASH JOIN  (cr=6 pr=0 pw=0 time=1647 us)
     23          INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_AK (cr=3 pr=0 pw=0 time=128 us)(object id 67942)
     23          INDEX FAST FULL SCAN BENEFIT_HEALTH_COV_CAT_PK (cr=3 pr=0 pw=0 time=156 us)(object id 67941)
     23        INDEX FULL SCAN BENEFIT_HEALTH_COV_CAT_ASSG_PK (cr=1 pr=0 pw=0 time=132 us)(object id 67944)
     24       VIEW  index$_join$_026 (cr=6 pr=0 pw=0 time=1714 us)
     24        HASH JOIN  (cr=6 pr=0 pw=0 time=1686 us)
     24         INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=3 pr=0 pw=0 time=166 us)(object id 68919)
     24         INDEX FAST FULL SCAN HEALTH_COVERAGE_CATEGORY_PK (cr=3 pr=0 pw=0 time=117 us)(object id 68918)
     24      INDEX FULL SCAN HEALTH_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=75 us)(object id 68919)
      0      NESTED LOOPS  (cr=1 pr=0 pw=0 time=39 us)
      0       NESTED LOOPS  (cr=1 pr=0 pw=0 time=34 us)
      0        INDEX FULL SCAN BENEFIT_DRUG_COV_CAT_ASSGN_PK (cr=1 pr=0 pw=0 time=29 us)(object id 67928)
      0        TABLE ACCESS BY INDEX ROWID BENEFIT_DRUG_COV_CAT (cr=0 pr=0 pw=0 time=0 us)
      0         INDEX UNIQUE SCAN BENEFIT_DRUG_COV_CAT_PK (cr=0 pr=0 pw=0 time=0 us)(object id 67925)
      0       TABLE ACCESS BY INDEX ROWID DRUG_COVERAGE_CATEGORY (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX UNIQUE SCAN DRUG_COVERAGE_CATEGORY_PK (cr=0 pr=0 pw=0 time=0 us)(object id 68697)
      0      INDEX FULL SCAN DRUG_COVERAGE_CATEGORY_AK (cr=1 pr=0 pw=0 time=24 us)(object id 68698)
Тынц.
Дата: 04.12.2009 02:35:18
Долго разукрашивал :)
Вячеслав Любомудров
Дата: 04.12.2009 02:43:13
sar не настроен на ежечасный сбор статистики?
Мож тенденцию какую-то выловить можно
Ну и всякие prstat
Relic Hunter
[root@sunsol06 ~]# time date
Thu Dec 3 16:30:51 MST 2009

real 0m0.100s
user 0m0.002s
sys 0m0.120s
[root@sunsol06 ~]#
Чета какая-то странная погрешность. sys >> real
YAP
Дата: 04.12.2009 10:47:47
на тормознутом сервере не мог ли CPU упасть в частоте? Виртуализация не используется?
YAP
Дата: 04.12.2009 11:14:47
следом идея: соберите системную статистику на обоих серверах, дабы сравнить их в ораклиных попугаях.