Home » SQL & PL/SQL » SQL & PL/SQL » driving_site hint (DB12.x)
driving_site hint [message #680515] |
Tue, 19 May 2020 10:30 |
John Watson
Messages: 8938 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 |
Lalit Kumar B
Messages: 3174 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 #680521 is a reply to message #680519] |
Tue, 19 May 2020 11:57 |
John Watson
Messages: 8938 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 |
John Watson
Messages: 8938 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 |
Lalit Kumar B
Messages: 3174 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 |
|
Michel Cadot
Messages: 68665 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.
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 #680528 is a reply to message #680526] |
Tue, 19 May 2020 12:27 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
John Watson wrote on Tue, 19 May 2020 22:27I 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 |
|
Michel Cadot
Messages: 68665 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 |
John Watson
Messages: 8938 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 |
|
Michel Cadot
Messages: 68665 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 |
John Watson
Messages: 8938 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 #680541 is a reply to message #680540] |
Wed, 20 May 2020 03:12 |
|
Michel Cadot
Messages: 68665 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 |
John Watson
Messages: 8938 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 #680550 is a reply to message #680549] |
Wed, 20 May 2020 14:34 |
John Watson
Messages: 8938 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 |
Lalit Kumar B
Messages: 3174 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 |
Lalit Kumar B
Messages: 3174 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.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 22:56:31 CDT 2024
|