DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Query optimization

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    Query optimization

    Hi,

    I am looking at some one to review this QEP as this is taking more than 40 seconds to run this query.
    Query :
    Tables used : AGGR_PL_FISCAL_PERIOD = 8million rows
    LU_ACCOUNT = 4million rows
    LU_ACCOUNT = 1Million rows


    select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
    sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
    from AGGR_PL_FISCAL_PERIOD a11,
    LU_ACCOUNT a12,
    LU_ACCOUNT_LOD6 a13
    where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
    a12.LOD6_ID = a13.LOD6_ID
    and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
    and a11.LEDGER_TYPE_ID in (1877)
    and a11.TIME_PERIOD_IND_ID in (1)
    and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '65100'
    and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
    and a12.COMPANY_ID in (3023))))
    group by a11.TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID
    /
    Will post the trace file in the next thread
    There is always a better way to do the things.

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    TKPROF: Release 9.2.0.4.0 - Production on Mon Jun 14 08:27:14 2004

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Trace file: biasia_ora_10975.trc
    Sort options: default

    ********************************************************************************
    count = number of times OCI procedure was executed
    cpu = cpu time in seconds executing
    elapsed = elapsed time in seconds executing
    disk = number of physical reads of buffers from disk
    query = number of buffers gotten for consistent read
    current = number of buffers gotten in current mode (usually for update)
    rows = number of rows processed by the fetch or execute call
    ********************************************************************************

    Error in CREATE TABLE of EXPLAIN PLAN table: BIUSER.prof$plan_table
    ORA-00955: name is already used by an existing object

    parse error offset: 20
    EXPLAIN PLAN option disabled.
    ********************************************************************************

    alter session set events '10046 trace name context forever , level 12'


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1 0.00 0.00 0 0 0 0

    Misses in library cache during parse: 0
    Misses in library cache during execute: 1
    Optimizer goal: CHOOSE
    Parsing user id: 62

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 1 0.00 0.00
    SQL*Net message from client 1 17.56 17.56
    ********************************************************************************

    alter session set events '10053 trace name context forever, level 1'


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 0.00 0.00 0 0 0 0

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 62

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 1 0.00 0.00
    SQL*Net message from client 1 4.44 4.44
    ********************************************************************************

    select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
    sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
    from AGGR_PL_FISCAL_PERIOD a11,
    LU_ACCOUNT a12,
    LU_ACCOUNT_LOD6 a13
    where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
    a12.LOD6_ID = a13.LOD6_ID
    and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
    and a11.LEDGER_TYPE_ID in (1877)
    and a11.TIME_PERIOD_IND_ID in (1)
    and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '63900'
    and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
    and a12.COMPANY_ID in (3023))))
    group by a11.TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.12 0.13 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 14.60 15.24 0 1340441 0 7
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 14.72 15.38 0 1340441 0 7

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 62

    Rows Row Source Operation
    ------- ---------------------------------------------------
    7 SORT GROUP BY (cr=1340441 r=0 w=0 time=15247163 us)
    357 HASH JOIN (cr=1340441 r=0 w=0 time=15244187 us)
    14893 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT_LOD6 (cr=3070 r=0 w=0 time=82395 us)
    14893 INDEX RANGE SCAN LOD6_OBJ_IDX_01 (cr=49 r=0 w=0 time=12499 us)(object id 42908)
    13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15115938 us)
    391755 TABLE ACCESS BY INDEX ROWID AGGR_PL_FISCAL_PERIOD (cr=162104 r=0 w=0 time=2573807 us)
    391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=218600 us)
    190 BITMAP INDEX RANGE SCAN AGGR_PL_FISCAL_INDX1 (cr=538 r=0 w=0 time=13603 us)(object id 49228)
    13293 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT (cr=1175267 r=0 w=0 time=11337688 us)
    391755 INDEX UNIQUE SCAN SYS_C0014576 (cr=783512 r=0 w=0 time=3733693 us)(object id 42896)
    There is always a better way to do the things.

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    SQL*Net message from client 2 4.33 4.41
    ********************************************************************************

    select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
    sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
    from AGGR_PL_FISCAL_PERIOD a11,
    LU_ACCOUNT a12,
    LU_ACCOUNT_LOD6 a13
    where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
    a12.LOD6_ID = a13.LOD6_ID
    and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
    and a11.LEDGER_TYPE_ID in (1877)
    and a11.TIME_PERIOD_IND_ID in (1)
    and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '64100'
    and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
    and a12.COMPANY_ID in (3023))))
    group by a11.TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.14 0.15 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 15.03 30.72 1113 1340462 0 7
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 15.17 30.87 1113 1340462 0 7

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 62

    Rows Row Source Operation
    ------- ---------------------------------------------------
    7 SORT GROUP BY (cr=1340462 r=1113 w=0 time=30722527 us)
    616 HASH JOIN (cr=1340462 r=1113 w=0 time=30718502 us)
    14924 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT_LOD6 (cr=3091 r=1113 w=0 time=15486382 us)
    14924 INDEX RANGE SCAN LOD6_OBJ_IDX_01 (cr=48 r=45 w=0 time=429005 us)(object id 42908)
    13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15168234 us)
    391755 TABLE ACCESS BY INDEX ROWID AGGR_PL_FISCAL_PERIOD (cr=162104 r=0 w=0 time=2611024 us)
    391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=217191 us)
    190 BITMAP INDEX RANGE SCAN AGGR_PL_FISCAL_INDX1 (cr=538 r=0 w=0 time=14299 us)(object id 49228)
    13293 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT (cr=1175267 r=0 w=0 time=11406918 us)
    391755 INDEX UNIQUE SCAN SYS_C0014576 (cr=783512 r=0 w=0 time=3752599 us)(object id 42896)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    db file sequential read 1113 0.72 15.22
    SQL*Net message from client 2 21.26 21.34
    ********************************************************************************

    select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
    sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
    from AGGR_PL_FISCAL_PERIOD a11,
    LU_ACCOUNT a12,
    LU_ACCOUNT_LOD6 a13
    where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
    a12.LOD6_ID = a13.LOD6_ID
    and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
    and a11.LEDGER_TYPE_ID in (1877)
    and a11.TIME_PERIOD_IND_ID in (1)
    and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '65100'
    and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
    and a12.COMPANY_ID in (3023))
    or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
    and a12.COMPANY_ID in (3023))))
    group by a11.TIME_PERIOD_IND_ID,
    a11.LEDGER_TYPE_ID,
    a11.FISCAL_PERIOD_ID

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.13 0.13 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 15.58 25.78 1887 1340653 0 7
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 15.71 25.91 1887 1340653 0 7

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 62

    Rows Row Source Operation
    ------- ---------------------------------------------------
    7 SORT GROUP BY (cr=1340653 r=1887 w=0 time=25782608 us)
    161 HASH JOIN (cr=1340653 r=1887 w=0 time=25780659 us)
    23049 TABLE ACCESS BY INDEX ROWID OBJ#(42907) (cr=3282 r=1887 w=0 time=9997512 us)
    23049 INDEX RANGE SCAN OBJ#(42908) (cr=74 r=71 w=0 time=522124 us)(object id 42908)
    13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15700208 us)
    391755 TABLE ACCESS BY INDEX ROWID OBJ#(42832) (cr=162104 r=0 w=0 time=2660936 us)
    391755 BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=266123 us)
    190 BITMAP INDEX RANGE SCAN OBJ#(49228) (cr=538 r=0 w=0 time=14247 us)(object id 49228)
    13293 TABLE ACCESS BY INDEX ROWID OBJ#(42893) (cr=1175267 r=0 w=0 time=11861617 us)
    391755 INDEX UNIQUE SCAN OBJ#(42896) (cr=783512 r=0 w=0 time=3884812 us)(object id 42896)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    db file sequential read 1887 0.19 9.60
    SQL*Net message from client 2 4.39 4.46



    ********************************************************************************

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 4 0.39 0.42 0 0 0 0
    Execute 5 0.00 0.00 0 0 0 0
    Fetch 6 45.21 71.75 3000 4021556 0 21
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 15 45.60 72.18 3000 4021556 0 21

    Misses in library cache during parse: 4
    Misses in library cache during execute: 1

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 8 0.00 0.00
    SQL*Net message from client 8 21.26 52.23
    db file sequential read 3000 0.72 24.83
    log file sync 1 0.01 0.01



    Trace file: biasia_ora_10975.trc
    Trace file compatibility: 9.00.01
    Sort options: default

    1 session in tracefile.
    5 user SQL statements in trace file.
    0 internal SQL statements in trace file.
    5 SQL statements in trace file.
    5 unique SQL statements in trace file.
    13241 lines in trace file.
    There is always a better way to do the things.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I would look at reducing your LIOs.
    Jeff Hunter

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    I would look at reducing your LIOs.
    Those LIO's are probably the result of the hash join ... v. LIO-intensive, hash joins.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Thanks, Marist,

    Right now it's using the bitmap indexes. Is that causing the increase LIO.

    There are no full table scans in the explain plan and all i seee is the index lookups.

    Pl Suggest

    Badrinath
    There is always a better way to do the things.

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    Thanks, Marist,

    Right now it's using the bitmap indexes. Is that causing the increase LIO.

    There are no full table scans in the explain plan and all i seee is the index lookups.

    Pl Suggest

    Badrinath
    There is always a better way to do the things.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about a little parallelism?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    How about a little parallelism?
    for 40 seconds worth of query?
    Jeff Hunter

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    40 seconds is in the eye of the beholder ... on some systems it's nothing, on some it's too much. If there's spare capacity on the box that's not being used, then use it to reduce the 40 seconds to, say, 8.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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