Friday, September 8, 2017

Driving Site Hint is not working with Insert statement

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.

No comments:

Post a Comment