You Asked

Hi Tom,

I can't seem to understand why to following scenario is happening and how I can avoid it.

I have a gigantic query, but to make it simple I will provide a dummy.

SELECT x, Y, Z
FROM schemaS.table1@Remote a inner join schemaS.table2@remote b on a.x = b.y
WHERE a.c = 'waarde1' OR b.h = 'Waarde2'

UNION ALL

SELECT x, Y, Z
FROM schemaT.table1@Remote a inner join schemaT.table2@remote b on a.x = b.y
WHERE a.c = 'waarde1' OR b.h = 'Waarde2'

If you provide a non existant value to as literal Waarde1 and Waarde2; you get an instant result with 0 rows; => My goal!

Now If i take the same query and replace the literals with actual parameters the quryplan looks completely different.
In the first query the complete query is taken into account to generate the plan;
In the second case; I get 2 seperate queries on remote with each a plan something like

plan 1
SELECT X,Y 
FROM schemaT.table1@Remote a WHERE a.x :=1

plan 2
SELECT Z
FROM schemaT.table2@Remote a WHERE b.y :=1

The result.. More then 30 minutes to return 0 rows.

and Connor said...

A quick test on my instance suggests that 12.1 supports running the plans fully remotely in both cases

SQL>
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>
SQL> explain plan for
  2  select *
  3  from   t1@loopback inner join t1@loopback t1a on t1.object_id = t1a.object_id
  4  where  t1.object_name = 'XXXXX' or t1a.subobject_name = 'YYYY'
  5  union all
  6  select *
  7  from   t2@loopback inner join t2@loopback t2a on t2.data_object_id = t2a.data_object_id
  8  where  t2.object_name = 'XXXXX' or t2a.subobject_name = 'YYYY';

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1911198332

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     5 |  1150 |  2575   (1)| 00:00:01 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|   5 |     TABLE ACCESS FULL  | T1   | 91774 |    10M|   429   (1)| 00:00:01 |   PDB1 |
|*  6 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|*  8 |     TABLE ACCESS FULL  | T1   | 91772 |    10M|   430   (1)| 00:00:01 |   PDB1 |
|*  9 |   HASH JOIN            |      |     1 |   230 |   858   (1)| 00:00:01 |        |
|* 10 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
|* 11 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"='YYYY')
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"='XXXXX')
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"='YYYY'))
   9 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
       filter("A3"."OBJECT_NAME"='XXXXX' OR "A2"."SUBOBJECT_NAME"='YYYY')
  10 - filter("A3"."DATA_OBJECT_ID" IS NOT NULL)
  11 - filter("A2"."DATA_OBJECT_ID" IS NOT NULL)

Note
-----
   - fully remote statement

35 rows selected.

SQL>
SQL> variable b1 varchar2(20)
SQL> variable b2 varchar2(20)
SQL> exec :b1 := 'XXXXX'; :b2 := 'YYYY';

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
  2  select *
  3  from   t1@loopback inner join t1@loopback t1a on t1.object_id = t1a.object_id
  4  where  t1.object_name = :b1 or t1a.subobject_name = :b2
  5  union all
  6  select *
  7  from   t2@loopback inner join t2@loopback t2a on t2.data_object_id = t2a.data_object_id
  8  where  t2.object_name = :b1 or t2a.subobject_name = :b2;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1911198332

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     5 |  1150 |  2575   (1)| 00:00:01 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|   5 |     TABLE ACCESS FULL  | T1   | 91774 |    10M|   429   (1)| 00:00:01 |   PDB1 |
|*  6 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|*  8 |     TABLE ACCESS FULL  | T1   | 91772 |    10M|   430   (1)| 00:00:01 |   PDB1 |
|*  9 |   HASH JOIN            |      |     1 |   230 |   858   (1)| 00:00:01 |        |
|* 10 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
|* 11 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"=:B2)
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"=:B1)
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"=:B2))
   9 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
       filter("A3"."OBJECT_NAME"=:B1 OR "A2"."SUBOBJECT_NAME"=:B2)
  10 - filter("A3"."DATA_OBJECT_ID" IS NOT NULL)
  11 - filter("A2"."DATA_OBJECT_ID" IS NOT NULL)

Note
-----
   - fully remote statement

35 rows selected.

so I suspect it might something environment related.

Are the character sets, versions, etc all the same between the databases ?

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 16, 2021 - 7:44 am UTC

Hi Tom, Thank you for investigating !

The NLS settings between remote and local are identical.
However, the version is different.
Th local database is 12.1.0.2 en de remote database is 11.2.0.4.

Could that be the issue ?

February 17, 2021 - 7:01 am UTC

I get the same as my original results with 12.1 talking to 11.2.0.4

SQL> explain plan for
  2      select *
  3      from   t1@db11 inner join t1@db11 t1a on t1.object_id = t1a.object_id
  4      where  t1.object_name = 'XXXXX' or t1a.subobject_name = 'YYYY'
  5      union all
  6      select *
  7      from   t2@db11 inner join t2@db11 t2a on t2.data_object_id = t2a.data_object_id
  8      where  t2.object_name = 'XXXXX' or t2a.subobject_name = 'YYYY';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 926263822

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |   166 | 68724 |  2875   (1)| 00:00:35 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |    14 |  5796 |   719   (1)| 00:00:09 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |    14 |  2898 |   359   (1)| 00:00:05 |   DB11 |
|   5 |     TABLE ACCESS FULL  | T1   |   110K|    21M|   360   (1)| 00:00:05 |   DB11 |
|*  6 |    HASH JOIN           |      |    14 |  5796 |   719   (1)| 00:00:09 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |    14 |  2898 |   359   (1)| 00:00:05 |   DB11 |
|*  8 |     TABLE ACCESS FULL  | T1   |   110K|    21M|   360   (1)| 00:00:05 |   DB11 |
|   9 |   CONCATENATION        |      |       |       |            |          |        |
|* 10 |    HASH JOIN           |      |    69 | 28566 |   719   (1)| 00:00:09 |        |
|* 11 |     TABLE ACCESS FULL  | T2   |    14 |  2898 |   359   (1)| 00:00:05 |   DB11 |
|  12 |     TABLE ACCESS FULL  | T2   |   101K|    20M|   359   (1)| 00:00:05 |   DB11 |
|* 13 |    HASH JOIN           |      |    69 | 28566 |   719   (1)| 00:00:09 |        |
|* 14 |     TABLE ACCESS FULL  | T2   |    14 |  2898 |   359   (1)| 00:00:05 |   DB11 |
|* 15 |     TABLE ACCESS FULL  | T2   |   101K|    20M|   360   (1)| 00:00:05 |   DB11 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"='YYYY')
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"='XXXXX')
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"='YYYY'))
  10 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
  11 - filter("A2"."SUBOBJECT_NAME"='YYYY')
  13 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
  14 - filter("A3"."OBJECT_NAME"='XXXXX')
  15 - filter(LNNVL("A2"."SUBOBJECT_NAME"='YYYY'))

Note
-----
   - fully remote statement
   - dynamic statistics used: dynamic sampling (level=2)

41 rows selected.

SQL>
SQL>
SQL> variable b1 varchar2(20)
SQL> variable b2 varchar2(20)
SQL> exec :b1 := 'XXXXX'; :b2 := 'YYYY';

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
  2      select *
  3      from   t1@db11 inner join t1@db11 t1a on t1.object_id = t1a.object_id
  4      where  t1.object_name = :b1 or t1a.subobject_name = :b2
  5      union all
  6      select *
  7      from   t2@db11 inner join t2@db11 t2a on t2.data_object_id = t2a.data_object_id
  8      where  t2.object_name =  :b1 or t2a.subobject_name =  :b2;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 926263822

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      | 11876 |  4801K|  2875   (1)| 00:00:35 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |  1110 |   448K|   719   (1)| 00:00:09 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |  1110 |   224K|   359   (1)| 00:00:05 |   DB11 |
|   5 |     TABLE ACCESS FULL  | T1   |   110K|    21M|   360   (1)| 00:00:05 |   DB11 |
|*  6 |    HASH JOIN           |      |  1111 |   449K|   719   (1)| 00:00:09 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |  1110 |   224K|   359   (1)| 00:00:05 |   DB11 |
|*  8 |     TABLE ACCESS FULL  | T1   |   109K|    21M|   360   (1)| 00:00:05 |   DB11 |
|   9 |   CONCATENATION        |      |       |       |            |          |        |
|* 10 |    HASH JOIN           |      |  4852 |  1961K|   719   (1)| 00:00:09 |        |
|* 11 |     TABLE ACCESS FULL  | T2   |  1019 |   205K|   359   (1)| 00:00:05 |   DB11 |
|  12 |     TABLE ACCESS FULL  | T2   |   101K|    20M|   359   (1)| 00:00:05 |   DB11 |
|* 13 |    HASH JOIN           |      |  4803 |  1941K|   719   (1)| 00:00:09 |        |
|* 14 |     TABLE ACCESS FULL  | T2   |  1019 |   205K|   359   (1)| 00:00:05 |   DB11 |
|* 15 |     TABLE ACCESS FULL  | T2   |   100K|    19M|   360   (1)| 00:00:05 |   DB11 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"=:B2)
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"=:B1)
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"=:B2))
  10 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
  11 - filter("A2"."SUBOBJECT_NAME"=:B2)
  13 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
  14 - filter("A3"."OBJECT_NAME"=:B1)
  15 - filter(LNNVL("A2"."SUBOBJECT_NAME"=:B2))

Note
-----
   - fully remote statement
   - dynamic statistics used: dynamic sampling (level=2)

41 rows selected.

Can you paste the output of your explain plan in the same way I have above so we can take a look

DB Link query

A reader, February 16, 2021 - 9:05 am UTC

Try with DRIVING_SITE hint and see if that help.

Cheers!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

DB Link query

More to Explore

Administration

Query plan with bind variables across db link

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

DB Link query

More to Explore

Administration