Are my Stored Outlines being used? Why not?
April 26, 2007 2 Comments
I seem to be blogging today about loose ends from previous blog articles that I’ve not tidied up
Frustrations with Stored Outlines today.
Yesterday I mentioned how Stored Outlines offered a solution to fixing a SQL performance problem within an application generating SQL via an ORM layer (i.e. no or little control over the SQL concerned).
It all seemed so simple. Create outlines on a “good” database, export them, import them and enable the usage of them on the “bad” database.
The first attempt was fine with an outline against one bit of SQL. It did exactly what it said on the tin. However subsequent attempts have been less than perfect, much to my disappointment because it encourages “the fear” and vindicates the position of sceptics who would resist this approach. I am 99% sure that there is a simple example of user error at work here (where user=me).
This morning I had some inconsistencies in usage of the outlines and I put it down to TOAD. I’m pretty sure it was TOAD in that two apparently identical SQL statements (copied and pasted) run one from SQL*Plus and one from Toad had different hash_values in V$SQL. And I put it down to some sort of wrapper or something else internal to do with how TOAD itself works. Anyway, moving on from Toad.
There are three main “tools” that I have been using to figure things out (apart from Toad and SQL*Plus) – V$SQL, extended session tracing and the OUTLN.OL$ table.
V$SQL is cool because it has a column OUTLINE_CATEGORY which is some sort of indication of whether the SQL used a Stored Outline.
And OUTLN.OL$ is good because it because it shows me what outlines are available for what SQL in what outline categories.
But what I’m struggling with is that if the usage of Stored Outlines is enabled (via alter session set use_stored_outlines=%lt;category_name>) and if the outlines/outline categories were there in the OUTLN tables, and if the hash_value of a SQL statement in V$SQL and in the extended trace file was the same as that in the OUTLN.OL$ table, why would that not be used?
I dunno. Not yet at least. Further tracing and investigation required tomorrow. I know that they are not being used now (since my first couple of outline extractions) primarily because of performance of the SQL on the “bad” database. It’s bad. Plus I can see from V$SQL and the trace that it has not been used.
I am currently not sure whether tracing event 10053 shows any information about Stored Outlines. I will see tomorrow, can’t investigate further at the moment. However, from the tracing I did earlier I was coming across the fact that you can’t do a 10053 trace against SQL which is embedded in PL/SQL. Not in 8i at least.
Which then makes my investigation slightly more complicated / artificial because the SQL has got “:1″ bind variable placeholders in it. So I can’t run it via SQL*Plus because you can’t create a variable called 1. And I can’t change the application code. And whilst I have a PLSQL harness that does allow this, I then have the 10053 trace issue. News of tomorrow’s success to follow later, I hope.