Need help in tuning the SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: Need help in tuning the SQL

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Need help in tuning the SQL

    Hi Friends,
    We are using oracle 8.1.7, solaris 2.8.

    can u please help me in tuning this SQL.

    INDEX is present on both date_added and ad_id column

    select count(distinct l.com_id),l.date_added, a.source
    from tabs a,lis l where
    (to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
    to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002') and
    l.ad_id = a.ad_id
    group by l.date_added,a.source

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=279116 Card=32505 By
    tes=942645)

    1 0 SORT* (GROUP BY) (Cost=279116 Card=32505 Bytes=942645) :Q142035
    001

    2 1 NESTED LOOPS* (Cost=278935 Card=32505 Bytes=942645) :Q142035
    000

    3 2 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card=33 :Q142035
    515 Bytes=670300) 000

    4 2 TABLE ACCESS* (BY INDEX ROWID) OF 'TABS' (Cost=2 Card=74 :Q142035
    92600 Bytes=67433400) 000

    5 4 INDEX* (UNIQUE SCAN) OF 'PKS_KEYS' (UNIQUE) (Cost= :Q142035
    1 Card=7492600) 000



    1 PARALLEL_TO_SERIAL SELECT A1.C0,A1.C1,COUNT(DISTINCT A1.C2) FRO
    M :Q142035000 A1 GROUP BY A1.C0,A1.C

    2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
    X(A2 "PKS_KEYS") */ A1.C1 C0,A2."S

    3 PARALLEL_COMBINED_WITH_PARENT
    4 PARALLEL_COMBINED_WITH_PARENT
    5 PARALLEL_COMBINED_WITH_PARENT

    U can see there is a full table scan happening on LIS table


    regards
    anandkl
    Last edited by anandkl; 09-15-2003 at 07:33 AM.
    anandkl

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    The following statement is wrong because you compare
    strings and not dates:

    (to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
    to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002')

    A string like '01/DEC/2003' will be smaler than '31/JAN/2003', I guess it's not soemthing you realy want.


    Try the follwing statement and check if it's faster

    select count(distinct l.com_id),l.date_added, a.source
    from tabs a,lis l
    where
    l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
    l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
    l.ad_id = a.ad_id
    group by l.date_added,a.source
    Last edited by mike9; 09-15-2003 at 08:56 AM.

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Mike,
    Its still doing FULL TABLESCAN


    select count(distinct l.com_id),l.date_added, a.source
    from tabs a,lis l
    where
    l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
    l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
    l.ad_id = a.ad_id
    group by l.date_added,a.source 2 3 4 5 6 7
    8 /

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=602938 Card=5108360
    Bytes=148142440)

    1 0 SORT* (GROUP BY) (Cost=602938 Card=5108360 Bytes=148142440 :Q142066
    ) 003

    2 1 MERGE JOIN* (Cost=559467 Card=5108360 Bytes=148142440) :Q142066
    002

    3 2 SORT* (JOIN) (Cost=234139 Card=5267012 Bytes=105340240 :Q142066
    ) 002

    4 3 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card= :Q142066
    5267012 Bytes=105340240) 001

    5 2 SORT* (JOIN) (Cost=325328 Card=7492600 Bytes=67433400) :Q142066
    002

    6 5 TABLE ACCESS* (FULL) OF 'TABS' (Cost=305557 Card=74926 :Q142066
    00 Bytes=67433400) 000



    1 PARALLEL_TO_SERIAL SELECT A1.C0,A1.C1,COUNT(DISTINCT A1.C2) FRO
    M :Q142066002 A1 GROUP BY A1.C0,A1.C

    2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
    / A1.C2 C0,A2.C1 C1,A1.C1 C2 FROM :Q

    3 PARALLEL_COMBINED_WITH_PARENT
    4 PARALLEL_TO_PARALLEL SELECT /*+ Q142066001 NO_EXPAND ROWID(A1) */
    A1."AD_ID" C0,A1."COMP_ID" C1,A1

    5 PARALLEL_COMBINED_WITH_PARENT
    6 PARALLEL_FROM_SERIAL
    anandkl

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    What is the % of recs being selected?.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Its close to 5 - 6%

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    Which % of rows of the table lis satisfay the follwoing condition:

    l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
    l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and

    Is it only 5-6% ?

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Yes..it 5 - 6%

    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    wht type of inx is it?

    wat is the card of the cols indexed?

    was the no of recs in the table 'lis'

    last thing that you may like to do is reorg table/index & analyze
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's not the % of rows that matters, it's the % of blocks that they represent.

    What is the clustering factor of the index on date_added?

    How many blocks are there in the LIS table below the high water mark?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    Its a normal bit map index, the total no of recoreds in lis is
    13368818 records

    I have analyzed the tables, as well as the index

    regards
    anandkl
    anandkl

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