Materialize

Summary – Note the recursive SQL, the association of an in-memory temporary table with a child cursor, and possible side-effects for distributed transactions.

Prompted by a recent thread on the OTN forums, if you /*+ materialize */ a subquery will you always get IO associated with that materialisation?

Short answer: Yes. For that is what materialisation is all about.

A longer answer is perhaps slightly more interesting.

In terms of materialisation, you can force it with the hint above or it will automatically kick in if you reference it at least twice. I’m not aware of this threshold being documented but Jonathan Lewis mentioned this observation here and it ties in with what I’ve seen.

And it doesn’t seem to matter how small the result set is, it will always be materialised if those materialisation criteria are met.

If we trace a new query, we can see some of the recursive sql involved.

Tracing older versions of Oracle can be more revealing because in 9iR2 for example the trace file explicitly lists the recursive INSERT into the temp table, whereas by the time you get to 11.2 the INSERT has disappeared and the associated waits incorporated into the SELECT.

All versions list the creation of the temporary table (if indeed it needs to be created – see below), the DDL for which includes the specifications IN_MEMORY_METADATA and CURSOR_SPECIFIC_SEGMENT.

Note in the creation of table SYS_TEMP_0FD9D6610_8A97FC that 0FD9D6610 is the hex of 4254950928, which is just the sequence-based objno. Not sure of the significance of the last part, e.g.8A97FC.

We can also see that the data is written to temp using a direct path write/direct path write temp depending on version … and selected back via the buffer cache (for efficient use of the data) using a db file sequential read or db file scattered read.

In older versions as mentioned, you should find the recursive INSERT listed separately, e.g. (different database, different version, different temp table name and if you’re interested in the control file sequential read see this post by Timur Akhmadeev):

Note that the temp table is associated with the child cursor. This can be observed by using multiple sessions and forcing the creation of multiple child cursors – for example by using different optimizer settings – and tracing those sessions.

Subsequent executions of this cursor – by this session or another – can reuse this existing in-memory temporary table with no need to recreate it.

So, if we ran into one of the numerous situations that exist – often caused by bugs – where there are excessive child cursors for sql statements, if these use materialised subqueries then this is something else to be slightly concerned about.

If the cursor ages out or we flush the shared pool, the table will be cleaned up along with the cursor.

This recursive creation of the temp table might raise some interesting questions. For example, how this (recursive DDL) might affect / be affected by transactions?

Short answer: It does and it doesn’t

The longer answer is that it only seems to affect distributed transactions and this effect is apparently a bug or bugs, separately listed in both 10.2 – bug 9399589 – and 11.1/11.2 – bug 9706532.

I’ve not tested the proposed patches to the issue, but certainly what happens in 11.2.0.3 is that if you hard-parse the statement as part of a distributed transaction, then the materialisation is silently bypassed (fixed in 12c)

Whereas if it’s a local transaction that does the hard-parse then materialisation can be used and subsequent executions of that cursor in a distributed transaction can make use of that plan and the existing temp table.

I saw on Twitter last week and this week that @Boneist had an interesting experience with this sort of thing.

Finally, as a quick related distraction, note that if you try to get a real time sql monitoring report within a distributed transaction – I mean, why would you? but anyway I found this whilst investing the distributed behaviour above – then it will bomb out with ORA-32036: unsupported case for inlining of query name in WITH clause.

Like this:

Related

15 Responses to Materialize

Yes, it was a highly interesting experience! Well, I might have used another word for it, but hey ho *{;-)

Re. “Whereas if it’s a local transaction that does the hard-parse then materialisation can be used and subsequent executions of that cursor in a distributed transaction can make use of that plan and the existing temp table.”

That wasn’t my experience – when the connection that was logging in with the distributed connection set, it got a different plan, even though I had run the query in a local transaction and had obtained the materialzed plan!

I’ve shown above that in 11.2.0.3 at least that a distribution transaction can re-use an existing materialisation plan. It may be that things change between versions – you were 10.2 I believe? – or of course there can be other reasons why existing cursors cannot be shared.

Your issue was good timiong for me because, whilst doing this post, I’d raised the question to myself about when this temp table creation might be an issue and had looked at a few local transactions and seen no issue. Distributed transactions hadn’t even crossed my mind.

Aah, yes, of course; the version difference might mean different behaviour! D’oh.

It would be even more tricky to have diagnosed if the “good” plan could have been reused I think; at least the behaviour was consistent on my db – when it was called through the python script, my SQL refused point blank to materialize, even with an existing materialized plan already there!

Not that that means anything other than materialisation may be affected inconsistently by distributed transactions depending perhaps on version and a bunch of other unknown things.

Your bug – 9399589 – talks about an issue with a global transaction or just the setting of attributes OCI_ATTR_EXTERNAL_NAME / OCI_ATTR_INTERNAL_NAME.

The other bug – 9706532 – is 11g specific, talks about the issue with OCI_ATTR_EXTERNAL_NAME being resolved by a separate fix in 11.2.0.1 but mentions that materialization will still fail for a PLSQL RPC over a DB LINK.

What I showed above is not a PLSQL RPC call over a DB LINK, so it might be related, might be not.

As you say, having a consistent issue – i.e. with consistently different plans – was probably helpful. Then you only need to ask the question why? Rather than two questions – 1. Why? and 2. Why only sometimes?

“Not that that means anything other than materialisation may be affected inconsistently by distributed transactions depending perhaps on version and a bunch of other unknown things.”

I wouldn’t mind if the decision was logged somewhere… it certainly wasn’t even mentioned in the 10053; it would have been lovely to have something that said “Can’t materialize” instead of it silently not doing it!

“No-one likes an intermittent blighter.” *Amen* to that!

My issue didn’t involve database links either, just python connecting to a database. I’m not convinced that those two attributes are not set, but we could not find them – they weren’t in the python scripts, or the environment/sys variables on the server!

Dominic,
It took me 3-4 reading before understanding the basic problem :) Does it means that verification of plans from Distributed transactions needs extra attention as may due to hitting the bug we get wrong plan. In such scenarios , filing a bug with Oracle is only solution or something else

Depends – if materialization is key to a piece of code being performant and that code is used in distributed transactions, then this could be an issue for you.

But then again combining different features often reveals edge cases and issues.

All depends on why the materialization is effective.

Although the main benefit of using materialisation is meant to be a reduction in IO for repeated subqierues, I think a lot of the times people see a benefit because they’re using it as a substitute for an orthodox subquery and perhaps no_merge or no_unnest depending on the code.

Recently I was trying to understand why an insert/select that was working well in 10g starts failing in error in 11g. It is an insert into a table(t) using a function(f) that selects from this inserted table(t). Until I reached the point stating that if you use the /*+ materialize */ hint in this kind of situations the 11g insert/select statement will work without error.

It seems that, thanks to the /*+ materialize */ hint, my insert into table (t) is not selecting directly from the function (which is selecting from table t) but it is selecting from the Global Temporary table created, behind the scene, in response to the /*+ materialize */ hint.

My tests exactly repeated your results on 11.2.0.3 with the global transaction plan materialization on remote instance over db link – I found your note a bit late
In addition, the baseline creation (for recursive sql in remote instance) does not help too – oracle behavior remains the same, which is expected bug result, as I understand