Re: Reading sequences is much slower in AIX 6.1 than Linux
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Sep 2011 07:22:04 +0200
Forgot to say that I did the same test in 11.2.0.2 with AIX 6.1 but without
using sequences (instead I used ROWNUM) and it run in 29 seconds instead of
67, this is the 10046 trace:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 20.23 29.86 3121 66766 366488
1738992
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 20.23 29.86 3121 66766 366488
1738992
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=66817 pr=3121
pw=0 time=29902718 us)
1738992 1738992 1738992 COUNT (cr=27 pr=0 pw=0 time=9316827 us)
1738992 1738992 1738992 PX COORDINATOR (cr=27 pr=0 pw=0
time=6989293 us)
0 0 0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0
pw=0 time=0 us cost=5069 size=235257560 card=1729835)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us
cost=5069 size=235257560 card=1729835)
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=9504 card=2376)
0 0 0 PX SEND BROADCAST :TQ10000 (cr=0
pr=0 pw=0 time=0 us cost=7 size=9504 card=2376)
2376 2376 2376 TABLE ACCESS FULL T1_TMP (cr=22
pr=0 pw=0 time=1866 us cost=7 size=9504 card=2376)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0
time=0 us cost=5060 size=228338220 card=1729835)
0 0 0 TABLE ACCESS FULL
T_DESTINATION(cr=0 pr=0 pw=0 time=0 us cost=5060 size=228338220
card=1729835)
On Thu, Sep 29, 2011 at 7:17 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
