Poll: SQL Plan Management

I’m interested in what SQL Plan Management features people are actively using.
Read more of this post

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.

SQL> alter session set events '10046 trace name context forever, level 12';
SQL>
SQL> with x as
  2  (select /*+ materialize */
  3          1 col1
  4   from   dual)
  5  select * from x;

      COL1
----------
         1

SQL> alter session set events '10046 trace name context off';
SQL>

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.

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6610_8A97FC" ("C0" NUMBER )
   IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950928 )
  NOPARALLEL

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):

INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO 
  "SYS"."SYS_TEMP_0FD9D662B_671BC5CD" SELECT /*+ */ 1 FROM "SYS"."DUAL" 
  "DUAL"

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    3        0.00          0.00
  direct path write                               1        0.00          0.00
********************************************************************************

Otherwise in newer versions, no insert but the waits for the recursive statement listed as part of the main select:

with x as
(select /*+ materialize */
        1 col1
 from   dual)
select * from x 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path sync                                1        0.02          0.02
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  SQL*Net message from client                     2       14.45         14.45
********************************************************************************

The temp table exists in memory and our session and other sessions cannot describe it but can select from it:

SQL> desc  sys.SYS_TEMP_0FD9D6610_8A97FC;
ERROR:
ORA-04043: object sys.SYS_TEMP_0FD9D6610_8A97FC does not exist


SQL> select * from sys.SYS_TEMP_0FD9D6610_8A97FC;

no rows selected

SQL> 

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.

SQL> alter system flush shared_pool;
SQL> select * from sys.SYS_TEMP_0FD9D6610_8A97FC;
select * from sys.SYS_TEMP_0FD9D6610_8A97FC
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 

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.

SQL> alter system flush shared_pool;
SQL> -- distributed transaction
SQL> insert into t1@test values(1);
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> commit;
SQL> -- no distributed transaction
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1
SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> 


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.

SQL> alter system flush shared_pool;

System altered.

SQL> -- no distributed transaction
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 3267439756

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |     4 (100)|
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |
|   2 |   LOAD AS SELECT           |                           |       |       |            |
|   3 |    FAST DUAL               |                           |     1 |       |     2   (0)|
|   4 |   VIEW                     |                           |     1 |     3 |     2   (0)|
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6604_8B16D2 |     1 |    13 |     2   (0)|
---------------------------------------------------------------------------------------------


18 rows selected.

SQL> -- distributed transaction
SQL> insert into t1@test values(1);

1 row created.

SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 3267439756

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |     4 (100)|
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |
|   2 |   LOAD AS SELECT           |                           |       |       |            |
|   3 |    FAST DUAL               |                           |     1 |       |     2   (0)|
|   4 |   VIEW                     |                           |     1 |     3 |     2   (0)|
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6604_8B16D2 |     1 |    13 |     2   (0)|
---------------------------------------------------------------------------------------------


18 rows selected.

SQL> 

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.

What job runs AWR snapshot? Eh?

There are a surprising number of online resources propagating the idea that the collecting of AWR snapshots is somehow connected to the GATHER_STATS_JOB (which was responsible for automatically gathering stats in 10g).

Eh?

Collecting AWR snapshots is one of the responsibilities of the background process MMON.

If you have a problem with the automatic snapshots:
– Check whether you can manually snap using DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT.
– Check MMON and alert log / trace files for related messages.
– See Metalink note 1301503.1: “Troubleshooting: AWR Snapshot Collection issues”

This came up in a question on the OTN forums where the OP said that AWR was not snapping automatically but the GATHER_STATS_JOB seemed to be ok. And the obvious first question would be where did you get that idea from?
Sometimes you can’t win on the forums. If you ask a question, it’s not uncommon to be pointed to a google search. But if you google something, you can’t trust everything that’s out there – it gets outdated and sometimes it never was accurate.

MERGE oddity

Here’s a little observation on some slightly odd behaviour with MERGE and some apparent inconsistency of error ORA-30926 (prompted by a question on the otn forums).

If you’re not familar with ORA-30926:

ORA-30926: unable to get a stable set of rows in the source tables

then this is expected with MERGE if the set of SOURCE rows (identified in the USING clause) that you’re merging into the TARGET (i.e. the MERGE INTO <target>) contain duplicate entries for the key columns concerned, i.e. if there are duplicates, which one do you want to be preserved by the MERGE.

Setup:

SQL> create table a
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> create table b
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> insert into a values(1,'A');

1 row created.

SQL> 
SQL> insert into b values(1,1);

1 row created.

SQL> 
SQL> insert into b values(1,2);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> select * from b;

       KEY V
---------- -
         1 1
         1 2
SQL> 

We might expect ORA-30926 to be raised in this next example, but it’s not:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 
SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> 

But if we run it again:

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');
merge into a
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables



SQL> rollback;

Rollback complete.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

If we change this slightly by adding and altering an ORDER BY:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val ASC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val DESC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

Perhaps we get an insight into how the check that results in ORA-30926 is implemented?

It seems to be not as simple as a question of whether there are duplicates in the SOURCE but whether the MERGE would update the same row twice – i.e. an UPDATE that results in an UPDATE not just updating it to the existing value.

The bottom line is that you should never be merging in multiple updates to the same target row.
If there are such incoming data, you have to have logic to filter that down.

Any thoughts on stats validation?

There is an application running on 9.2.0.8 and this application’s approach to object statistics is that they are gathered along with each quarterly release of code and do not change outside of the release schedule.

There are a number of other additional issues with some elements of this particular implementation, discussion of which are outside the scope of this.

Now, as part of the upgrade to 11.2, it’s highly likely that we will move to the default built-in stat proc, albeit gathering in a weekend window not daily.

One of the biggest advantages of the current situation is that the stats are tested along with the release and released with the release (of course, plans can still change depite stats being fixed – even because they are fixed due to time ticking ever onwards, sequences increasing, etc).

And so, as part of a possibly non-negotiable comfort blanket to move to the default 11g stats job, there is a management requirement to have some sort of validation routine – i.e. a set of sql statements broadly representing important or problem areas of the application – that will be run before/after and compared on some sort of metric and judgement passed on whether the stats are “good”.

There are a number of nifty features like pending stats that we can use here but my initial thoughts on this was that this sounded like a fairly easy job for SQL Tuning Sets (STS) and the SQL Performance Analyzer (SPA).

SPA requires the additional Real Application Testing (RAT) license and, now that our licensing has been negotiated, this isn’t something we’ve got.

So… hand-rolling a validation routine.

I would see SQL Tuning Sets with the sql statements and bind sets as still part of a bespoke solution
(STS are available with the Tuning Pack license which is on top of EE both of which we have).

I could picture a simple approach that would execute these statements in parallel sessions probably via DBMS_SQL, fetch all the rows, record the session stats before/after, pick a metric e.g. buffer gets, elapsed time, or some sort of ratio between those sorts of things and then and do some sort of metric comparison – yes, that run was broadly the same or better, or at least not a catastrophic degradation – these stats are ok.

(Of course, being able to picture a possible approach does not mean that I advocate it)

But I just wondered whether this is something anyone else has done?

I’d be very interested in hearing more if you have.

Edit:
I’ve tried to reword this to avoid some confusion.
Alex – I’ve deleted your comments and my replies because a) they reduce the chance of getting the input the article was aiming for b) they were based on nested recursive misunderstandings and c) much of what you were saying is preaching to the choir.

In Support of DBMS_JOB

DBMS_SCHEDULER – great set of functionality, vastly more powerful and flexible than DBMS_JOB but there is at least one thing that DBMS_JOB is still best suited for and that is why DBMS_JOB is still not, as far as I know, officially deprecated.

The documentation on DBMS_JOB may say that it’s only supported for backward compatibility but there’s one massive, great thing that it overlooks – transaction control.

And it’s a point that is quickly forgotten on the forums for example when someone is often leapt upon for even suggesting DBMS_JOB (and probably rightly so 93% of the time).

You might as part of a wider transaction want to submit a piece of asychronous work, something that can be fired off in the background, a job.

The oft-cited example is an email, i.e.

start transaction;
do some work;
send a related email;
maybe do some more work;
commit;

And the sending of the email should stand or fail with the transaction.

If the transaction completes the email gets sent.

If the transaction fails along the line, the job to send the email gets rolled back along with everything else.

DBMS_SCHEDULER is not suitable for this.

Yes, it’s vastly more powerful and flexible than DBMS_JOB but it has a flaw – the call to DBMS_SCHEDULER commits.

So, not only does that mean that in the example mentioned, the job to send the email cannot be rolled back but you’ve only just gone and committed your whole transaction when you didn’t want to.

So then you go down the route of using an autonomous transaction to call DBMS_SCHEDULER and so, whilst your wider transaction control hasn’t been violated, that email is going regardless and it’s going just as soon as the scheduler picks it up.

When what you really should be still using is DBMS_JOB.

And I can’t see DBMS_JOB ever going until that oversight in the implementation of DBMS_SCHEDULER is addressed.

Committing over a Select For Update

I’ve read a couple of articles recently reflecting on changes and bugs roughly in and around read consistency in recent versions.

There’s one here from Jonathan Lewis that may or may not be a bug.

There was another one yesterday from Charles Hooper that definitely must be a bug because it’s related to whether a unique index is used as the access mechanism.

This reminded me of a good change in behaviour that I thought I had blogged about but it appears not.

I mentioned this in a comment on Charles’ post above and Mohamed Houri quite rightly questioned my assertion that this behaviour changed as recently as 10.2.0.5.

I can’t find the metalink note that I thought I had referenced before soooo if anyone can verify behaviour on versions before and between those that I currently have access to (need to get that laptop fixed) – 9.2.0.8 and 11.2.0.2 – then I’d be grateful, with particular interest in 7, 8, 8i, 10.2.0.4 and 10.2.0.5.

This is a simple, noddy example but horrendously bad practice, flawed and responsible for a serious duplicate messaging issue recently on a system that I know. But it’s PLSQL code that I reckon there’s still quite a lot of out there.

Forget about the lack of bulk operations, when you look at the results from 9.2.0.8 below, just think about concurrent executions and a SELECT FOR UPDATE driving a LOOP that COMMITS and what that COMMIT does to the supposed protection offered by the SELECT FOR UPDATE on subsequent loops. Thank goodness this is prevented in recent versions.

Test is:

SELECT * FROM v$version;  

DROP TABLE t1;  

CREATE TABLE t1  
AS  
SELECT ROWNUM col1  
FROM   DUAL  
CONNECT BY ROWNUM <=10;  
   
DECLARE  
 CURSOR c1  
 IS  
   SELECT col1 FROM t1 FOR UPDATE;  
 l1 t1.col1%TYPE;  
BEGIN  
  OPEN c1;  
  LOOP  
     FETCH c1 INTO l1;  
     EXIT WHEN c1%NOTFOUND;  
     COMMIT;  
 END LOOP;  
 CLOSE c1;
 DBMS_OUTPUT.PUT_LINE(l1);  
END;  
/ 

Result on 9.2.0.8 is:

SQL> SELECT * FROM v$version;  

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> 
SQL> DROP TABLE t1;  

Table dropped.

SQL> 
SQL> CREATE TABLE t1  
  2  AS  
  3  SELECT ROWNUM col1  
  4  FROM   DUAL  
  5  CONNECT BY ROWNUM <=10;  

Table created.

SQL>    
SQL> DECLARE  
  2   CURSOR c1  
  3   IS  
  4     SELECT col1 FROM t1 FOR UPDATE;  
  5   l1 t1.col1%TYPE;  
  6  BEGIN  
  7    OPEN c1;  
  8    LOOP  
  9       FETCH c1 INTO l1;  
 10       EXIT WHEN c1%NOTFOUND;  
 11       COMMIT;  
 12   END LOOP;  
 13   CLOSE c1;
 14   DBMS_OUTPUT.PUT_LINE(l1);  
 15  END;  
 16  / 
10

PL/SQL procedure successfully completed.

SQL> 

Result on 11.2.0.2 is:

SQL> SELECT * FROM v$version;  

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> 
SQL> DROP TABLE t1;  

Table dropped.

SQL> 
SQL> CREATE TABLE t1  
  2  AS  
  3  SELECT ROWNUM col1  
  4  FROM   DUAL  
  5  CONNECT BY ROWNUM <=10;  

Table created.

SQL>    
SQL> DECLARE  
  2   CURSOR c1  
  3   IS  
  4     SELECT col1 FROM t1 FOR UPDATE;  
  5   l1 t1.col1%TYPE;  
  6  BEGIN  
  7    OPEN c1;  
  8    LOOP  
  9       FETCH c1 INTO l1;  
 10       EXIT WHEN c1%NOTFOUND;  
 11       COMMIT;  
 12   END LOOP;  
 13   CLOSE c1;
 14   DBMS_OUTPUT.PUT_LINE(l1);  
 15  END;  
 16  / 
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 9


SQL> 

As mentioned, if anyone can double check the behaviour on other versions, particularly 10.2.0.4 and 10.2.0.5 then please post.

P.S. Metalink note 14542.1 talks about committing over a select for update and mentions relevance for PLSQL from 10.2.0.5. This isn’t the note that I remember confirming the change.

P.P.S. Talk about horrendously bad practice, I missed the “CLOSE c1;”, corrected now!

P.P.P.S. That missing CLOSE reminds me of something else I meant to blog about when someone asked me about whether it was really necessary, as was stipulated in their coding standards, to have all this “IF c1%ISOPEN THEN CLOSE c1;” type code – it’s all about cursor scope…

Follow

Get every new post delivered to your Inbox.

Join 68 other followers