DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: tune the sql

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Chris, all you say is true. If the date is very selective, I'd index it and try a hint, rather than fight the stats to make them do what I want (Humpty Dumpty had something to say on that subject).
    "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

  2. #12
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Chris,
    Only the users table is on remote db and listings_new is on local db.

    regards
    anandkl
    anandkl

  3. #13
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi All,
    I tried the below sql,but i am still seeing high CPU utilization and cost


    SELECT COUNT(*) FROM GALLERY_LISTINGS_NEW GN,GALLERY_USERS GU
    WHERE (GN.COMPANY_ID=GU.COMPANY_ID AND GU.STATUS='A') AND
    GN.STATUS = 'A' and GN.PICTURE_URL_IMAGE_ID > 0
    and GN.SCHEDULED_END_TIME > sysdate

    regards
    anandkl
    anandkl

  4. #14
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by anandkl
    Hi Chris,
    Only the users table is on remote db and listings_new is on local db.

    regards
    anandkl
    Okay then, what you want to do is bring the user data over as quickly as possible. Your plan was very hard to read - please try to indent it properly next time. If I'm seeing this right - what you want to do is something like:
    Code:
    SELECT --+ ORDERED NO_PUSH_PRED(U) 
       COUNT(*)
    FROM
    	(
    	SELECT
    		USER_ID
    	FROM
    	   USERS       
    	WHERE
    	   STATUS    =   'A'
    	)					U	, 
       LISTINGS_NEW   LN
    WHERE
       LN.USER_ID  =   U.USER_ID   AND
       LN.STATUS   =   'A'         AND
       LN.IMAGE_ID >   0           AND
       LN.END_TIME >   SYSDATE
    Let me know if this changes the plan, I may have the wrong hint (always get confused with this set)

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

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I think his driving table is user rite? If that is the case his plan isnt that bad, I mean I am guessing he is doing a FTS in users which shouldnt be too big then from every row it returns Oracle probes listing_new using index scan which shouldnt be too bad... well I am guessing here OKE!

    Now if you tell us the number of rows in each table and the result of count(*).....




    I think the plan is

    Code:
    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_DB
    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)

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