SQL Tuning made much easier with SQLTXPLAIN (SQLT)

2. SQL Tuning made much easier with SQLTXPLAIN (SQLT) Mauro PaganoPrincipal Technical Support Engineer

3. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

4. Program Agenda • Introduction • Installation • Main methods • XTRACT, XECUTE and XTRXEC • XTRSBY and XPLAIN • XPREXT and XPREXC • Main HTML diagnostics report • Q&A

5. Oracle Services Enabling the success of your Oracle hardware and software investments • Oracle Experts Helping You Succeed withYour Oracle Investments Complete Support for Oracle Hardware, Software, and Engineered Systems Mission Critical Support Services for All Oracle Applications and Technologies Your Complete Training Source for Oracle Hardware and Software Extend Your Oracle Investments to the Cloud with Value, Choice, and Confidence

6. Oracle Premier Support Comprehensive Coverage Tools and Resources Service and Support Product Innovation Quickly diagnose and resolve issues • Expert technical support • Rapid-response field service • Lifetime Support Get the most of your Oracle products with proactive services • Oracle knowledgebase • Product health checks • My Oracle Support Community Keep pace with change and capitalize on new opportunities • Updates • New releases • Tools to assist with patching and upgrades

7. Get Proactive Portfolio—an integral component of your Premier Support Contract Helping you get the most value fromOracle Premier Support

8. SQL Tuning made much easier with SQLTXPLAIN (SQLT)

9. Program Agenda • Introduction • Installation • Main methods • XTRACT, XECUTE and XTRXEC • XTRSBY and XPLAIN • XPREXT and XPREXC • Main HTML diagnostics report • Q&A

10. What is SQLTXPLAIN (SQLT)? Tool to diagnose SQL statements performing poorly • Add-on (MOS 215187.1) • 12c/11g/10g • Linux, UNIX, Windows • Single instance, RAC, Exadata • Easy to install and execute • download and use

11. SQLTXPLAIN (SQLT) benefits What is in it for me? • Collect SQL tuning diagnostics for one SQL statement • Over a hundred health-checks (HC) around the SQL statement • If Oracle Tuning or Diagnostics Pack are properly licensed • Invokes SQL Tuning Advisor (STA) • Includes Active Session History (ASH) and SQL Monitor Report • Includes Automatic Workload Repository (AWR) • Automatic SQL tuning test case (TC) extraction • SQLT helps to expedite SQL tuning process

12. SQLTXPLAIN (SQLT) mechanics How does it work? • 3 + 4 main methods to analyze one SQL statement • Inputs one SQL statement and outputs one zip file • Output contains a main html report and other files • AWR, ADDM, ASH, 10053, 10046, TKPROF, etc. • Exports SQL tuning test cases • SQLT TC and TCX • 11g Test Case Builder (TCB) • SQLT installs its own two schemas and objects on them!

13. Program Agenda • Introduction • Installation • Main methods • XTRACT, XECUTE and XTRXEC • XTRSBY and XPLAIN • XPREXT and XPREXC • Main HTML diagnostics report • Q&A

14. How do I install SQLT? My Oracle Support (MOS) 215187.1 • Download tool (sqlt.zip file) • Unzip into database server • Execute sqlt/install/sqcreate.sql connected as SYS • Input installation parameters

15. SQLT Installation Parameters Provided inline or when asked • Optional Connect Identifier (ie: @PROD) • Password for user SQLTXPLAIN • Default Tablespace • Temporary Tablespace • Main application user of SQLT • Oracle Pack license [ T | D | N ]

16. How do you know Installation succeeded?

17. How do I uninstall SQLT In case you no longer need SQLT • Execute sqlt/install/sqdrop.sql connected as SYS

18. Program Agenda • Introduction • Installation • Main methods • XTRACT, XECUTE and XTRXEC • XTRSBY and XPLAIN • XPREXT and XPREXC • Main HTML diagnostics report • Q&A

19. SQLT Main Methods All 3 + 2 + 2 act on one SQL statement • SQLT XTRACT • SQLT XECUTE • SQLT XTRXEC • SQLT XPLAIN • SQLT XTRSBY • SQLT XPREXT • SQLT XPREXC

20. Using SQLT Main Methods

21. SQLT Output Overlap XPLAIN Common XTRACT XTRSBY XECUTE • XTRXEC includes XTRACT and XECUTE

22. Program Agenda • Introduction • Installation • Main methods • XTRACT, XECUTE and XTRXEC • XTRSBY and XPLAIN • XPREXT and XPREXC • Main HTML diagnostics report • Q&A

23. What is included in Main Report? Partial list of contents (1/3) • Health-checks • SQL text • Parameters • Cursor sharing • SQL Tuning Advisor (STA) report • Execution plans

24. What is included in Main Report? Partial list of contents (2/3) • Plan stability • Active Session History (ASH) • SQL performance metrics • SQL Monitor • Segment and session statistics • Tables

25. What is included in Main Report? Partial list of contents (3/3) • Indexes • CBO statistics • Objects and dependencies • Policies • Metadata

26. Navigating the SQLT Main Report Typical Navigation • SQL Text • Plans Summary • Observations • Branch as per findings

27. Demonstration Main Report Navigation • SQLT XTRACT • SQLT XECUTE

28. SQL Additional Methods What else can I do with SQLT? • COMPARE • Compares everything that surrounds a SQL (object definition, statistics, parameters, execution plan, etc) across two systems • XPLORE • Evaluates the impact of every CBO parameter / fix on the execution plan • Automates hundreds on tests in single operation

29. While You’re at Oracle OpenWorld… • Support Stars BarMoscone West, Level 2 • Oracle experts • Live demos • Mini-briefings • Videos • Support Breakout Sessions • Oracle Database • Oracle E-Business Suite • SQL Tuning • Oracle Exadata • Siebel CRM • PeopleSoft • JD Edwards • Oracle Business Analytics • Oracle Fusion Middleware • Oracle Solaris • Oracle RAC Cluster • Oracle Communications Solutions • Sun Server and Storage Systems

30. 4th Annual My Oracle Support Community Meet Up Event • Monday, September 23 • 6:30 – 9:30 pm • Join us for fun, networking, beverages and appetizers • Meet fellow Community members and Oracle moderators • Admission is free but registration is required • Details and registration at: • www.bit.ly/MeetUpEvent2013

31. Summary

32. SQL Tuning made easier with SQLTXPLAIN How is that? • Full collection of SQL tuning diagnostics • Consolidated into an easy-to-navigate html report • Consistent view • Allow offline expert analysis • Over a hundred health-checks around the SQL statement • Some with pointers to particular notes or bugs • Automatic SQL tuning test case (TC) extraction • Allow WHAT-IF evaluations on a test environment • Dynamic readme with commands for further diagnostics

33. Want to master SQLTXPLAIN? Book: Oracle SQL Tuning with Oracle SQLTXPLAIN • Author: Stelios Charalambides • Released: March 20, 2013 • Level: Intermediate • Available • Apress • Amazon • Barnes&Noble

34. Eager to master SQL Tuning? Some SQL Tuning Gurus (Google them!) • Carlos Sierra • Jonathan Lewis • Maria Colgan • Tom Kyte • Wolfgang Breitling • Cary Millsap • Christian Antognini • Guy Harrison • Karen Morton

35. Q&A and Important Support Resources • Discover more about Get Proactive: • http://www.oracle.com/goto/proactivesupport • MOS Notes: • 215187.1 SQLTXPLAIN (SQLT) Tool Download • 1454160.1 SQLTXPLAIN (SQLT) FAQ • MOS Community: • SQLTXPLAIN (SQLT): General Discussion

36. Thank you!