1 2 Previous Next 16 Replies Latest reply on Jan 2, 2020 8:18 PM by William Robertson

    Create table as select (CTAS) takes forever, even its select query takes just 10 minutes

    R4C_SuteeS

      I have the attached CTAS query which takes forever to run (the most I have waited was 24 hours+).
      But when I try run just only the select query alone, it takes only 10 minutes to complete (I also try to export data to excel via SQL developer, it only take 10 minutes as well).
      I also have tried use PARALLEL at CTAS only, at select query only and both (as below) bu still have the issue).

       

      So, I'm not sure why CTAS takes forever to complete. Any idea?

       

      CREATE TABLE XX_XXX_XXXXX01 PARALLEL NOLOGGING AS

        (SELECT

            /*+ PARALLEL */

            LEFT_CODE.SEQ,

            LEFT_CODE.C1   AS C1,

            LEFT_CODE.C2   AS C2,

            LEFT_CODE.C3   AS C3,

            LEFT_CODE.C4   AS C4,

            LEFT_CODE.C5   AS C5,

            LEFT_CODE.C6   AS C6,

            LEFT_CODE.C7   AS C7,

            LEFT_CODE.C8   AS C8,

            LEFT_CODE.C9   AS C9,

            LEFT_CODE.C10  AS C10,

            LEFT_CODE.C11  AS C11,

            LEFT_CODE.C12  AS C12,

            LEFT_CODE.C13  AS C13,

            LEFT_CODE.C14  AS C14,

            LEFT_CODE.C15  AS C15,

            LEFT_CODE.C16  AS C16,

            LEFT_CODE.C17  AS C17,

            LEFT_CODE.C18  AS C18,

            LEFT_CODE.C19  AS C19,

            LEFT_CODE.C20  AS C20,

            LEFT_CODE.C21  AS C21,

            RIGHT_CODE.C11 AS C22,

            RIGHT_CODE.C12 AS C23,

            RIGHT_CODE.C13 AS C24,

            RIGHT_CODE.C14 AS C25,

            RIGHT_CODE.C15 AS C26,

            RIGHT_CODE.C11 AS C27,

            RIGHT_CODE.C12 AS C28,

            RIGHT_CODE.C13 AS C29,

            RIGHT_CODE.C14 AS C30,

            RIGHT_CODE.C15 AS C31,

            RIGHT_CODE.C16 AS C32,

            RIGHT_CODE.C17 AS C33,

            RIGHT_CODE.C18 AS C34,

            RIGHT_CODE.C19 AS C35,

            RIGHT_CODE.C20 AS C36,

            RIGHT_CODE.C21 AS C37,

            RIGHT_CODE.C22 AS C38,

            RIGHT_CODE.C23 AS C39,

            RIGHT_CODE.C24 AS C40,

            RIGHT_CODE.C25 AS C41,

            RIGHT_CODE.C26 AS C42

          FROM

            (SELECT T1.*,

              (SELECT COL2

              FROM IMPORT_METADATA

              WHERE SNAPSHOT_ID=2

              AND TYPE         ='USER'

              AND COL1         =T1.XXX_LAST_UPDATED_BY

              ) AS LAST_UPDATED_BY_NAME,

              (SELECT COL2

              FROM IMPORT_METADATA

              WHERE SNAPSHOT_ID=2

              AND TYPE         ='USER'

              AND COL1         =T1.XXX_CREATED_BY

              ) AS CREATED_BY_NAME

            FROM

              (SELECT *

              FROM XX_XXX_T3351

              WHERE SNAPSHOT_ID              =2

              AND ( ( XXX_INV_ORG_ID        IS NOT NULL

              OR XXX_OU_ID                  IS NOT NULL

              OR XXX_LEDGER_ID              IS NOT NULL

              OR XXX_BG_ID                  IS NOT NULL )

              OR ( XXX_INV_ORG_ID           IS NULL

              AND XXX_OU_ID                 IS NULL

              AND XXX_LEDGER_ID             IS NULL

              AND XXX_BG_ID                 IS NULL ) )

              AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

              OR XXX_CREATED_BY NOT         IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

              ) T1

            ORDER BY SEQ

            ) LEFT_CODE

          LEFT JOIN

            (SELECT T1.*,

              (SELECT COL2

              FROM IMPORT_METADATA

              WHERE SNAPSHOT_ID=2

              AND TYPE         ='USER'

              AND COL1         =T1.XXX_LAST_UPDATED_BY

              ) AS LAST_UPDATED_BY_NAME,

              (SELECT COL2

              FROM IMPORT_METADATA

              WHERE SNAPSHOT_ID=2

              AND TYPE         ='USER'

              AND COL1         =T1.XXX_CREATED_BY

              ) AS CREATED_BY_NAME

            FROM

              (SELECT *

              FROM XX_XXX_T3353

              WHERE SNAPSHOT_ID              =2

              AND ( ( XXX_INV_ORG_ID        IS NOT NULL

              OR XXX_OU_ID                  IS NOT NULL

              OR XXX_LEDGER_ID              IS NOT NULL

              OR XXX_BG_ID                  IS NOT NULL )

              OR ( XXX_INV_ORG_ID           IS NULL

              AND XXX_OU_ID                 IS NULL

              AND XXX_LEDGER_ID             IS NULL

              AND XXX_BG_ID                 IS NULL ) )

              AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

              OR XXX_CREATED_BY NOT         IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

              ) T1

            ORDER BY SEQ

            ) RIGHT_CODE

          ON 1                       =1

          AND NVL(LEFT_CODE.C8,'X')  = NVL(RIGHT_CODE.C8,'X')

          AND NVL(LEFT_CODE.C10,'X') = NVL(RIGHT_CODE.C10,'X')

          AND NVL(LEFT_CODE.C1,'X')  = NVL(RIGHT_CODE.C1,'X')

          AND NVL(LEFT_CODE.C1,'X')  = 'VALUE SET'

          AND NVL(LEFT_CODE.C8,'X') IS NOT NULL

        );

        • 1. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
          AndrewSayer

          Is the execution plan different between the CTAS and the select statement (other than the create table part)?

          Adding parallelism is only going to make it difficult to compare, sometimes you’ll get the parallel slaves you want, sometimes you’ll get less and sometimes you’ll get none depending on the amount of other parallel work going on.

           

          What is the session executing the CTAS waiting on? Check v$session_event to see where it’s spending most of its time.

          Any chance youre referencing views/synonyms that point to remote tables?

          Remember we don’t have your DDL.

          • 2. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
            GregV

            Hi,

             

            As Andrew suggested, start by checking what the session is waiting for. Since you're using SQL Developer check this from the "monitor sessions" screen.

            When you say the query takes 10 min to complete, are you talking about retrieving all the rows? How many of them in total?

            Using PARALLEL with a wrong execution plan only makes things worse because the wrong plan is multiplied to different sessions. Make sure the stats are up to date.

            • 3. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
              R4C_SuteeS

              Andrew,

               

              Thanks for quick response, and  yes, the explain plain are differnt. I don't know why it does the INDEX RANGE SCAN when we do CTAS but it doesn't when we just select.

              What wait event on all active sessions just CPU (resmgr:cpu quantum).

               

              The SELECT statement only explain plan:

              cid:d02facd8-106c-4b26-80ab-1a048a26200a

               

              The complete CTAS explain plan:

              cid:0f0b7330-faa1-4901-8e16-edb49370caeb

              • 4. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                R4C_SuteeS

                Greg,

                 

                When you say the query takes 10 min to complete, are you talking about retrieving all the rows? How many of them in total?

                Yes, either just run the select statement directly or retrieve all rows (by export that result to an excel file from SQL developer). It takes just ~10 minutes.

                The excel sheet in return about 65K of rows. We have try the parallel cause the select statement get executing slowly (just 1 x vCPU on 4 x vCPU machine).

                It just return in ~10 when we add the parallel hint. But however, once added to CTAS then it just take forever.

                • 5. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                  AndrewSayer

                  3339281 wrote:

                   

                  Andrew,

                   

                  Thanks for quick response, and yes, the explain plain are differnt. I don't know why it does the INDEX RANGE SCAN when we do CTAS but it doesn't when we just select.

                  What wait event on all active sessions just CPU (resmgr:cpu quantum).

                   

                  The SELECT statement only explain plan:

                  cid:d02facd8-106c-4b26-80ab-1a048a26200a

                   

                  The complete CTAS explain plan:

                  cid:0f0b7330-faa1-4901-8e16-edb49370caeb

                  I am not going to download files.

                  Please use sql*plus, grab the execution (not explain) plan from memory using dbms_xplan.display_cursor and copy and paste the plans using a fixed width font here.

                   

                  resmgr:cpu quantum is NOT CPU, it's specifically a wait event to prevent you from using CPU. How is your resource manager configured? Is this wait event really the most significant - ie it's spent hours on it?  Show us the results from v$session_event (remember to use a fresh session each time as this shows the aggregate usage for the lifetime of a session). How does that compare to the simple select statement?

                  • 6. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                    GregV

                    I've noticed you're querying 4 times the IMPORT_METADATA table and you're using 2 ORDER BY clauses within in-line views, which gives unecessary pain to the optimizer. And worse, you're not even selecting columns from the IMPORT_METADATA table in your final SELECT list! So remove the scalar queries on IMPORT_METADATA and the ORDER BY clauses and see if it improves your query performance.

                    1 person found this helpful
                    • 7. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                      R4C_SuteeS

                      Andrew,

                       

                      Explain plans are below, the output of "v$session_event" of all SIDs for the CTAS query. Please feel free to let me know if you need more information.

                       

                      Select statement explain plan:

                       

                      SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('g7qxhcnvvwc3s',0));

                      SQL_ID  g7qxhcnvvwc3s, child number 0

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

                      SELECT /*+ PARALLEL */ LEFT_CODE.SEQ,       LEFT_CODE.C1   AS C1,

                      LEFT_CODE.C2   AS C2,       LEFT_CODE.C3   AS C3,       LEFT_CODE.C4

                      AS C4,       LEFT_CODE.C5   AS C5,       LEFT_CODE.C6   AS C6,

                      LEFT_CODE.C7   AS C7,       LEFT_CODE.C8   AS C8,       LEFT_CODE.C9

                      AS C9,       LEFT_CODE.C10  AS C10,       LEFT_CODE.C11  AS C11,

                      LEFT_CODE.C12  AS C12,       LEFT_CODE.C13  AS C13,       LEFT_CODE.C14

                      AS C14,       LEFT_CODE.C15  AS C15,       LEFT_CODE.C16  AS C16,

                      LEFT_CODE.C17  AS C17,       LEFT_CODE.C18  AS C18,

                      LEFT_CODE.C19  AS C19,       LEFT_CODE.C20  AS C20,       LEFT_CODE.C21

                      AS C21,       RIGHT_CODE.C11 AS C22,       RIGHT_CODE.C12 AS C23,

                      RIGHT_CODE.C13 AS C24,       RIGHT_CODE.C14 AS C25,

                      RIGHT_CODE.C15 AS C26,       RIGHT_CODE.C11 AS C27,

                      RIGHT_CODE.C12 AS C28,       RIGHT_CODE.C13 AS C29,

                      RIGHT_CODE.C14 AS C30,       RIGHT_CODE.C15 AS C31,

                      RIGHT_CODE.C16 AS C32,       RIGHT_CODE.C17 AS C33,       RIGHT_CO

                       

                       

                      Plan hash value: 3171693698

                       

                       

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

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

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

                      |   0 | SELECT STATEMENT          |              |       |       |       |   944K(100)|          |        |      |            |

                      |   1 |  PX COORDINATOR           |              |       |       |       |            |          |        |      |            |

                      |   2 |   PX SEND QC (ORDER)      | :TQ10001     | 60208 |  4259M|       |   944K  (1)| 03:08:53 |  Q1,01 | P->S | QC (ORDER) |

                      |   3 |    SORT ORDER BY          |              | 60208 |  4259M|   470M|   944K  (1)| 03:08:53 |  Q1,01 | PCWP |            |

                      |   4 |     PX RECEIVE            |              | 60208 |  4259M|       | 15167   (1)| 00:03:03 |  Q1,01 | PCWP |            |

                      |   5 |      PX SEND RANGE        | :TQ10000     | 60208 |  4259M|       | 15167   (1)| 00:03:03 |  Q1,00 | P->P | RANGE      |

                      |   6 |       NESTED LOOPS OUTER  |              | 60208 |  4259M|       | 15167   (1)| 00:03:03 |  Q1,00 | PCWP |            |

                      |   7 |        PX BLOCK ITERATOR  |              |       |       |       |            |          |  Q1,00 | PCWC |            |

                      |*  8 |         TABLE ACCESS FULL | XX_XXX_T3351 | 60208 |  2419M|       |    58   (2)| 00:00:01 |  Q1,00 | PCWP |            |

                      |   9 |        VIEW               |              |     1 | 32032 |       |            |          |  Q1,00 | PCWP |            |

                      |* 10 |         FILTER            |              |       |       |       |            |          |  Q1,00 | PCWP |            |

                      |* 11 |          TABLE ACCESS FULL| XX_XXX_T3353 |     1 | 38129 |       |    24   (0)| 00:00:01 |  Q1,00 | PCWP |            |

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

                       

                       

                      Predicate Information (identified by operation id):

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

                       

                       

                         8 - access(:Z>=:Z AND :Z<=:Z)

                             filter(("SNAPSHOT_ID"=2 AND (("XXX_LAST_UPDATED_BY"<>(-1) AND "XXX_LAST_UPDATED_BY"<>0 AND

                                    "XXX_LAST_UPDATED_BY"<>1 AND "XXX_LAST_UPDATED_BY"<>2 AND "XXX_LAST_UPDATED_BY"<>3 AND "XXX_LAST_UPDATED_BY"<>4 AND

                                    "XXX_LAST_UPDATED_BY"<>5 AND "XXX_LAST_UPDATED_BY"<>6 AND "XXX_LAST_UPDATED_BY"<>7 AND "XXX_LAST_UPDATED_BY"<>120 AND

                                    "XXX_LAST_UPDATED_BY"<>121 AND "XXX_LAST_UPDATED_BY"<>122 AND "XXX_LAST_UPDATED_BY"<>123 AND

                                    "XXX_LAST_UPDATED_BY"<>124 AND "XXX_LAST_UPDATED_BY"<>125 AND "XXX_LAST_UPDATED_BY"<>126 AND

                                    "XXX_LAST_UPDATED_BY"<>127 AND "XXX_LAST_UPDATED_BY"<>128 AND "XXX_LAST_UPDATED_BY"<>129) OR ("XXX_CREATED_BY"<>(-1)

                                    AND "XXX_CREATED_BY"<>0 AND "XXX_CREATED_BY"<>1 AND "XXX_CREATED_BY"<>2 AND "XXX_CREATED_BY"<>3 AND

                                    "XXX_CREATED_BY"<>4 AND "XXX_CREATED_BY"<>5 AND "XXX_CREATED_BY"<>6 AND "XXX_CREATED_BY"<>7 AND "XXX_CREATED_BY"<>120

                                    AND "XXX_CREATED_BY"<>121 AND "XXX_CREATED_BY"<>122 AND "XXX_CREATED_BY"<>123 AND "XXX_CREATED_BY"<>124 AND

                                    "XXX_CREATED_BY"<>125 AND "XXX_CREATED_BY"<>126 AND "XXX_CREATED_BY"<>127 AND "XXX_CREATED_BY"<>128 AND

                                    "XXX_CREATED_BY"<>129)) AND ("XXX_INV_ORG_ID" IS NOT NULL OR "XXX_OU_ID" IS NOT NULL OR "XXX_LEDGER_ID" IS NOT NULL OR

                                    "XXX_BG_ID" IS NOT NULL OR ("XXX_INV_ORG_ID" IS NULL AND "XXX_OU_ID" IS NULL AND "XXX_LEDGER_ID" IS NULL AND

                                    "XXX_BG_ID" IS NULL))))

                        10 - filter((NVL("XX_XXX_T3351"."C8",'X') IS NOT NULL AND NVL("XX_XXX_T3351"."C1",'X')='Value Set'))

                        11 - filter(("SNAPSHOT_ID"=2 AND (("XXX_LAST_UPDATED_BY"<>(-1) AND "XXX_LAST_UPDATED_BY"<>0 AND

                                    "XXX_LAST_UPDATED_BY"<>1 AND "XXX_LAST_UPDATED_BY"<>2 AND "XXX_LAST_UPDATED_BY"<>3 AND "XXX_LAST_UPDATED_BY"<>4 AND

                                    "XXX_LAST_UPDATED_BY"<>5 AND "XXX_LAST_UPDATED_BY"<>6 AND "XXX_LAST_UPDATED_BY"<>7 AND "XXX_LAST_UPDATED_BY"<>120 AND

                                    "XXX_LAST_UPDATED_BY"<>121 AND "XXX_LAST_UPDATED_BY"<>122 AND "XXX_LAST_UPDATED_BY"<>123 AND

                                    "XXX_LAST_UPDATED_BY"<>124 AND "XXX_LAST_UPDATED_BY"<>125 AND "XXX_LAST_UPDATED_BY"<>126 AND

                                    "XXX_LAST_UPDATED_BY"<>127 AND "XXX_LAST_UPDATED_BY"<>128 AND "XXX_LAST_UPDATED_BY"<>129) OR ("XXX_CREATED_BY"<>(-1)

                                    AND "XXX_CREATED_BY"<>0 AND "XXX_CREATED_BY"<>1 AND "XXX_CREATED_BY"<>2 AND "XXX_CREATED_BY"<>3 AND

                                    "XXX_CREATED_BY"<>4 AND "XXX_CREATED_BY"<>5 AND "XXX_CREATED_BY"<>6 AND "XXX_CREATED_BY"<>7 AND "XXX_CREATED_BY"<>120

                                    AND "XXX_CREATED_BY"<>121 AND "XXX_CREATED_BY"<>122 AND "XXX_CREATED_BY"<>123 AND "XXX_CREATED_BY"<>124 AND

                                    "XXX_CREATED_BY"<>125 AND "XXX_CREATED_BY"<>126 AND "XXX_CREATED_BY"<>127 AND "XXX_CREATED_BY"<>128 AND

                                    "XXX_CREATED_BY"<>129)) AND ("XXX_INV_ORG_ID" IS NOT NULL OR "XXX_OU_ID" IS NOT NULL OR "XXX_LEDGER_ID" IS NOT NULL OR

                                    "XXX_BG_ID" IS NOT NULL OR ("XXX_INV_ORG_ID" IS NULL AND "XXX_OU_ID" IS NULL AND "XXX_LEDGER_ID" IS NULL AND

                                    "XXX_BG_ID" IS NULL)) AND NVL("XX_XXX_T3353"."C1",'X')='Value Set' AND

                                    NVL("XX_XXX_T3351"."C8",'X')=NVL("XX_XXX_T3353"."C8",'X') AND

                                    NVL("XX_XXX_T3351"."C10",'X')=NVL("XX_XXX_T3353"."C10",'X') AND

                                    NVL("XX_XXX_T3351"."C1",'X')=NVL("XX_XXX_T3353"."C1",'X')))

                       

                       

                      Note

                      -----

                         - dynamic sampling used for this statement (level=2)

                         - automatic DOP: skipped because of IO calibrate statistics are missing

                       

                       

                       

                       

                      77 rows selected.

                       

                       

                      SQL>

                       

                      The CTAS statement explain plan:


                      SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ca338d9a4tj7d',0));

                      SQL_ID  ca338d9a4tj7d, child number 0

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

                      CREATE TABLE XX_XXX_SUTEES01 PARALLEL NOLOGGING AS   (SELECT       /*+

                      PARALLEL */       LEFT_CODE.SEQ,       LEFT_CODE.C1   AS C1,

                      LEFT_CODE.C2   AS C2,       LEFT_CODE.C3   AS C3,       LEFT_CODE.C4

                      AS C4,       LEFT_CODE.C5   AS C5,       LEFT_CODE.C6   AS C6,

                      LEFT_CODE.C7   AS C7,       LEFT_CODE.C8   AS C8,       LEFT_CODE.C9

                      AS C9,       LEFT_CODE.C10  AS C10,       LEFT_CODE.C11  AS C11,

                      LEFT_CODE.C12  AS C12,       LEFT_CODE.C13  AS C13,       LEFT_CODE.C14

                      AS C14,       LEFT_CODE.C15  AS C15,       LEFT_CODE.C16  AS C16,

                      LEFT_CODE.C17  AS C17,       LEFT_CODE.C18  AS C18,

                      LEFT_CODE.C19  AS C19,       LEFT_CODE.C20  AS C20,       LEFT_CODE.C21

                      AS C21,       RIGHT_CODE.C11 AS C22,       RIGHT_CODE.C12 AS C23,

                      RIGHT_CODE.C13 AS C24,       RIGHT_CODE.C14 AS C25,

                      RIGHT_CODE.C15 AS C26,       RIGHT_CODE.C11 AS C27,

                      RIGHT_CODE.C12 AS C28,       RIGHT_CODE.C13 AS C29,

                      RIGHT_CODE.C14 AS C30,       RIGHT_CODE.C15 AS C31,

                       

                       

                      Plan hash value: 1306750257

                       

                       

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

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

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

                      |   0 | CREATE TABLE STATEMENT             |                     |       |       |       |  4995M(100)|          |        |      |            |

                      |   1 |  PX COORDINATOR                    |                     |       |       |       |            |          |        |      |            |

                      |   2 |   PX SEND QC (RANDOM)              | :TQ10001            |  2655M|   178T|       |   389M  (1)|999:59:59 |  Q1,01 | P->S | QC (RAND)  |

                      |   3 |    LOAD AS SELECT                  |                     |       |       |       |            |          |  Q1,01 | PCWP |            |

                      |   4 |     NESTED LOOPS OUTER             |                     |  2655M|   178T|       |   389M  (1)|999:59:59 |  Q1,01 | PCWP |            |

                      |   5 |      VIEW                          |                     | 60208 |  2414M|       | 81279   (1)| 00:16:16 |  Q1,01 | PCWP |            |

                      |*  6 |       TABLE ACCESS BY INDEX ROWID  | IMPORT_METADATA     |   315 |  1251K|       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |*  7 |        INDEX RANGE SCAN            | IMPORT_METADATA_IDX |       |       |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |*  8 |       TABLE ACCESS BY INDEX ROWID  | IMPORT_METADATA     |   315 |  1251K|       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |*  9 |        INDEX RANGE SCAN            | IMPORT_METADATA_IDX |       |       |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |  10 |       SORT ORDER BY                |                     | 60208 |  2421M|   470M| 73446   (1)| 00:14:42 |  Q1,01 | PCWP |            |

                      |  11 |        PX RECEIVE                  |                     | 60208 |  2421M|       |    58   (2)| 00:00:01 |  Q1,01 | PCWP |            |

                      |  12 |         PX SEND RANGE              | :TQ10000            | 60208 |  2421M|       |    58   (2)| 00:00:01 |  Q1,00 | P->P | RANGE      |

                      |  13 |          PX BLOCK ITERATOR         |                     | 60208 |  2421M|       |    58   (2)| 00:00:01 |  Q1,00 | PCWC |            |

                      |* 14 |           TABLE ACCESS FULL        | XX_XXX_T3351        | 60208 |  2421M|       |    58   (2)| 00:00:01 |  Q1,00 | PCWP |            |

                      |  15 |      VIEW                          |                     | 44103 |  1347M|       |            |          |  Q1,01 | PCWP |            |

                      |* 16 |       FILTER                       |                     |       |       |       |            |          |  Q1,01 | PCWP |            |

                      |* 17 |        VIEW                        |                     | 44103 |  1599M|       | 73232   (1)| 00:14:39 |  Q1,01 | PCWP |            |

                      |* 18 |         TABLE ACCESS BY INDEX ROWID| IMPORT_METADATA     |   315 |  1251K|       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |* 19 |          INDEX RANGE SCAN          | IMPORT_METADATA_IDX |       |       |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |* 20 |         TABLE ACCESS BY INDEX ROWID| IMPORT_METADATA     |   315 |  1251K|       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |* 21 |          INDEX RANGE SCAN          | IMPORT_METADATA_IDX |       |       |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

                      |  22 |         SORT ORDER BY              |                     | 44103 |  2194M|   344M| 66538   (1)| 00:13:19 |  Q1,01 | PCWP |            |

                      |* 23 |          TABLE ACCESS FULL         | XX_XXX_T3353        | 44103 |  2194M|       |    24   (0)| 00:00:01 |  Q1,01 | PCWP |            |

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

                       

                       

                      Predicate Information (identified by operation id):

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

                       

                       

                         6 - filter(TO_NUMBER("COL1")=:B1)

                         7 - access("SNAPSHOT_ID"=2 AND "TYPE"='USER')

                         8 - filter(TO_NUMBER("COL1")=:B1)

                         9 - access("SNAPSHOT_ID"=2 AND "TYPE"='USER')

                        14 - access(:Z>=:Z AND :Z<=:Z)

                             filter(("SNAPSHOT_ID"=2 AND (("XXX_LAST_UPDATED_BY"<>(-1) AND "XXX_LAST_UPDATED_BY"<>0 AND "XXX_LAST_UPDATED_BY"<>1 AND

                                    "XXX_LAST_UPDATED_BY"<>2 AND "XXX_LAST_UPDATED_BY"<>3 AND "XXX_LAST_UPDATED_BY"<>4 AND "XXX_LAST_UPDATED_BY"<>5 AND

                                    "XXX_LAST_UPDATED_BY"<>6 AND "XXX_LAST_UPDATED_BY"<>7 AND "XXX_LAST_UPDATED_BY"<>120 AND "XXX_LAST_UPDATED_BY"<>121 AND

                                    "XXX_LAST_UPDATED_BY"<>122 AND "XXX_LAST_UPDATED_BY"<>123 AND "XXX_LAST_UPDATED_BY"<>124 AND "XXX_LAST_UPDATED_BY"<>125 AND

                                    "XXX_LAST_UPDATED_BY"<>126 AND "XXX_LAST_UPDATED_BY"<>127 AND "XXX_LAST_UPDATED_BY"<>128 AND "XXX_LAST_UPDATED_BY"<>129) OR

                                    ("XXX_CREATED_BY"<>(-1) AND "XXX_CREATED_BY"<>0 AND "XXX_CREATED_BY"<>1 AND "XXX_CREATED_BY"<>2 AND "XXX_CREATED_BY"<>3 AND

                                    "XXX_CREATED_BY"<>4 AND "XXX_CREATED_BY"<>5 AND "XXX_CREATED_BY"<>6 AND "XXX_CREATED_BY"<>7 AND "XXX_CREATED_BY"<>120 AND

                                    "XXX_CREATED_BY"<>121 AND "XXX_CREATED_BY"<>122 AND "XXX_CREATED_BY"<>123 AND "XXX_CREATED_BY"<>124 AND "XXX_CREATED_BY"<>125 AND

                                    "XXX_CREATED_BY"<>126 AND "XXX_CREATED_BY"<>127 AND "XXX_CREATED_BY"<>128 AND "XXX_CREATED_BY"<>129)) AND ("XXX_INV_ORG_ID" IS NOT

                                    NULL OR "XXX_OU_ID" IS NOT NULL OR "XXX_LEDGER_ID" IS NOT NULL OR "XXX_BG_ID" IS NOT NULL OR ("XXX_INV_ORG_ID" IS NULL AND "XXX_OU_ID"

                                    IS NULL AND "XXX_LEDGER_ID" IS NULL AND "XXX_BG_ID" IS NULL))))

                        16 - filter((NVL("LEFT_CODE"."C8",'X') IS NOT NULL AND NVL("LEFT_CODE"."C1",'X')='Value Set'))

                        17 - filter((NVL("LEFT_CODE"."C8",'X')=NVL("RIGHT_CODE"."C8",'X') AND NVL("LEFT_CODE"."C10",'X')=NVL("RIGHT_CODE"."C10",'X') AND

                                    NVL("LEFT_CODE"."C1",'X')=NVL("RIGHT_CODE"."C1",'X')))

                        18 - filter(TO_NUMBER("COL1")=:B1)

                        19 - access("SNAPSHOT_ID"=2 AND "TYPE"='USER')

                        20 - filter(TO_NUMBER("COL1")=:B1)

                        21 - access("SNAPSHOT_ID"=2 AND "TYPE"='USER')

                        23 - filter(("SNAPSHOT_ID"=2 AND (("XXX_LAST_UPDATED_BY"<>(-1) AND "XXX_LAST_UPDATED_BY"<>0 AND "XXX_LAST_UPDATED_BY"<>1 AND

                                    "XXX_LAST_UPDATED_BY"<>2 AND "XXX_LAST_UPDATED_BY"<>3 AND "XXX_LAST_UPDATED_BY"<>4 AND "XXX_LAST_UPDATED_BY"<>5 AND

                                    "XXX_LAST_UPDATED_BY"<>6 AND "XXX_LAST_UPDATED_BY"<>7 AND "XXX_LAST_UPDATED_BY"<>120 AND "XXX_LAST_UPDATED_BY"<>121 AND

                                    "XXX_LAST_UPDATED_BY"<>122 AND "XXX_LAST_UPDATED_BY"<>123 AND "XXX_LAST_UPDATED_BY"<>124 AND "XXX_LAST_UPDATED_BY"<>125 AND

                                    "XXX_LAST_UPDATED_BY"<>126 AND "XXX_LAST_UPDATED_BY"<>127 AND "XXX_LAST_UPDATED_BY"<>128 AND "XXX_LAST_UPDATED_BY"<>129) OR

                                    ("XXX_CREATED_BY"<>(-1) AND "XXX_CREATED_BY"<>0 AND "XXX_CREATED_BY"<>1 AND "XXX_CREATED_BY"<>2 AND "XXX_CREATED_BY"<>3 AND

                                    "XXX_CREATED_BY"<>4 AND "XXX_CREATED_BY"<>5 AND "XXX_CREATED_BY"<>6 AND "XXX_CREATED_BY"<>7 AND "XXX_CREATED_BY"<>120 AND

                                    "XXX_CREATED_BY"<>121 AND "XXX_CREATED_BY"<>122 AND "XXX_CREATED_BY"<>123 AND "XXX_CREATED_BY"<>124 AND "XXX_CREATED_BY"<>125 AND

                                    "XXX_CREATED_BY"<>126 AND "XXX_CREATED_BY"<>127 AND "XXX_CREATED_BY"<>128 AND "XXX_CREATED_BY"<>129)) AND ("XXX_INV_ORG_ID" IS NOT

                                    NULL OR "XXX_OU_ID" IS NOT NULL OR "XXX_LEDGER_ID" IS NOT NULL OR "XXX_BG_ID" IS NOT NULL OR ("XXX_INV_ORG_ID" IS NULL AND "XXX_OU_ID"

                                    IS NULL AND "XXX_LEDGER_ID" IS NULL AND "XXX_BG_ID" IS NULL)) AND NVL("XX_XXX_T3353"."C1",'X')='Value Set'))

                       

                       

                      Note

                      -----

                         - dynamic sampling used for this statement (level=2)

                         - automatic DOP: skipped because of IO calibrate statistics are missing

                       

                       

                       

                       

                      92 rows selected.

                       

                      The V$SESSION_EVENT output (I indeed not sure I collected it right, I disconnect SQL developer connection (using a particular user) used for running the CTAS query, then reconnect & re-run the CTAS query, then query for all the SID by the user). Please advise if this is not right.

                       

                      SQL> select * from V$SESSION_EVENT where SID in (105,220,218,11,316,315,115,9,212) order by SID;

                               9 PX Deq: Execution Msg                                                     25              0       77923      3116.92          0         779230507   98582416    2723168908           6 Idle

                               9 events in waitclass Other                                                  1              0           0          .48          0              4778 1736664284    1893977003           0 Other

                              11 Disk file operations I/O                                                   2              0           0            0          0                69  166678035    1740759767           8 User I/O

                              11 latch: cache buffers chains                                                6              0           1          .08          0              5074 2779959231    3875070507           4 Concurrency

                              11 PX Deq Credit: send blkd                                                   1              0           0            0          0                 4 2610814049    2723168908           6 Idle

                              11 PX Deq: Execution Msg                                                      2              0           2         1.14          2             22725   98582416    2723168908           6 Idle

                              11 PX Deq: Table Q Normal                                                   151              0         959         6.35        102           9588775  799271425    2723168908           6 Idle

                              11 PX Deq: Table Q Sample                                                     2              0           3         1.49          3             29859 1062854067    2723168908           6 Idle

                             105 Disk file operations I/O                                                   1              0           0          .01          0                59  166678035    1740759767           8 User I/O

                             105 latch: cache buffers chains                                                5              0           1          .12          0              5838 2779959231    3875070507           4 Concurrency

                             105 PX Deq: Table Q Sample                                                     1              0           3            3          3             29974 1062854067    2723168908           6 Idle

                             105 PX Deq: Table Q Normal                                                    44              0         960        21.81        359           9595532  799271425    2723168908           6 Idle

                             105 PX Deq: Execution Msg                                                      2              0           2         1.23          2             24522   98582416    2723168908           6 Idle

                             105 PX Deq Credit: send blkd                                                   1              0           0          .01          0                68 2610814049    2723168908           6 Idle

                             115 events in waitclass Other                                                  1              0           3         2.92          3             29218 1736664284    1893977003           0 Other

                             115 PX Deq: Execution Msg                                                     14              0       77873      5562.39          1         778734213   98582416    2723168908           6 Idle

                             212 PX Deq: Execution Msg                                                     18              0       77739      4318.86          1         777394350   98582416    2723168908           6 Idle

                             212 PX Deq Credit: need buffer                                                 1              0           0          .01          0                93 2267953574    2723168908           6 Idle

                             212 events in waitclass Other                                                  1              0           0          .04          0               354 1736664284    1893977003           0 Other

                             218 os thread startup                                                          8              0           7           .9          1             72023   86156091    3875070507           4 Concurrency

                             218 PX Deq: Join ACK                                                           8              0           0          .03          0              2162 4205438796    2723168908           6 Idle

                             218 events in waitclass Other                                                  3              3           0            0          0                 9 1736664284    1893977003           0 Other

                             218 SQL*Net break/reset to client                                             16              0           0          .01          0              2225 1963888671    4217450380           1 Application

                             218 SQL*Net message from client                                               40              0        1442        36.05       1435          14418196 1421975091    2723168908           6 Idle

                             218 SQL*Net message to client                                                 40              0           0            0          0                78 2067390145    2000153315           7 Network

                             218 PX Deq: Execute Reply                                                     80              0       78756       984.46        107         787564557 2599037852    2723168908           6 Idle

                             218 PX Deq: Parse Reply                                                        8              0           0          .06          0              4751 4255662421    2723168908           6 Idle

                             220 Disk file operations I/O                                                   2              0           0          .01          0               113  166678035    1740759767           8 User I/O

                             220 latch: cache buffers chains                                                8              0           2          .21          1             16801 2779959231    3875070507           4 Concurrency

                             220 PX Deq Credit: send blkd                                                   1              0           0          .01          0               133 2610814049    2723168908           6 Idle

                             220 PX Deq: Execution Msg                                                      2              0           2         1.19          2             23700   98582416    2723168908           6 Idle

                             220 PX Deq: Table Q Normal                                                   142              0         959         6.75        308           9589512  799271425    2723168908           6 Idle

                             220 PX Deq: Table Q Sample                                                     1              0           3            3          3             30026 1062854067    2723168908           6 Idle

                             315 PX Deq Credit: need buffer                                                 1              0           0            0          0                45 2267953574    2723168908           6 Idle

                             315 PX Deq: Execution Msg                                                     17              0       77935       4584.4          1         779348726   98582416    2723168908           6 Idle

                             315 events in waitclass Other                                                  1              0           0          .02          0               170 1736664284    1893977003           0 Other

                             316 PX Deq: Execution Msg                                                      2              0           2         1.23          2             24654   98582416    2723168908           6 Idle

                             316 PX Deq Credit: send blkd                                                   1              0           0          .03          0               258 2610814049    2723168908           6 Idle

                             316 latch: cache buffers chains                                                8              0           1          .07          0              5566 2779959231    3875070507           4 Concurrency

                             316 Disk file operations I/O                                                   2              0           0            0          0                78  166678035    1740759767           8 User I/O

                             316 PX Deq: Table Q Normal                                                   399              0         958          2.4         22           9576229  799271425    2723168908           6 Idle

                             316 PX Deq: Table Q Sample                                                     2              0           3         1.49          3             29768 1062854067    2723168908           6 Idle

                       

                       

                      42 rows selected.

                      • 8. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                        R4C_SuteeS

                        Greg,

                         

                        Thanks for the suggestion, the quey indeed not mine one, I basically a system administrator which has some basic DBA skill. They to help my SQL dev to check the issue, I will get the guy to review your comment and try your suggestion, see whether or not that improves my query response time (it good for us anyway, appreciate this).

                         

                        However, the select statement just returns a result in 10 minutes (which acceptable in our case) but I don't know why, once it is added to the CTAS. It then the CTAS just takes forever (to be honest, I was waiting up to 24 hours, I just thinking that why the select statement takes just 10 minutes, but CTAS for more 24 hours, that doesn't make any sense). I tried to research myself already but no luck, hence, try to raise the question to look for some advice here.

                         

                        Thanks,
                        Sutee

                        • 9. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                          AndrewSayer

                          Do you know why the standard select statement isn't accessing IMPORT_METADATA at all?

                           

                          You're doing nested loop full tablescans in both plans, my guess is that Greg is spot on when he suggested that you probably weren't measuring the time it takes to complete the full select statement. It is completely unfair to time the time it takes to get the first few rows with the time it takes to get all of the rows.

                           

                          Anyway, you're likely getting the nested loop full tablescans due to the weirdness of your outer joins.

                           

                            ON 1                      =1

                              AND NVL(LEFT_CODE.C8,'X')  = NVL(RIGHT_CODE.C8,'X')

                              AND NVL(LEFT_CODE.C10,'X') = NVL(RIGHT_CODE.C10,'X')

                              AND NVL(LEFT_CODE.C1,'X')  = NVL(RIGHT_CODE.C1,'X')

                              AND NVL(LEFT_CODE.C1,'X')  = 'VALUE SET'

                              AND NVL(LEFT_CODE.C8,'X') IS NOT NULL

                           

                          That final statement is very redundant (whoever wrote the statement needs to go back and learn what NVL does) and is confusing the optimizer. A quick test case suggests that it forces the optimizer to do the same thing as https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-forcing-nested-loops/

                           

                          Use this instead

                            ON 1                      =1

                              AND NVL(LEFT_CODE.C8,'X')  = NVL(RIGHT_CODE.C8,'X')

                              AND NVL(LEFT_CODE.C10,'X') = NVL(RIGHT_CODE.C10,'X')

                              AND NVL(LEFT_CODE.C1,'X')  = NVL(RIGHT_CODE.C1,'X')

                              AND NVL(LEFT_CODE.C1,'X')  = 'VALUE SET'

                          --   AND NVL(LEFT_CODE.C8,'X') IS NOT NULL

                          1 person found this helpful
                          • 10. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                            Jonathan Lewis

                            The most significant difference between your two plans is that the optimizer has discarded the redundant scalar subqueries in the simple select statement, but has not done so in the CTAS. This may be a limitation of your (11g) version of Oracle, which has a number of defects in the way it handles "ANSI" style SQL - which is first transformed into the traditional Oracle syntax.

                             

                            Since the optimizer is not doing it for you , start by eliminating the scalar subqueries you don't need (as others have suggested).

                             

                            Regards

                            Jonathan Lewis

                             

                            Currently collecting and matching donations to a life-saving cause.

                            1 person found this helpful
                            • 11. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                              Jonathan Lewis

                              I've created a model of this problem, and it is an optimizer defect.

                               

                              With an "order by" in the inline view the scalar subqueries "survive" in the CTAS even though they are correcly eliminated in the SELECT (but, interestingly, a SORT ORDER BY does get promoted into the main query on the SELECT).

                               

                              The problem persists through 12.2.0.1 and 19.3.0.0

                               

                               

                              Regards

                              Jonathan Lewis

                               

                              Currently collecting and matching donations to a life-saving cause.

                               

                              UPDATE:  I've also reporudced the problem with "classical" Oracle syntax, so it's not an ANSI transform issue.

                              1 person found this helpful
                              • 13. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                                R4C_SuteeS

                                Jonathan,

                                 

                                Thank you very much for, I have also raised SR with Oracle as you described that this is an optimizer bug that it impact multiple version and my SQL developer still not able to figure what to remove.

                                 

                                Regards,
                                Sutee

                                • 14. Re: Create table as select (CTAS) takes forever, even its select query takes just 10 minutes
                                  AndrewSayer

                                  R4C_SuteeS wrote:

                                   

                                  Jonathan,

                                   

                                  Thank you very much for, I have also raised SR with Oracle as you described that this is an optimizer bug that it impact multiple version and my SQL developer still not able to figure what to remove.

                                   

                                  Regards,
                                  Sutee

                                  Do you mean your human developer or did you mean to refer to the application? It should be relatively easy to go through your query and remove the scalar subqueries that arent needed because the columns they are projecting aren’t used later on.

                                   

                                  I hope you didn’t miss my explanation of why you’re getting nested loop full table scans ( separate from the scalar subqueries being executed)

                                  1 2 Previous Next