If I'm now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command to switch any unqualified object references to the schema set via CURRENT_SCHEMA rather than editing the SQL and add all the object references.
This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn't do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.
The following simple test case demonstrates the issue in 10.2.0.4 Win32:
SQL>
SQL> drop user dynsamp_test cascade;
drop user dynsamp_test cascade
*
ERROR at line 1:
ORA-01918: user 'DYNSAMP_TEST' does not exist
SQL>
SQL> create user dynsamp_test identified by dynsamp_test;
User created.
SQL>
SQL> grant create session, alter session to dynsamp_test;
Grant succeeded.
SQL>
SQL> drop table dynsamp_test_table purge;
drop table dynsamp_test_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table dynsamp_test_table
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;
Table created.
SQL>
SQL> grant select on dynsamp_test_table to dynsamp_test;
Grant succeeded.
SQL>
SQL> -- this is the plan we get as schema owner
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';
Explained.
SQL>
SQL> -- It uses obviously dynamic sampling as instructed
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> -- We get the same from different schema with qualified reference (or valid synonym)
SQL> connect dynsamp_test/dynsamp_test@orcl
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';
Session altered.
SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 cbo_test.dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';
Explained.
SQL>
SQL> -- Still uses dynamic sampling
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> -- Now switch the current_schema
SQL> alter session set current_schema = cbo_test;
Session altered.
SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';
Explained.
SQL>
SQL> -- No more dynamic sampling!
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 10 | 1280 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')
13 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> -- Check the 10053 trace file
SQL> -- It will show that the dynamic samping query fails
SQL> -- and therefore no dynamic sampling will be performed
SQL> -- presumably because the current_schema is not obeyed
SQL> -- by the recursively executed query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10053';
Session altered.
SQL>
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';
Explained.
SQL>
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> -- Check the 10046 trace file
SQL> -- which confirms a ORA-00942 while parsing the dynamic sampling query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10046';
Session altered.
SQL>
SQL> alter session set sql_trace = true;
Session altered.
SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';
Explained.
SQL>
SQL> alter session set sql_trace = false;
Session altered.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> spool off
So as you can see the simply query uses dynamic sampling as instructed (and in 10.2 would use dynamic sampling by default anyway since the table has no statistics), but when using the CURRENT_SCHEMA trick and then an unqualified reference, the dynamic sampling is not performed and the estimate is based on hardcoded defaults.
Checking the 10053 optimizer trace file confirms that the dynamic sampling is attempted, but the recursive query fails, and the 10046 SQL trace shows that an "ORA-00942 table or view does not exist" error is encountered while parsing the recursive dynamic sampling query.
So be careful when using this (rather unusual I have to admit) setup, the execution plans might be different if you're using a non-default CURRENT_SCHEMA and dynamic sampling is involved.
The issue seems to be fixed in 11.1.0.7 (dynamic sampling is performed as expected when using non-default CURRENT_SCHEMA), however I couldn't identify a corresponding bug on Metalink. If anyone comes across a bug number or document ID this would be helpful.
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.
ReplyDeleteBest regards
Maxim
Randolf,
ReplyDeleteThe 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 well
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.
ReplyDeleteI would agree this is bug 5586604 which is part of the 11.1.0.7 patch set. There is also a backport available for 10.2.0.4.
ReplyDelete>> 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.
ReplyDeleteGary,
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).
But you're right, it could happen,
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.
Regards,
Randolf
Maxim, Coskan, Greg,
ReplyDeletethanks for the bug and Metalink reference / pointer.
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.
Shouldn't these keywords at least show this document as one of the top results?
Regards,
Randolf
Bug returns at the top without the ""
ReplyDeleteI think Metalink search engine is not as smart as Oracle DB.
For Gary's suggestion yes it works if user have an object with that name.
ReplyDeleteSQL> create table dynsamp_test_table as select * from hr.dynsamp_test_table;
Table created.
SQL> alter session set current_schema=HR;
Session altered.
SQL> @rtest
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3511482271
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> drop table coskan.dynsamp_test_table purge;
Table dropped.
SQL> @rtest
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3511482271
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1536 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 12 | 1536 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')
13 rows selected.
Coskan,
ReplyDeletethanks for the information.
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.
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.
Regards,
Randolf
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 :)
ReplyDelete