Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Posts Tagged ‘SQLTXPLAIN

SQL Tuning Workshop I (updated schedule of sessions)

leave a comment »

I opened 3 sessions of the internal SQL Tuning Workshop I.

  • June 11-14, Orlando, FL
  • July 9-13, Toronto, Canada
  • July 30-August 3, Colorado Springs, CO

This intermediate to advanced class includes these topics:

  • Explaining the Explain Plan
  • Statistics Foundations
  • CBO Fundamentals I
  • Statistics Best Practices
  • CBO Fundamentals II
  • Extended Statistics
  • Dynamic Sampling
  • Event 10053 Tracing the CBO
  • Event 10046 Tracing the Execution
  • SQLT Foundations
  • SQLT Intermediate
  • Real-life Case Studies

I still have some seats available for Orlando and Colorado Springs. For registration please follow instructions on internal link.

Written by Carlos Sierra

May 29, 2012 at 2:43 pm

SQLTXPLAIN (SQLT) 11.4.4.5 is now available

with 4 comments

SQLT XTRSBY new method

If you have a poorly-performing SQL statement on a read-only standby Data Guard database, now you can analyze it with SQLT. Just need to install SQLT on Primary, create a database link into the read-only and use new method SQLT XTRSBY passing SQL_ID and db link name. Read sqlt/sqlt_instructions.html for more details.

In addition, there are two stand-alone scripts (with no SQLT dependencies)  that you can use directly on any read-only database. They are sqlt/utl/roxtract.sql and sqlt/utl/roxecute.sql. They mimic SQLT XTRACT and XECUTE but in a smaller scale. Notice that you can also use these two stand-alone scripts safely on any production environment since they do not execute any DDL and they perform queries only (no inserts, updates or deletes). What do you get from them? You get the basics: execution plans; peeked binds; CBO statistics for Tables, Table Columns, Indexes and Index Columns; SQL Monitor Active report; 10053 trace; 10046 trace and TKPROF on roxecute.sql.

If using SQLT XTRSBY on a Data Guard Primary database, you may also want to use roxtract.sql on the read-only standby database. Combined you will get more about your SQL. XTRSBY focuses on GV$* from standby plus DBA* from Primary. Script roxtract.sql only looks at standby.

As always, download latest version of SQLT from MOS 215187.1.

SQLT 11.4.4.5 changes:

Fixes:

  1. Removed annoying message on main “sqlt$a: *** t:plan_operation%: ORA-01403: no data found”.
  2. Test Case (TC) script q.sql now takes values of NULL on binds instead of continue searching for not NULL values. It also handles string “NULL” as NULL on dba_hist_sqlbind.

Enhancements:

  1. SQLT is now Data Guard aware. There is a new method SQLT XTRSBY which is executed on Primary and extracts from stand-by almost everything XTRACT does. Please refer to instructions before using this new method.
  2. In addition to new SQLT XTRSBY which runs in Data Guard Primary, there are two new scripts sqlt/utl/roxtract.sql and sqlt/utl/roxecute.sql which are super lite versions of SQLT XTRACT and XECUTE. The new read-only (RO) scripts can be used on any read-only database since they do not install anything and do not update anything. They provide a quick and safe view of the environment around one SQL statement.
  3. New instrumentation on SQLT XTRACT, XECUTE, XTRXEC, XTRSBY and XPLAIN. All these methods produce now a TKPROF for the actual execution of the tool. The new TKPROF is located in the log zip file. It helps to diagnose SQLT taking longer than expected.
  4. Skip MDSYS objects from metadata script. Since MDSYS is part of the data dictionary, it makes sense to skip it from Metadata as we do with similar schemas.
  5. Column remap on SQLT TC now considers map by QUALIFIED_COL_NAME when name is other than SYS%. This helps to match more columns when we are restoring CBO schema statistics while remapping to new TC user.
  6. Metadata includes now PL/SQL library which executed the SQL being analyzed. This is useful to actually see the piece of program that invoked the SQL being analyzed.

Written by Carlos Sierra

May 3, 2012 at 7:56 am

Posted in SQLTXPLAIN (SQLT)

Tagged with ,

Free Webinar: Using SQLTXPLAIN to diagnose SQL statements performing poorly

with 8 comments

SQLTXPLAIN (SQLT) is a free tool to diagnose SQL statements performing poorly. If you have used it you know its installation and use is simple (understanding the output may still be overwhelming). But if you have never used it, you may feel intimidated by this tool. I will be doing a 1hr Webinar in how to install SQLT and how to use its main methods: XTRACT, XECUTE, XTRXEC, XTRSBY and XPLAIN. I will allocate 45 minutes for presentation and demo, followed by 15 minutes for a Q&A session. This Webinar is free but its capacity is limited, so if you want to attend please register early. It will be on May 15 at 11am (UTC-05:00) Eastern Time (US & Canada).

Written by Carlos Sierra

April 28, 2012 at 7:26 am

Posted in SQLTXPLAIN (SQLT)

Tagged with ,

Custom SQL Profile and Plan Stability on 10g

with one comment

SQLTXPLAIN includes into the sqlt/utl sub-directory a script named coe_xfr_sql_profile.sql. This script inputs a SQL_ID, then it asks to choose from a list of known Plan Hash Values (PHV), and after a few seconds it outputs another script that contains the Outline of the PHV together with the SQL Text for the given SQL_ID.

These are some of the things we can do with the script generated by coe_xfr_sql_profile.sql for your SQL_ID/PHV:

  1. If we execute the generated script with no modifications into the same system, then it creates a Custom SQL Profile with the Outline of the plan from given SQL_ID/PHV. This Plan is independent of CBO Statistics, meaning your Plan will be stable even if you gather new statistics (or delete them).
  2. If we execute this generated script into a similar system (one with same schema objects), then it creates the Custom SQL Profile from the source system into the target system. That means we basically migrated the PHV into another system. Think on this: “DEV instance exhibits a good-performing Plan. PROD only generates a poorly-performing Plan. You want to quickly migrate the good Plan from DEV into PROD”.
  3. You can only generate a good-performing Plan if you place a CBO Hint in your SQL, but you cannot modify the application. So, you execute a modified version of your SQL which includes the CBO Hint, then execute coe_xfr_sql_profile.sql for this modified SQL. When you open the generated script you remove the CBO Hint from the SQL Text section. Then this newly generated and modified script contains the original SQL without the Hint and the Outline of the Plan with the Hint. When executed either on the source system or a target system, it creates a custom SQL Profile on it. This Profile associates the original SQL with the good-performing Plan.
  4. Your original SQL contains a CBO Hint or set of hints and it produces a poorly-performing Plan. When you remove them you notice you get a good-performing Plan, so you execute  coe_xfr_sql_profile.sql for the modified SQL (the one with the hints removed). Then you open the generated script and replace on it the SQL Text with the original one (with the hints on it). This generated and modified script, when executed on the source or target system, will create a Custom SQL Profile for the original SQL with the good-performing Plan.

If after you create a Custom SQL Profile with coe_xfr_sql_profile.sql you change your mind and want to drop such Profile, you will find the drop command on the same script that was generated by coe_xfr_sql_profile.sql containing your Profile.

This coe_xfr_sql_profile.sql script uses API DBMS_SQLTUNE.IMPORT_SQL_PROFILE, which is not documented.

A word of caution:

SQL Profiles are basically a set of CBO Hints associated to a SQL Text through a “signature”. This signature is a hash function on the SQL Text. If a Hint from the Profile cannot be applied, it does not invalidate the entire Profile. In other words, the CBO will still try to apply as many Hints as possible. Imagine that one of the Hints refers to an Index, and the Index is dropped, then the Hint will get silently ignored. This may produce sub-optimal Plans.

Another consideration is the effect of a Virtual Private Database (VPD), which appends some dynamic predicates to your SQL. In this case, the Plan produced with the Profile my not be optimal.

If your database is 11g or higher, you’d rather want to use SQL Plan Management (SPM), which provides better Plan Control functionality.

Written by Carlos Sierra

April 9, 2012 at 8:13 am

What is SQLTXPLAIN?

with 53 comments

OK, I will start with the basics…

SQLTXPLAIN, also known as SQLT for short, is a tool I developed by myself and to myself. At least that was the idea when its name was coe_xplain.sql. That was long ago… like in the past millenia or so.

Today, SQLT is a tool that Oracle uses to diagnose SQL statements performing poorly. So, it went from a single-user tool into one that is used by hundreds of engineers at Oracle. Thanks to the valuable contribution of ideas from some of the brightest minds at Oracle Support in the area of query tuning, SQLT was reborn between the years 2008 and 2012. In particular I thank Abel Macias, Mauro Pagano and Mark Jefferys for their constant flow of ideas and by reporting to me my own flaws. I also thank the more than 650 participants of my two SQL Tuning Workshops, since it is in these sessions where I have learned about most of the enhancements I have implemented over the past 3 years or so.

Anyways, back to the question “What is SQLTXPLAIN?”. This is a tool developed in PL/SQL that inputs one SQL statement and output a zip file that contains many pieces. Some are for diagnostics, and some for further use as a Test Case (TC) is created. So, when someone at Oracle asks for a SQLTXPLAIN, basically the request is to install this tool and execute one of its main methods in order for the requestor to analyze your issue.

SQLT does not come pre-installed, nor it comes with the product. To get it to work, you need to download it first from My Oracle Support (MOS). You can find it under Doc ID 215187.1. After you download it, please unzip it and read included file sqlt_instructions.html. These instructions are going to basically ask you to copy this sqlt.zip file to the database server, unzip it there again, and execute sqlt/install/sqcreate.sql connected as SYS. Provide a value to the inline parameters, and SQLT should be installed in a few minutes. If you notice the sqlplus session exists then something went wrong. Read the *.log files and look for errors. Fix them or at least try. If you get stuck, contact the tool author (me).

Once SQLT is installed then you are ready to use it. Start with method XTRACT. Refer to the instructions provided with the tool. Basically, connect into sqlplus as the application user and execute sqlt/run/sqltxtract.sql passing the SQL_ID of the SQL that you want to analyze. SQLT XTRACT will generate a zip file. Open it up and look for file sqlt_sNNNNN_main.html. Try to make sense out of it. Start with links on SQL Text, Plans Summary and Observations. Then take it from there.

What you will see in the main html report is basically the entire environment around your SQL. I mean in terms of performance. Everything that may affect your SQL performance: from CBO statistics, to parameters, to bind variables, etc. You will find details about cursor sharing, SQL Plan Management (SPM), SQL Profiles, Stored Outlines, etc. Tons of details. Not everything is used every time. What is important is that everything is there in case you need it. Even more, it is there in case someone with more expertise in SQL tuning may need it later on.

SQLT works on RAC. It works also on a distributed environment. It will soon work on data-guard  (stand-by read-only) databases. If you want to see a sample of its output, download it from same MOS 215187.1. Feedback on this tool is always welcomed.

Written by Carlos Sierra

April 3, 2012 at 4:24 pm

Posted in SQLTXPLAIN (SQLT)

Tagged with ,

SQLT 11.4.4.4 is now available

leave a comment »

SQLTXPLAIN (SQLT) 11.4.4.4 was made available on April 2, 2012. Find this tool under MOS 215187.1. It contains 2 fixes and 37 enhancements:

Fixes:

  1. Peeked and Captured Binds in Execution Plan of MAIN was showing :B1 where predicate was “COL=:B10” (false positives). Fix display of bind peeking and bind capture when SQL contains binds like :b1 and :b10.
  2. Metadata script includes now creation of Statistics Extensions.

Enhancements:

  1. New HC when derived stats are detected on a Table or Index (GLOBAL_STATS = ‘NO’ and PARTITIONED = ‘YES’).
  2. New HC when SQL Plan Baseline contains non-reproducible Plans.
  3. New HC indicating sizes of SQL Plan History and SQL Plan Baseline (enabled and accepted Plans).
  4. New HC when there is an enabled SQL Profile and there are VPD policies affecting your SQL. Plan may be unstable.
  5. New HC when there is more than 1 CBO environment in memory or AWR for given SQL.
  6. New HC when Indexes or their Partitions/Subpartitions have UNUSABLE segments.
  7. New HC when Indexes are INVISIBLE.
  8. New HC when an Index is referenced in a Plan and the index or its partitions/subpartitions are now UNUSABLE.
  9. New HC when an Index is referenced in a Plan and the index is now INVISIBLE.
  10. New HC when Table has locked statistics.
  11. New HC when INTERNAL_FUNCTION is present in a Filter Predicate since it may denote an undesired implicit data_type conversion.
  12. New HC when Plan Operations have a Cost of 0 and Cardinality of 1. Possible incorrect Selectivity.
  13. New HC when SREADTIM differs from actual db file sequential read for more then 10%.
  14. New HC when MREADTIM differs from actual db file scattered read for more then 10%.
  15. New HC when BLEVEL has changed for an Index, an Index Partition or an Index Subpartition according to statistics versions.
  16. New HC when NUM_ROWS has changed more than 10% for a Table, a Table Partition or a Table Subpartition according to statistics versions.
  17. New HC when Index is redundant because its leading columns are a subset of the leading columns of another Index on same Table.
  18. New HC when leaf blocks on a normal non-partitioned index are greater than estimated leaf blocks with a 70% efficiency.
  19. Active Session History sections on MAIN report include now up to 20 sessions and 20 snapshots (it was 10 and 10).
  20. Parameter _optimizer_fkr_index_cost_bias has been added to SQLT XPLORE.
  21. SQLTPROFILE and script coe_xfr_sql_profile.sql take now SQL statement with SQL Text larger than 32767 characters.
  22. Add metrics similar to what we have now on summary tables/indexes on SQLHC.
  23. Tables and Indexes sections on MAIN contain now links showing object counts instead of a constant. Similar to SQLHC.
  24. Execution Plans on SQLT to show with mouse-over, schema statistics for both: current and as per plan timestamp.
  25. Add new columns on 11.2.0.3 for all V$, GV$ and DBA views accessed by SQLT.
  26. Include reason WHY a cursor is not shared (out of XML “reason” column on gv$sql_shared_cursor).
  27. MAIN report heading includes now a link to MOS SQLT document.

Written by Carlos Sierra

April 2, 2012 at 2:40 pm

Posted in SQLTXPLAIN (SQLT)

Tagged with ,

SQLTXPLAIN 101

with 3 comments

SQLTXPLAIN, also know as SQLT, is a tool that helps to diagnose SQL statements performing poorly in an Oracle database. This tool is free to download and use. You only need to have access to My Oracle Support (MOS) and search for Document Id: 215187.1.

Q: What do you get from this tool?

A: In short, everything you want to know about one SQL statement if you intention is to analyze this SQL and eventually tune it. When you use this SQLT tool on a SQL statement, it generates many files then compresses them all into one zip. Always start your analysis reading the file with name sqlt_sNNNNN_main.html.

Written by Carlos Sierra

April 1, 2012 at 1:48 pm

Posted in SQLTXPLAIN (SQLT)

Tagged with ,