We had recently came across a situation that, Driving site Hint is not working with insert statement. Please see the test case below. Here you can see that select statement is working fine with hint and Driving site Hint is not working as expected when we add insert statement at the beginning.
Select statement with hint(Working fine as expected)
Execution Plan
----------------------------------------------------------
Plan hash value: 1144483799
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 160M| 30G| | 15M (1)| 00:09:57 |
| 1 | HASH GROUP BY | | 160M| 30G| 33G| 15M (1)| 00:09:57 |
|* 2 | HASH JOIN RIGHT OUTER| | 160M| 30G| 18M| 8120K (1)| 00:05:18 |
| 3 | TABLE ACCESS FULL | MASTER | 467K| 12M| | 9744 (1)| 00:00:01 |
|* 4 | HASH JOIN | | 160M| 26G| 121M| 6687K (1)| 00:04:22 |
| 5 | REMOTE | JOBS | 1874K| 100M| | 17789 (1)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | DETAILS_MASTER | 304M| 33G| | 4777K (1)| 00:03:07 |
-----------------------------------------------------------------------------------------------------------
In the above Execution plan you can see that Table "JOBS" referred as REMOTE and this query complete in 5 Minutes.
Insert statement explain plan
Plan hash value: 1159224495
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 160M| 52G| | 19M (1)| 00:13:00 |
| 1 | LOAD TABLE CONVENTIONAL | SUMMARY_TMP | | | | | |
| 2 | HASH GROUP BY | | 160M| 52G| 55G| 19M (1)| 00:13:00 |
|* 3 | HASH JOIN RIGHT OUTER | | 160M| 52G| 57M| 8000K (1)| 00:05:13 |
| 4 | REMOTE | MASTER | 467K| 52M| | 6235 (1)| 00:00:01 |
|* 5 | HASH JOIN | | 160M| 34G| 76M| 6144K (1)| 00:04:01 |
| 6 | TABLE ACCESS FULL | JOBS | 1874K| 55M| | 27801 (1)| 00:00:02 |
| 7 | REMOTE | DETAILS_MASTER | 304M| 56G| | 3061K (1)| 00:02:00 |
-------------------------------------------------------------------------------------------------------------
In the above execution plan, you can see that Table "DETAILS_MASTER" referred as REMOTE table and entire insert is taking almost 1 hour to complete their execution.
Comparison between both execution plan, we identified that Driving site hint is not working as expected with insert statement.
I have decided to give a PL\SQL block to resolve this issue, Also added bulk collect + FOR ALL (Improve the Bulk Insert). Please see the below details.
DECLARE
TYPE ARRAY IS TABLE OF alltmrsmry%ROWTYPE;
CURSOR get_data
IS
<
l_data ARRAY;
BEGIN
OPEN get_data;
LOOP
FETCH get_data
BULK COLLECT INTO l_data LIMIT 2000;
FORALL i IN 1 .. l_data.COUNT
INSERT INTO SUMMARY_TMP
VALUES l_data (i);
EXIT WHEN get_data%NOTFOUND;
END LOOP;
CLOSE get_data;
COMMIT;
END;
/
Select query with Driving hint is working fine with cursor and insert 2000 rows each. Entire PL\SQL block (SELECT+INSERT) is completing in 10 minutes now.
Customer is Happy and also our team.
