Oracle optimizer cost based?

pts.
Tags:
Oracle
Greetings, I am very new to Oracle and am having issues with Oracles cost based optimizer. To be honest from what I am seeing it is horrible. We are having to use way to many hints such a rule and index in order to get queries to run in a decent amount of time. I have updated the stats on all tables and indexes yet the optimizes frequently does not use indexes that would be ideal for the query. I am sure I have probably missed something due to my lack of experiance with oracle so any tips would be a great help. Things like config parameters, recommended ways to generate histagrams and other tips and tricks be great. TIA

Answer Wiki

Thanks. We'll let you know when a new response is added.

What tools are you using in your tuning ?

Are you using Explain Plans, tracing w/binds & waits ?

Ken

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bozzo999
    What tools are you using in your tuning ? Are you using Explain Plans, tracing w/binds & waits ? Ken
    0 pointsBadges:
    report
  • Hayden1
    mostly using explain plans.
    0 pointsBadges:
    report
  • AlanMuster
    Oracle has provided database-level parameters to aid developers to migrate existing applications from the old rule-based optimiser to the cost-based optimiser. The "optimizer_mode" can be set to first_rows or all_rows for example, to favour response time or throughput. "optimizer_index_cost_adj = 25" will adjust the optimizer to favor index access. There are a couple of other parameters also. These settings can make a major difference to chosen SQL optimizer execution plans. It is best not to use too many hints in your code, if you constantly needd to use them then I would question your indexing or database statistics. Good luck. Alan.
    0 pointsBadges:
    report
  • Hayden1
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-optimizer-cost-based/ (0) Comments Read [...]
    0 pointsBadges:
    report
  • Hayden1
    [...] Original Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-optimizer-cost-based/ [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following