Reading sequences is much slower in AIX 6.1 than Linux
Date: Thu, 29 Sep 2011 07:17:20 +0200
Message-ID: <CAJ2-Qb_YJOKxXAoCrPJWWMDWWX+MiAG6LkSBUFar6ThrSqiqRw_at_mail.gmail.com>
Hi all
We have some batch processes which need to meet some SLA, the execution time should exceed certaint threshold of time. The processes are mainly insert.... select sequence, ... from table.
In our development system which is Linux x86-64 running 11.2.0.2 the process runs in 36 second whereas in AIX 6.1 (our future production, not yet prouction though) runs in 67 seconds, in 10046 traces the only big differece is that in Linux it spends 13 seconds reading sequences and AIX 56 seconds. I have a SR open with support but they are totally lost, dont have a clue, they thought it was CPU speed but AIX is running in Power 6 4200 MHz and Linux runs on Intel 2600 MHz, I also did a CPU intensive operation on both servers and the result is similar so CPU speed is ruled out.
There is another difference between Linux and AIX as well, Linux is single instance and AIX RAC but I dont think this is a problem neither because when I did the test I only had one AIX node up. I also did same test in Oracle 10.2.0.3 and AIX 5.3 with two nodes RAC, less powerful machine and it only spended 22 seconds reading sequences.
this is linux 10046 trace:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0 Execute 1 16.79 35.88 9 72113 458980 1738992 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
total 2 16.80 35.89 9 72114 458980 1738992
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
0 0 0 LOAD TABLE CONVENTIONAL (cr=72590 pr=9 pw=0 time=35908674 us)
1738992 1738992 1738992 SEQUENCE SEQUENCE_S1 (cr=26 pr=0 pw=0 time=13383792 us)
1738992 1738992 1738992 PX COORDINATOR (cr=22 pr=0 pw=0 time=8581025 us)
0 0 0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=4863 size=249094944 card=1729826)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=4863 size=249094944 card=1729826)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=7 size=30888 card=2376)
0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=7 size=30888 card=2376)
2376 2376 2376 TABLE ACCESS FULL T1_TMP (cr=17 pr=0 pw=0 time=332 us cost=7 size=30888 card=2376)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=4853 size=226607206 card=1729826)
0 0 0 TABLE ACCESS FULL T_DESTINATION (cr=0 pr=0 pw=0 time=0 us cost=4853 size=226607206 card=1729826)
AIX 10046 trace:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 1 0 0 Execute 1 59.36 67.77 258 72974 460699 1738992 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
total 2 59.40 67.82 258 72975 460699 1738992
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
0 0 0 LOAD TABLE CONVENTIONAL (cr=76554 pr=258 pw=0 time=68364037 us)
1738992 1738992 1738992 SEQUENCE SEQUENCE_S1 (cr=3503 pr=1 pw=0 time=56762861 us)
1738992 1738992 1738992 PX COORDINATOR (cr=22 pr=1 pw=0 time=6525173 us)
0 0 0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=2432 size=250115760 card=1736915)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=2432 size=250115760 card=1736915)
0 0 0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=6 size=30888 card=2376)
0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=6 size=30888 card=2376)
2376 2376 2376 TABLE ACCESS FULL T1_TMP (cr=17 pr=0 pw=0 time=1890 us cost=6 size=30888 card=2376)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2425 size=227535865 card=1736915)
0 0 0 TABLE ACCESS FULL T_DESTINATION (cr=0 pr=0 pw=0 time=0 us cost=2425 size=227535865 card=1736915)
I attemped to truss the process in AIX but didnt see any significant calls or similar.
Anyone seen this sequence issue with this Oracle version and OS combination?
Thanks in advance
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 29 2011 - 00:17:20 CDT