iggy_fernandez's picture

The Twelve Days of SQLT: Day Three: Just a Mess Without a Clue

P.O.I.S.E.D. is an acronym for a six-step performance tuning method; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. Every problem-solving exercise has these six stages, whether the exercise takes six minutes or six weeks.(read more)

iggy_fernandez's picture

The Twelve Days of SQLT: Day Two: Miles To Go Before We Sleep

SQLT adoption has been limited even though it is the best tool for investigating SQL performance: It is only available as a download from Oracle Support. It creates tables and PL/SQL procedures in the database. It does not support Statspack, only AWR. (read more)

iggy_fernandez's picture

The Twelve Days of SQLT: Day One: Do What You Do Do Well

In his fifteen years with Oracle Support, Carlos Sierra created and perfected a tool called SQLTXPLAIN (SQL Tuning and Explain Plan; SQLT for short) for SQL performance investigations. SQLT collects all the information that could possibly be required to investigate SQL performance including environment information, execution plan history, statistics history (system statistics, table statistics, index statistics), and object definitions.(read more)

mwidlake's picture

Pragma UDF – Some Current Limitations

There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions from SQL.

In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.

In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds went up to 0.33 seconds when the formatting SQL was put in a user defined PL/SQL function. This impact on performance is a shame as it is so beneficial to encapsulate business logic in one single place with PL/SQL. Stating that the PL/SQL function is a user defined one with the pragma UDF option reduced the run time to 0.08 seconds – which is removing most of the context switching overhead. Check out the prior post for full details.

iggy_fernandez's picture

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)

mwidlake's picture

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

{please see this second post on some limitations of pragma UDF in respect of IN & RETURN data types and parameter defaults}.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.

alexgorbachev's picture

The 2nd Annual PASS Summit Bloggers Meetup (2015)

class="l-submain-h g-html i-cf">

I’m excited to announce the second annual PASS Summit Bloggers Meetup! We began this last year and it was cool but this year will be even cooler!

What: PASS Summit Bloggers Meetup 2015 /> When: Thursday, October 29th, 5:30pm /> Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109. /> How: Please comment with “COUNT ME IN” if you’re coming — we need to know attendance numbers.

mwidlake's picture

Where do my trace files go? V$DIAG_INFO

Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, forgetful me.

V$DIAG_INFO has been available since oracle V11. All the trace files go into the Automatic Diagnostic Repository (ADR) by default.

mwidlake's picture


I think I’ve found an (admittedly obscure) bug with DBMS_RANDOM, group functions, PL/SQL and/or SQL.

Have a look and see if you also think this is odd – or have I missed the totally obvious?

(This is all on

{Update – my conclusion is, and thanks to Joel and Sayan for their comments, that this is not a “bug”. Oracle do not promise us how PL/SQL functions are executed due to the way SQL can be re-written by the parser. I think most of us stumbling over something like this would treat it as a bug though. You have to look at the column projection, again see the comments, to see how Oracle is deciding to get the columns derived by a naked call to DBMS_RANDOM.VALUE (by naked I mean no inclusion of parameters passed in and, significantly, no reference to columns). It’s just the way it is}

iggy_fernandez's picture

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 40: Why is it so hard to get SQL performance right the first time?

In the August 2015 issue of the NoCOUG Journal, we asked Stéphane Faroult why it is so hard to get SQL performance right the first time. His answer implies that SQL itself and the way it is taught are the problems.(read more)

To prevent automated spam submissions leave this field empty.
Syndicate content