SQL Tuning made easier with SQLTXPLAIN (SQLT)

1. SQL Tuning Made Easier with SQLTXPLAIN (SQLT) Carlos Sierra 2. Carlos Sierra • SQLTXPLAIN + SQL Health-Check SQLHC + • Consultant/Developer/DBA/Design/+ • Oracle Performance + SQL Tuning • Oracle Database Health-Check • Tools + Scripts • Speaker Enkitec (c) 2014 2 3. Topics • SQLTXPLAIN 101 • Sample Output • Installation • Using XTRACT and XECUTE • Demos Enkitec (c) 2014 3 4. SQLTXPLAIN (SQLT) • Tool for SQL Tuning – Diagnostics • Inputs one SQL • Outputs one compressed file – Main HTML Report + many other files • MOS 215187.1 – Free download and use Enkitec (c) 2014 4 5. Some Tools • One Database – OEM, AWR, OS Stats, etc • One Process – SQL Trace, TKPROF, Method R, ASH, etc • One SQL – SQLT, SQLHC, planx, sqlmon, sqlash, etc Enkitec (c) 2014 5 6. Enkitec (c) 2014 6 7. Demos 1 and 2 • Main HTML Report • SQLT XTRACT Output Enkitec (c) 2014 7 8. SQLT Installation • My Oracle Support (Metalink) – Note 215187.1 • Unzip sqlt.zip on Database Server • Read sqlt/sqlt_instructions.html • Execute sqlt/install/sqcreate.sql – Connect as SYS Enkitec (c) 2014 8 9. Installation Parameters Enkitec (c) 2014 9 10. Using SQLT • Read sqlt/sqlt_instructions.html • Navigate to sqlt/run • Connect as application user – Or user granted sqlt_user_role or dba • Execute one of these two – sqltxtract.sql – sqltxecute.sql Enkitec (c) 2014 10 11. SQLT XTRACT • Inputs – SQL_ID – SQLTXPLAIN Password • Outputs – ZIP File • Main HTML Report • Other Files Enkitec (c) 2014 11 12. SQLT XECUTE • Inputs – File Name with SQL • Binds declaration and assignment • SQL Text – SQLTXPLAIN Password • Outputs – ZIP File Enkitec (c) 2014 12 13. Demos 3 and 4 • SQLT Installation • SQLT XTRACT – SQL_ID 8u0n7w1jug5dg Enkitec (c) 2014 13 14. Conclusion • SQLT is a comprehensive tool for SQL Tuning • SQLT is free to download and use • SQLT is easy to use • SQLT makes SQL Tuning easier Enkitec (c) 2014 14 15. Mastering SQLTXPLAIN • Practice using SQLT • Read Stelios Charalambides book Enkitec (c) 2014 15 16. Some SQL Tuning Masters • Jonathan Lewis • Maria Colgan • Tom Kyte • Kerry Osborne • Guy Harrison • Karen Morton Enkitec (c) 2014 16 17. Contact Information • [email protected] • carlos-sierra.net • @csierra_usa Enkitec (c) 2014 17