tag:blogger.com,1999:blog-5124641802818980374.post8091806119412371259..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: TEMP Table Transformation and Remote Procedure CallsUnknownnoreply@blogger.comBlogger10125tag:blogger.com,1999:blog-5124641802818980374.post-87576299950552664572012-03-18T21:01:24.160+01:002012-03-18T21:01:24.160+01:00Igor,
thanks for the comment. Yes, this is probab...Igor,<br /><br />thanks for the comment. Yes, this is probably the same problem.<br /><br />If you need a workaround for the view variation of the problem then check the bug no 9706532 - this is a one-off patch that didn't make it yet into one of the patch sets and is only fixed in 12.1.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-25682657346942211012012-03-18T10:37:30.473+01:002012-03-18T10:37:30.473+01:00Thanks for interesting note
Oracle shows the same...Thanks for interesting note<br /><br />Oracle shows the same behavior with remote view query which uses TEMP TABLE TRANSFORMATION when executed locally<br /><br />In case of view the "autonomous transaction" workaround is not suitable, imho<br /><br />11.2.0.2.@SQL> create or replace view SF_REMOTE_TEST<br /> 2 as<br /> 3 with v5 as<br /> 4 (<br /> 5 select--+ materialize<br /> 6 rownum from dual<br /> 7 connect by rownum <= 100<br /> 8 )<br /> 9 select * from v5<br /> 10 /<br /><br />View created.<br /><br />11.2.0.2.@SQL> select * from SF_REMOTE_TEST;<br /><br />100 rows selected.<br /><br />--------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |<br />| 1 | VIEW | SF_REMOTE_TEST | 1 | 13 | 4 (0)| 00:00:01 |<br />| 2 | TEMP TABLE TRANSFORMATION | | | | | |<br />| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6656_7A1969 | | | | |<br />| 4 | COUNT | | | | | |<br />|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |<br />| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |<br />| 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |<br />| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6656_7A1969 | 1 | 13 | 2 (0)| 00:00:01 |<br />--------------------------------------------------------------------------------------------------------------<br /><br />11.2.0.2.@SQL> select * from SF_REMOTE_TEST@LOOPBACK;<br /><br />100 rows selected.<br /><br />---------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT REMOTE | | 1 | 13 | 2 (0)| 00:00:01 |<br />| 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |<br />| 2 | COUNT | | | | | |<br />|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |<br />| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |<br />---------------------------------------------------------------------------------------<br /><br />IgorIgor Usoltsevhttps://www.blogger.com/profile/15303587951058938350noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-11617792323613243232011-01-24T00:06:21.757+01:002011-01-24T00:06:21.757+01:00Thanks for the bug pointer.
RandolfThanks for the bug pointer.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-44503839469903158352011-01-24T00:00:10.200+01:002011-01-24T00:00:10.200+01:00This is Bug 9399589.This is Bug 9399589.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-80192583400232005502010-08-04T21:03:09.701+02:002010-08-04T21:03:09.701+02:00Amit,
thanks for the comment.
The "autonom...Amit,<br /><br />thanks for the comment. <br /><br />The "autonomous transaction" is an interesting idea and as you say might be helpful depending on the particular situation.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-26467967047083776452010-08-04T18:02:33.598+02:002010-08-04T18:02:33.598+02:00We encountered the same problem (No temp table tra...We encountered the same problem (No temp table transformation - when procedure is called remotely).<br /><br />In the scenario mentioned if I make procedure test_simple_statement a autonomous transaction..It gets the the correct plan(temp table transformation) even if called remotely.<br /><br />Though We cannot use this approach (some GTTs involved)..this might be a workaround for some.<br /><br />Thanks<br />AmitAmithttps://www.blogger.com/profile/10967774279370366263noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-55121153679884787832010-03-18T20:02:16.879+01:002010-03-18T20:02:16.879+01:00We also found a similar issue in one of our databa...We also found a similar issue in one of our databases. The query when called from the application doesn't do TEMP TABLE TRANSFORMATION in some cases. It does the TEMP TABLE TRANSFORMATION each time when executed from sql*plus. I have opened a SR with Oracle with this regard. Will update this blog if I find anything interesting. <br /><br />But this does sounds like a bug with the WITH clause and TEMP TABLE TRANSFORMATION.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-33851897748748853792010-01-13T15:52:39.671+01:002010-01-13T15:52:39.671+01:00Randolf,
You are right, I was only thinking on ru...Randolf,<br /><br />You are right, I was only thinking on run time and I forgot compile time at all.<br /><br />Thanks for the response.<br /><br />Joaquin GonzalezJoaquin Gonzalezhttps://www.blogger.com/profile/12638256027643700661noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-18565999082778305612010-01-13T15:03:35.137+01:002010-01-13T15:03:35.137+01:00Joaquin,
an interesting point - I didn't spen...Joaquin,<br /><br />an interesting point - I didn't spent much time on this since the the focus of the post was a totally different matter.<br /><br />In principle I think you're right - static SQL will also make use of roles at runtime with AUTHID CURRENT_USER. <br /><br />The problem here is that obviously the PL/SQL compiler (at least with 10.2.0.4) doesn't use the enabled roles to check the privileges at <b>compile</b> time, so if I rewrite the statement using tables from the SH schema to static SQL I get a "Table or View doesn't not exist" error even with the appropriate roles enabled at compile time.<br /><br />I assume this is meant in the <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1778" rel="nofollow">10.2 manual</a> with the sentence "Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects <b>because roles apply at run time, not at compile time.</b>" under the heading "Using Roles with Invoker's Rights Subprograms".<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-49633634019092922812010-01-13T11:11:31.986+01:002010-01-13T11:11:31.986+01:00Hi Randolf,
Just one question about "I'v...Hi Randolf,<br /><br />Just one question about "I've only wrapped it in dynamic SQL to take advantage of any enabled roles due to the AUTHID CURRENT_USER clause."<br /><br />If the procedure is AUTHID CURRENT_USER there's no need to be dynamic sql in order to be able to use roles? I mean, with AUTHID CURRENT_USER static sql also "sees" enabled roles. Am I wrong?<br /><br />Thanks.<br /><br />Joaquin GonzalezJoaquin Gonzalezhttps://www.blogger.com/profile/12638256027643700661noreply@blogger.com