tag:blogger.com,1999:blog-5124641802818980374.post8895087287608736194..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Dynamic sampling and set current_schema anomalyUnknownnoreply@blogger.comBlogger10125tag:blogger.com,1999:blog-5124641802818980374.post-21985616005687355512009-06-29T23:16:47.511+02:002009-06-29T23:16:47.511+02:00Yes you are right, I was focusing on just the dyna...Yes you are right, I was focusing on just the dynamic sampling message. Change my word from "it works" to "it shows that it does dynamic sampling" thanks for the warning :)Coskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-31528367328965523632009-06-29T17:47:25.034+02:002009-06-29T17:47:25.034+02:00Coskan,
thanks for the information.
I think it&#...Coskan,<br /><br />thanks for the information.<br /><br />I think it's important to point out that "it works" is not the correct term in my opinion... It doesn't produce an error, but the result is - as I've pointed out in my previous comment - at least questionable, and certainly always wrong, since the dynamic sampling is performed on a totally different table.<br /><br />Revising my search terms (not sure why I used that underscore in "dynamic_sampling") to "dynamic sampling current_schema" (all without the double quotes) in fact shows the document on Metalink, so I shouldn't blame it too much, although one could argument that "dynamic_sampling" should also work for the search.<br /><br />Regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-80357358950298357762009-06-29T17:23:46.953+02:002009-06-29T17:23:46.953+02:00For Gary's suggestion yes it works if user hav...For Gary's suggestion yes it works if user have an object with that name. <br /><br />SQL> create table dynsamp_test_table as select * from hr.dynsamp_test_table;<br /><br />Table created.<br /><br />SQL> alter session set current_schema=HR;<br /><br />Session altered.<br /><br />SQL> @rtest<br /><br />Explained.<br /><br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------<br />-------------------------------------------------<br />Plan hash value: 3511482271<br /><br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |<br />----------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')<br /><br />Note<br />-----<br /> - dynamic sampling used for this statement<br /><br />17 rows selected.<br /><br />SQL> drop table coskan.dynsamp_test_table purge;<br /><br />Table dropped.<br /><br />SQL> @rtest<br /><br />Explained.<br /><br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------<br />-------------------------------------------------<br />Plan hash value: 3511482271<br /><br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 12 | 1536 | 5 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 12 | 1536 | 5 (0)| 00:00:01 |<br />----------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')<br /><br />13 rows selected.Coskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-74755787713219597892009-06-29T17:15:45.516+02:002009-06-29T17:15:45.516+02:00Bug returns at the top without the ""
...Bug returns at the top without the "" <br /><br />I think Metalink search engine is not as smart as Oracle DB.Coskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-13489917954324364842009-06-29T09:54:24.010+02:002009-06-29T09:54:24.010+02:00Maxim, Coskan, Greg,
thanks for the bug and Metal...Maxim, Coskan, Greg,<br /><br />thanks for the bug and Metalink reference / pointer.<br /><br />I'm still a bit irritated though, since a search for "dynamic_sampling current_schema" doesn't show any hits in the Knowledge base search, and in the bug database I simply get too many hits back.<br /><br />Shouldn't these keywords at least show this document as one of the top results?<br /><br />Regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-74870073553164087172009-06-29T09:51:17.941+02:002009-06-29T09:51:17.941+02:00>> So what happens if you have a table of th...>> So what happens if you have a table of that name in the logged on schema ? Does it dynamically sample that, and if so, what if the columns are different.<br /><br />Gary,<br /><br />interesting question. Initially I haven't tested this scenario since it doesn't correspond to what I've described (The user connected to usually doesn't own any objects). <br /><br />But you're right, it could happen, <br />and the result might be quite confusing: If the query can be run on the local version of the table (so the table/column names match) it will actually show the dynamic sampling result based on the "wrong" table, and if the columns differ then the same happens as when the table doesn't exist: The query fails (with a different error, in this case ORA-00904 obviously) and the dynamic sampling isn't performed.<br /><br />Regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-50196186575734668242009-06-29T07:10:21.138+02:002009-06-29T07:10:21.138+02:00I would agree this is bug 5586604 which is part of...I would agree this is <a href="https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUG&p_id=5586604" rel="nofollow">bug 5586604</a> which is part of the 11.1.0.7 patch set. There is also a backport available for 10.2.0.4.Greg Rahnhttp://structureddata.orgnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-19038316039911407772009-06-29T01:42:53.420+02:002009-06-29T01:42:53.420+02:00So what happens if you have a table of that name i...So what happens if you have a table of that name in the logged on schema ? Does it dynamically sample that, and if so, what if the columns are different.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-6948077947945258652009-06-29T01:29:50.233+02:002009-06-29T01:29:50.233+02:00Randolf,
The bug Maxim mentioned listed in 11.1.0...Randolf,<br />The bug Maxim mentioned listed in 11.1.0.7 Patch Set (Undocumented Oracle Server section) but dynamic sampling works fine on 11.1.0.6 as wellCoskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-8704124152047442042009-06-29T00:19:26.794+02:002009-06-29T00:19:26.794+02:00Seems to be a 5586604.8 - however, metalink states...Seems to be a 5586604.8 - however, metalink states, it were fixed first in 11.2 ?. Also, in my opinion, the setup you describe is not so unusual.<br /><br />Best regards<br /><br />MaximMaximhttps://www.blogger.com/profile/03678566399786170754noreply@blogger.com