0

First query to optimize, with table ODS_SAMPLE having 100 millions of records, and table B having hundreds of thousands of records.

INSERT /*+APPEND */ INTO TEMP_SAMPLE_00  NOLOGGING
(SELECT OBJEK,
       ATINN,
       ATZHL,
       MAFID,
       KLART,
       ADZHL,
       MAX(TST)   LST_DAT
     FROM ODS_SAMPLE A, PARAM B
    WHERE A.COD_ZON = 'P08'
        AND A.COD_ZON = B.COD_ZON
        AND B.COD_GEN_CLA = 'MAT'
         AND A.KLART = B.COD_TYP_CLA
    GROUP BY OBJEK, ATINN, ATZHL, MAFID, KLART, ADZHL);

Second query comes after the first one. Data being inserted into TEMP_SAMPLE_00 is around 80 million rows.

INSERT /*+ APPEND */ INTO TEMP_SAMPLE_10 
    (SELECT
          A.OBJEK,
          A.ATINN,
          A.ATZHL,
          A.MAFID,
          A.KLART,
          A.ADZHL,
          A.ATWRT,
          A.ATFLV,
          A.ATAWE,
          A.ATFLB,
          A.ATAW1,
          A.ATCOD,
          A.ATTLV,
          A.ATTLB,
          A.ATPRZ,
          A.ATINC,
          A.ATAUT,
          A.AENNR,
          A.DATUV,
          A.LKENZ,
          A.ATIMB,
          A.ATZIS,
          A.ATSRT,
          A.ATVGLART
        FROM ODS_SAMPLE A, TEMP_SAMPLE_00 B
        WHERE A.OBJEK = B.OBJEK
          AND A.ATINN=B.ATINN
          AND A.ATZHL=B.ATZHL
          AND A.MAFID=B.MAFID
          AND A.KLART=B.KLART
          AND A.ADZHL=B.ADZHL
          AND (((A.TST = B.LST_DAT) AND (A.TST IS NOT NULL)) OR
                  (A.TST IS NULL))
          AND A.COD_ZON = 'P08');

I have tried using WITH clause in the first step but the execution time was almost just the same.

Execution plan is below for the first query:

Plan hash value: 3884167348

--------------------------------------------------------------
| Id  | Operation                          | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |
|   1 |  PX COORDINATOR                    |                 |
|   2 |   PX SEND QC (RANDOM)              | :TQ10002        |
|   3 |    HASH GROUP BY                   |                 |
|   4 |     PX RECEIVE                     |                 |
|   5 |      PX SEND HASH                  | :TQ10001        |
|   6 |       HASH GROUP BY                |                 |
|   7 |        HASH JOIN                   |                 |
|   8 |         BUFFER SORT                |                 |
|   9 |          PART JOIN FILTER CREATE   | :BF0000         |
|  10 |           PX RECEIVE               |                 |
|  11 |            PX SEND BROADCAST       | :TQ10000        |
|  12 |             VIEW                   | VW_GBF_9        |
|  13 |              HASH GROUP BY         |                 |
|  14 |               PARTITION LIST SINGLE|                 |
|  15 |                TABLE ACCESS FULL   | PARAM           |
|  16 |         PX BLOCK ITERATOR          |                 |
|  17 |          TABLE ACCESS FULL         | ODS_SAMPLE      |
--------------------------------------------------------------

And below is for the second part:

Plan hash value: 3450208322

-------------------------------------------------
| Id  | Operation               | Name          |
-------------------------------------------------
|   0 | SELECT STATEMENT        |               |
|   1 |  PX COORDINATOR         |               |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001      |
|   3 |    HASH JOIN            |               |
|   4 |     PX RECEIVE          |               |
|   5 |      PX SEND BROADCAST  | :TQ10000      |
|   6 |       PX SELECTOR       |               |
|   7 |        TABLE ACCESS FULL| TEMP_SAMPLE_00|
|   8 |     PX BLOCK ITERATOR   |               |
|   9 |      TABLE ACCESS FULL  | ODS_SAMPLE    |
-------------------------------------------------
William Robertson
  • 15,273
  • 4
  • 38
  • 44
Nikz Nadura
  • 1
  • 2
  • I'm not sure how `WHERE COD_ZON = 'P08'` is working in the first query if both tables have a `COD_ZON` column. Also the `NOLOGGING` isn't doing anything except acting as an unused alias for `TEMP_SAMPLE_00`. Are the tables actually defined `nologging`, assuming that was the intention? I'm afraid a lot more detail would be needed for tuning advice. What is the execution plan? What are the main waits? Can you use parallel? – William Robertson Jan 13 '19 at 10:24
  • 'PO8' is actually a variable, value is dependent on how job was executed. I just used 'P08' so i could manually execute it upon testing. NOLOGGING is used in almost all the query insert in tables, this enables direct path writes. I could not use parallel execution as second query depends on first query. – Nikz Nadura Jan 13 '19 at 10:59
  • Here is the explain plan of the first query, – Nikz Nadura Jan 13 '19 at 11:11
  • 1
    *NOLOGGING is used in almost all the query insert in tables, this enables direct path writes* - no it doesn't. I think you meant `alter table temp_sample_00 nologging;` which sets the `logging` attribute of the table, which you can check in `user_tables`. The way it's used in your example it is a table alias. You could just as well have written `insert into temp_sample_00 banana`. – William Robertson Jan 13 '19 at 11:14
  • Okay ill check that out, but we could actually just disregard the insert part, just the select part of query has been taking too long. – Nikz Nadura Jan 13 '19 at 11:18
  • 1
    *'PO8' is actually a variable* - that's fine, but my concern was the ambiguously defined `COD_ZON`, as there are `A.COD_ZON` and `B.COD_ZON` to choose from, and this normally leads to a syntax error. – William Robertson Jan 13 '19 at 11:18
  • But thank you about that, it actually made sense to me though as indeed as i checked that should be in alter table part, anyways, ill confirm that with the developers. But as of now, it would really be glad if there's any way that I could rewrite the select query to make it faster. thanks in advance! – Nikz Nadura Jan 13 '19 at 11:22
  • that was a typo error in my part, i have updated the query. – Nikz Nadura Jan 13 '19 at 11:24
  • The row stats (ideally actual as well as estimated) would be helpful, although I appreciate SO has limited width for displaying stats listings. https://blogs.oracle.com/optimizer/how-do-i-know-if-the-cardinality-estimates-in-a-plan-are-accurate. Also, what indexes exist and how did you hope they would be used? – William Robertson Jan 13 '19 at 11:32
  • In my opinion, "two queries" -- particularly two unrelated queries -- makes the question too broad for one question on Stack Overflow. – Gordon Linoff Jan 13 '19 at 13:17
  • A SQL Monitor report for both of these would be a good place to start. However the fact that PX SELECTOR is being used for the scan makes me suspicious. How representative are the stats ? – BobC Feb 05 '19 at 05:42

0 Answers0