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

Thread: tune the sql

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

    tune the sql

    Hi Friends,
    Can you please help me in tuning the below sql..
    I tired lot of option but no sucess.
    I am runing oracle 8.1.7.4, sun solars 5.6

    SELECT COUNT(*) FROM LISTINGS_NEW WHERE STATUS = 'A' and IMAGE_ID > 0 and END_TIME > sysdate AND
    user_ID IN ( SELECT user_ID FROM USERS WHERE STATUS = 'A' )

    here user_id is indexed

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    First, re-write the statement
    Code:
    SELECT
       COUNT(*)
    FROM
       USERS          U ,
       LISTINGS_NEW   LN
    WHERE
       U.STATUS    =   'A'         AND
       LN.USER_ID  =   U.USER_ID   AND
       LN.STATUS   =   U.STATUS    AND
       LN.STATUS   =   'A'         AND
       LN.IMAGE_ID >   0           ND
       LN.END_TIME >   SYSDATE
    Then, notice that LN is restricted by 4 fields. An index on one is not going to be as fast as an index on all 4. Since 2 are single restrictions, the fastest index would likeley be USER_ID, STATUS, IMAGE_ID, END_TIME, but that's a guess.

    Anyway, always avoid sub-selects when you can, join everything that can be joined and restrict everything that can be restricted. These simple rules will solve most of your performance issues. Follow these by indexing properly and you've got 80% covered.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Try posying the exaplian plan at least

    Give this a try

    SELECT COUNT(*)
    FROM LISTINGS_NEW ln ,users u
    WHERE STATUS = 'A' and
    IMAGE_ID > 0 and
    END_TIME > sysdate AND
    ln.user_id=u.user_id

    regards
    Hrishy

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Chris

    :-)..we posted the solution with almost the same table aliases ;-)

    regards
    Hrishy

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Random thoughts:

    If user_id is the PK of the table users, then I think you could write it as a simple inner join:

    ... FROM listings_new l, users u
    WHERE l.user_id=u.user_id
    AND . . . . . . .

    alternatively recode the IN as an EXISTS (often faster).

    Is "END_TIME > sysdate" very selective, if so an index on this field might help a lot.

    (I'm assuming STATUS='A' is not very selective - i.e. most users are active?)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Oh, I should also probably mention things like dats, range comparisons and histograms. Generally, ranged queries operate best on data that is evenly distributed. That is how the optimizer guesses the data is distributed. Now, to do this on a range query, it must have a real value. Right now, it is looking at that SYSDATE call and seeing it as a bind variable. That means it sees WHERE END_DT > :B1. Not knowing the value, it can't even guess at what percentage of records might come back so it guesses 50% and obviously doesn't then use an index.

    Now, if you provided the actual value, the optimizer would have a better shot. Of course, you would now have more than one copy in the statement cache, but that's the trade-off. With an actual value, it would say: Hmmm, where does that value fall between the minimum and maximum values in that field? Now, here's the usual problems with dates:

    1 - The data is usually not clean. All that has to happen is some legacy record has a bad data from last century in it that can't be fixed and your distribution is screwed.
    2 - The data is always ahead of the stats. You are always getting new dates on the leading edge ahead of what the optimizer last recorded. With other fields, any new data is usually peppered throughout existing ranges - new statuses are not created every day, for example. But new dates *are*.
    3 - Dates are rarely evenly distributed. Some years are better than others. Same with Months. There are usually only a few old legacy records and probably a few *bad* records. This means the optimizer will always be wrong with it's calculated distribution.

    So, the answer with dates that are range-selected is usually to hard-code the ranges, add histograms to the fields, and re-compute the stats often.

    Again, this has inherent overhead in the stats-processing, copies of records in the SGA, etc. But if the field is used a lot this can make a lot of difference.

    Anyway, just things to think about,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Friends,
    I justed wanted to tell u one thing that values in status column in USERS table in not the same in status column in listings_new table.

    The explain plan is as below

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6491 Card=1 Bytes=37
    )

    1 0 SORT (AGGREGATE)
    2 1 NESTED LOOPS (Cost=6491 Card=24126 Bytes=892662)
    3 2 REMOTE* (Cost=25 Card=53 Bytes=795) LINK_D
    B

    4 2 TABLE ACCESS (BY INDEX ROWID) OF LIST_NEW
    ' (Cost=122 Card=45520 Bytes=1001440)

    5 4 INDEX (RANGE SCAN) OF 'LIST_NEW' (NON
    -UNIQUE) (Cost=10 Card=45520)



    3 SERIAL_FROM_REMOTE SELECT "USER_ID","STATUS" FROM "U
    SERS" "USERS" WHERE "STATUS"
    anandkl

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by DaPi
    alternatively recode the IN as an EXISTS (often faster).
    Actually, EXISTS is faster when you are looking in a table that has a M:n relationship with your base table.

    SELECT * FROM B1 WHERE EXISTS (...L2) works best with B1 n:M L2

    In this case, we are starting with the LN table and looking to the U table. Their relationship is probably LN M:1 U, so an EXISTS would, in all likelihood, not be any better.

    Just an FYI,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by chrisrlong
    Code:
       U.STATUS    =   'A'         AND
       LN.STATUS   =   U.STATUS    AND
       LN.STATUS   =   'A'         AND
    Hey Chris, are you preparing for non-Euclidean SQL? (Euclid, Book 1, Chapter 1, Page 1: "Things equal to the same thing are equal to each other").
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Ahhh, so both tables are remote? If they are on the same remote link, then add a DRIVING_SITE(LN) hint

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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