When talking tools to help with SQL Tuning the question on SQLTXPLAIN vs SQLd360 comes out very often. “What’s the difference?”, “Which one should I use?”, “Why another tool?” are probably the top ones 🙂
I’ll try to make a fair and (as much as possible) unbiased comparison between the two on some general aspects. If you want to know of any specific area just let me know in the comments.
|Installation||Requires installation. Creates two schemas that serve as metadata and code repository, they need to be present every time the tool is executed||No installation, at the end of the execution no evidence is left behind|
|Source code||Mainly PL/SQL, unwrapped (clear text). Code is Oracle property||SQL scripts, clear text, true open source released under GPL. Code available on GitHub|
|Main Goal||Automate every aspect of diagnostic collection for SQL tuning||Same as SQLT, but less “obsessive” 🙂|
|Main Focus||Execution plan generation, why CBO generated plan X,Y,Z (includes custom testcase, very flexible)||Execution plan generation (but a little less than SQLT) and how plans execute at runtime|
|Main advantage||Includes every bit of info needed to drill into CBO decisions, unlikely the need to go back and collect anything else||Makes diagnostic consumption faster and easier because of data visualization, lots of focus on runtime|
|Main disadvantage||Requires installation. Not much focus on execution time (“same plan different performance” type of cases harder to deal with)||Might miss some information under rare circumstances|
Outside the comparison table, what we use today: SQLd360
- No installation.
- Root Cause Analysis of CBO decisions usually belongs to Oracle Support (for the most part), SQL Tuning in the real world focuses on easier aspects of plan generation, thus lower need for strong focus on it.
- Performance data available in Oracle grows by release, charting allows quick and way more effective consumption of large amount of such data.
April 29, 2017 at 9:57 am
I’ve always wondered what the differences were and other than the no install of SQLd360, I didn’t know some of the others. Thanks for writing this up!
April 29, 2017 at 10:06 am
🙂 and if you have any other question that is not up in the post let me know and I’ll add it!
May 2, 2017 at 8:19 am
Hi Mauro, I think other difference is the creation of SQL Profile. With SQLTXPLAIN I can create a SQL Profile, with SQLd360 I can not create a SQL Profile.
May 2, 2017 at 8:26 am
Not exactly (anymore), that was disabled few years ago. You can use a standalone script (coe_xfr_sql_profile) regardless of SQLT / SQLd360 🙂