A New Version of an Old Trick

A long, long time ago, people started using the following SQL idiom:

with t as (select /*+ materialize */ …..)

select …. from t

If my memory serves me right, Jonathan Lewis was the first person whom I saw using this idiom. The use of that idiom was to speed create a global temporary table and reuse the query results, without having to re-execute query again. Here is an example:

SQL> set autotrace on

SQL> set timing on

SQL> with t as (select /*+ materialize */ * from dba_objects)

2 select count(*) from t;

COUNT(*)

———-

87661

Elapsed: 00:00:02.67

Execution Plan

———————————————————-

Plan hash value: 3274171655

——————————————————————————–

——————————

| Id | Operation | Name | Rows | B

ytes | Cost (%CPU)| Time |

——————————————————————————–

——————————

| 0 | SELECT STATEMENT | | 1 |

| 570 (15)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | |

| | |

| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6608_4A7EB2 | |

| | |

| 3 | VIEW | DBA_OBJECTS | 87374 |

13M| 239 (24)| 00:00:01 |

| 4 | UNION-ALL | | |

| | |

|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 |

9 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 |

| 0 (0)| 00:00:01 |

| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 |

30 | 3 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 |

| 2 (0)| 00:00:01 |

|* 9 | FILTER | | |

| | |

|* 10 | HASH JOIN | | 87373 |

10M| 237 (24)| 00:00:01 |

| 11 | TABLE ACCESS FULL | USER$ | 99 |

1683 | 2 (0)| 00:00:01 |

|* 12 | HASH JOIN | | 87373 |

9044K| 230 (23)| 00:00:01 |

| 13 | INDEX FULL SCAN | I_USER2 | 99 |

2178 | 1 (0)| 00:00:01 |

|* 14 | TABLE ACCESS FULL | OBJ$ | 87373 |

7167K| 224 (21)| 00:00:01 |

| 15 | NESTED LOOPS | | 1 |

29 | 2 (0)| 00:00:01 |

|* 16 | INDEX SKIP SCAN | I_USER2 | 1 |

20 | 1 (0)| 00:00:01 |

|* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 |

9 | 1 (0)| 00:00:01 |

| 18 | NESTED LOOPS | | 1 |

105 | 2 (0)| 00:00:01 |

| 19 | NESTED LOOPS | | 99 |

105 | 2 (0)| 00:00:01 |

| 20 | TABLE ACCESS FULL | USER$ | 99 |

1683 | 2 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | I_LINK1 | 1 |

| 0 (0)| 00:00:01 |

| 22 | TABLE ACCESS BY INDEX ROWID| LINK$ | 1 |

88 | 0 (0)| 00:00:01 |

| 23 | SORT AGGREGATE | | 1 |

| | |

| 24 | VIEW | | 87374 |

| 331 (8)| 00:00:01 |

| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_4A7EB2 | 87374 |

13M| 331 (8)| 00:00:01 |

——————————————————————————–

——————————

Predicate Information (identified by operation id):

—————————————————

5 – filter(BITAND(“S”.”XPFLAGS”,8388608)=8388608)

6 – access(“S”.”OBJ#”=:B1)

8 – access(“EO”.”OBJ#”=:B1)

9 – filter(“O”.”TYPE#”<>4 AND “O”.”TYPE#”<>5 AND “O”.”TYPE#”<>7 AND “O”.”TYPE

#”<>8 AND

“O”.”TYPE#”<>9 AND “O”.”TYPE#”<>11 AND “O”.”TYPE#”<>12 AND “O”.”TY

PE#”<>13 AND “O”.”TYPE#”<>14 AND

“O”.”TYPE#”<>22 AND “O”.”TYPE#”<>87 AND “O”.”TYPE#”<>88 OR BITAND(

“U”.”SPARE1″,16)=0 OR

(“O”.”TYPE#”=4 OR “O”.”TYPE#”=5 OR “O”.”TYPE#”=7 OR “O”.”TYPE#”=8

OR “O”.”TYPE#”=9 OR “O”.”TYPE#”=10

OR “O”.”TYPE#”=11 OR “O”.”TYPE#”=12 OR “O”.”TYPE#”=13 OR “O”.”TYPE

#”=14 OR “O”.”TYPE#”=22 OR

“O”.”TYPE#”=87) AND (SYS_CONTEXT(‘userenv’,’current_edition_name’)

=’ORA$BASE’ AND “U”.”TYPE#”<>2 OR

“U”.”TYPE#”=2 AND “U”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’cu

rrent_edition_id’)) OR EXISTS

(SELECT 0 FROM SYS.”USER$” “U2″,SYS.”OBJ$” “O2” WHERE “O2”.”OWNER#

“=”U2″.”USER#” AND “O2”.”TYPE#”=88

AND “O2”.”DATAOBJ#”=:B1 AND “U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NU

MBER(SYS_CONTEXT(‘userenv’,’current_

edition_id’)))))

10 – access(“O”.”SPARE3″=”U”.”USER#”)

12 – access(“O”.”OWNER#”=”U”.”USER#”)

14 – filter(“O”.”TYPE#”<>10 AND “O”.”NAME”<>’_NEXT_OBJECT’ AND

“O”.”NAME”<>’_default_auditing_options_’ AND “O”.”LINKNAME” IS NUL

L AND BITAND(“O”.”FLAGS”,128)=0)

16 – access(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’

current_edition_id’)))

filter(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’

current_edition_id’)))

17 – access(“O2”.”DATAOBJ#”=:B1 AND “O2”.”TYPE#”=88 AND “O2″.”OWNER#”=”U2”.”US

ER#”)

21 – access(“L”.”OWNER#”=”U”.”USER#”)

Statistics

———————————————————-

11 recursive calls

1279 db block gets

2384 consistent gets

1251 physical reads

928 redo size

528 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

The lines in bold font are showing the temp table transformation. If the alias “t” is used repeatedly in the script, the “select * from dba_objects” will not be re-executed but the results will be loaded from the temp table. Now, there is a new version of the same trick:

1 with t as (select /*+ result_cache */ * from dba_objects)

2* select count(*) from t

SQL> /

COUNT(*)

———-

87661

Elapsed: 00:00:01.90

Execution Plan

———————————————————-

Plan hash value: 1250653294

——————————————————————————–

——-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim

e |

——————————————————————————–

——-

| 0 | SELECT STATEMENT | | 1 | | 237 (24)| 00:

00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

|

| 2 | VIEW | DBA_OBJECTS | 87374 | | 237 (24)| 00:

00:01 |

| 3 | UNION-ALL | | | | |

|

|* 4 | FILTER | | | | |

|

|* 5 | HASH JOIN | | 87373 | 9385K| 236 (24)| 00:

00:01 |

| 6 | INDEX FULL SCAN | I_USER2 | 99 | 396 | 1 (0)| 00:

00:01 |

|* 7 | HASH JOIN | | 87373 | 9044K| 230 (23)| 00:

00:01 |

| 8 | INDEX FULL SCAN | I_USER2 | 99 | 2178 | 1 (0)| 00:

00:01 |

|* 9 | TABLE ACCESS FULL| OBJ$ | 87373 | 7167K| 224 (21)| 00:

00:01 |

| 10 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:

00:01 |

|* 11 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:

00:01 |

|* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:

00:01 |

| 13 | NESTED LOOPS | | 1 | 17 | 1 (0)| 00:

00:01 |

| 14 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:

00:01 |

|* 15 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:

00:01 |

——————————————————————————–

——-

Predicate Information (identified by operation id):

—————————————————

4 – filter(“O”.”TYPE#”<>4 AND “O”.”TYPE#”<>5 AND “O”.”TYPE#”<>7 AND

“O”.”TYPE#”<>8 AND “O”.”TYPE#”<>9 AND “O”.”TYPE#”<>11 AND “O”.”TYP

E#”<>12 AND

“O”.”TYPE#”<>13 AND “O”.”TYPE#”<>14 AND “O”.”TYPE#”<>22 AND “O”.”T

YPE#”<>87

AND “O”.”TYPE#”<>88 OR BITAND(“U”.”SPARE1″,16)=0 OR (“O”.”TYPE#”=4

OR

“O”.”TYPE#”=5 OR “O”.”TYPE#”=7 OR “O”.”TYPE#”=8 OR “O”.”TYPE#”=9 O

R

“O”.”TYPE#”=10 OR “O”.”TYPE#”=11 OR “O”.”TYPE#”=12 OR “O”.”TYPE#”=

13 OR

“O”.”TYPE#”=14 OR “O”.”TYPE#”=22 OR “O”.”TYPE#”=87) AND

(SYS_CONTEXT(‘userenv’,’current_edition_name’)=’ORA$BASE’ AND “U”.

“TYPE#”<>2

OR “U”.”TYPE#”=2 AND “U”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,

‘current_edit

ion_id’)) OR EXISTS (SELECT 0 FROM SYS.”USER$” “U2″,SYS.”OBJ$” “O

2″ WHERE

“O2″.”OWNER#”=”U2″.”USER#” AND “O2”.”TYPE#”=88 AND “O2″.”DATAOBJ#”

=:B1 AND

“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’

current_editi

on_id’)))))

5 – access(“O”.”SPARE3″=”U”.”USER#”)

7 – access(“O”.”OWNER#”=”U”.”USER#”)

9 – filter(“O”.”TYPE#”<>10 AND “O”.”NAME”<>’_NEXT_OBJECT’ AND

“O”.”NAME”<>’_default_auditing_options_’ AND “O”.”LINKNAME” IS NUL

L AND

BITAND(“O”.”FLAGS”,128)=0)

11 – access(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,

‘current_edition_id’)))

filter(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,

‘current_edition_id’)))

12 – access(“O2”.”DATAOBJ#”=:B1 AND “O2”.”TYPE#”=88 AND

“O2″.”OWNER#”=”U2″.”USER#”)

15 – access(“L”.”OWNER#”=”U”.”USER#”)

Statistics

———————————————————-

0 recursive calls

0 db block gets

1115 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

The difference is in the /*+ result_cache */ hint instead of /*+ materialize /. The purpose is exactly the same, the difference in general should be marginal, coming from not having to create a temporary object in the dictionary. However, it is my general experience that the scripts with the /*+ result_cache */ hint behave a little bit better and perform a little bit faster than with the old material girl version.

The numbers in the script above prove nothing, because the table rows have already been read by the previous version, so the fact that the /*+ materialize */ version did some physical reads and the /*+ result_cache */ version didn’t didn’t do any physical reads was to be expected and proves nothing. Again, it is my impression that the result cache behaves better than the “material girl” version.

Advertisements

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

4 Responses to A New Version of an Old Trick

  1. andyblg says:

    are you talking about db 12c ?

    thanks

    • mgogala says:

      The post applies to both versions. This hint is legal in both Oracle 11G and Oracle 12c. Of course, I am not suggesting to start using it mechanically by doing something like
      perl -i .bak -e ‘s/materialize/result_cache/i’ *.sql
      You should test your script and consider the alternatives only when it matters, when the performance is a problem. The basic rule is test, test, test and then test some more.
      I don’t test often, but when I do, I do it in production.

  2. Mohammed Imran says:

    Hi,

    Thanks for the post!
    I have a query having Merge and WIth clause but it takes more than expected time to complete so I have added result_cache hint however it throws error in sqlplus when I try to execute. Could you throw some light on why error is coming up?

    Error:
    ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x2AF2CA0F6FB8], [qksqbCloneByCursTxt : sql text], [], [], [], [], [], [], [], [], []

    DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

    The table road_lines has around 2 lakh rows in it and treewalk would have around 1,40,000 rows while execution.
    Query :
    MERGE INTO road_lines rt
    USING (WITH treewalk AS
    ( SELECT /*+ result_cache */
    CONNECT_BY_ROOT rl.line_no new_line_no, SYS_CONNECT_BY_PATH(sgmt_seq, ‘/’) sgmts, LEVEL AS lvl
    FROM road_lines rl
    WHERE free_end_ind IN (‘B’, ‘E’) AND CONNECT_BY_ISLEAF = 1
    START WITH rl.junction IN (‘E’, ‘B’) AND
    rl.free_end_ind IN (‘B’, ‘E’)
    CONNECT BY NOCYCLE PRIOR to_longitude = from_longitude AND
    PRIOR to_latitude = from_latitude AND
    PRIOR RANK = RANK AND
    PRIOR rd_name = rd_name AND
    PRIOR ROWID != ROWID AND
    free_end_ind IN (‘S’, ‘B’) AND
    junction IN (‘B’, ‘S’))
    SELECT new_line_no
    ,LEVEL AS lvl
    ,TO_NUMBER(REGEXP_SUBSTR(sgmts, ‘[^/]+’, 1, LEVEL))
    AS sgmt_seq_no
    ,DECODE(REGEXP_COUNT(sgmts, ‘/’), LEVEL, 1, 0) isleaf
    FROM (SELECT *
    FROM treewalk
    WHERE lvl IN (SELECT MAX(lvl)
    FROM treewalk
    HAVING MAX(lvl) > 1) AND
    ROWNUM = 1)
    CONNECT BY LEVEL <= REGEXP_COUNT(sgmts, '/') + 1) nl
    ON (nl.sgmt_seq_no = rt.sgmt_seq)
    WHEN MATCHED THEN
    UPDATE SET line_no = nl.new_line_no, line_no_seq = nl.lvl, free_end_ind = DECODE(DECODE(nl.lvl, 1, -1, DECODE(nl.isleaf, 1, -2, 0)), -1, DECODE(rt.free_end_ind, 'B', 'S', 'E', 'N'), -2, DECODE(rt.free_end_ind, 'B', 'E', 'S', 'N'), 'N');

  3. mgogala says:

    Hi Mohammed, this is actually a bug 6434757 is fixed in Oracle release 11.2.and described in the support document 735599.1. Long story short, you have some patching to do.
    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s