Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2
This problem can occur on any platform.
Symptoms
For optimization of distributed queries Oracle provides the DRIVING_SITE hint.
The Oracle® Database Administrator’s Guide 11g Release 1 (11.1)
(http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm#sthref3180)
describes it as follows:
The DRIVING_SITE hint lets you specify the site where the query execution is performed. It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.
Following is an example of a SELECT statement with a DRIVING_SITE hint:
SELECT /*+ DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com dept
WHERE emp.deptno = dept.deptno;
So when you’re using this hint you will see in the query execution plan REMOTE in the corresponding operation section, here’s an example for a SELECT. Note that we have the table example_data on both databases, i.e. we have a local table example_data and a remote table example_data. The SQL is launched from the local side:
select /*+ DRIVING_SITE(remote_example_data) */ *
from example_data local_example_data, example_data@v10203 remote_example_data
where local_example_data.x = remote_example_data.x;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 4264 | 5 (20)| 00:00:01 | | |
|* 1 | HASH JOIN | | 164 | 4264 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL| EXAMPLE_DATA | 2 | 26 | 2 (0)| 00:00:01 | | |
| 3 | REMOTE | EXAMPLE_DATA | 82 | 1066 | 2 (0)| 00:00:01 | V10203 | R->S |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LOCAL_EXAMPLE_DATA"."X"="REMOTE_EXAMPLE_DATA"."X")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "X" FROM "EXAMPLE_DATA" "REMOTE_EXAMPLE_DATA" (accessing 'V10203' )
So this works as expected as is also clearly visible in the “Remote SQL Information” section.
You might wonder what happens when you do this in a CREATE TABLE AS SELECT, i.e.:
create table remote_new_data (x,y) — need to give unique names to columns
as
select /*+ DRIVING_SITE(remote_example_data) */ *
from example_data local_example_data, example_data@v10203 remote_example_data
where local_example_data.x = remote_example_data.x;
When you check the execution plan, you’ll notice hint seems to work for the query itself, but the newly
created table is created locally. That also doesn’t change if you include the DRIVING_SITE hint directly after the CREATE TABLE portion, you’ll get an execution plan like:
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 8 | 48 | 6 (17)| 00:00:01 | | |
| 1 | LOAD AS SELECT | REMOTE_NEW_DATA | | | | | | |
|* 2| HASH JOIN | | 8 | 48 | 5 (20)| 00:00:01 | | |
| 3 | REMOTE | EXAMPLE_DATA | 2 | 6 | 2 (0)| 00:00:01 | V10203 |R->S |
| 4 | TABLE ACCESS FULL | EXAMPLE_DATA | 8 | 24 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOCAL_EXAMPLE_DATA"."X"="REMOTE_EXAMPLE_DATA"."X")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT /*+ */ "X" FROM "EXAMPLE_DATA" "REMOTE_EXAMPLE_DATA" (accessing 'V10203' )
Cause
What happened? That’s actually expected behaviour, a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this. DRIVING_SITE hint means that entire cursor (not a subquey) is supposed to be mapped remotely. That also means CREATE TABLE cannot be executed remotely (which is also the reason why you get ORA-2021 when you try to accomplish this with an Create Table table_name@remote_database).
Solution
So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL.
© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
Hdr: 5517609 9.2.0.7 RDBMS 9.2.0.7 QRY OPTIMIZER PRODID-5 PORTID-46
Abstract: DRIVING_SITE HINT IS IGNORED FOR INSERT AS SELECT
PROBLEM:
——–
A query joining two tables using driving_site hint is performing as expected.
Insert into a local table using the same query is ignoring driving_site hint.
DIAGNOSTIC ANALYSIS:
——————–
We have 2 databases (database A and B).
We have a db link on B pointing to A.
We have table A in database A and table B in database B
Eplain plan for select:
PLAN_TABLE_OUTPUT
——————————————————————————
——————————————
——————————————————————————
————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Inst |IN-OUT|
——————————————————————————
————-
| 0 | SELECT STATEMENT REMOTE| | 7 | 49 | 175 (12)|
|
|* 1 | HASH JOIN | | 7 | 49 | 175 (12)|
|
| 2 | REMOTE | B | 1 | 3 | 3 (0)| !
|
| 3 | TABLE ACCESS FULL | A | 420K| 1641K| 163 (8)|
EMR10~ |
——————————————————————————
————-
Predicate Information (identified by operation id):
—————————————————
1 – access(“A2″.”COLA”=”A1″.”COLB”)
Note: fully remote operation
Eplain plan for insert as select:
PLAN_TABLE_OUTPUT
——————————————————————————
——————————————
——————————————————————————
———–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Inst |IN-OUT|
——————————————————————————
———–
| 0 | INSERT STATEMENT | | 7 | 49 | 195 (22)|
| |
|* 1 | HASH JOIN | | 7 | 49 | 195 (22)|
| |
| 2 | TABLE ACCESS FULL | B | 1 | 3 | 4 (25)|
| |
| 3 | REMOTE | | 420K| 1641K| |
TO_A | R->S |
——————————————————————————
———–
Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”COLA”=”B”.”COLB”)
Remote SQL Information (identified by operation id):
—————————————————-
Eplain plan for insert as select using view:
PLAN_TABLE_OUTPUT
——————————————————————————
——————————————
——————————————————————————
———–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Inst |IN-OUT|
——————————————————————————
———–
| 0 | INSERT STATEMENT | | 1 | 26 | 3 (34)|
| |
| 1 | REMOTE | | | | |
TO_A | R->S |
——————————————————————————
———–
Tkprof shows the following elapsed times:
insert with driving_site hint – 2.61 s
insert using view – 0.52 s
select using driving site hint – 1.08 s
select without driving_site hint – 2.50 s
WORKAROUND:
———–
Create a view on the remote database (A) and then issue the insert
query by selecting from the view@link_name.
RELATED BUGS:
————-
Bug 2262861 but here there is not bind variable
Bug 2593117
REPRODUCIBILITY:
—————-
Reproduced in-house.
Database A: 10.2.0.2
Database B: 9.2.0.7
TEST CASE:
———-
In Database A:
create user a identified by a default tablespace users temporary tablespace
temp;
grant dba to a;
conn a/a
create table a (cola number);
— insert some rows t odemonstrate that is faster when we use the view or
when
query without insert
insert into a select object_id from dba_objects;
/
/
/
/
/
/
commit;
begin dbms_stats.gather_schema_stats(‘a’); end;
/
create database link TO_b connect to b identified by b using
create force view b_v as select * from b@to_b;
In Database B:
create user b identified by b default tablespace users temporary tablespace
temp;
grant dba to b;
conn b/b
create table b (colb number);
insert into b values (100);
create table inas (cola number, colb number);
begin dbms_stats.gather_schema_stats(‘B’); end;
/
create database link TO_a connect to a identified by a using
To reproduce, connect to database b and issue the following:
explain plan for
SELECT /*+ driving_site(a) */ *
FROM a@to_a a , b
WHERE a.cola = b.colb;
explain plan for
INSERT INTO inas
SELECT /*+ driving_site(a) */ *
FROM a@to_a a , b
WHERE a.cola = b.colb;
explain plan for
INSERT INTO inas
SELECT *
FROM a@to_a a, b_v@to_a b
WHERE a.cola = b.colb;
All these steps will be provided in three script files.