This is a 2nd post in a series which started with:
Oracle SQL Query Tuning Part 1
This time, we'll look into a query which is doing 2 nested loops, and we'll see that by adding parallel hint for the driving table of the first nested loop being executed, the query is being speed up considerably.
The unexpected here is that by their nature, Nested Loops are being execute serially, meaning that the driving tables is being accessed and then for each entry which satisfy the query, we go to the inner table and searching for matches. Now, by adding parallel hint for the driving table, even if it is a small table, we'll still gain in performance, because the nested loop is being run in parallel.
The example below is proving this assumption:
SELECT /*+ parallel(test2,4) */
test3.ban,test3.ban_status,test3.account_type,test3.account_sub_type,
test3.bill_cycle,test1.link_type,test2.*
FROM test1 anl , test2 adr , test3 ba
WHERE test1.address_id = test2.address_id
AND NVL(test1.expiration_date,'31-dec-4700')>SYSDATE
AND test1.id = test3.id
/
593 rows selected.
Elapsed: 00:00:06.17
Execution Plan
----------------------------------------------------------
Plan hash value: 2057958791
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7223 | 3491K| 3096 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 7223 | 3491K| 3096 | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 7223 | 3491K| 3096 | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 7223 | 3378K| 2962 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | test2 | 31937 | 13M| 4 | | | Q1,00 | PCWP | |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| test1 | 1 | 21 | 1 | ROWID | ROWID | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | test1_7IX | 5 | | 1 | | | Q1,00 | PCWP | |
| 9 | PARTITION RANGE ITERATOR | | 1 | 16 | 1 | KEY | KEY | Q1,00 | PCWP | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | test3 | 1 | 16 | 1 | KEY | KEY | Q1,00 | PCWP | |
|* 11 | INDEX UNIQUE SCAN | test3_PK | 1 | | 1 | KEY | KEY | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
225299 consistent gets
43033 physical reads
0 redo size
38215 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
593 rows processed
Without parallel:
SELECT
test3.ban,test3.ban_status,test3.account_type,test3.account_sub_type,
test3.bill_cycle,test1.link_type,test2.*
FROM test1 anl , test2 adr , test3 ba
WHERE test1.address_id = test2.address_id
AND NVL(test1.expiration_date,'31-dec-4700')>SYSDATE
AND test1.id = test3.id;
593 rows selected.
Elapsed: 00:02:17.19
Execution Plan
----------------------------------------------------------
Plan hash value: 2057958791
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7223 | 3491K| 9284 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 7223 | 3491K| 9284 | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 7223 | 3491K| 9284 | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 7223 | 3378K| 8882 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | test2 | 31937 | 13M| 10 | | | Q1,00 | PCWP | |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| test1 | 1 | 21 | 1 | ROWID | ROWID | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | test1_7IX | 5 | | 1 | | | Q1,00 | PCWP | |
| 9 | PARTITION RANGE ITERATOR | | 1 | 16 | 1 | KEY | KEY | Q1,00 | PCWP | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | test3 | 1 | 16 | 1 | KEY | KEY | Q1,00 | PCWP | |
|* 11 | INDEX UNIQUE SCAN | test3_PK | 1 | | 1 | KEY | KEY | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------
P
Statistics
----------------------------------------------------------
27 recursive calls
3 db block gets
225016 consistent gets
43006 physical reads
728 redo size
37747 bytes sent via SQL*Net to client
917 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
593 rows processed
A nested loops join that uses an index to join two tables can be fully parallelized providing that the driving table is accessed by a table scan.
No comments:
Post a Comment