Home » RDBMS Server » Server Utilities » SQL optimizer
SQL optimizer [message #188032] Wed, 16 August 2006 17:33 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
does anyone know of a good SQL optimizer utility?

Thanks,
Stan
Re: SQL optimizer [message #188034 is a reply to message #188032] Wed, 16 August 2006 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What sort of mythical s/w are you expecting to find?
Some magical utility that accepts a file containing "BAD" SQL & it spits out FAST SQL automagically?
When you find a solution, please post the results back here.
Re: SQL optimizer [message #188035 is a reply to message #188032] Wed, 16 August 2006 18:26 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
The sarcasm in your reply is completely uncalled for! What a jerk!
I guess Oracle deploys mythical software….I won’t download the Oracle SQL Developer because I thought it would magically write all of the code for me.

I’m just looking for a tool that would help aid in the development process while learning how to code efficient SQL.

Thanks,
Stan
Re: SQL optimizer [message #188036 is a reply to message #188032] Wed, 16 August 2006 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
First make it work.
Then make it fast(er).
If you can NOT get the correct answer, it is a waste of time worrying about getting the answer in less time.
Re: SQL optimizer [message #188037 is a reply to message #188032] Wed, 16 August 2006 18:42 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
1) I'm getting the correct answer! So don't assume I'm not.
2) Trying to make if faster, hence the request for an optimizer!
IE: I have a query that runs in about 20 seconds and by using the hint /*+ ordered */ it now runs in 1 second. Just looking to further optimize my code.
Re: SQL optimizer [message #188039 is a reply to message #188032] Wed, 16 August 2006 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you are serious about learning a rigorous methodology for SQL tuning, Dan Tow's book titled "SQL Tuning".
For a Q&D approach that works in many cases use SQL_TRACE & TKPROF.
Re: SQL optimizer [message #188042 is a reply to message #188032] Wed, 16 August 2006 19:29 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks for the information.

Stan
Re: SQL optimizer [message #188065 is a reply to message #188042] Wed, 16 August 2006 21:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Stan,

For a free resource (not software, but a tuning methodology), see my website.

Ross Leishman
Re: SQL optimizer [message #188204 is a reply to message #188032] Thu, 17 August 2006 07:42 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thank you very much.

Stan
Re: SQL optimizer [message #188565 is a reply to message #188037] Sat, 19 August 2006 15:12 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
What version of Oracle are you using? Do you use dbms_stats at frequent intervals, so that the cost-based optimizer (CBO) has current statistics to use for selecting the optimum execution plan? With a few exceptions, hints are unnecessary. If you have current statistics, Oracle should be able to choose the best plan on its own. Providing hints is kind of like manually shifting gears in a car with automatic transmission. However, if you are using an older version with outdated statistics, then you may need those hints.
Previous Topic: sqlldr how and where to execute
Next Topic: Pound symbols in external table definition
Goto Forum:
  


Current Time: Sat Dec 10 09:03:34 CST 2016

Total time taken to generate the page: 0.11851 seconds