SQL Tuning

[FredBear]

I am looking for reviews and opinions on using software for SQL tuning
oracle 9i release 2. Any URL's that compares several oracle 9i release
2 SQL tuning software programs are welcome.

In case there are no such reviews.

What are you're opinions about the following SQL tuning software
programs:

1. Quest Central SQL tuning option for oracle.

2. Lecco DB Expert for oracle.

3. Lecco SQL Expert for oracle.

4. Embarcadero SQL Tuner for oracle.

5. CA SQL-Station.

Which is the best and why?

Are these programs about to be extinct because of the (I assume
improved) CBO in Oracle 10i which will be finished late 2003?

Thanks

[Hans Forbrich]

Daniel Morgan wrote:
That said ... add BMC to your list then download the free copies of these

I suggest you also add Oracle's Tuning Pack to the list. It adds on to the
Oracle Enterprise Manager that's already included in your licenses.

(In total support to Daniel's comments - esp. about competent DBAs with those
tools + statspack)
/Hans

[FredBear]

On Sun, 29 Jun 2003 16:49:00 -0700, Daniel Morgan
<damorgan@exxesolutions.com> wrote:
Nothing tunes Oracle as well as a competent DBA or developer with EXPLAINPLAN, TKPROF, and DBMS_PROFILER.

I agree.
There is nothing these tools do that can't be done in SQL*Plus exceptempty your bank account.

Yes, still I do like the easy of these product that allow you to test
for instance 5 sql statements at the same time and then compare the
results next to each other. Try doing that with explain plan, tkprof
and dbms_profiler. Perhaps there is a freeware tool with similair
functionality.
Invest your money in education.

Yes, well this software will make it more easy to learn SQL tuning
also.
That said ... add BMC to your list then download the free copies of theseproducts and work with them yourself if you really want a GUI. Each hasits strengths and its weaknesses. Weigh any responses from others againstthe following that you didn't ask:1. Which of the products have you actually used yourself?2. On what size system did you use it? number of objects, number of GB orTB, number of users?3. Which version did you use?4. How long ago?5. How extensively did you use it?6. Do you know it well enough to train someone else on its use?If someone hasn't used all of them, in the current version, for at least 3months their opinion will be worth less than yours. And what tunes 10MBwill not necessary tune 2TB.

Yes, thank you for your reply but now it almost becomes as difficult
as making the CBO jumping hoops. :>. This will be my next of questions
for anybody who responds.

[Ryan Gaffuri]

<- (-)> wrote in message news:3f006993.14631320@newzilla.xs4all.nl... I am looking for reviews and opinions on using software for SQL tuning oracle 9i release 2. Any URL's that compares several oracle 9i release 2 SQL tuning software programs are welcome. In case there are no such reviews. What are you're opinions about the following SQL tuning software programs: 1. Quest Central SQL tuning option for oracle. 2. Lecco DB Expert for oracle. 3. Lecco SQL Expert for oracle. 4. Embarcadero SQL Tuner for oracle. 5. CA SQL-Station. Which is the best and why? Are these programs about to be extinct because of the (I assume improved) CBO in Oracle 10i which will be finished late 2003? Thanks

i downlaoded the free verison of embarcadero. its a piece of garbage. all it
does is try out every possible message to see if it can improve your query.

completely worthless. all of its 'reports' are just generic sql scripts
freely available on the web that they put in a nice gui.

I would assume that most of this SQL software is worthless also. Get Guy
Harrisons SQL tuning book.
Its cheaper than the software and more useful.

[Ryan Gaffuri]

<- (-)> wrote in message news:3f0276d2.83568388@newzilla.xs4all.nl... On Sun, 29 Jun 2003 16:49:00 -0700, Daniel Morgan <damorgan@exxesolutions.com> wrote:Nothing tunes Oracle as well as a competent DBA or developer with EXPLAINPLAN, TKPROF, and DBMS_PROFILER. I agree.There is nothing these tools do that can't be done in SQL*Plus exceptempty your bank account. Yes, still I do like the easy of these product that allow you to test for instance 5 sql statements at the same time and then compare the results next to each other. Try doing that with explain plan, tkprof and dbms_profiler. Perhaps there is a freeware tool with similair functionality.Invest your money in education. Yes, well this software will make it more easy to learn SQL tuning also.That said ... add BMC to your list then download the free copies of theseproducts and work with them yourself if you really want a GUI. Each hasits strengths and its weaknesses. Weigh any responses from others againstthe following that you didn't ask:1. Which of the products have you actually used yourself?2. On what size system did you use it? number of objects, number of GB orTB, number of users?3. Which version did you use?4. How long ago?5. How extensively did you use it?6. Do you know it well enough to train someone else on its use?If someone hasn't used all of them, in the current version, for at least
3months their opinion will be worth less than yours. And what tunes 10MBwill not necessary tune 2TB. Yes, thank you for your reply but now it almost becomes as difficult as making the CBO jumping hoops. :>. This will be my next of questions for anybody who responds.

go to tom kytes page. there is a script he has there for comparing two
queries that is outstanding. he uses it in his responses.

outstanding script.

[Joe]

"Ryan" <rgaffuri@cox.net> wrote in message
news:qS4Ma.38498$pH3.13540@news2.east.cox.net... <- (-)> wrote in message news:3f006993.14631320@newzilla.xs4all.nl... I am looking for reviews and opinions on using software for SQL tuning oracle 9i release 2. Any URL's that compares several oracle 9i release 2 SQL tuning software programs are welcome. In case there are no such reviews. What are you're opinions about the following SQL tuning software programs: 1. Quest Central SQL tuning option for oracle. 2. Lecco DB Expert for oracle. 3. Lecco SQL Expert for oracle. 4. Embarcadero SQL Tuner for oracle. 5. CA SQL-Station. Which is the best and why? Are these programs about to be extinct because of the (I assume improved) CBO in Oracle 10i which will be finished late 2003? Thanks i downlaoded the free verison of embarcadero. its a piece of garbage. all
it does is try out every possible message to see if it can improve your
query. completely worthless. all of its 'reports' are just generic sql scripts freely available on the web that they put in a nice gui. I would assume that most of this SQL software is worthless also. Get Guy Harrisons SQL tuning book. Its cheaper than the software and more useful.

It may be worth pointing out that Guy Harrison works for Quest these days...

[Joe]

"Ryan Gaffuri" <rgaffuri@cox.net> wrote in message
news:1efdad5b.0307010317.7c1b4898@posting.google.com... "John" <junk@junk.com> wrote in message
news:<bdr9ft$epn$1@otis.netspace.net.au>... "Ryan" <rgaffuri@cox.net> wrote in message news:qS4Ma.38498$pH3.13540@news2.east.cox.net... <- (-)> wrote in message news:3f006993.14631320@newzilla.xs4all.nl... > I am looking for reviews and opinions on using software for SQL
tuning > oracle 9i release 2. Any URL's that compares several oracle 9i
release > 2 SQL tuning software programs are welcome. > > In case there are no such reviews. > > What are you're opinions about the following SQL tuning software > programs: > > 1. Quest Central SQL tuning option for oracle. > > 2. Lecco DB Expert for oracle. > > 3. Lecco SQL Expert for oracle. > > 4. Embarcadero SQL Tuner for oracle. > > 5. CA SQL-Station. > > Which is the best and why? > > Are these programs about to be extinct because of the (I assume > improved) CBO in Oracle 10i which will be finished late 2003? > > Thanks > > i downlaoded the free verison of embarcadero. its a piece of garbage.
all it does is try out every possible message to see if it can improve your query. completely worthless. all of its 'reports' are just generic sql
scripts freely available on the web that they put in a nice gui. I would assume that most of this SQL software is worthless also. Get
Guy Harrisons SQL tuning book. Its cheaper than the software and more useful. It may be worth pointing out that Guy Harrison works for Quest these
days... why does that matter? man cant right a good book becuase of his employer?

No, I meant that perhaps the employer's product may be better, if the man
is/was involved in it's development.

[FredBear]

On Mon, 30 Jun 2003 13:22:41 -0700, Daniel Morgan
<damorgan@exxesolutions.com> wrote:
Yes, thank you for your reply but now it almost becomes as difficult as making the CBO jumping hoops. :>. This will be my next of questions for anybody who responds.Glad to help but I don't understand your question about the CBO.

I was just saying that making the CBO do what your want is very
difficult. Sometimes the CBO seems to have a mind of it's own but not
a very smart one. Also sometimes thing just become too compilate
because you have to take too much factors into consideration to be
cost effective. The quest for inteligent life in the CBO is one of
those things I like to avoid because I think it would not be cost
effective as it would take a lot of time.

BTW you can use EXPLAIN PLAN to compare statements. You just use separatestatement ids when you run them.The same thing the GUI tool does.

Thank you for the tip. I will take it into consideration.

[FredBear]

On Tue, 01 Jul 2003 00:48:30 GMT, "Ryan" <rgaffuri@cox.net> wrote:
go to tom kytes page. there is a script he has there for comparing twoqueries that is outstanding. he uses it in his responses.outstanding script.

Thank you. I will have a look.

[FredBear]

On Tue, 01 Jul 2003 00:47:18 GMT, "Ryan" <rgaffuri@cox.net> wrote:
i downlaoded the free verison of embarcadero. its a piece of garbage. all itdoes is try out every possible message to see if it can improve your query.completely worthless. all of its 'reports' are just generic sql scriptsfreely available on the web that they put in a nice gui.

Yes, you mean the XPlan program. They also do not maintain it anymore
so they must have come to the same conclusion as you. The last version
I found was 1999 so it would not be up to speed with any of the new
versions of oracle.
I would assume that most of this SQL software is worthless also.

The problem is that if I start to assume things it always goes wrong.
Besides I got to have someting to keep me busy.
Get GuyHarrisons SQL tuning book.Its cheaper than the software and more useful.

Thank you I will have a look.

[Niall Litchfield]

<- (-)> wrote in message news:3f06e73a.248344370@newzilla.xs4all.nl... On Mon, 30 Jun 2003 13:22:41 -0700, Daniel Morgan <damorgan@exxesolutions.com> wrote: Yes, thank you for your reply but now it almost becomes as difficult as making the CBO jumping hoops. :>. This will be my next of questions for anybody who responds.Glad to help but I don't understand your question about the CBO. I was just saying that making the CBO do what your want is very difficult. Sometimes the CBO seems to have a mind of it's own but not a very smart one. Also sometimes thing just become too compilate because you have to take too much factors into consideration to be cost effective. The quest for inteligent life in the CBO is one of those things I like to avoid because I think it would not be cost effective as it would take a lot of time.

I confess that I cannot understand this attitude. You seem to be saying that
you'd like to ignore new features, force somewhat arcane syntax on
developers, make sql behave in the same way regardless of whether the
driving dataset has 2 rows or 2 billion. all this because the CBO is going
to take 'a lot of time' to understand. And presumably abandon relational
databases altogether when 9i is desupported as there won't be a single RDBMS
on the market with simple rules to follow regardless of the datasets
involved by then.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

[Daniel Morgan]

- wrote:
On Mon, 30 Jun 2003 13:22:41 -0700, Daniel Morgan <damorgan@exxesolutions.com> wrote: Yes, thank you for your reply but now it almost becomes as difficult as making the CBO jumping hoops. :>. This will be my next of questions for anybody who responds.Glad to help but I don't understand your question about the CBO. I was just saying that making the CBO do what your want is very difficult. Sometimes the CBO seems to have a mind of it's own but not a very smart one. Also sometimes thing just become too compilate because you have to take too much factors into consideration to be cost effective. The quest for inteligent life in the CBO is one of those things I like to avoid because I think it would not be cost effective as it would take a lot of time.BTW you can use EXPLAIN PLAN to compare statements. You just use separatestatement ids when you run them.The same thing the GUI tool does. Thank you for the tip. I will take it into consideration.

I couldn't agree less.

The CBO's decisions are intelligent and if you learn how it works, and use
hints, blows away the performance of the RBO.

Those that don't learn new things ... there is a word for them ...
unemployable. I'd suggest following Sybrand's advice.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Hans Forbrich]

Daniel Morgan wrote:
I couldn't agree less. The CBO's decisions are intelligent and if you learn how it works, and use hints, blows away the performance of the RBO.

I think there is an exception to the above statement. You are making the
assumption that the developer is actually capable of [thinking and] leveraging
some of the non-simplistic features of the RDBMS.

In my experience, the majority of developers are not aware of things like
bit-mapped indexex, function indexes, bitmap-join indexes, partitioning, IOTs,
etc. Most still want to reinvent stuff like intermedia & context and auditing.
(Support for this - look at how many 'features' like XA & constraints they are
trying to introduce at the development language level.) Thus they still tend to
design and code like we did in the 1980's - in which case the RBO *may* still be
valid.


However, if they leverage virtually any feature that has been introduced since
Oracle7.1, I tend to agree with your statement.

[Daniel Morgan]

Hans Forbrich wrote:
Daniel Morgan wrote: I couldn't agree less. The CBO's decisions are intelligent and if you learn how it works, and use hints, blows away the performance of the RBO. I think there is an exception to the above statement. You are making the assumption that the developer is actually capable of [thinking and] leveraging some of the non-simplistic features of the RDBMS. In my experience, the majority of developers are not aware of things like bit-mapped indexex, function indexes, bitmap-join indexes, partitioning, IOTs, etc. Most still want to reinvent stuff like intermedia & context and auditing. (Support for this - look at how many 'features' like XA & constraints they are trying to introduce at the development language level.) Thus they still tend to design and code like we did in the 1980's - in which case the RBO *may* still be valid. However, if they leverage virtually any feature that has been introduced since Oracle7.1, I tend to agree with your statement.

If that is true no doubt he'll be flipping burgers in the near future. Oracle is
heading in directions
where experience in Oracle 6 and 7 will not take him: Hopefully you are incorrect.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[FredBear]

On Sat, 05 Jul 2003 15:04:14 GMT, - (-) wrote:

To all the 5 above that responded to my remark that I found it not
cost effective to work with the CBO. I actually meant that I did not
like the RBO and the CBO as I am a developer. As a developer I would
prefer using a ESR (External SQL Rewriter). As some of these products
I asked questions about offer or are. Saying that I also realise that
these ESR or other tuning apps are expensive so I will just need to
see if they are too expensive and if they are consider just buying a
book for me and my DBA to go through and look at any other cheap and
free aids. Another problem is it would also take some effort on the
part of our DBA and I do not think he will be very happy with that
idea as he already is busy enough with other stuff. Please keep in
mind that we only have one DBA for a lot of Oracle Database
installations and he also does unix stuff. So the guy has his hands
full.

[FredBear]

On Sun, 06 Jul 2003 21:53:57 GMT, Hans Forbrich
<forbrich@telusplanet.net> wrote:
Daniel Morgan wrote: I couldn't agree less. The CBO's decisions are intelligent and if you learn how it works, and use hints, blows away the performance of the RBO.I think there is an exception to the above statement. You are making theassumption that the developer is actually capable of [thinking and] leveragingsome of the non-simplistic features of the RDBMS.In my experience, the majority of developers are not aware of things likebit-mapped indexex, function indexes, bitmap-join indexes, partitioning, IOTs,etc. Most still want to reinvent stuff like intermedia & context and auditing.(Support for this - look at how many 'features' like XA & constraints they aretrying to introduce at the development language level.) Thus they still tend todesign and code like we did in the 1980's - in which case the RBO *may* still bevalid.

You are right I am a developer and have not really studied bit-mapped
indexex, function indexes, bitmap-join indexes, partitioning, IOTs etc
because I am developing. I feel real guilty about that and try to do
someting about it. I actually had to partially reinvent intermedia as
the first version of it was not mature enough so you are right again.
Fortunatelly intermedia is now mature enough but it took its time
again and it was too late.

Again to the other 4 people:
Let just say I am just a simple soul with a small mind who thinks too
much for other people.

[Ryan Gaffuri]

- (-) wrote in message news:<3f09ecd5.249779131@newzilla.xs4all.nl>... On Thu, 3 Jul 2003 10:34:54 +1000, "John" <junk@junk.com> wrote: > It may be worth pointing out that Guy Harrison works for Quest these days... why does that matter? man cant right a good book becuase of his employer?No, I meant that perhaps the employer's product may be better, if the manis/was involved in it's development. OK, so it is Guy Harrison that quest mean with : Offers expert tuning advice developed by Quest Software?s world-renowned Oracle experts on their Quest Central webpage.

well companies tend to play up their products when they get their
hands on an author. they pay them more money so they can use them for
marketing. and throw their name around.

im raelly skeptical of tuning products. they seem to use scripts that
are freely available all over the web and then put a GUI on it...
telling you look at our ingenious product.

embarcadero is totally worthless. go download a trial version and see
for yourself.

[richto]

Why don't you try LECCO SQL Expert or DB Expert in your database with
enough data and complex SQL. May be you will surprise the result SQL
alternative and execution plans generated by these tools. I wonder
there are no human DBA can try so many SQL Rewrite within such a time.

--
Posted via http://dbforums.com