Re: SQL Tuning Tool

Breaking down complex queries to smaller ones to analyse bottlenecks is likely to give irrelevant results because the optimiser rewrites queries, flattens etc.

You can’t beat running the query against representative data with statistics level = all and getting the runtime execution plan and metrics via dbms_xplan.display_cursor (or real time sql monitoring especially if parallel execution). Then reviewing what it says.

Yes, you need to be familiar with what it’s telling you but the sooner the team gets familiar the better. And you should document all that information against the change.

Regards
Dominic

Sent from my iPhone

On 20 Nov 2017, at 01:17, dba oracle <iamanoracledba_at_gmail.com<mailto:iamanoracledba_at_gmail.com>> wrote:

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<https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.SQLFast.com&data=02%7C01%7Cdombrooks%40hotmail.com%7Cfd802b3276914e23db5108d52fb469df%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636467374274931429&sdata=p4Z5QgFxIurPL2BkGYUqzupAEWKhsaKkBZX039hlTlQ%3D&reserved=0>. 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