Re: SQL Tuning Tool

Hi Wil,

Tools like SQLD360 are terrific but they give way too much information for 95% of performance problems. If your developers know the SQL that needs to be faster, then just run it with row source statistics enabled and grab the execution plan with them (as others have mentioned). It takes barely any effort to do and there's no extra licencing requirements.

If the developer can understand how to read an execution plan (and they should) then they should easily make sense of the row source statistics added to it.

If you have purchased the diagnostic pack licence then querying v$ash will give you a goldmine of information. You can even join it to v$sql_plan to identify lines of your plans that are hot spots for time taken, and you can see what events were waited on for them (and with what associated objects). Putting those sorts of queries together could be a great learning exercise for dealing with AWR. You could even save these queries as SQL Developer reports and add to them over time as necessary, I've just started doing this myself and have shared what you might find a decent starting point on my blog https://ctandrewsayer.wordpress.com/2017/11/24/visualising-ash/

I had a quick look at sqlbooster (never heard of it before), it looks like it's trying to bombard the user with a metric tonne of information in a format that they would have to put in a lot of work to make sense of. Have you tried it out with a real performance problem? How long did it take you to figure out the issue and come up with a fix? I’m skeptical that it will do anything other than give your developers and DBAs even more work. Your mileage might vary though.

Hope this helps,
Andrew

On 23 November 2017 at 22:13, dba Wilson <iamanoracledba_at_gmail.com> wrote:

> A quick update.
>
> We did a sharing session for each tool assessed with Dev team, collected
> the feedback from them. Most developers prefer to use sqlbooster. Our DBA
> team also think it matches most requirements. We can control the
> authorization by creating the authorization file from our side, then sent
> it to dev to be used. The DBA account will not be leaked and the software
> can also utilize it. While developers can export the SQL analyze result to
> DBA team for further review.
> We are preparing training session for the developers. If anyone is
> interested in the training materials, we can send the PPT to you once it's
> prepared.
> BTW, the DBAs love SQLD360. None of us ever used it.
>
> Thanks for your valuable advice.
>
>
> Regards,
> Wil
>
>
> 2017-11-22 9:42 GMT+11:00 dba Wilson <iamanoracledba_at_gmail.com>:
>
>> Thanks guys!
>>
>> Sorry for late reply. What we are thinking is to find a tool can be used
>> by the developers. And this tool is easy to be used, does not require too
>> much DBA knowledge. So, the developers can give a initial tuning on the
>> developed/changed queries, to reduced the risk of crash our business. We
>> don't expect them to read the AWR/ASH, or do SQL trace and read the tkprof
>> files, just hope them find the potential performance bottleneck of their
>> query, and they can do something from logic side. At least, they understand
>> the logic much better than DBAs.
>> SQLBooster looks good so far, we are seeking support from the author as
>> we are still facing some problem. The SQLD360 sounds good too, we are
>> arranging assessment on it as well.
>>
>>
>>
>> Regards,
>> Wil
>>
>>
>> 2017-11-22 9:32 GMT+11:00 dba Wilson <iamanoracledba_at_gmail.com>:
>>
>>> Thanks, Stefan. I will try it
>>>
>>>
>>>
>>> Regards,
>>> Wil
>>>
>>> 2017-11-21 1:46 GMT+11:00 Stefan Koehler <contact_at_soocs.de>:
>>>
>>>> Hello Wil,
>>>> I have to mention (free) SQLd360 by Mauro Pagano as this is the tool
>>>> that I use all the time: https://github.com/mauropagano/sqld360
>>>>
>>>> ... and then train your developers and DBAs in SQL tuning - especially
>>>> in understanding what has gone wrong and how to avoid these issues in the
>>>> future ...
>>>>
>>>> IMHO all of these "automatic" tuning and analysis tools suck most of
>>>> the time - especially if you just blindly follow and implement their
>>>> suggestion without understanding the root cause.
>>>>
>>>> P.S.: I can only agree with what Dominic wrote/said.
>>>>
>>>> Best Regards
>>>> Stefan Koehler
>>>>
>>>> Independent Oracle performance consultant and researcher
>>>> Website: http://www.soocs.de
>>>> Twitter: _at_OracleSK
>>>>
>>>> > dba oracle <iamanoracledba_at_gmail.com> hat am 20. November 2017 um
>>>> 01:39 geschrieben:
>>>> >
>>>> > Hi Gurus,
>>>> >
>>>> > I am currently looking for a SQL Tuning tool can be used by DBA as
>>>> well as developers. The background is that we have many complex SQLs being
>>>> developed in our products, each developer may touch those SQLs and change
>>>> them if he/she is handling a ticket related to them. Every change might
>>>> cause performance changing (and actually they did). We want to find a tool
>>>> can be easily used by the developers, and then give them a training
>>>> session, they will be able to tune their new developed/changed SQL before
>>>> tagged to the release.
>>>> >
>>>> > I've tried SQL developer. It just simply give us the chance to use
>>>> SQL Advisor.
>>>> > I also tried Toad, it more looks like an offline SQL advisor. And it
>>>> has crashed several times in my Win 10. It is really frustrating.
>>>> > I also found this tool, SQLBooster, from [www.SQLFast.com](
>>>> http://www.SQLFast.com). It's cool because it can brake the complex
>>>> SQL down to small queries to analyze the bottleneck. But there is only a
>>>> few documents provided in the website and the UI is not so friendly. I am
>>>> still struggling on testing it.
>>>> >
>>>> > Do you guys have any recommendation?
>>>> >
>>>> > Regards,
>>>> > Wil
>>>>
>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l