The following piece of Pl/SQL uses a clever bit of math to be able to raise a number to a very large power without having overflow problems. Suppose, for example, you wanted to calculate 2318 % 367 (2 to the… Read More
Writing Efficient Pl/SQL is Hard
In the databases that I currently manage, PL/SQL functions are used everywhere. Honestly, one of the things I do when I try to get serious about tuning a SQL query is to (if it can be easily done) eliminate any… Read More
Selecting From a View: ORA-01031: insufficient privileges
If you’re selecting from a view in a different schema, and you’re encountering ORA-01031: insufficient privileges, this is probably why: You’re logged in under schema “A” You’re trying to select from a view owned by schema “B.” The view in… Read More
Calculating Scalar Subquery Cache Size, Part 2
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 Migrate a Baseline From One Database to Another
If you have a query that performs well in in one environment (such as dev) but performs poorly in a different environment (such as prod), it might be because Oracle selects a good plan in dev, and a bad plan… 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
Using PRAGMA AUTONOMOUS_TRANSACTION in a Function
Here’s a quick example of how to use the AUTONOMOUS_TRANSACTION pragma in a function: If you use the AUTONOMOUS_TRANSACTION pragma in Pl/SQL, it creates a separate transaction from the transaction you are currently working in. Because we included it in… Read More