Archive for the ‘SQLTXPLAIN (SQLT)’ Category
SQLTXPLAIN capabilities – free webinar for Oracle partners (Wednesday 27)
This Wednesday, February 27 at 8 am Pacific Time (11 am ET), I will deliver a one hour webinar to Oracle partners. The topic is about SQLTXPLAIN capabilities and how this tool helps to diagnose SQL statements performing poorly. Details are here. Format is 45 minutes of presentation and demo, followed by 15 minutes for a Q&A session.
SQL Tuning Workshop at the NoCOUG
I am delivering a two-days SQL Tuning Workshop at the Norther California Oracle Users Group (NoCOUG) in Pleasanton, California. Today is the 2nd and last day of this class. We discussed yesterday: Cost-based Optimizer (CBO) Statistics, Event 10053, Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM). We will discuss today some SQL Tuning tools: Event 10046, Trace Analyzer TRCANLZR, SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) and SQLT Test Case (TC). There are around 35 participants and I would say the level of the class is between intermediate to advanced. Most participants are seasoned DBAs and some have a good level of expertise as Developers. Abel Macias is participating in this session as my co-instructor. He is bringing to class his extensive expertise in SQL Tuning. It is always a challenge to keep the attention of such a large and diverse group, but I would say the level of participation has been quite high. I appreciate all the effort from several contributors of the NoCOUG who have made this training possible. My special thanks to Gwen Shapira, Randy Samberg, Iggy Fernandez and Eric Hutchinson. It is very rewarding to see how Oracle Users Groups are putting together events like this where we can openly share knowledge with our Oracle users community.
I will speak at the actual NoCOUG Conference this Thursday. I will talk about the content of SQLTXPLAIN (SQLT) main report, which is quite comprehensive. I will go over some samples and I will allocate plenty of time for questions during and after my presentation. These days SQLT is widely used within Oracle and now by several enterprise Oracle customers.
Stelios Charalambides book about SQLT Tuning and SQLTXPLAIN will be released on March 20. The title is Oracle SQL Tuning with Oracle SQLTXPLAIN. This book can now be pre-ordered in Amazon. If you want to learn more about how to use SQLTXPLAIN to diagnose SQL Tuning issues you may want to get a copy of this book.
After NoCOUG I will spend one week at the office, where I will deliver a live Webinar in SQL Tuning to our Oracle Partners. Then my next conference is the popular Hotsos Symposium in March 3-7. In my humble opinion, this annual Hotsos event is the single most relevant in the area of Oracle Performance. Thus I am very excited to have the opportunity to speak there. A real honor!
RMOUG Training Days February 12 (day 2)
Second day is over now. I attended several interesting sessions. The one I liked the most was about the role of women in technology. I am so glad RMOUG provides now this kind of session! I think we need to encourage women to fill positions that are still dominated by men, like speaking at conferences. If you consider that 40% of DBAs are women while only 11% are speakers, you get to understand something can be improved here…
Today, I will deliver two one-hour sessions:
- How to improve SQL Performance with new SQL Health Check Tool. Wednesday, February 13 at 8:30 am. Room 4a.
- How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN. Wednesday, February 13 at 1:30 pm. Room 403.
I am so happy to participate as a speaker at the RMOUG! My reward is simple: to see participants nodding and smiling when a concept clicks in, or when they get an answer to a question that has been boiling in their heads for some time. After 17 years at Oracle and having learned from so many smart guys, I am lucky to be in a position where I can answer many questions. Still I say “I do not know” when asked something I do not know, but when it comes to SQL Tuning, I feel pretty comfortable with most subtopics. I notice a great interest on Plan Stability and SQL Plan Management in particular. I will be offering sessions in this area within SQL Tuning…
Looking forward to a great third and last day of this event today!
RMOUG Training Days February 11 (day 1)
First day of the RMOUG Training Days February 11-13, 2013 is over. I had the honor to present Using SQLTXPLAIN (SQLT) to Diagnose SQL Statements Performing Poorly. I had 14 participants and we were able to go into the details of several topics that were raised by this small group. We covered all the material I had prepared. We also covered SQL Plan Management (SPM) in a live demo branched from the SQLT core presentation and demos. The most rewarding part for me was watching the smiles in the faces of most participants. I think everyone got something out of this session.
I wanted to share my personal experience at this great conference. In short, I am having a blast! From being picked up at the airport by Prashant Khanolkar and sharing the ride with Cary Millsap and Faun deHerry; to speaking at the pre-conference event; and sharing a beer (or two) with other speakers. Just being here at this RMOUG event has been an awesome experience that I highly recommend to all! Last night, I had the chance to socially share with well recognized personalities, including: Kerry Osborne, Maria Colgan, Karl Arao, Kellyn Pot’Vin, Kyle Hailey, Jeff Jacobs, Andy Colvin, Faun deHerry and Scott Spendolini. I also had the change to meet or meet for the second time: Tim Gorman, Mogens Nørgaard, Ken Johnson, David Stokes, Lisa Cocklin, George Trujillo and Galo Balda. I met a lot more nice people but I am not so good with names to remember them all, specially this early in the morning…
It feels like yesterday when I participated at the Hotsos Seminar on March 2012. I was so intimidated just by thinking that some of the Oracle performance key players were there, and they would actually might sit in my session… I feel deep respect for most of the professionals that gather and are regulars at these events. What I learned at Hotsos 2012 was that most of these “big shots” were so approachable and down to earth. It was a big relief since I had been learning from them for years, through their books and blogs. For some reason I always thought they would be too busy to talk to someone like me… How wrong I was! That Hotsos 2012 event almost a year ago was the inspiration I needed to submit topics and try to participate in other similar events. RMOUG has had the same friendly, open, welcoming environment I discovered at Hotsos. My kudos to RMOUG organizers for making this event an awesome experience! As long as Oracle is willing to sponsor my travel expenses I will keep submitting topics to the RMOUG annual meeting… and Hotsos, and NoCOUG, and ECO, and some others…
Now, it is time for some workout before enjoying a busy day at the RMOUG!
2-days open SQL Tuning Class in Pleasanton, CA this month!
I will be delivering a two-days SQL Tuning class on February 19-20 in Pleasanton, CA. It is available to registered Oracle Users thanks to the help from NoCOUG. Space is limited, so if you want to attend you need to hurry up.
This class covers:
- CBO statistics (fundamentals and good practices);
- Tracing with Events 10046 and 10053;
- Plan Stability with Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM);
- SQL Tuning tools: Trace Analyzer TRCANLZR (TRCA), SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC); and
- SQL Tuning Test Cases (creation and implementation).
This is the first time this 2-days class is offered outside Oracle. Its content is an extract of the internal SQL Tuning Workshops I have developed and delivered to over 700 Oracle Engineers around the globe.
This is not an Oracle University (OU) class, which means the scope is more into the actual trades of the day-to-day life of SQL Tuning and less into product features. So if you have taken a SQL Tuning OU class, then this 2-days session would be a nice addition.
Another thing that you should expect from this class is lots of participation. In my sessions I always encourage participants to bring to the table their current SQL Tuning issues or concerns, so we can openly analyze and discuss them during class. The environment is friendly, welcoming and open.
I hope you can join us on February 19-20.
Materialized Views Health Check and Diagnostics Reports
About mvhcdr.sql
I uploaded to My Oracle Support (MOS) under document_id 1517362.1 a new script mvhcdr.sql to aid in the diagnostics of issues regarding Materialized Views (MV). This script installs nothing in the database and it produces a zip file with 5 zip files within. The output includes HTML and CSV files from all DBA views related to Materialized Views, as well as Metadata for the MVs and their Logs. Output also includes some Health-Checks like: MV Logs with Outdated Statistics, MV Logs larger than their Master Table, Complete Refresh Candidates and Old Materialized Views. A list of Materialized Views capabilities is also included within the set of output reports, as well as Logs per MV and MVs per Log with some important attributes. CBO Statistics, actual row counts, segment sizes in blocks, indexes, partitions and similar related metadata is included as well. The output of mvhcdr.sql is quite comprehensive. You can find an output sample under same MOS 1517362.1.
The main reason to have this new script is to aid Oracle Support to collect diagnostics information around Materialized Views. It can also be used by any Oracle user that may want to analyze the health of the MVs on a system, or two compare their setup to a similar database. Another use is to simply document all these MVs on a database, together with their related metadata.
You can download mvhcdr.sql from its own MOS note, or from SQLTXPLAIN 11.4.5.4 or higher. If the latter, you can find mvhcdr.sql under directory sqlt/utl.
This new script mvhcdr.sql can be used on 10g and 11g databases. You execute it connected as SYS. It has a couple of pre-requisites and just one execution parameter. The output gets generated under the same local directory from where you executed SQL*Plus.
Pre-requisites
Tables PLAN_TABLE and MV_CAPABILITIES_TABLE must be available. Else, create them with following scripts:
- PLAN_TABLE: $ORACLE_HOME/rdbms/admin/catplan.sql; or $ORACLE_HOME/rdbms/admin/utlxplan.sql;
- MV_CAPABILITIES_TABLE: $ORACLE_HOME/rdbms/admin/utlxmv.sql;
The MV_CAPABILITIES_TABLE is used to generate and report MV Capabilities as the name implies. The PLAN_TABLE is used as a staging repository for the duration of the execution of mvhcdr.sql.
Execution Parameters
- Materialized View name or prefix (optional, defaults to %)
Deep-dive on SQLTXPLAIN offered externally for the first time
The Rocky Mountain Oracle Users Group (RMOUG) Training Days is approaching soon: February 11-13, 2013. I hope you are attending this Oracle relevant event. There are so many good sessions that it is hard to decide which ones to attend!
For the first time, I will be conducting a 4-hours deep-dive external-session on SQLTXPLAIN (SQLT). The title is Using SQLTXPLAIN (SQLT) to Diagnose SQL Statements Performing Poorly. If you want to know SQLT from the basics to an intermediate level, consider attending this 4-hrs session. It will be offered on Monday, February 11 from 1 to 5 pm at the Colorado Convention Center in Denver, Colorado (USA). I love interactive sessions, so I will encourage voluntary participation and open discussions.
In addition to the 4-hrs deep-dive on SQLT, I will also speak on Wednesday, February 13 at 8:30 am about How to Improve SQL Performance with New SQL Health Check Tool. This is a 1-hour seminar with plenty of time for Q&A.
If you attend the event and want to discuss any issue with regard to SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) or simply SQL Tuning, please approach me. I will be there to help, share, listen, learn, discuss, etc.
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.
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.
Carlos Sierra's Tools and Tips 