MATERIALIZE optimizer hint in WITH Clause:Subquery Factoring

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

MATERIALIZE optimizer hint in WITH Clause:Subquery Factoring

Postby Lalit Kumar B » Tue Oct 15, 2013 5:25 pm

Hi Tim,

Apropos this link, http://www.oracle-base.com/articles/misc/with-clause.php , I was replying to OP in OraFAQ forum. There happened a discussion when I said "MATERIALIZE hint tells the optimizer to resolve the result of the subquery into a global temporary table." Michel Cadot deferred with me, saying it's not a GTT, rather a memory table. This is the link of the thread from ORaFAQ http://www.orafaq.com/forum/mv/msg/189923/598473/#msg_598473.

I believe that GTT are not supposed to be created and dropped on the fly, rather once created, it resides in the DB. A memory table, however, is used to hold the sub-result set while the query is under execution.

Could you please clarify on this.

Thanks in advance.

Regards,
Lalit
Lalit Kumar B
Member
 
Posts: 2
Joined: Tue Oct 15, 2013 12:34 pm

Re: MATERIALIZE optimizer hint in WITH Clause:Subquery Facto

Postby Tim... » Tue Oct 15, 2013 8:36 pm

Hi.

Well, let's see what SQL Trace tells us...

Create a test table.

Code: Select all
CONN test/test

CREATE TABLE t1 AS
SELECT level AS id
FROM   dual
CONNECT BY level <= 100;


Check what the trace file name is.

Code: Select all
SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4278.trc

SQL>


Now let's trace a query using the materialize hint.

Code: Select all
EXEC DBMS_MONITOR.session_trace_enable;

WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1;

EXEC DBMS_MONITOR.session_trace_disable;


What do we see in the trace file?

Amongst a load of recursive SQL we see the following.

Code: Select all
=====================
PARSING IN CURSOR #140100560521424 len=174 dep=1 uid=0 oct=1 lid=0 tim=733844612 hv=1878591410 ad='80b179f0' sqlid='40a2untrzk1xk'
CREATE GLOBAL TEMPORARY T
END OF STMT
PARSE #140100560521424:c=11998,e=13650,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=733844602
EXEC #140100564467640:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=733845228
FETCH #140100564467640:c=0,e=257,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=733845533
CLOSE #140100564467640:c=0,e=8,dep=2,type=3,tim=733845599
EXEC #140100564467640:c=8999,e=9111,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=813480514,tim=733854993
FETCH #140100564467640:c=0,e=153,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=733855273
CLOSE #140100564467640:c=0,e=9,dep=2,type=3,tim=733855351
=====================


Looks like it's creating a global temporary table to me. :)

When we get to the actual query itself we get this.

Code: Select all
=====================
PARSING IN CURSOR #140100560423976 len=77 dep=0 uid=109 oct=3 lid=109 tim=733865863 hv=3518560624 ad='a35bc6c0' sqlid='9fzhbw78vjybh'
WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1
END OF STMT
PARSE #140100560423976:c=73988,e=76506,p=0,cr=61,cu=0,mis=1,r=0,dep=0,og=1,plh=3663054683,tim=733865857
WAIT #140100560423976: nam='Disk file operations I/O' ela= 727 FileOperation=2 fileno=203 filetype=2 obj#=-1 tim=733873396
WAIT #140100560423976: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=203 filetype=2 obj#=-1 tim=733877458
WAIT #140100560423976: nam='direct path write temp' ela= 486 file number=203 first dba=136 block cnt=1 obj#=-1 tim=733878156
WAIT #140100560423976: nam='direct path sync' ela= 3202 File number=203 Flags=0 p3=0 obj#=-1 tim=733881970
EXEC #140100560423976:c=16998,e=18494,p=0,cr=3,cu=7,mis=0,r=0,dep=0,og=1,plh=3663054683,tim=733884496
WAIT #140100560423976: nam='SQL*Net message to client' ela= 12 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=733885175
WAIT #140100560423976: nam='db file sequential read' ela= 123 file#=203 block#=136 blocks=1 obj#=-40016341 tim=733886476
FETCH #140100560423976:c=1000,e=1293,p=1,cr=5,cu=1,mis=0,r=1,dep=0,og=1,plh=3663054683,tim=733886986
WAIT #140100560423976: nam='SQL*Net message from client' ela= 1701 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733889186
WAIT #140100560423976: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733889343
FETCH #140100560423976:c=0,e=110,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733889410
WAIT #140100560423976: nam='SQL*Net message from client' ela= 4726 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733894243
WAIT #140100560423976: nam='SQL*Net message to client' ela= 37 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733894444
FETCH #140100560423976:c=0,e=128,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733894516
WAIT #140100560423976: nam='SQL*Net message from client' ela= 1682 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733896320
WAIT #140100560423976: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733896498
FETCH #140100560423976:c=0,e=134,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733896562
WAIT #140100560423976: nam='SQL*Net message from client' ela= 2887 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733899553
WAIT #140100560423976: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733899662
FETCH #140100560423976:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733899707
WAIT #140100560423976: nam='SQL*Net message from client' ela= 2650 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733902404
WAIT #140100560423976: nam='SQL*Net message to client' ela= 39 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733902652
FETCH #140100560423976:c=0,e=194,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733902733
WAIT #140100560423976: nam='SQL*Net message from client' ela= 1370 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733904202
WAIT #140100560423976: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733904369
FETCH #140100560423976:c=0,e=130,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3663054683,tim=733904437
WAIT #140100560423976: nam='SQL*Net message from client' ela= 1966 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733906496
WAIT #140100560423976: nam='SQL*Net message to client' ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733906641
FETCH #140100560423976:c=0,e=113,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=3663054683,tim=733906700
STAT #140100560423976 id=1 cnt=100 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=15 pr=1 pw=1 time=19589 us)'
STAT #140100560423976 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT  (cr=3 pr=0 pw=1 time=16243 us)'
STAT #140100560423976 id=3 cnt=100 pid=2 pos=1 obj=91676 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=1514 us cost=3 size=300 card=100)'
STAT #140100560423976 id=4 cnt=100 pid=1 pos=2 obj=0 op='VIEW  (cr=12 pr=1 pw=0 time=1257 us cost=2 size=1300 card=100)'
STAT #140100560423976 id=5 cnt=100 pid=4 pos=1 obj=4254950955 op='TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB (cr=12 pr=1 pw=0 time=1203 us cost=2 size=300 card=100)'
WAIT #140100560423976: nam='SQL*Net message from client' ela= 3502 driver id=1413697536 #bytes=1 p3=0 obj#=-40016341 tim=733911643
CLOSE #140100560423976:c=0,e=19,dep=0,type=0,tim=733911755
=====================


Notice the "TEMP TABLE TRANSFORMATION" step and the "TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB" step. Seems pretty conclusive to me it is using a global temporary table to me.

BUT, consider the following.

- This is an undocumented feature. I've given you an example that definitely uses a GTT, but perhaps there are other circumstances that don't.
- Oracle will often try to do many actions in memory (like sorts for example) and only overspill to temporary segments when there is not enough memory to complete the operation. Perhaps materialize can do this too. Perhaps not...
- Yes. Like any DDL operation, you should not create and drop tables (including GTTs) on the fly, but this is an internal action of the optimizer, so however they do it, I can only assume it is efficient.

A few overriding messages from this:

- Arguing over undocumented features is not the most productive thing in the world. :)
- If in doubt, trace it using a 10046 trace.
- If regular trace doesn't help explain things, consider a 10053 trace to see exactly what the optimizer is doing.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: MATERIALIZE optimizer hint in WITH Clause:Subquery Facto

Postby Tim... » Tue Oct 15, 2013 8:55 pm

Hi.

I've added a small note about this into the original article for clarity.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: MATERIALIZE optimizer hint in WITH Clause:Subquery Facto

Postby Lalit Kumar B » Tue Oct 15, 2013 10:05 pm

Thanks sir,

I have posted the same in OraFAQ too http://www.orafaq.com/forum/mv/msg/189923/598592/#msg_598592, I hope it will help a lot of folks browsing for similar topic in either of the forums.

I really appreciate that you took out a minute or two from your precious time to update your blog.

Regards,
Lalit
Lalit Kumar B
Member
 
Posts: 2
Joined: Tue Oct 15, 2013 12:34 pm

Re: MATERIALIZE optimizer hint in WITH Clause:Subquery Facto

Postby Tim... » Tue Oct 15, 2013 10:20 pm

No problem. :-)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest