Performance issue on 9i

Sun solaris 10, Oracle 9208

8 CPU,48GB RAM

Datafiles on RAID 10,Temp file on striped mount point

Parallel Query enabled

SELECT /*+ CHOOSE */
*
FROM INT_DIVPAY ORD,
INT_DIVPAY ORD2
WHERE ORD.ORDUID = ORD2.ORDUID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.06 0.97 0 10 0 0
Fetch 41539 33.22 1067.85 0 0 0 623071
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41541 33.28 1068.83 0 10 0 623071

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 92 (****)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: CHOOSE
0 HASH JOIN [:Q976002]
SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A1.C0,A2.C0,A1.C1,
A1.C2,A1.C3,A1.C4,A1.C5,A1.C6,A1.C7,A1.C8,A1.C9,A1.C10,A1.C11,
A1.C12,A1.C13,A1.C14,A1.C15,A1.C16,A1.C17,A1.C18,A1.C19,A1.C20,
A1.C21,A1.C22,A1.C23,A1.C24,A1.C25,A1.C26,A1.C27,A1.C28,A1.C29,
A1.C30,A1.C31,A1.C32,A1.C33,A1.C34,A1.C35,A1.C36,A1.C37,A1.C38,
A1.C39,A1.C40,A1.C41,A1.C42,A1.C43,A1.C44,A1.C45,A1.C46,A1.C47,
A1.C48,A1.C49,A1.C50,A1.C51,A1.C52,A1.C53,A1.C54,A1.C55,A1.C56,
A1.C57,A1.C58,A1.C59,A1.C60,A1.C61,A1.C62,A2.C1,A2.C2,A2.C3,
A2.C4,A2.C5,A2.C6,A2.C7,A2.C8,A2.C9,A2.C10,A2.C11,A2.C12,
A2.C13,A2.C14,A2.C15,A2.C16,A2.C17,A2.C18,A2.C19,A2.C20,A2.C21,
A2.C22,A2.C23,A2.C24,A2.C25,A2.C26,A2.C27,A2.C28,A2.C29,A2.C30,
A2.C31,A2.C32,A2.C33,A2.C34,A2.C35,A2.C36,A2.C37,A2.C38,A2.C39,
A2.C40,A2.C41,A2.C42,A2.C43,A2.C44,A2.C45,A2.C46,A2.C47,A2.C48,
A2.C49,A2.C50,A2.C51,A2.C52,A2.C53,A2.C54,A2.C55,A2.C56,A2.C57,
A2.C58,A2.C59,A2.C60,A2.C61,A2.C62 FROM :Q976000 A1,:Q976001
A2 WHERE A1.C0=A2.C0
0 TABLE ACCESS (FULL) OF 'INT_DIVPAY' [:Q976000]
SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ORDUID" C0,A1."TRSTCODE"
C1,A1."UNITTYPE" C2,A1."INVCODE" C3,A1."EXDATE" C4,A1."SEQNO"
C5,A1."GROUP1" C6,A1."GROUP2" C7,A1."TAXCRED" C8,A1."NETALL"
C9,A1."EQUAL" C10,A1."TRANTYP1" C11,A1."TRANTYP2" C12,
A1."TRANTYP3" C13,A1."REINV" C14,A1."POSTDT" C15,A1."PRTDT"
C16,A1."DUPPDT" C17,A1."DUSER" C18,A1."WARRNO" C19,
A1."WARRCASH" C20,A1."RTYPE" C21,A1."SAVPEP" C22,A1."MPNAME"
C23,A1."MADL1" C24,A1."MADL2" C25,A1."MADL3" C26,A1."MADL4"
C27,A1."MADL5" C28,A1."MPCODE" C29,A1."ETAXCRED" C30,
A1."ENETALL" C31,A1."EEQUAL" C32,A1."FTAXCRED" C33,
A1."FNETALL" C34,A1."FEQUAL" C35,A1."ITAXCRED" C36,
A1."INETALL" C37,A1."IEQUAL" C38,A1."NTAXCRED" C39,
A1."NNETALL" C40,A1."NEQUAL" C41,A1."MANDAC" C42,A1."MACNAME"
C43,A1."MANDACX" C44,A1."NONRES" C45,A1."ENCASHDT" C46,
A1."STATNO" C47,A1."MANDATE" C48,A1."POSTCD" C49,A1."RLDISTS"
C50,A1."OSIND" C51,A1."GONEAWAY" C52,A1."DECEASED" C53,
A1."UNCLAIMED" C54,A1."BACS" C55,A1."CANC" C56,A1."CANCDET"
C57,A1."PRTCODE" C58,A1."OPRTCODE" C59,A1."DSEQNO" C60,
A1."CORPORATE_HOLD" C61,A1."PTRANUID" C62 FROM "INT_DIVPAY"
PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1
0 TABLE ACCESS (FULL) OF 'INT_DIVPAY' [:Q976001]
SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ORDUID" C0,A1."TRSTCODE"
C1,A1."UNITTYPE" C2,A1."INVCODE" C3,A1."EXDATE" C4,A1."SEQNO"
C5,A1."GROUP1" C6,A1."GROUP2" C7,A1."TAXCRED" C8,A1."NETALL"
C9,A1."EQUAL" C10,A1."TRANTYP1" C11,A1."TRANTYP2" C12,
A1."TRANTYP3" C13,A1."REINV" C14,A1."POSTDT" C15,A1."PRTDT"
C16,A1."DUPPDT" C17,A1."DUSER" C18,A1."WARRNO" C19,
A1."WARRCASH" C20,A1."RTYPE" C21,A1."SAVPEP" C22,A1."MPNAME"
C23,A1."MADL1" C24,A1."MADL2" C25,A1."MADL3" C26,A1."MADL4"
C27,A1."MADL5" C28,A1."MPCODE" C29,A1."ETAXCRED" C30,
A1."ENETALL" C31,A1."EEQUAL" C32,A1."FTAXCRED" C33,
A1."FNETALL" C34,A1."FEQUAL" C35,A1."ITAXCRED" C36,
A1."INETALL" C37,A1."IEQUAL" C38,A1."NTAXCRED" C39,
A1."NNETALL" C40,A1."NEQUAL" C41,A1."MANDAC" C42,A1."MACNAME"
C43,A1."MANDACX" C44,A1."NONRES" C45,A1."ENCASHDT" C46,
A1."STATNO" C47,A1."MANDATE" C48,A1."POSTCD" C49,A1."RLDISTS"
C50,A1."OSIND" C51,A1."GONEAWAY" C52,A1."DECEASED" C53,
A1."UNCLAIMED" C54,A1."BACS" C55,A1."CANC" C56,A1."CANCDET"
C57,A1."PRTCODE" C58,A1."OPRTCODE" C59,A1."DSEQNO" C60,
A1."CORPORATE_HOLD" C61,A1."PTRANUID" C62 FROM "INT_DIVPAY"
SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ORDUID" C0,A1."TRSTCODE"
C1,A1."UNITTYPE" C2,A1."INVCODE" C3,A1."EXDATE" C4,A1."SEQNO"
C5,A1."GROUP1" C6,A1."GROUP2" C7,A1."TAXCRED" C8,A1."NETALL"
C9,A1."EQUAL" C10,A1."TRANTYP1" C11,A1."TRANTYP2" C12,
A1."TRANTYP3" C13,A1."REINV" C14,A1."POSTDT" C15,A1."PRTDT"
C16,A1."DUPPDT" C17,A1."DUSER" C18,A1."WARRNO" C19,
A1."WARRCASH" C20,A1."RTYPE" C21,A1."SAVPEP" C22,A1."MPNAME"
C23,A1."MADL1" C24,A1."MADL2" C25,A1."MADL3" C26,A1."MADL4"
C27,A1."MADL5" C28,A1."MPCODE" C29,A1."ETAXCRED" C30,
A1."ENETALL" C31,A1."EEQUAL" C32,A1."FTAXCRED" C33,
A1."FNETALL" C34,A1."FEQUAL" C35,A1."ITAXCRED" C36,
A1."INETALL" C37,A1."IEQUAL" C38,A1."NTAXCRED" C39,
A1."NNETALL" C40,A1."NEQUAL" C41,A1."MANDAC" C42,A1."MACNAME"
C43,A1."MANDACX" C44,A1."NONRES" C45,A1."ENCASHDT" C46,
A1."STATNO" C47,A1."MANDATE" C48,A1."POSTCD" C49,A1."RLDISTS"
C50,A1."OSIND" C51,A1."GONEAWAY" C52,A1."DECEASED" C53,
A1."UNCLAIMED" C54,A1."BACS" C55,A1."CANC" C56,A1."CANCDET"
C57,A1."PRTCODE" C58,A1."OPRTCODE" C59,A1."DSEQNO" C60,
A1."CORPORATE_HOLD" C61,A1."PTRANUID" C62 FROM "INT_DIVPAY"
PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1
0 TABLE ACCESS (FULL) OF 'INT_DIVPAY' [:Q976001]
SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ORDUID" C0,A1."TRSTCODE"
C1,A1."UNITTYPE" C2,A1."INVCODE" C3,A1."EXDATE" C4,A1."SEQNO"
C5,A1."GROUP1" C6,A1."GROUP2" C7,A1."TAXCRED" C8,A1."NETALL"
C9,A1."EQUAL" C10,A1."TRANTYP1" C11,A1."TRANTYP2" C12,
A1."TRANTYP3" C13,A1."REINV" C14,A1."POSTDT" C15,A1."PRTDT"
C16,A1."DUPPDT" C17,A1."DUSER" C18,A1."WARRNO" C19,
A1."WARRCASH" C20,A1."RTYPE" C21,A1."SAVPEP" C22,A1."MPNAME"
C23,A1."MADL1" C24,A1."MADL2" C25,A1."MADL3" C26,A1."MADL4"
C27,A1."MADL5" C28,A1."MPCODE" C29,A1."ETAXCRED" C30,
A1."ENETALL" C31,A1."EEQUAL" C32,A1."FTAXCRED" C33,
A1."FNETALL" C34,A1."FEQUAL" C35,A1."ITAXCRED" C36,
A1."INETALL" C37,A1."IEQUAL" C38,A1."NTAXCRED" C39,
A1."NNETALL" C40,A1."NEQUAL" C41,A1."MANDAC" C42,A1."MACNAME"
C43,A1."MANDACX" C44,A1."NONRES" C45,A1."ENCASHDT" C46,
A1."STATNO" C47,A1."MANDATE" C48,A1."POSTCD" C49,A1."RLDISTS"
C50,A1."OSIND" C51,A1."GONEAWAY" C52,A1."DECEASED" C53,
A1."UNCLAIMED" C54,A1."BACS" C55,A1."CANC" C56,A1."CANCDET"
C57,A1."PRTCODE" C58,A1."OPRTCODE" C59,A1."DSEQNO" C60,
A1."CORPORATE_HOLD" C61,A1."PTRANUID" C62 FROM "INT_DIVPAY"
PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
process startup 16 0.04 0.67
PX Deq: Join ACK 7 0.00 0.00
PX Deq Credit: send blkd 18 0.00 0.00
PX Deq: Parse Reply 5 0.00 0.00
SQL*Net message to client 41539 0.00 0.04
SQL*Net more data to client 41539 0.00 2.32
PX Deq: Execute Reply 1396 1.96 1035.14
SQL*Net message from client 41538 0.57 716.80


We are getting hight waits on direct path write on the Temp tablespace.

The SAN stats show <1ms service time and IO is flying along.

iostat -xn shows that the device with the temp file on it is 98% busy but not waiting.

Are there any parameters that might help speed up the parallel processing?

We ran this on 10g/ASM and it took half the time with the same init.ora params so sould it be that 10g is better at parallel processing or ASM is better for IO than standard ufs filesystems?


Current init.ora

pga_agg_target 2gb
parallel_threads_per_cpu 2
sga_max 4gb



any ideas welcome

Have you thought about creating a new temp tablespace on the SAN and changing your schema to point to that tablespace? It is possible that one of the disks making up the temp tablespace striped volume is bad, offline, missing...

I set up a new filesystem on a stripe and added 5 smaller temp files rather than one big one.

I also added a huge pga_agg_target and its got a bit better but as each process only uses 5% of it, I've found its better to set the sort_area_size manually in the session and set workload to manual.

10g is way better with PGA than 9i