sql tuning made much easier with sqltxplain (sqlt) mauro pagano senior principal technical support...
TRANSCRIPT
SQL Tuning made much easier with SQLTXPLAIN (SQLT)
Mauro PaganoSenior Principal Technical Support Engineer
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 3
Safe Harbor StatementThe 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.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 4
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 5
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
What is SQLTXPLAIN (SQLT)?
• Add-on (available at MOS 215187.1)• Free to download and use • Works on 12c,11g,10g• Linux, UNIX, Windows• Single instance, RAC, Exadata, In-Memory• Easy to install and execute
Tool to diagnose SQL statements performing poorly
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQLTXPLAIN (SQLT) origins
• SQL Tuning diagnostics gathering– time consuming and time sensitive manual process– not straightforward, need to know what to extract and how – more info/diag release after release
• Avoid reinventing the wheel, quickly spot known issues / concerns
• Several RDBMS features provide insights but often overlooked
Why do I need a tool to collect diagnostics?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQLTXPLAIN (SQLT) benefits
• 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
What is in it for me?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQLTXPLAIN (SQLT) mechanics
• Install once, execute many• 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, SQL Mon, 10053, 10046, TKPROF, etc.
• Exports SQL tuning test cases– SQLT TC and TCX plus 11g Test Case Builder (TCB)
• SQLT installs its own two schemas, does not pollute the application!
How does it work?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 10
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQLT Main Methods
• SQLT XTRACT• SQLT XECUTE• SQLT XTRXEC• SQLT XPLAIN• SQLT XTRSBY• SQLT XPREXT• SQLT XPREXC
All 3 + 2 + 2 act on one SQL statement
All methods have these requirements
SQL*Plus connecting as application user
Application user must have SQLT_USER_ROLE granted
Password for SQLTXPLAIN must be provided
One SQL per use of SQLT
SQLT has its own configuration parameters
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Using SQLT Main MethodsMethod Script Features
SQLT XTRACT sqlt/run/sqltxtract.sql Most common methodInputs SQL_IDSQL statement is not executed
SQLT XECUTE sqlt/run/sqltxecute.sql Inputs a script name which contains ALTER SESSION commands (if applicable) Binds declaration and assignment (if applicable) SQL statement
Executes the SQL statement
SQLT XTRXEC sqlt/run/sqltxtrxecsql Combines XTRACT and XECUTEInputs SQL_ID
SQLT XPLAIN sqlt/run/sqltxplain.sql Inputs a file name which contains a SQL statementWhat if SQL contains binds?Leave them in place, OR Replace with literals of same data type
SQLT XTRSBY sqlt/run/sqltxtrsby.sql For read-only databasesExecutes in Primary and connects into stand-byWorks like XTRACTInputs SQL_ID and DBLINK
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
XTRXEC includes XTRACT and XECUTE
XPLAIN
XTRSBY
CommonXTRACT
XECUTE
SQLT Output Overlap
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 14
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
What is included in Main Report?
• Health-checks• SQL text• Parameters• Cursor sharing• SQL Tuning Advisor (STA) report• Execution plans
Partial list of contents (1/3)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
What is included in Main Report?
• Plan stability• Active Session History (ASH)• SQL performance metrics• SQL Monitor• Segment and session statistics• Tables
Partial list of contents (2/3)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
What is included in Main Report?
• Indexes• CBO statistics• Objects and dependencies• Policies• Metadata
Partial list of contents (3/3)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Navigating the SQLT Main Report
1. SQL Text
2. Plans Summary
3. Observations
4. Branch as per findings
Typical Navigation
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Main Report Navigation
• SQLT XECUTE
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstrantion
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Same SQL in 12c uses an Adaptive Plan, SQLT helps making it more readable
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
If we want to force this plan in another db we can just grab these hints
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
SQLT provides info about the performance history for the SQL (from AWR)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
SQLT extract info from ASH to help identify where time was spent
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Demonstration
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQLT Additional Methods
• 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
What else can I do with SQLT?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 40
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQL Tuning made easier with SQLTXPLAIN
• 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
How is that?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
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
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
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
What if I want to know more?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 44
Program Agenda
Introduction
Main Methods
Main HTML Diagnostic Report
Summary
Q&A
1
2
3
4
5
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 45