Results 1 to 14 of 14

Thread: Optimizer Hint

  1. #1
    Join Date
    Dec 2004
    Posts
    19

    Unanswered: Optimizer Hint

    hi,

    index on all columns of a@link1
    index on mobile of sada_mob


    UPDATE sada_mob SET (accno,subno,subnores,svr) = (SELECT
    account_no,subscr_no,subscr_no_resets,server_id FROM a@link1 eiem WHERE
    external_id=mobile AND external_id_type=11 AND
    active_date = (SELECT
    MAX(eiem2.active_date) FROM a@link1 eiem2 WHERE
    eiem2.external_id=eiem.external_id AND eiem2.external_id_type=11));


    How do I make the execution of this statement faster by aplying an optimizer hint. Thanks

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    As I recall from my Optimizer lessons, if you're using Cost Based Optimizer then there shouldn't be a need for a hint, as "CBO is more clever than RBO"; also, although you can influence the optimizer by using a hint the optimizer can choose to ignore the hint if it makes sense to do so. So you couls add a hint, but it still might not make a difference.

    That said, are you asking for the general syntax for a hint, or a specific hint for your query?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Dec 2004
    Posts
    19
    Thanks...I do have RBO, and I ma looking for specific hint for this update query as I use it frequently

    Quote Originally Posted by cis_groupie
    As I recall from my Optimizer lessons, if you're using Cost Based Optimizer then there shouldn't be a need for a hint, as "CBO is more clever than RBO"; also, although you can influence the optimizer by using a hint the optimizer can choose to ignore the hint if it makes sense to do so. So you couls add a hint, but it still might not make a difference.

    That said, are you asking for the general syntax for a hint, or a specific hint for your query?

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Just a thought, have you used 'explain plan' to see what indexes (if any) are being used at the moment?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Dec 2004
    Posts
    19
    Says "Execution plan not available for this statement"

    Quote Originally Posted by cis_groupie
    Just a thought, have you used 'explain plan' to see what indexes (if any) are being used at the moment?

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Has 'Explain Plan' been set up? (you need to run the script utlxplan.sql)?

    If you can't get Explain Plan to work you could always try Autotrace (It does basically the same, but executes the statement at the same time).

    Hmm, this started out as a 'quickie' to make sure that you weren't trying to repeat what Oracle was already doing, and it seems to be ever-so-slowly turning into a mini-saga! Oh, I just LOVE Oracle, don't you?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Oh, I just LOVE Oracle, don't you?
    I do too .

    BTW, cis_groupie, when you say :
    if you're using Cost Based Optimizer then there shouldn't be a need for a hint
    I'd say you're right with "shouldn't". Using 9i, with up-to-date stats, the CBO already made me crazy sometimes with some choices it was making ! Example with Oracle Text Indexes... Two plans had the same total cost, but it chose to use the one with the index on the date field instead of the one with the Text Index (CONTAINS query with a condition on date field)... which resulted in a MUCH slower query... And I still wonder how I got the correct result without accessing the Text Index (it didn't appear at all in the execution plan). The only way I found to make it use the "right" plan is to add a hint (which isn't that good as the query is in a client app, so I'd better not change the table or index name ). I hope the CBO is really cleverer in 10g...

    Regards,

    RBARAER

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK just taking a guess until you get an execution plan but a composite index on a(external_id , external_id_type, active_date) will help you lot if you dont have it already. Make sure you have upto date stats (dont forget analyze has lots of options) if your using the CBO. A hint which might help you is DRIVING_SITE (check spelling as I dont have access to the sql manual) as it tells the query where to execute.

    Alan

  9. #9
    Join Date
    Dec 2004
    Posts
    19
    hi,
    Explain plan works for a select query from the same database but fails for the mentioned update query.table a cannot be modified by me as it required DBA Rights.on table seperate indexes are created on all columns
    and I cannot create any index(Only DBA can do so and in this particular team they wont...Please assume that only sada_mob is fully in my control)

    I use the update query 20 times a day, and hence want to make the query execution faster as this would mean a lot of time saved.


    Thanks to all



    Quote Originally Posted by AlanP
    OK just taking a guess until you get an execution plan but a composite index on a(external_id , external_id_type, active_date) will help you lot if you dont have it already. Make sure you have upto date stats (dont forget analyze has lots of options) if your using the CBO. A hint which might help you is DRIVING_SITE (check spelling as I dont have access to the sql manual) as it tells the query where to execute.

    Alan

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Did you try the DRIVING_SITE hint as it might help.

    As for your DBAs, what are they there for?
    Last edited by AlanP; 12-10-04 at 11:41.

  11. #11
    Join Date
    Dec 2004
    Posts
    19
    hi,

    good to know about DRIVING_SITE hint. Tried it. Doesnt work in this particulat case

    Quote Originally Posted by AlanP
    Did you try the DRIVING_SITE hint as it might help.

    As for you DBAs, what are they there for?

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137

    Question

    OK if you cant get the index you require then how about deleteing all records from sada_mob and then inserting them using a select from table@@link1 in one transaction (so selects will not show an empty table). This may be more efficient as you seem to be updating all the records in sada_mob.

    Alan

  13. #13
    Join Date
    Dec 2004
    Posts
    19
    Hi Alan,
    Firstly thanks for paying attention to my requirement. In this case a select wont work that way, because mobile numbers have to be inserted into sada_mob with every requirement

    If user a wants me to gine him subscriber address, I first insert the mobile numbers into sada_mob and then update with table a@link1

    Sada_mob contains 100 records on an average
    table a@link1 contains the entire base (10 lakh records)


    Quote Originally Posted by AlanP
    OK if you cant get the index you require then how about deleteing all records from sada_mob and then inserting them using a select from table@@link1 in one transaction (so selects will not show an empty table). This may be more efficient as you seem to be updating all the records in sada_mob.

    Alan

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK attacking it another way could you create sada_mob on the link1 database and run the update on the other database? The only other thing which will improve performance is have the composite index I suggested earlier.

    Alan

Posting Permissions

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