Performance issue on 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Performance issue on 9i

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    94

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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...
    this space intentionally left blank

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width