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:17:20 +0200

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: