Where are my SQLTXPLAIN files?
Every so often I get this question in multiple forms:
- Where does SQLTXPLAIN (SQLT) genetares the output files?
- I cannot find my SQLTXPLAIN output.
- SQLTXPLAIN (SQLT) disconnected. Where do I see the error?
- SQLT failed to execute. How do I know what happened?
SQLT main methods (XTRACT, XECUTE, XTRXEC, XTRSBY and XPLAIN) open a log file under the current SQL*Plus directory from where you are executing the SQLTXPLAIN module. This log file contains the name of the module: sqltxtract.log, sqltxecute.log, etc. So, finding the reason WHY your SQLT failed requires that you find and read that log file.
Then the original question becomes “How do I know which is my current SQL*Plus directory?”. I used to answer: “the same from where you started SQL*Plus”. But to provide a better answer now I say:
To find your SQL*Plus current directory and SQLT log, please issue this command from your SQL*Plus session:
- From UNIX or Linux:
- HOS pwd
- HOS ls sqlt*.log
- From WIndows:
- HOS dir sqlt*.log
If your SQLT method completed successfully then the log file is compressed into a ZIP file, together with the entire SQLT output. Look then for sqlt_sNNNNN_<method><id>.zip. Where NNNNN is your SQLT statement_id (a sequence). This ZIP file is also created under your current SQL*Plus directory.
SQL Dynamic eXtractor (SQLDX) is now available under SQLT and SQLHC
Background
As most of you know SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC) include lots of information about one SQL statement. SQLT requires to install an schema and some objects while SQLHC requires no installation. The main difference between the two are:
- The level of details: (SQLT provides a lot more).
- The capability of automatic Test Case (TC) extraction (SQLT does this).
SQLT is always the preferred method, while SQLHC is the fall back when SQLT cannot be installed in a timely manner.
Both SQLT and SQLHC work on 10g and 11g databases. SQLT presents details about many GV$ and DBA views referencing your SQL statement. These views include both 10g and 11g. In other words, SQLT is not restricted to the minimum common denominator (10g) but it expects to extract information from any relevant view referencing a SQL_ID on 11g. There is one small limitation although: SQLT does not show in its main report all views and tables referencing a SQL_ID, but most of what is usually needed for SQL Tuning.
What is the SQL Dynamic eXtractor (SQLDX)?
SQL Dynamic eXtractor (SQLDX) is a new stand-alone SQL script (sqldx.sql) that requires no installation. It dynamically generates another script that selects from all SYS views and tables which reference a column SQL_ID, extracting all rows matching the SQL_ID passed. It then computes the SIGNATURE for the SQL’s text and proceeds to generate dynamic SQL to select from all SYS tables and views referencing this SIGNATURE. For SIGNATURE it uses exact and force matching.
SQLDX input and output
SQLDX output is a ZIP file that contains the output of all dynamic SELECTs on all SYS tables and views referencing your SQL_ID and its SIGNATURE (exact and force). The output of every dynamic SELECT is a file on its own. Their format is HTML and Coma Separated Values (CSV). The latter can be opened in Excel to ease aggregations, finer selections, sorts, etc.
SQLDX takes as input 3 parameters:
- Oracle Pack License: Diagnostics Pack or Tuning Pack (D|T).
- Output Type: HTML, CSV or BOTH (H|C|B).
- SQL_ID: resident in memory or AWR.
SQLDX is included on SQLT and SQLHC 11.4.5.3 and higher (released on December 31, 2012). SQLDX is automatically executed at the end of any SQLT main method and at the end of SQLHC. Keep in mind that SQLDX can also be executed stand-alone and it requires no installation whatsoever. So it can be used in a Production environment or in a read-only database.
Where do you get SQLDX from?
SQLT (MOS 215187.1) includes sqlt/run/sqldx.sql and SQLHC (MOS 1366133.1) includes sqlhc/sqldx.sql. It is the same sqldx.sql script. When sqldx.sql is executed from within SQLT or SQLHC it generates a ZIP file identified as “sqldx”. Look for it next time you use SQLT or SQLHC (version 11.4.5.3 or higher). Or you may want to try sqldx.sql stand-alone.
Adaptive Cursor Sharing (ACS) free Webinar on December 11 at 4pm UCT (11am ET, 9am PT)
Understanding how Adaptive Cursor Sharing (ACS) works is easy. But finding out WHY sometimes it works and sometimes it doesn’t is not trivial. This one-hour webinar explains how ACS is expected to work. Then it uses a demo to show how a SQL statement may or may not become “bind aware”. Concepts like ACS Histogram and ACS Selectivity Profile are explained and demonstrated. At the end of this session, participants should have a better understanding of ACS, its benefits, and its ramp-up process. If interested to attend please sign in to this free webinar under WebEx.
Recording is now available.
Speaking at the UKOUG 2012
I had the honor to be accepted as a speaker of two sessions at the UK Oracle Users Group 2012. So I will be in Birmingham, UK from December 1st to December 5, 2012.
My topics are in SQLT Test Case (TC) functionality and in new SQL Health-Check (SQLHC) script. Both sessions will include a demo since I believe it is easier to grasp capabilities by showing them instead of just navigating through a PPT.
SQLT has now a 3rd method to generate a Test Case. So now we have 11g Test Case Builder (from the product), plus SQLT TC and the new SQLT TCX or Test Case eXpress. This last method was requested by James Cremonini. SQLT TCX is mostly an internal tool to expedite the implementation of a test case for SQL Tuning. I am making it available to anyone interested.
SQLHC continues growing. I will work soon on an enhancement that will capture a lot more regarding one SQL, while not installing anything in the database. SQLHC is used in cases where for some reason SQLT cannot be installed any time soon.
Anyways, if you are attending UK OUG I hope to meet face to face.
SQLTXPLAIN 11.4.5.0 is now available. SQLTXADMIN was born on November 21, 2012!
So, what is new in version 11.4.5.0?
There are some functional enhancements and a couple of small fixes. What really changed is the security model supporting SQLT. Now we have two users: SQLTXPLAIN and SQLTXADMIN. Legacy SQLTXPLAIN owns all SQLT Tables, Indexes and Sequences, while SQLTXADMIN owns all SQLT Packages and Views. Why? It has to do with restricting as much as possible access to a system. SQLTXPLAIN used to have several system privileges needed by its set of PL/SQL libraries. Once we split this account into two we can strip SQLTXPLAIN from all its system privileges and assign a subset of them to new SQLTXADMIN.
Why this change?
We needed SQLTXPLAIN with no system privileges since any SQLT user needs to provide the SQLTXPLAIN password every time SQLT is used. This latter requirement supports the export of the SQLTXPLAIN repository for the SQL being analyzed, which in turn is needed for Test Case (TC) creation. We also needed those system privileges to extract a Test Case Builder (TCB) and to perform several other “admin” tasks SQLT does in the background.
SQLTXADMIN has now some system privileges listed in the “Security Model” of the sqlt_instructions.html file that comes within the sqlt.zip file from MOS 215187.1. SQLTXPLAIN only has CREATE SESSION system privilege, and it is no longer granted SQLT_USER_ROLE. So, if you try to execute SQLTXPLAIN connected as SQLTXPLAIN you will get this error:
BEGIN * ERROR at line 1: ORA-20109: User "SQLTXPLAIN" lacks required "SQLT_USER_ROLE" role. Request to your DBA: "GRANT SQLT_USER_ROLE TO SQLTXPLAIN;". ORA-06512: at line 3
Do you want to grant SQLT_USER_ROLE to SQLTXPLAIN?
The anser is NO. What you want instead is to execute SQLT connected as the application user that generated the SQL statement you want to analyze. Be sure this application user has been granted the SQLT_USER_ROLE, or you will get the same error. If for some reason you cannot use the application user or you cannot grant SQLT_USER_ROLE to it, then and only then execute SQLT connecting as SYS or any DBA account.
Be also aware that SQLTXADMIN is created locked and with a random password. There is no need to unlock this account or reset the password. Of course SYS could do that but it is not needed nor wanted by SQLT. Think of SQLTXADMIN as the guardian of the system privileges SQLT needs. It uses those system privileges from within SQLT code in those PL/SQL libraries owned by SQLTXADMIN and mostly acting on top of SQLTXPLAIN Objects.
Regarding SQL_USER_ROLE.
This SQLT_USER_ROLE has also been stripped from system privileges preserving only two: ADVISOR and SELECT_CATALOG_ROLE. If granting those privileges to the application user through this role is a concern at your corporation, you can always strip this role from the application user when SQLT is not used by this user, or you can execute SQLT as SYS or any other DBA account.
What happens when you connect and execute SQLT as SYS or DBA instead of the application user that issued the SQL?
Well, you get almost everything, but what you don’t get is the plan produced by the EXPLAIN PLAN FOR command, which basically shows you the plan if you had disabled bind peeking. Not a big issue in most cases. So, if you don’t want to grant your applications users a couple of system privileges then don’t do it, and execute SQLT connecting as any DBA. You will also lose the EVENT 10053 Trace.
What about Trace Analyzer?
Trace Analyzer (MOS 224270.1) also known as TRCANLZR or TRCA for short, went through the same process. So TRCA 11.4.5.0 is also split into two accounts: TRCANLZR and TRCADMIN. Legacy TRCANLZR owns Tables, Indexes and Sequences, while TRCADMIN owns Views and Packages. Similar concepts as SQLTXPLAIN and SQLTXADMIN division of functions, but on a smaller scale. TRCA users must now be granted the new TRCA_USER_ROLE before executing the trcanlzr.sql script to analyze a Trace.
How this new “Security Model” affects SQLT or TRCA functionality?
The new security model is expected to have no effect in the functionality of these two tools. The only thing you may notice is that some operations that could be done connecting as SQLTXPLAIN cannot longer be done from this account. This means some operations like creating a SQL Plan Baseline for a SQL statement would require now a qualified account other than SQLTXPLAIN. But, for the most part like executing any of the main methods, a user connecting as such, should not notice any functional difference after 11.4.5.0 is applied.
Oracle SQL Tuning with Oracle SQLTXPLAIN
Stelios Charalambides book on SQLTXPLAIN is now announced in Apress website. You may want to check it out or pre-order it.
Mutating histograms, mutating number of rows, mutating blevel, mutating number of distinct levels… What do they mean?
Every so often I get an email from a SQLTXPLAIN user asking the meaning of these “mutating” observation from the health-checks engine on SQLT. On next SQLT release 11.4.5.0 (due on November 21, 2012), these messages will read “fluctuating” instead of “mutating”, but the health checks will remain the same. So, what do they mean?
Take “fluctuating number of histogram endpoints count”. What it means is the following: every time we gather CBO statistics on a Table, we have a new “version” of the statistics. We store the old one automatically (DBMS_STATS does that starting on 10g) and the new version becomes “current”. We store 31 days of history by default. So if we gather statistics on a Table lets say daily, we may have about 30 statistics versions for that Table and its columns (and Indexes). Now, if a column has lets say 5 buckets in its histogram and the next version has 6, there was a recorded change of 20%, which may be relevant for your investigation. If a column during its stored history has a number of buckets that fluctuates more than 10% from one version to the next, then it is flagged as “fluctuating” (use to be mutating in SQLT 11.4.4.8 and older). Changes of less than 10% are ignored.
Same concept applies to other elements like the number of distinct values in a column, or the height of an index, or the number of rows in a Table or Partition. If they fluctuate more than 10% from one version of the CBO statistics to the next, then they are flagged and reported in the Observations section of the main SQLT report.
Are we supposed to do anything with those health-checks? All depends. Lets say your SQL suddenly stop using index X and now uses index Y. If blevel on X has increased recently, you can suspect a cost increased due to the new blevel and investigate accordingly. Keep in mind that “Observations” section of SQLT, which reports outcome of health-checks, is basically a collection of heads-up. Things to investigate and maybe pointing to the root cause of a recent change in an execution plan for the SQL being investigated.
CBO Statistics on 11g: How to reduce poorly performing Execution Plans
Today, October 23 at 4:00 pm GMT (London time), Mauro Pagano will be presenting an interesting topic in CBO Statistics from Support perspective: what works and what almost works. This presentation is not that much about explaining features, but more about sharing Mauro’s experience regarding CBO Statistics. As many of you know, Mauro is one of the top Oracle resources when it comes to SQL Tuning hands-on within the Query Tuning team at Oracle Support.
This Webinar has registered over 600 participants, and I would not be surprise many more will join today. If you are interested please try to pre-register since the Webinar closes when it reaches 1000 participants.
Webinar will start at 4:00 pm GMT (London), 8:00 am San Francisco time, 11:00 am eastern time (Orlando!).
I (Carlos Sierra) will be there answering posted questions. Abel Macias and Steve Dixon will also join to help Mauro answering questions, which we expect to come in large numbers during the Webinar. This is a hot topic, since poor CBO Statistics causes more SQL to perform poorly than any other factor.
Link to register: WebEx
I hope you can attend live. If you cannot make it, you can always review the recording after a few days.
OOW12: Meeting Carlos Sierra face to face
This year I will be attending OOW. Besides presenting SQLTXPLAIN on October 4 at 12:45pm, I will be available at the Oracle Support Stars Bar in case you want to meet.
Moscone West, Lobby Level 2, Oracle Support Stars Bar for Oracle Database and Oracle Enterprise Manager.
- Mon, Oct 1 from 10:00am to 2:00pm
- Tue, Oct 2 from 10:00am to 2:00pm
- Wed, Oct 3 from 9:00am to 1:00pm
If you want to reserve some specific time please text me at 1.407.701.9355 or email me at carlos.sierra.usa@gmail.com, otherwise just stop by. We can chat about anything including SQLTXPLAIN or some SQL Tuning issue you may be working on. If the latter, please try to bring on a USB drive your SQLT files and I will review them with you.
So far I also plan to attend these two events (and any other cool one I get to hear about!):
- Oracle Bloggers Meetup 2012 (Wed, 3-Oct-2012, 5:30pm at Main Dining Room, Jillian’s Billiards @ Metreon)
- Oracle OpenWorld 2012 Tweet Meet! (Tuesday, October 2, from 4:30 p.m. to 6:30 p.m. at the OTN lounge)
I hope to see you there!
OOW OCT 4: SQL Tuning Made Easier with SQLTXPLAIN (SQLT)
Carlos Sierra will be presenting SQLT at OOW on October 4 at 12:45 at the Moscone West – 3022. If you are interested to attend this session please register soon. As of September 5 the assigned room is at 50% capacity. Also, once at OOW please verify room name since we may have had to move this SQLT session to a larger room. Please find below the abstract for this session.
SQL tuning is a daunting task. Too many things affect the Cost-Based Optimizer (CBO) when you’re deciding on an execution plan. CBO statistics, parameters, bind variables, their peeked values, histograms, and a few more are common contributors. The list of areas to analyze keeps growing. Over the past few years, Oracle has been using SQLTXPLAIN (SQLT) as part of a systematic way to collect all the information pertinent to a poorly performing SQL statement and its environment. With a consistent view of this environment, an expert on SQL tuning can perform more diligently, focusing more on the analysis and less on the information-gathering. This tool can also be used by experienced DBAs to make their life easier, at least when it comes to SQL tuning. Learn more in this session.
Carlos Sierra's Tools and Tips 