Home » SQL & PL/SQL » SQL & PL/SQL » driving_site hint (DB12.x)
driving_site hint [message #680515] Tue, 19 May 2020 10:30 Go to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
I am having trouble understanding how to use the driving_site hint. This is a query joining a few large tables at a remote site to a small local table, and as far as I can see the large tables are all being sent through the db link and the join is done locally. If I run the query at the remote site and use a link in the other direction to get to the small table, it runs astronomically faster. So I figure that I need to tell Oracle to send the small table to the remote site and fetch the result back across the link. The driving_site hint should do that, but I can't see it having any effect. In this example, in the scott schema, the plans are unchanged:
orclz>
orclz> create database link l1 using 'orclz';

Database link created.

orclz> set autot trace exp
orclz>
orclz> select * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Execution Plan
----------------------------------------------------------
Plan hash value: 383447681

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS       |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS      |       |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           | EMP   |     1 |    38 |     0   (0)| 00:00:01 |     L1 | R->S |
|   5 |   REMOTE            | DEPT  |     1 |    20 |     1   (0)| 00:00:01 |     L1 | R->S |
---------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM
       "EMP" "EMP" WHERE "ENAME"=:1 (accessing 'L1' )

   5 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" WHERE :1="DEPTNO" (accessing
       'L1' )


orclz> select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Execution Plan
----------------------------------------------------------
Plan hash value: 383447681

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS       |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS      |       |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           | EMP   |     1 |    38 |     0   (0)| 00:00:01 |     L1 | R->S |
|   5 |   REMOTE            | DEPT  |     1 |    20 |     1   (0)| 00:00:01 |     L1 | R->S |
---------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT /*+ */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
       FROM "EMP" "EMP" WHERE "ENAME"=:1 (accessing 'L1' )

   5 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" WHERE :1="DEPTNO" (accessing
       'L1' )


orclz>
orclz>
What am I missing?

Thank you for any insight.
Re: driving_site hint [message #680517 is a reply to message #680515] Tue, 19 May 2020 10:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
John, I believe you need to use a table alias in the hint. Could you please try using an alias to the tables in the join and use the same in the hint as well.
Re: driving_site hint [message #680519 is a reply to message #680517] Tue, 19 May 2020 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did agree with you, Lalit, in former versions but it seems it is no more the case in 12.2, as you can see Oracle now internally adds an alias with same name than the table when the alias does not exist.

Re: driving_site hint [message #680521 is a reply to message #680519] Tue, 19 May 2020 11:57 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. I've tried with aliases, no difference: I still get two remote queries with the join local.

When using EXPLAIN PLAN, in release 19 you get a Hint Report which tells you about any hints that didn't work but there is no mention of my hint, which implies that it DID work. Except that it didn't. Forexample, a leading hint definitely has an effect:
orclz> explain plan for  select /*+ driving_site(emp) leading(dept) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 184774649

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    97 |     8   (0)| 00:00:01 |        |      |
|*  1 |  HASH JOIN         |       |     1 |    97 |     8   (0)| 00:00:01 |        |      |
|*  2 |   HASH JOIN        |       |    14 |   812 |     6   (0)| 00:00:01 |        |      |
|   3 |    REMOTE          | DEPT  |     4 |    80 |     3   (0)| 00:00:01 |     L1 | R->S |
|   4 |    REMOTE          | EMP   |    14 |   532 |     3   (0)| 00:00:01 |     L1 | R->S |
|   5 |   TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
--------------------------------------------------------------------------------------------

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

   1 - access("EMP"."ENAME"="BONUS"."ENAME")
   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" (accessing 'L1' )

   4 - SELECT /*+ */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
       FROM "EMP" "EMP" (accessing 'L1' )
Re: driving_site hint [message #680522 is a reply to message #680519] Tue, 19 May 2020 11:57 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. I've tried with aliases, no difference: I still get two remote queries with the join local.

When using EXPLAIN PLAN, in release 19 you get a Hint Report which tells you about any hints that didn't work but there is no mention of my hint, which implies that it DID work. Except that it didn't. Forexample, a leading hint definitely has an effect:
orclz> explain plan for  select /*+ driving_site(emp) leading(dept) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 184774649

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    97 |     8   (0)| 00:00:01 |        |      |
|*  1 |  HASH JOIN         |       |     1 |    97 |     8   (0)| 00:00:01 |        |      |
|*  2 |   HASH JOIN        |       |    14 |   812 |     6   (0)| 00:00:01 |        |      |
|   3 |    REMOTE          | DEPT  |     4 |    80 |     3   (0)| 00:00:01 |     L1 | R->S |
|   4 |    REMOTE          | EMP   |    14 |   532 |     3   (0)| 00:00:01 |     L1 | R->S |
|   5 |   TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
--------------------------------------------------------------------------------------------

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

   1 - access("EMP"."ENAME"="BONUS"."ENAME")
   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" (accessing 'L1' )

   4 - SELECT /*+ */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
       FROM "EMP" "EMP" (accessing 'L1' )
Re: driving_site hint [message #680523 is a reply to message #680519] Tue, 19 May 2020 12:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks Michel, I wasn't aware of this. Mostly, for fully qualified objects having schema name or dblink I always used aliases as best practice, so thought alias might be required in John's query.

I checked the 11.1 documentation and here's an excerpt:

Quote:

Using the DRIVING_SITE Hint

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
   WHERE emp.deptno = dept.deptno;
You're right, there's no alias used in the above example from online 11.1 documentation too.
Re: driving_site hint [message #680525 is a reply to message #680523] Tue, 19 May 2020 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There were never aliases in the documentation even when they were required, these were documentation bugs (as this can't be a code bug, can it be?) in all previous versions never fixed. Sad
So we can't rely on the documentation on this point.

[Updated on: Tue, 19 May 2020 12:13]

Report message to a moderator

Re: driving_site hint [message #680526 is a reply to message #680525] Tue, 19 May 2020 12:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 19 May 2020 22:42

So we can't rely on the documentation on this point.
I completely agree!
Re: driving_site hint [message #680528 is a reply to message #680526] Tue, 19 May 2020 12:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Tue, 19 May 2020 22:27
I still get two remote queries with the join local.
Yeah, that's strange because with the hint I expect to see just one REMOTE operation in the plan for BONUS table. Unless the operation must be done at particular site, this is a pure select so ideally optimizer should enforce the hint. Something is happening under the hood which is not transparently shown in explain plan. Perhaps you could raise a SR and find the reason for this behavior with Oracle.
Re: driving_site hint [message #680530 is a reply to message #680515] Tue, 19 May 2020 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John,

I reproduce the same test and it works for me:
SQL> set autot trace exp
SQL> select * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Execution Plan
----------------------------------------------------------
Plan hash value: 383447681

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS       |       |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS      |       |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           | EMP   |     1 |    38 |     0   (0)| 00:00:01 |     L1 | R->S |
|   5 |   REMOTE            | DEPT  |     1 |    20 |     1   (0)| 00:00:01 |     L1 | R->S |
---------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM
       "EMP" "EMP" WHERE "ENAME"=:1 (accessing 'L1' )

   5 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" WHERE :1="DEPTNO" (accessing
       'L1' )


SQL> select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Execution Plan
----------------------------------------------------------
Plan hash value: 3545540607

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS                          |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                         |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS                        |           |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   4 |     REMOTE                             | BONUS     |     1 |    39 |     2   (0)| 00:00:01 |      ! | R->S |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     1 |    38 |     0   (0)| 00:00:01 | MIKC2~ |      |
|*  6 |      INDEX RANGE SCAN                  | EMP_ENAME |     1 |       |     0   (0)| 00:00:01 | MIKC2~ |      |
|*  7 |    INDEX UNIQUE SCAN                   | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 | MIKC2~ |      |
|   8 |   TABLE ACCESS BY INDEX ROWID          | DEPT      |     1 |    20 |     1   (0)| 00:00:01 | MIKC2~ |      |
--------------------------------------------------------------------------------------------------------------------

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

   6 - access("A3"."ENAME"="A1"."ENAME")
   7 - access("A3"."DEPTNO"="A2"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "ENAME","JOB","SAL","COMM" FROM "BONUS" "A1" (accessing '!' )


Note
-----
   - fully remote statement
   - this is an adaptive plan
Local version is: 18.3.0.0.180717 EE
Remote version is: 12.2.0.1.181016 EE

[Updated on: Tue, 19 May 2020 13:50]

Report message to a moderator

Re: driving_site hint [message #680531 is a reply to message #680530] Tue, 19 May 2020 14:05 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Well, tickle my bum and call me Donald. You have the result Lalit and I expected. THank you for comfirming that it does (or should) work.

I'm not sure I can face the soul destroying process of engaging with Support. This whole business has come from an appalling and still incomplete exercise in upgrading from 12.1 to 19 (the whole stack: RAC, Data Guard, Exadata, plus a shed load of application tier stuff) with the "assistance" of Platinum Support.
Re: driving_site hint [message #680532 is a reply to message #680531] Tue, 19 May 2020 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Strange the generated aliases are A1, A2, A3 in my version (18.3) when in yours they have the name of the tables;
this definitively shows the optimizer has changed between these 2 versions (and that these aliases are not mandatory for this hint).

[Updated on: Tue, 19 May 2020 15:19]

Report message to a moderator

Re: driving_site hint [message #680535 is a reply to message #680532] Wed, 20 May 2020 01:43 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Michel, may I ask you to do one more test for me:

explain plan for select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);
select * from table(dbms_xplan.display(format=>'outline');

perhaps with a full set of hints I can work out what is going on. Anything to avoid raising a TAR, which I think would be hopeless for this one.
Re: driving_site hint [message #680539 is a reply to message #680535] Wed, 20 May 2020 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's what I get:
SQL> explain plan for select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

SQL> select * from table(dbms_xplan.display(format=>'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3545540607

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS                          |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                         |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS                        |           |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   4 |     REMOTE                             | BONUS     |     1 |    39 |     2   (0)| 00:00:01 |      ! | R->S |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     1 |    38 |     0   (0)| 00:00:01 | MIKC2~ |      |
|*  6 |      INDEX RANGE SCAN                  | EMP_ENAME |     1 |       |     0   (0)| 00:00:01 | MIKC2~ |      |
|*  7 |    INDEX UNIQUE SCAN                   | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 | MIKC2~ |      |
|   8 |   TABLE ACCESS BY INDEX ROWID          | DEPT      |     1 |    20 |     1   (0)| 00:00:01 | MIKC2~ |      |
--------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "A2"@"SEL$1")
      USE_NL(@"SEL$1" "A2"@"SEL$1")
      USE_NL(@"SEL$1" "A3"@"SEL$1")
      LEADING(@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1" "A2"@"SEL$1")
      INDEX(@"SEL$1" "A2"@"SEL$1" ("DEPT"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A3"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A3"@"SEL$1" ("EMP"."ENAME"))
      FULL(@"SEL$1" "A1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   6 - access("A3"."ENAME"="A1"."ENAME")
   7 - access("A3"."DEPTNO"="A2"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "ENAME","JOB","SAL","COMM" FROM "BONUS" "A1" (accessing '!' )


Note
-----
   - fully remote statement
   - this is an adaptive plan

53 rows selected.
Re: driving_site hint [message #680540 is a reply to message #680539] Wed, 20 May 2020 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Made another test which works:
SQL> explain plan for select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

SQL> select * from table(dbms_xplan.display(format=>'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3545540607

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS                          |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                         |           |     1 |    97 |     3   (0)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS                        |           |     1 |    77 |     2   (0)| 00:00:01 |        |      |
|   4 |     REMOTE                             | BONUS     |     1 |    39 |     2   (0)| 00:00:01 |      ! | R->S |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     1 |    38 |     0   (0)| 00:00:01 | MIKCD~ |      |
|*  6 |      INDEX RANGE SCAN                  | EMP_ENAME |     1 |       |     0   (0)| 00:00:01 | MIKCD~ |      |
|*  7 |    INDEX UNIQUE SCAN                   | PK_DEPT   |     1 |       |     0   (0)| 00:00:01 | MIKCD~ |      |
|   8 |   TABLE ACCESS BY INDEX ROWID          | DEPT      |     1 |    20 |     1   (0)| 00:00:01 | MIKCD~ |      |
--------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "A2"@"SEL$1")
      USE_NL(@"SEL$1" "A2"@"SEL$1")
      USE_NL(@"SEL$1" "A3"@"SEL$1")
      LEADING(@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1" "A2"@"SEL$1")
      INDEX(@"SEL$1" "A2"@"SEL$1" ("DEPT"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A3"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "A3"@"SEL$1" ("EMP"."ENAME"))
      FULL(@"SEL$1" "A1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   6 - access("A3"."ENAME"="A1"."ENAME")
   7 - access("A3"."DEPTNO"="A2"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "ENAME","JOB","SAL","COMM" FROM "BONUS" "A1" (accessing '!' )


Note
-----
   - fully remote statement
   - this is an adaptive plan
Local version is: 12.2.0.1.181016 EE
Remote version is: 12.1.0.2.181016 EE

Re: driving_site hint [message #680541 is a reply to message #680540] Wed, 20 May 2020 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And another one which works:
SQL> explain plan for select /*+ driving_site(emp) */ * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

SQL> select * from table(dbms_xplan.display(format=>'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2831136951

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE      |         |     1 |   156 |     6   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS                |         |     1 |   156 |     6   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS               |         |     1 |   156 |     6   (0)| 00:00:01 |        |      |
|*  3 |    HASH JOIN                 |         |     1 |   126 |     5   (0)| 00:00:01 |        |      |
|   4 |     REMOTE                   | BONUS   |     1 |    39 |     2   (0)| 00:00:01 |      ! | R->S |
|   5 |     TABLE ACCESS FULL        | EMP     |    14 |  1218 |     3   (0)| 00:00:01 | MIKCD~ |      |
|*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | MIKCD~ |      |
|   7 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 | MIKCD~ |      |
--------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "A2"@"SEL$1")
      USE_NL(@"SEL$1" "A2"@"SEL$1")
      USE_HASH(@"SEL$1" "A3"@"SEL$1")
      LEADING(@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1" "A2"@"SEL$1")
      INDEX(@"SEL$1" "A2"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "A3"@"SEL$1")
      FULL(@"SEL$1" "A1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   3 - access("A3"."ENAME"="A1"."ENAME")
   6 - access("A3"."DEPTNO"="A2"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "ENAME","JOB","SAL","COMM" FROM "BONUS" "A1" (accessing '!' )


Note
-----
   - fully remote statement
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
Using 2 pluggable db in the same container, version 12.1.0.2.181016 EE.

[Updated on: Wed, 20 May 2020 03:15]

Report message to a moderator

Re: driving_site hint [message #680542 is a reply to message #680541] Wed, 20 May 2020 03:31 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
When I paste in the full set of hints (which I notice does not include DRIVING_SITE) I get a plan which does join the two remote tables remotely, but it still does not push the local table to the remote site. I did create your index on ENAME. I am beginning to think there is no option but to raise a TAR.
orclz> explain plan for select
  2  /*+
  3        BEGIN_OUTLINE_DATA
  4        NLJ_BATCHING(@"SEL$1" "A2"@"SEL$1")
  5        USE_NL(@"SEL$1" "A2"@"SEL$1")
  6        USE_NL(@"SEL$1" "A3"@"SEL$1")
  7        LEADING(@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1" "A2"@"SEL$1")
  8        INDEX(@"SEL$1" "A2"@"SEL$1" ("DEPT"."DEPTNO"))
  9        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A3"@"SEL$1")
 10        INDEX_RS_ASC(@"SEL$1" "A3"@"SEL$1" ("EMP"."ENAME"))
 11        FULL(@"SEL$1" "A1"@"SEL$1")
 12        OUTLINE_LEAF(@"SEL$1")
 13        ALL_ROWS
 14        DB_VERSION('12.1.0.2')
 15        OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
 16        IGNORE_OPTIM_EMBEDDED_HINTS
 17        END_OUTLINE_DATA
 18    */
 19  * from emp@l1 join dept@l1 using (deptno) join bonus using(ename);

Explained.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3421844489

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |   143 |     8   (0)| 00:00:01 |        |      |
|*  1 |  HASH JOIN         |       |     1 |   143 |     8   (0)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS FULL| BONUS |     1 |    39 |     2   (0)| 00:00:01 |        |      |
|   3 |   VIEW             |       |    14 |  1456 |     6   (0)| 00:00:01 |        |      |
|   4 |    REMOTE          |       |       |       |            |          |     L1 | R->S |
--------------------------------------------------------------------------------------------

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

   1 - access("from$_subquery$_003"."ENAME"="BONUS"."ENAME")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
       "A1"."DEPTNO","A2"."EMPNO","A2"."ENAME","A2"."JOB","A2"."MGR","A2"."HIREDATE","A2"."
       SAL","A2"."COMM","A1"."DNAME","A1"."LOC" FROM "EMP" "A2","DEPT" "A1" WHERE
       "A2"."DEPTNO"="A1"."DEPTNO" (accessing 'L1' )


Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (N - Unresolved (8))
---------------------------------------------------------------------------

   4 -  SEL$1

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
         N -  LEADING(@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1" "A2"@"SEL$1")
         N -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A3"@"SEL$1")
         N -  FULL(@"SEL$1" "A1"@"SEL$1")
         N -  INDEX(@"SEL$1" "A2"@"SEL$1" ("DEPT"."DEPTNO"))
         N -  INDEX_RS_ASC(@"SEL$1" "A3"@"SEL$1" ("EMP"."ENAME"))
         N -  NLJ_BATCHING(@"SEL$1" "A2"@"SEL$1")
         N -  USE_NL(@"SEL$1" "A2"@"SEL$1")
         N -  USE_NL(@"SEL$1" "A3"@"SEL$1")

39 rows selected.

orclz>
Re: driving_site hint [message #680549 is a reply to message #680542] Wed, 20 May 2020 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My latest test between 2 PDB in 12.1 does not use the index on ENAME (it does not exist in these bases) but still use "fully remote statement".

Re: driving_site hint [message #680550 is a reply to message #680549] Wed, 20 May 2020 14:34 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
For the actual problem (which is of course a lot more complicated than this test case) I have a solution, which is this hint:
/*+ driving_site(@sel$1 s) */
s is the alias of one of the remote tables and I just experimented with the @sel$? until it worked.

Using the set of hints you provided, I did identify the hint that mattered: it is OUTLINE_LEAF(@"SEL$1"), I can remove all the others and with just that one I do get most of the query running remotely. Clearly that hint is not saying "run this remotely" but it does seem to trigger a re-write that means the optimizer does run the query remotely.

This is beyond my knowledge, which I find extremely annoying. I am very grateful for the input from both of you on this and If I ever do get a proper understanding of what is going on I'll write back.
Re: driving_site hint [message #680551 is a reply to message #680532] Wed, 20 May 2020 15:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 20 May 2020 01:48

Strange the generated aliases are A1, A2, A3 in my version (18.3) when in yours they have the name of the tables;
this definitively shows the optimizer has changed between these 2 versions (and that these aliases are not mandatory for this hint).
Hi Michel,

It's weird behavior in different versions. I am using Oracle version 19.3, and it uses table names for aliases internally, if an alias is not used for the respective table. I tried to find more on this behavior, but I couldn't find anything neither in online documentation nor in MOS.

[Updated on: Wed, 20 May 2020 16:07]

Report message to a moderator

Re: driving_site hint [message #680552 is a reply to message #680550] Wed, 20 May 2020 15:53 Go to previous message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Thu, 21 May 2020 01:04

This is beyond my knowledge, which I find extremely annoying. I am very grateful for the input from both of you on this and If I ever do get a proper understanding of what is going on I'll write back.
Yes, please let us know here. I tried to gather some more insights, but couldn't find any. Regarding alias and how optimizer interprets it, I see different behavior in different versions. Documentation about it is either not available in most versions, or it's sparse.

Specifically about the driving_site hint, I didn't come across such an issue in recent past. I used it long back in 11.2.0.4 version and it worked as expected back then.
Previous Topic: ORA-21561: OID generation failed !!
Next Topic: The cost of FILTER operations
Goto Forum:
  


Current Time: Sat Sep 26 00:47:36 CDT 2020