There is one hint you can use if you want to seee the number of executions in the execution plan of the query.
It's the GATHER_PLAN_STATISTICS hint.
Query:
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id;
Execution plan:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bn9y09ykff9m2, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b)
*/ * from t a,t b where a.user_id=b.user_id
Plan hash value: 1647061426
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 135 (100)| 132 |00:00:00.01 | 12 | | | |
| 1 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 12 | | | |
| 2 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 9 | | | |
| 3 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 5 | 1025K| 1025K| |
|* 4 | INDEX RANGE SCAN | T | 132 | 1 | | 0 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 132 | 1 | 2202 | 1 (0)| 132 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."USER_ID"="B"."USER_ID")
- dynamic sampling used for this statement (level=2)
Here you can see, how the nested loop works. For each row in the table a it has to run the index range scan on the table b. So it has to start the index range scan 132 times.
On the other side, the hash join will buffer the output of the first table and tahan comapre it to the outup of the second table. So all the operations will be executed only once.
Query:
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id;
Execution plan:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g9jrn9h5mzu73, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a)
INDEX(b) */ * from t a,t b where a.user_id=b.user_id
Plan hash value: 3043401287
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| 132 |00:00:00.01 | 12 | | | |
|* 1 | HASH JOIN | | 1 | 132 | 567K| 9 (0)| 132 |00:00:00.01 | 12 | 1780K| 1780K| 1262K (0)|
| 2 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 132 | 283K| 6 (0)| 132 |00:00:00.01 | 8 | | | |
| 4 | INDEX FULL SCAN | T | 1 | 132 | | 1 (0)| 132 |00:00:00.01 | 2 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."USER_ID"="B"."USER_ID")
- dynamic sampling used for this statement (level=2)
It's the GATHER_PLAN_STATISTICS hint.
Query:
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id;
Execution plan:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bn9y09ykff9m2, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b)
*/ * from t a,t b where a.user_id=b.user_id
Plan hash value: 1647061426
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 135 (100)| 132 |00:00:00.01 | 12 | | | |
| 1 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 12 | | | |
| 2 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 9 | | | |
| 3 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 5 | 1025K| 1025K| |
|* 4 | INDEX RANGE SCAN | T | 132 | 1 | | 0 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 132 | 1 | 2202 | 1 (0)| 132 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."USER_ID"="B"."USER_ID")
- dynamic sampling used for this statement (level=2)
Here you can see, how the nested loop works. For each row in the table a it has to run the index range scan on the table b. So it has to start the index range scan 132 times.
On the other side, the hash join will buffer the output of the first table and tahan comapre it to the outup of the second table. So all the operations will be executed only once.
Query:
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id;
Execution plan:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g9jrn9h5mzu73, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a)
INDEX(b) */ * from t a,t b where a.user_id=b.user_id
Plan hash value: 3043401287
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| 132 |00:00:00.01 | 12 | | | |
|* 1 | HASH JOIN | | 1 | 132 | 567K| 9 (0)| 132 |00:00:00.01 | 12 | 1780K| 1780K| 1262K (0)|
| 2 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 132 | 283K| 6 (0)| 132 |00:00:00.01 | 8 | | | |
| 4 | INDEX FULL SCAN | T | 1 | 132 | | 1 (0)| 132 |00:00:00.01 | 2 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."USER_ID"="B"."USER_ID")
- dynamic sampling used for this statement (level=2)