![Page 1: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/1.jpg)
Copyright © 2013, Oracle and/or its affiliates. 1
![Page 2: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/2.jpg)
Copyright © 2013, Oracle and/or its affiliates. 2
SQL Tuning made much easier with SQLTXPLAIN (SQLT)Carlos SierraConsulting Technical Advisor Center of Expertise (CoE)
![Page 3: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/3.jpg)
Copyright © 2013, Oracle and/or its affiliates. 3
Carlos Sierra
SQL Tuner handyman: developer, advisor, trainer, support IT: Oracle(17), UNISYS(12), Ford(3), others(3) Florida(17), Venezuela(3), Puerto Rico(6), Michigan(1), Mexico(X) Tools: SQLTXPLAIN(SQLT), SQLHC, TRCANLZR(TRCA), others Motto: Life is good!
Oracle Server Technologies(ST) Center of Expertise(CoE)
![Page 4: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/4.jpg)
Copyright © 2013, Oracle and/or its affiliates. 4
Agenda
Introduction Installation Main methods
– XTRACT, XECUTE and XTRXEC
– XTRSBY and XPLAIN
– XPREXT and XPREXC
Main HTML diagnostics report
SQL Tuning made much easier with SQLTXPLAIN (SQLT)
![Page 5: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/5.jpg)
Copyright © 2013, Oracle and/or its affiliates. 5
SQLTXPLAIN (SQLT) Introduction
![Page 6: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/6.jpg)
Copyright © 2013, Oracle and/or its affiliates. 6
What is SQLTXPLAIN (SQLT)?
Add-on (MOS 215187.1) 11g/10g Linux, UNIX, Windows Single instance, RAC, Exadata Easy to install and execute download and use
Tool to diagnose SQL statements performing poorly
![Page 7: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/7.jpg)
Copyright © 2013, Oracle and/or its affiliates. 7
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?
![Page 8: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/8.jpg)
Copyright © 2013, Oracle and/or its affiliates. 8
SQLTXPLAIN (SQLT) mechanics
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!
How does it work?
![Page 9: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/9.jpg)
Copyright © 2013, Oracle and/or its affiliates. 9
SQLT Installation
![Page 10: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/10.jpg)
Copyright © 2013, Oracle and/or its affiliates. 10
How do I install SQLT?
Download tool (sqlt.zip file) Unzip into database server Execute sqlt/install/sqcreate.sql
connected as SYS Input installation parameters
My Oracle Support (MOS) 215187.1
![Page 11: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/11.jpg)
Copyright © 2013, Oracle and/or its affiliates. 11
SQLT Installation Parameters
Optional Connect Identifier (ie: @PROD) Password for user SQLTXPLAIN Default Tablespace Temporary Tablespace Main application user of SQLT Oracle Pack license [ T | D | N ]
Provided inline or when asked
![Page 12: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/12.jpg)
Copyright © 2013, Oracle and/or its affiliates. 12
How do you know Installation succeeded?
![Page 13: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/13.jpg)
Copyright © 2013, Oracle and/or its affiliates. 13
How do I uninstall SQLT
Execute sqlt/install/sqdrop.sql connected as SYS
In case you no longer need SQLT
![Page 14: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/14.jpg)
Copyright © 2013, Oracle and/or its affiliates. 14
SQLT Main Methods
![Page 15: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/15.jpg)
Copyright © 2013, Oracle and/or its affiliates. 15
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
![Page 16: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/16.jpg)
Copyright © 2013, Oracle and/or its affiliates. 16
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
![Page 17: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/17.jpg)
Copyright © 2013, Oracle and/or its affiliates. 17
XTRXEC includes XTRACT and XECUTE
XPLAIN
XTRSBY
CommonXTRACT
XECUTE
SQLT Output Overlap
![Page 18: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/18.jpg)
Copyright © 2013, Oracle and/or its affiliates. 18
SQLT Main HTML Diagnostics Report
![Page 19: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/19.jpg)
Copyright © 2013, Oracle and/or its affiliates. 19
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)
![Page 20: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/20.jpg)
Copyright © 2013, Oracle and/or its affiliates. 20
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)
![Page 21: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/21.jpg)
Copyright © 2013, Oracle and/or its affiliates. 21
What is included in Main Report?
Indexes CBO statistics Objects and dependencies Policies Metadata
Partial list of contents (3/3)
![Page 22: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/22.jpg)
Copyright © 2013, Oracle and/or its affiliates. 22
Navigating the SQLT Main Report
1. SQL Text
2. Plans Summary
3. Observations
4. Branch as per findings
Typical Navigation
![Page 23: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/23.jpg)
Copyright © 2013, Oracle and/or its affiliates. 23
Demonstration
Main Report Navigation
SQLT XTRACT SQLT XECUTE
![Page 24: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/24.jpg)
Copyright © 2013, Oracle and/or its affiliates. 24
Summary
![Page 25: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/25.jpg)
Copyright © 2013, Oracle and/or its affiliates. 25
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?
![Page 26: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/26.jpg)
Copyright © 2013, Oracle and/or its affiliates. 26
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
![Page 27: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/27.jpg)
Copyright © 2013, Oracle and/or its affiliates. 27
Eager to master SQL Tuning?
Jonathan Lewis Maria Colgan Tom Kyte Wolfgang Breitling Cary Millsap Christian Antognini Guy Harrison Karen Morton
Some SQL Tuning Gurus (Google them!)
![Page 28: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/28.jpg)
Copyright © 2013, Oracle and/or its affiliates. 28
My Oracle Support Notes and Contact Info
215187.1 SQLTXPLAIN (SQLT) Tool Download 1454160.1 SQLTXPLAIN (SQLT) FAQ
MOS (Metalink) Notes
[email protected] http://carlos-sierra.net @csierra_usa
![Page 29: Copyright © 2013, Oracle and/or its affiliates. 1](https://reader034.vdocuments.us/reader034/viewer/2022051614/5518a1d0550346b31f8b48ce/html5/thumbnails/29.jpg)
Copyright © 2013, Oracle and/or its affiliates. 29