insert query is too slow to execute

Question

I have a insert statement which is taking too long to execute or running forever.I am running this query from sql*plus. Below are the counts and keys/indexes defined for each table used in the select query.

TAB_A; --1000000(composite keys defined ORD_ID,C_TYPE)
TAB_B;--40000000(ID,GAME_ID,START_TIME)
TAB_C;--1000000(EMAIL_ADDR,SRC_FILE_DT)
TAB_D;--100000(EMAIL_ADDR)
TAB_E;--900000
TAB_F ;--7000
TAB_G;--250

     Insert statement
    -----------------

  INSERT /*+ append */ INTO TEST1
select * from 
(
SELECT  'TAG' AS CAT,
        A.ID AS CAT_ID,
        A.ORD_ID,
        B.P_NAME AS P_NAME,
        A.C_TYPE AS C_TYPE,
        round(NVL(A.AMT,0)/E.RATE,2) as PAY_USD,
        C.CNTY_CD AS BILL_CNTY,
        F.YEAR AS YEAR,
        G.COUNTRY AS COUNTY,
        D.VAT_ID AS VAT_ID,
        C.CONV,
        A.KEY_ORG AS KEY_ORG,
        row_number() over (partition by A.KEY_ORG order by A.KEY_ORG) AS RNK
    FROM (SELECT * FROM TAB_A) A
        LEFT JOIN TAB_B B on B.ID = A.ID
        LEFT JOIN TAB_C C ON C.ID = A.ID
        LEFT JOIN TAB_D D ON D.ID = A.ID
        LEFT JOIN TAB_E E  on trunc(A.FROM_DT) = trunc(E.cal_dt) and A.CURR_CD = E.CURR_CD       
        LEFT JOIN TAB_F F ON TRUNC(F.CAL_DT) = TRUNC(A.YEAR)
        LEFT JOIN TAB_G G ON UPPER(TRIM(G.COUNTRY)) = UPPER(TRIM(A.COUNTY))
)a
where RNK = 1;

Is something wrong in the join used or any other way to write this query in more efficient manner.Please let me know if any information is missing.

Explain Plan

Plan hash value: 2478932650



-------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |              |     1 |  2199 |       |   940M  (3)| 01:18:01 |        |      |            |
|   1 |  LOAD AS SELECT                  | TEST1        |       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                 |              |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)           | :TQ10005     |     1 |  2199 |       |   940M  (3)| 01:18:01 |  Q1,05 | P->S | QC (RAND)  |
|   4 |     OPTIMIZER STATISTICS GATHERING  |           |     1 |  2199 |       |   940M  (3)| 01:18:01 |  Q1,05 | PCWC |            |
|*  5 |      VIEW                           |           |     1 |  2199 |       |   940M  (3)| 01:18:01 |  Q1,05 | PCWP |            |
|*  6 |       WINDOW SORT PUSHED RANK       |           |   218G|    62T|    64T|   940M  (3)| 01:18:01 |  Q1,05 | PCWP |            |
|   7 |        PX RECEIVE                   |           |   218G|    62T|       |   940M  (3)| 01:18:01 |  Q1,05 | PCWP |            |
|   8 |         PX SEND HASH                | :TQ10004  |   218G|    62T|       |   940M  (3)| 01:18:01 |  Q1,04 | P->P | HASH       |
|*  9 |          WINDOW CHILD PUSHED RANK   |           |   218G|    62T|       |   940M  (3)| 01:18:01 |  Q1,04 | PCWP |            |
|* 10 |           HASH JOIN RIGHT OUTER     |           |   218G|    62T|   446M|  6387K (20)| 00:00:32 |  Q1,04 | PCWP |            |
|  11 |            PX RECEIVE               |           |    42M|  1298M|       |   148K  (1)| 00:00:01 |  Q1,04 | PCWP |            |
|  12 |             PX SEND BROADCAST       | :TQ10000  |    42M|  1298M|       |   148K  (1)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  13 |              PX BLOCK ITERATOR      |           |    42M|  1298M|       |   148K  (1)| 00:00:01 |  Q1,00 | PCWC |            |
|  14 |               TABLE ACCESS FULL     | TAB_B     |    42M|  1298M        |            |   148K  (1)| 00:00:01 |  Q1,00 | PCWP |
|* 15 |            HASH JOIN RIGHT OUTER    |           |  1468M|   384G|       | 22273  (39)| 00:00:01 |  Q1,04 | PCWP |            |
|* 16 |             TABLE ACCESS FULL       | TAB_E     |   757K|    15M        |            |   177   (7)| 00:00:01 |  Q1,04 | PCWP |
|* 17 |             HASH JOIN RIGHT OUTER   |           |    23M|  5813M|       | 13894   (3)| 00:00:01 |  Q1,04 | PCWP |            |
|  18 |              TABLE ACCESS FULL      | TAB_B     |  1631K|    29M|       |  7019   (1)| 00:00:01 |  Q1,04 | PCWP |            |
|* 19 |              HASH JOIN RIGHT OUTER  |           |  4033K|   927M|       |  6730   (2)| 00:00:01 |  Q1,04 | PCWP |            |
|  20 |               PX RECEIVE            |           |   247 |  3211 |       |    11   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  21 |                PX SEND BROADCAST    | :TQ10001  |   247 |  3211 |       |    11   (0)| 00:00:01 |  Q1,01 | S->P | BROADCAST  |
|  22 |                 PX SELECTOR         |           |       |       |       |            |          |  Q1,01 | SCWC |            |
|  23 |                  TABLE ACCESS FULL  | TAB_G     |   247 |  3211 |       |    11   (0)| 00:00:01 |  Q1,01 | SCWP |            |
|* 24 |               HASH JOIN RIGHT OUTER |           |  1633K|   355M|       |  6697   (2)| 00:00:01 |  Q1,04 | PCWP |            |
|  25 |                PX RECEIVE           |           |   117K|  6979K|       |   848   (1)| 00:00:01 |  Q1,04 | PCWP |            |
|  26 |                 PX SEND BROADCAST   | :TQ10002  |   117K|  6979K|       |   848   (1)| 00:00:01 |  Q1,02 | P->P | BROADCAST  |
|  27 |                  PX BLOCK ITERATOR  |           |   117K|  6979K|       |   848   (1)| 00:00:01 |  Q1,02 | PCWC |            |
|  28 |                   TABLE ACCESS FULL | TAB_D     |   117K|  6979K|       |   848   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|* 29 |                HASH JOIN RIGHT OUTER|           |  1629K|   259M|       |  5838   (2)| 00:00:01 |  Q1,04 | PCWP |            |
|  30 |                 PX RECEIVE          |           |  7671 | 92052 |       |    10  (10)| 00:00:01 |  Q1,04 | PCWP |            |
|  31 |                  PX SEND BROADCAST  | :TQ10003  |  7671 | 92052 |       |    10  (10)| 00:00:01 |  Q1,03 | S->P | BROADCAST  |
|  32 |                   PX SELECTOR       |           |       |       |       |            |          |  Q1,03 | SCWC |            |
|  33 |                    TABLE ACCESS FULL| TAB_F     |  7671 | 92052 |       |    10  (10)| 00:00:01 |  Q1,03 | SCWP |            |
|  34 |                 PX BLOCK ITERATOR   |           |  1873K|   276M|       |  5818   (2)| 00:00:01 |  Q1,04 | PCWC |            |
|  35 |                  TABLE ACCESS FULL  | TAB_A     |  1873K|   276M|       |  5818   (2)| 00:00:01 |  Q1,04 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

enter image description here

I've no experience in Oracle but doing functions in joins is, in general, bad practice in SQL. Please clean your data before querying it. Saves vital time. Also, is the insert taking too much time or the select? Regarding the select and it's structure, you have to give more insight on which indexes are on the tables — Casper Broeren, Oct 27 '19 at 10:43
Query optimisation depends on a lot of factors and you haven't provided nearly enough information. Please read [this post on asking Oracle tuning questions](https://stackoverflow.com/q/34975406/146325). It will give you an insight into the amount of detail you need to provide, and may even help you start tuning your queries yourself. — APC, Oct 27 '19 at 10:56
Also you need to explain why everything is an outer join. If there's no matching record in table TAB_E what result are you expecting for `round(NVL(A.AMT,0)/null,2)`??? — APC, Oct 27 '19 at 11:00
Thanks for your comments. I have already mentioned the indexes build on the tables above. @APC : i dont want to loose any of the records thats why used outer join and i am expecting 0 when there are no matching records in TAB_E and one more thing after joining TAB_E (~ 40 mn records ) i am facing the performance issue.. — kashi, Oct 27 '19 at 12:01
What is your intent with `row_number() over (partition by A.KEY_ORG order by A.KEY_ORG)`? Since the partition is the same as the order by, this actually gives you a *random* ranking in the group. — GMB, Oct 27 '19 at 19:52
@kashi For optimizing large data warehouse queries you'll want to generate a SQL Monitor Report. Explain plans are helpful, but they only tell you what Oracle *thinks* will be slow, not what's really slow. Find the relevant SQL_ID in GV$SQL, then plug that value into `select dbms_sqltune.report_sql_monitor('SQL_ID') from dual;` The results will tell you which operation is slow, and where the query has gone wrong. — Jon Heller, Oct 27 '19 at 20:12
@jonheller - you really shouldn't advise people to use SQL Monitor report without cautioning them regarding licensing. They need the Tuning and Diagnostics packs, which are chargeable extras to the Enterprise Edition. — APC, Oct 28 '19 at 11:08
@APC i have posted the explain plan . Can you click the description link to see the "time remaining ". Its running damn slow. — kashi, Oct 28 '19 at 11:36
@APC Thanks, that's a good point. @kashi If you don't have the packs licensed, you can get similar results using the hint `/*+ gather_plan_statistics */` and then running something like `SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));` It's annoying, but without that data you're just going to be guessing forever. For example, the image you posted is still incomplete. It lists the status of a single operation, but which operation? And is that operation in a loop and will be run multiple times? For large queries we tune operations, not statements. — Jon Heller, Oct 28 '19 at 17:15
The explain plan is not the same as a SQL Monitor report (idea) or the output from the cursor cache using GATHER_PLAN_STATISTICS. Unfortunately, some of the information from GATHER_PLAN_STATISTICS using parallel query is not reliable. You are joining big tables (al least from the explain plan), so it's unlikely indexes is the answer. One thing to note, is that while your query is parallel, you are doing the insert serially. You can try ALTER SESSION ENABLE PARALLEL DML — BobC, Nov 03 '19 at 19:10

score 0 · Answer 1 · answered Oct 27 '19 at 14:02

I suggest building the following indexes:

CREATE INDEX TAB_A_1 ON TAB_A (trunc(FROM_DT), CURR_CD);
CREATE INDEX TAB_A_2 ON TAB_A (TRUNC(YEAR), ID);
CREATE INDEX TAB_A_3 ON TAB_A (UPPER(TRIM(COUNTY)));

CREATE INDEX TAB_E_1 ON TAB_E (trunc(cal_dt), CURR_CD);

CREATE INDEX TAB_F_1 ON TAB_F (TRUNC(CAL_DT));

CREATE INDEX TAB_G_1 ON TAB_G (UPPER(TRIM(COUNTRY)), ID);

I have no idea if they'll help, but at least it gives the optimizer some choices. It appears you're reading every row from A, which means it's going to go for a FULL TABLE SCAN, and of course I have no idea what the fan-out from A to the other tables looks like.

insert query is too slow to execute

1 Answers1