OraFAQ Forum: Performance Tuning » NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS

Home » RDBMS Server » Performance Tuning » NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS (9i/10g/11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569656]

Tue, 30 October 2012 13:56

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

This is on an 11.2.0.2 database.

Hi all. I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).

What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?

I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.

I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).

SELECT                     A.POLICY , 
                           F.MIN_MEMBER_ID, 
                           MIN(A.EFF_DATE) EFF_DATE, 
                           A.EXP_DATE , 
                           G.DESCRIPTION PROGRAM_NAME, 
                           E.DESCRIPTION TRANS_DESCR, 
                           A.BOOK_DATE, 
                           SUM(A.PREMIUM)  PREMIUM 
FROM                      PRMM_DTL_HSTRY a, 
                          GRP_CODE97 b, 
                          Table_0002 @ITMS.THEHARTFORD.COM E, 
                          Table_0408 @ITMS.THEHARTFORD.COM G, 
                          CAPTV.MEMBER_ID_TABLE F                    
WHERE   A.gc_key=b.gc_key 
AND     A.POLICY =F.POLICY(+) 
AND     A.trans_type=E.PREMIUM_TRX 
AND     TRIM(G.PROGRAM_ID)=SUBSTR(A.POLICY,6,3) - put 'OH2' here as well
and     A.book_date>'200912'
And     A.TRANS_EFF_DATE>'200912'
AND     A.eff_date >='201201'
AND     A.EXP_DATE <='201307'
AND     A.BTC ='1' 
AND     TRIM(G.PROGRAM_ID) in ('OH2')
AND     SUBSTR(B.mjr_line_code,1,1)='6' 
GROUP BY A.POLICY, 
            F.MIN_MEMBER_ID, 
            G.DESCRIPTION, 
            E.DESCRIPTION, 
            A.BOOK_DATE,                  
            A.EXP_DATE ;

---------------------------------------------------------------------------------------------------------------------------
| Id  |Operation                      | Name             | Rows | Bytes| Cost (%CPU)| Time    | Pstart| Pstop| Inst|IN-OUT|
---------------------------------------------------------------------------------------------------------------------------
|   0 |SELECT STATEMENT               |                  |     1|   177|   781   (1)| 00:00:07|       |      |     |      |
|   1 | HASH GROUP BY                 |                  |     1|   177|   781   (1)| 00:00:07|       |      |     |      |
|   2 |  NESTED LOOPS                 |                  |      |      |            |         |       |      |     |      |
|   3 |   NESTED LOOPS                |                  |     1|   177|   780   (1)| 00:00:07|       |      |     |      |
|   4 |    NESTED LOOPS               |                  |     1|   166|   778   (1)| 00:00:07|       |      |     |      |
|*  5 |     HASH JOIN OUTER           |                  |     1|   126|   776   (1)| 00:00:07|       |      |     |      |
|*  6 |      HASH JOIN                |                  |     1|   113|   773   (1)| 00:00:07|       |      |     |      |
|*  7 |       TA BY GLOBAL INDEX ROWID| PRMM_DTL_HSTRY   |     4|   220|   771   (1)| 00:00:07| ROWID | ROWID|     |      |
|*  8 |        INDEX RANGE SCAN       | PRMM_DTL_HSTRY_N7|   12K|      |    39   (0)| 00:00:01|       |      |     |      |
|   9 |       REMOTE                  | TABLE_0408       |     8|   464|     2   (0)| 00:00:01|       |      | ITMS| R->S |
|  10 |      TABLE ACCESS FULL        | MEMBER_ID_TABLE  |     1|    13|     2   (0)| 00:00:01|       |      |     |      |
|  11 |     REMOTE                    | TABLE_0002       |     1|    40|     2   (0)| 00:00:01|       |      | ITMS| R->S |
|* 12 |    INDEX UNIQUE SCAN          | GRP_CODE97_PK    |     1|      |     1   (0)| 00:00:01|       |      |     |      |
|* 13 |   TABLE ACCESS BY INDEX ROWID | GRP_CODE97       |     1|    11|     2   (0)| 00:00:01|       |      |     |      |
---------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   9 - SELECT PROGRAM_ID,DESCRIPTION FROM TABLE_0408 G WHERE TRIM(PROGRAM_ID)='OH2' (accessing <remote host name goes here> )
 
  11 - SELECT PREMIUM_TRX,DESCRIPTION FROM TABLE_0002 E WHERE PREMIUM_TRX=:1 (accessing <remote host name goes here> )
 
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Anything you guys can tell me is worth it to me. I think I know what happens but I am really just guessing. Any documentation links you know of would be most helpful too.

Kevin

[Updated on: Tue, 30 October 2012 14:01]

Report message to a moderator

Re: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569665 is a reply to message #569656]

Tue, 30 October 2012 16:58

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hello Kevin

in my opinion you are right with your assumption regarding nested loop across the network. You can verify this, if you run your sql with runtime statistics and after that find out the execution plan. Alternatively you can try to get a report of sql monitoring.

Best regards
Leonid

Report message to a moderator

Re: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569676 is a reply to message #569665]

Wed, 31 October 2012 03:16

John Watson
Messages: 8928
Registered: January 2010
Location: Global Village

Senior Member

I've tried to simplify things: working in SCOTT, with a dblink that loops back. I had to hint the query to get a nested loop with the local table as the driving table. First, just to check the plan:

orcl> set autot traceonly exp
orcl> select /*+ use_nl(emp dept) ordered */ * from emp natural join dept@l1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2064233004

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   812 |    31   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS      |      |    14 |   812 |    31   (0)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |        |      |
|   3 |   REMOTE           | DEPT |     1 |    20 |     2   (0)| 00:00:01 |     L1 | R->S |
-------------------------------------------------------------------------------------------

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

   3 - SELECT /*+ USE_NL ("DEPT") */ "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT"
       WHERE :1="DEPTNO" (accessing 'L1' )


orcl>

Interesting that the remote operation at Id 3 is written to use a bind variable.
And now with the plan statistics, as Leonid suggested:

orcl> select /*+ use_nl(emp dept) ordered gather_plan_statistics */ * from emp natural join dept@l1;

    DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM DNAME
---------- ---------- ---------- --------- ---------- ----------------- ---------- ---------- ------
--
        20       7369 SMITH      CLERK           7902 17-12-80 00:00:00        800            RESEAR
        30       7499 ALLEN      SALESMAN        7698 20-02-81 00:00:00       1600        300 SALES
        30       7521 WARD       SALESMAN        7698 22-02-81 00:00:00       1250        500 SALES
        20       7566 JONES      MANAGER         7839 02-04-81 00:00:00       2975            RESEAR
        30       7654 MARTIN     SALESMAN        7698 28-09-81 00:00:00       1250       1400 SALES
        30       7698 BLAKE      MANAGER         7839 01-05-81 00:00:00       2850            SALES
        10       7782 CLARK      MANAGER         7839 09-06-81 00:00:00       2450            ACCOUN
        20       7788 SCOTT      ANALYST         7566 19-04-87 00:00:00       3000            RESEAR
        10       7839 KING       PRESIDENT            17-11-81 00:00:00       5000            ACCOUN
        30       7844 TURNER     SALESMAN        7698 08-09-81 00:00:00       1500          0 SALES
        20       7876 ADAMS      CLERK           7788 23-05-87 00:00:00       1100            RESEAR
        30       7900 JAMES      CLERK           7698 03-12-81 00:00:00        950            SALES
        20       7902 FORD       ANALYST         7566 03-12-81 00:00:00       3000            RESEAR
        10       7934 MILLER     CLERK           7782 23-01-82 00:00:00       1300            ACCOUN

14 rows selected.

orcl> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
------------------------------
SQL_ID  8ppbqscuwr8zu, child number 0
-------------------------------------
select /*+ use_nl(emp dept) ordered gather_plan_statistics */ * from
emp natural join dept@l1

Plan hash value: 2064233004

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
|   3 |   REMOTE           | DEPT |     14 |      1 |     14 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------


16 rows selected.

orcl>

For each row returned by Id 2, there is a remote lookup returning 1 row at Id 3. Is that what you were asking?

Report message to a moderator

Re: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569699 is a reply to message #569676]

Wed, 31 October 2012 07:08

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I think so. The more I think about it the more confused I get. This is pretty good work so far. But what I would really like to know is if there is any kind of batching going on. In the end, all rows that come out of EMP will make there way over to the remote site and drive a lookup into the dept table. I just wonder how it is batched if at all. I don't think the query plan output will tell us that. I think I have to look either in the OUTLINE section on an 11g database for a NLJ_BATCH operation, or find some other way to monitor network traffic or SQL*NET / ORACLE*NET traffic.

Thanks. Kevin

[Updated on: Wed, 31 October 2012 07:12]

Report message to a moderator

Re: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569704 is a reply to message #569699]

Wed, 31 October 2012 07:58

John Watson
Messages: 8928
Registered: January 2010
Location: Global Village

Senior Member

I don't think there is any sort of batching of the remote query. In my little example, see this:

orcl> alter system flush shared_pool;

System altered.

orcl> select sql_text,executions from v$sql where lower(sql_text) like '%dept%';

SQL_TEXT
----------------------------------------------------------------------------------------------------
------------------------------
EXECUTIONS
----------
select sql_text,executions from v$sql where lower(sql_text) like '%dept%'
         1


orcl> select /*+ use_nl(emp dept) ordered */ * from emp natural join dept@l1;

    DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM DNAME
---------- ---------- ---------- --------- ---------- ----------------- ---------- ---------- ------
--
        20       7369 SMITH      CLERK           7902 17-12-80 00:00:00        800            RESEAR
        30       7499 ALLEN      SALESMAN        7698 20-02-81 00:00:00       1600        300 SALES
        30       7521 WARD       SALESMAN        7698 22-02-81 00:00:00       1250        500 SALES
        20       7566 JONES      MANAGER         7839 02-04-81 00:00:00       2975            RESEAR
        30       7654 MARTIN     SALESMAN        7698 28-09-81 00:00:00       1250       1400 SALES
        30       7698 BLAKE      MANAGER         7839 01-05-81 00:00:00       2850            SALES
        10       7782 CLARK      MANAGER         7839 09-06-81 00:00:00       2450            ACCOUN
        20       7788 SCOTT      ANALYST         7566 19-04-87 00:00:00       3000            RESEAR
        10       7839 KING       PRESIDENT            17-11-81 00:00:00       5000            ACCOUN
        30       7844 TURNER     SALESMAN        7698 08-09-81 00:00:00       1500          0 SALES
        20       7876 ADAMS      CLERK           7788 23-05-87 00:00:00       1100            RESEAR
        30       7900 JAMES      CLERK           7698 03-12-81 00:00:00        950            SALES
        20       7902 FORD       ANALYST         7566 03-12-81 00:00:00       3000            RESEAR
        10       7934 MILLER     CLERK           7782 23-01-82 00:00:00       1300            ACCOUN

14 rows selected.

orcl> select sql_text,executions from v$sql where lower(sql_text) like '%dept%';

SQL_TEXT
----------------------------------------------------------------------------------------------------
------------------------------
EXECUTIONS
----------
select /*+ use_nl(emp dept) ordered */ * from emp natural join dept@l1
         1

SELECT /*+ USE_NL ("DEPT") */ "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" WHERE :1="DEPTNO"
        14

SELECT /*+ FULL(P) +*/ * FROM "DEPT" P
         0

select sql_text,executions from v$sql where lower(sql_text) like '%dept%'
         2


orcl>

so the query against dept, with the bind variable, does seem to have been executed 14 times. I think this could be pretty inefficient! If the optimizer can't do a rewrite to a plan that brings across the whole table in one go, I suppose one has to hint it, which I would hate to do.

Incidentally, I do not understand where that query
SELECT /*+ FULL(P) +*/ * FROM "DEPT" P
which apparently wasn't executed once came from.

ps - I see you are in Connecticut. Some people I work with in Rhode Island have been offline for two days now. Clearly, you must have better power and comms infrastructure.

Report message to a moderator

Re: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS [message #569706 is a reply to message #569704]

Wed, 31 October 2012 08:13

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I guess I will have to do some testing to see if these number change for the new NLJ_BATCH method. Thanks for showing me how to get the information I need easily.

Yes, I am in CT. I used to live in RI but 25 or so years ago when the people elected a convicted criminal to be the mayor of the capitol city Providence I figured it was time to move. I think it was a good move. Today RI is officially broke. I think 2 out of 5 people works for the state (might even be higher)? RI was alwasys a state like that. They can not afford to pay their pension obligations now and have chopped them way back. So I suspect there is no money for infrastructure updates which might explain why they have no power. This has been a problem for them for many years.

We got lucky too though. We just moved to a new house two miles from the old one in July (taking advantage of prices / rates / inflaction benefits to move up (we got an almost unbelievable deal compared to the last house we owned)). It is a much better neighborhood (richer I guess so it reflects certain differences). We lost four trees at the new place. Two fell close but missed the house by a few inches. My power lines are underground (another example of upper scale habitation) so no trees fell on my lines. It could have been bad for us had the trees hit anything.

I read Sandy took out 1/3 of all cell towers. That was an interesting stat.

Thanks for noticing.

Report message to a moderator

Previous Topic:	Merge Join Cartesian in Outline Data
Next Topic:	SQL execution plan different on different hardware

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 07:21:07 CDT 2024