Diagnostic tools
Additional Information on SQL Patches
Our last post on SQL patches generated a lot of comments and questions. Today I will address a couple of questions that are quick to answer. For those that require more discussion, we'll post more details in the next few weeks. 1) Do we need additional licenses or the SQL diag pack to use this? No. No additional licenses are needed to use SQL Repair Advisor or SQL patches. SQL Repair Advisor is available as part of Oracle Database Enterprise Edition 11g. 2) Can I supply a...
Friday, March 2, 2012 | Diagnostic tools | Read More
Using SQL Patch to add hints to a packaged application
From Oracle Database 12c Release 2 onwards there's a public API call to create SQL patches using DBMS_SQLDIAG.CREATE_SQL_PATCH. If you're using this release you should check out this post too. In the last post, I showed how you can use the BIND_AWARE hint to skip the monitoring phase of adaptive cursor sharing. If you have a packaged application, you might be wondering how you can use this hint if you can't edit a query directly. In this post I'll who you how to do just...
Monday, February 27, 2012 | Diagnostic tools | Read More
Capturing 10053 trace files continued
In our previous blog post I described how you can use the new diagnostic event infrastructure in Oracle Database 11g to capture an Optimizer trace (10053) for any SQL statement once you have its SQL_ID. The approach I showed using the traditional ‘Alter session set event’ approach and thus required you to execute the SQL statement. What I forgot to mention (and have been scolded for) was that you can also use the new infrastructure to generate an Optimizer trace for any SQL...
Friday, December 2, 2011 | Diagnostic tools | Read More
What's Changed between my New Query Plan and the Old One?
In most cases the first step in debugging a performance problem caused by a plan change is to visually inspect both of the execution plans generated by the query optimizer. Usually the customer has a known plan that performed well and the new plan that performs worse. Visual inspection of plans is easy when the query is not too complex but becomes a tedious exercise when the query is complex (involving tens of joins, sub-queries, views, etc).This article introduces a new plan...
Tuesday, September 1, 2009 | Diagnostic tools | Read More
Oracle keeps closing my TAR because I cannot provide a testcase, can you help?
The answer to this question is yes. The Oracle Database provides a diagnostic tool called SQL Test Case Builder. In this article, we explain what SQL Test Case Builder is, and how to use it with examples.Why SQL Test Case Builder? For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to...