I’ve written earlier about how to calculate a query’s scalar subquery cache size, and I’ve had an interesting thought since the last blog entry. The last entry was all about scalar subqueries that appear in a query’s select clause, but… Read More
How to Get A Query’s Execution Plan (With All The Details)
In Oracle, if you want to view an execution plan for a query, you use the DBMS_XPLAN.DISPLAY_CURSOR() procedure. NOTE: The execution plan is totally different than an explain plan; don’t confuse these two. The easiest way to get an execution… Read More
Using DBMS_UTILITY.EXPAND_SQL_TEXT
If you have a query that contains views, or a query where the columns are not all qualified, the DBMS_UTILITY.EXPAND_SQL_TEXT function might be useful. The DBMS_UTILITY.EXPAND_SQL_TEXT function shows you what your query looks like without the views, and with all… Read More
How to Get Row Source Statistics In Oracle
One great way to get time analysis information for a query in Oracle is to use row source statistics. Not everyone has the tuning and diagnostic pack, meaning not everyone can generate SQL Monitor reports. Row source statistics serves as… Read More
How to Create a 10046 Trace File in Oracle
This article will show you how to create a 10046 trace file in Oracle. There are multiple different ways of creating a SQL trace file, including doing an alter session set sql_trace = true but if you want the most… Read More
How To Create A SQL Monitor Report
First of all, you want to ensure that you have the tuning and diagnostic pack for your database–if you do not have the tuning and diagnostic pack, Oracle does not authorize you to create SQL Monitor Reports. Important Information About… Read More
How to Tell If Statistics are Stale in Oracle
There are two ways to tell if statistics are stale in Oracle. One way is to allow Oracle to tell you if it thinks the statistics are stale. The other way is to compare the statistics of what Oracle thinks… Read More
Index Range Scan vs. Leading Wildcard
Can you use an index range scan with a LIKE if you have a leading wildcard in your expression? e.g. Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables,… Read More
The *LOWEST* Possible Cost For a Query
Quick: If you had to pick the least-expensive-cost query you could think of, what would that query look like? …(hums Jeopardy theme)…. Ding! Time’s up!! Most people respond with something “select from a one-block table.” Let’s give it a shot:… Read More
Are Single-Table Hash Clusters USELESS?!?
I remember being at an Oracle conference a few years ago, and I attended a session that was related to SQL Tuning. One of the things that was discussed in this lecture was Oracle Single Table Hash Clusters, and I… Read More