1 2 Previous Next 19 Replies Latest reply on Oct 30, 2018 6:11 PM by Charles M

    New data warehouse report has long running query

    Charles M

      Hi All,

       

      Development team has created a report which takes approximately 1.5 hours to complete. The database team was asked to look into it from the db-side.

       

      I have gathered the following as an initial assessment of the SQL that is hitting the data warehouse:

       

      DB Parameters:

      NAME                             TYPE    VALUE
      optimizer_adaptive_features      boolean TRUE
      optimizer_adaptive_reporting_onlyboolean FALSE
      optimizer_capture_sql_plan_baselines boolean FALSE
      optimizer_dynamic_sampling       integer 2
      optimizer_features_enable        string  12.1.0.2
      optimizer_index_caching          integer 0
      optimizer_index_cost_adj         integer 100
      optimizer_inmemory_aware         boolean TRUE
      optimizer_mode                   string  ALL_ROWS
      optimizer_secure_view_merging    boolean TRUE
      optimizer_use_invisible_indexes  boolean FALSE
      optimizer_use_pending_statistics boolean FALSE
      optimizer_use_sql_plan_baselines boolean TRUE

       

      NAME                             TYPE    VALUE
      db_file_multiblock_read_count    integer 128

       

      NAME                             TYPE    VALUE
      db_block_size                    integer 8192

       

      NAME                             TYPE    VALUE
      cursor_sharing                   string  EXACT

       

      SNAME            PNAME                 PVAL1 PVAL2
      SYSSTATS_INFO    STATUS                      COMPLETED
      SYSSTATS_INFO    DSTART                      03-11-2017 00:00
      SYSSTATS_INFO    DSTOP                       03-11-2017 00:00
      SYSSTATS_INFO    FLAGS                     1
      SYSSTATS_MAIN    CPUSPEEDNW             1332
      SYSSTATS_MAIN    IOSEEKTIM                10
      SYSSTATS_MAIN    IOTFRSPEED             4096
      SYSSTATS_MAIN    SREADTIM
      SYSSTATS_MAIN    MREADTIM
      SYSSTATS_MAIN    CPUSPEED
      SYSSTATS_MAIN    MBRC
      SYSSTATS_MAIN    MAXTHR
      SYSSTATS_MAIN    SLAVETHR

       

      Explain Plan:

      PLAN_TABLE_OUTPUT

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

      Plan hash value: 3953956312

       

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

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

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

      |   0 | SELECT STATEMENT                                             |                             |     6 |  1692 |       | 68819   (1)| 00:00:03 |        |      |            |

      |   1 |  TEMP TABLE TRANSFORMATION                                   |                             |       |       |       |            |          |        |      |            |

      |   2 |   LOAD AS SELECT                                             | SYS_TEMP_0FD9D699F_B77B4DD0 |       |       |       |            |          |        |      |            |

      |*  3 |    TABLE ACCESS FULL                                         | W_MCAL_DAY_D                |   156 |  4368 |       |  2121   (1)| 00:00:01 |        |      |            |

      |   4 |   PX COORDINATOR                                             |                             |       |       |       |            |          |        |      |            |

      |   5 |    PX SEND QC (RANDOM)                                       | :TQ10009                    |     1 |   426 |       | 66588   (1)| 00:00:03 |  Q1,09 | P->S | QC (RAND)  |

      |   6 |     LOAD AS SELECT (TEMP SEGMENT MERGE)                      | SYS_TEMP_0FD9D69A0_B77B4DD0 |       |       |       |            |          |  Q1,09 | PCWP |            |

      |*  7 |      HASH JOIN                                               |                             |     1 |   426 |       | 66588   (1)| 00:00:03 |  Q1,09 | PCWP |            |

      |   8 |       PX RECEIVE                                             |                             |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,09 | PCWP |            |

      |   9 |        PX SEND HYBRID HASH                                   | :TQ10008                    |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,08 | P->P | HYBRID HASH|

      |  10 |         STATISTICS COLLECTOR                                 |                             |       |       |       |            |          |  Q1,08 | PCWC |            |

      |* 11 |          HASH JOIN                                           |                             |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,08 | PCWP |            |

      |  12 |           PX RECEIVE                                         |                             |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,08 | PCWP |            |

      |  13 |            PX SEND HYBRID HASH                               | :TQ10007                    |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,07 | P->P | HYBRID HASH|

      |  14 |             STATISTICS COLLECTOR                             |                             |       |       |       |            |          |  Q1,07 | PCWC |            |

      |* 15 |              HASH JOIN                                       |                             |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,07 | PCWP |            |

      |  16 |               PX RECEIVE                                     |                             |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,07 | PCWP |            |

      |  17 |                PX SEND HYBRID HASH                           | :TQ10006                    |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,06 | P->P | HYBRID HASH|

      |  18 |                 STATISTICS COLLECTOR                         |                             |       |       |       |            |          |  Q1,06 | PCWC |            |

      |* 19 |                  HASH JOIN BUFFERED                          |                             |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,06 | PCWP |            |

      |  20 |                   BUFFER SORT                                |                             |       |       |       |            |          |  Q1,06 | PCWC |            |

      |  21 |                    PX RECEIVE                                |                             | 27659 |  1728K|       |   580   (1)| 00:00:01 |  Q1,06 | PCWP |            |

      |  22 |                     PX SEND HYBRID HASH                      | :TQ10000                    | 27659 |  1728K|       |   580   (1)| 00:00:01 |        | S->P | HYBRID HASH|

      |  23 |                      STATISTICS COLLECTOR                    |                             |       |       |       |            |          |        |      |            |

      |* 24 |                       TABLE ACCESS BY INDEX ROWID BATCHED    | W_AP_HOLDS_F                | 27659 |  1728K|       |   580   (1)| 00:00:01 |        |      |            |

      |  25 |                        BITMAP CONVERSION TO ROWIDS           |                             |       |       |       |            |          |        |      |            |

      |  26 |                         BITMAP AND                           |                             |       |       |       |            |          |        |      |            |

      |  27 |                          BITMAP MERGE                        |                             |       |       |       |            |          |        |      |            |

      |  28 |                           BITMAP KEY ITERATION               |                             |       |       |       |            |          |        |      |            |

      |* 29 | ED                         TABLE ACCESS BY INDEX ROWID BATCH | W_INT_ORG_D                 |     9 |    81 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 30 |                             INDEX RANGE SCAN                 | W_INT_ORG_D_M10             |    20 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |* 31 |                            BITMAP INDEX RANGE SCAN           | W_AP_HOLDS_F_F11            |       |       |       |            |          |        |      |            |

      |  32 |                          BITMAP MERGE                        |                             |       |       |       |            |          |        |      |            |

      |  33 |                           BITMAP KEY ITERATION               |                             |       |       |       |            |          |        |      |            |

      |* 34 |                            TABLE ACCESS FULL                 | W_AP_HOLD_RELEASE_REASON_D  |   125 |   875 |       |     5   (0)| 00:00:01 |        |      |            |

      |* 35 |                            BITMAP INDEX RANGE SCAN           | W_AP_HOLDS_F_F1             |       |       |       |            |          |        |      |            |

      |  36 |                   PX RECEIVE                                 |                             |  1336K|   338M|       | 67695   (7)| 00:00:03 |  Q1,06 | PCWP |            |

      |  37 |                    PX SEND HYBRID HASH                       | :TQ10005                    |  1336K|   338M|       | 67695   (7)| 00:00:03 |  Q1,05 | P->P | HYBRID HASH|

      |  38 |                     VIEW                                     |                             |  1336K|   338M|       | 67695   (7)| 00:00:03 |  Q1,05 | PCWP |            |

      |  39 |                      UNION-ALL                               |                             |       |       |       |            |          |  Q1,05 | PCWP |            |

      |  40 |                       HASH UNIQUE                            |                             |   663K|    12M|    20M|  5224   (1)| 00:00:01 |  Q1,05 | PCWP |            |

      |  41 |                        PX RECEIVE                            |                             |   663K|    12M|       |  5224   (1)| 00:00:01 |  Q1,05 | PCWP |            |

      |  42 |                         PX SEND HASH                         | :TQ10004                    |   663K|    12M|       |  5224   (1)| 00:00:01 |  Q1,04 | P->P | HASH       |

      |  43 |                          HASH UNIQUE                         |                             |   663K|    12M|    20M|  5224   (1)| 00:00:01 |  Q1,04 | PCWP |            |

      |  44 |                           PX BLOCK ITERATOR                  |                             |       |       |       |            |          |  Q1,04 | PCWC |            |

      |* 45 |                            BITMAP INDEX FAST FULL SCAN       | W_AP_XACT_F_XCUSTOM1        |       |       |       |            |          |  Q1,04 | PCWP |            |

      |  46 |                       PX SELECTOR                            |                             |       |       |       |            |          |  Q1,05 | PCWP |            |

      |  47 |                        HASH UNIQUE                           |                             |   672K|    15M|    25M| 62471   (1)| 00:00:03 |  Q1,05 | PCWP |            |

      |* 48 |                         TABLE ACCESS FULL                    | W_AP_XACT_F                 |   672K|    15M|       | 57725   (1)| 00:00:03 |  Q1,05 | PCWP |            |

      |  49 |               BUFFER SORT                                    |                             |       |       |       |            |          |  Q1,07 | PCWC |            |

      |  50 |                PX RECEIVE                                    |                             |     9 |   225 |       |     4   (0)| 00:00:01 |  Q1,07 | PCWP |            |

      |  51 |                 PX SEND HYBRID HASH                          | :TQ10001                    |     9 |   225 |       |     4   (0)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 52 |                  TABLE ACCESS BY INDEX ROWID BATCHED         | W_INT_ORG_D                 |     9 |   225 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 53 |                   INDEX RANGE SCAN                           | W_INT_ORG_D_M10             |    20 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |  54 |           BUFFER SORT                                        |                             |       |       |       |            |          |  Q1,08 | PCWC |            |

      |  55 |            PX RECEIVE                                        |                             |   125 |  5500 |       |     5   (0)| 00:00:01 |  Q1,08 | PCWP |            |

      |  56 |             PX SEND HYBRID HASH                              | :TQ10002                    |   125 |  5500 |       |     5   (0)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 57 |              TABLE ACCESS FULL                               | W_AP_HOLD_RELEASE_REASON_D  |   125 |  5500 |       |     5   (0)| 00:00:01 |        |      |            |

      |  58 |       BUFFER SORT                                            |                             |       |       |       |            |          |  Q1,09 | PCWC |            |

      |  59 |        PX RECEIVE                                            |                             |  8027 |   211K|       |  2228   (1)| 00:00:01 |  Q1,09 | PCWP |            |

      |  60 |         PX SEND HYBRID HASH                                  | :TQ10003                    |  8027 |   211K|       |  2228   (1)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 61 |          TABLE ACCESS FULL                                   | W_PARTY_D                   |  8027 |   211K|       |  2228   (1)| 00:00:01 |        |      |            |

      |* 62 |   FILTER                                                     |                             |       |       |       |            |          |        |      |            |

      |  63 |    SORT GROUP BY                                             |                             |     6 |  1692 |       |   102   (2)| 00:00:01 |        |      |            |

      |  64 |     NESTED LOOPS                                             |                             |   105 | 29610 |       |    97   (2)| 00:00:01 |        |      |            |

      |  65 |      NESTED LOOPS                                            |                             |   105 | 29610 |       |    97   (2)| 00:00:01 |        |      |            |

      |  66 |       NESTED LOOPS                                           |                             |     3 |   783 |       |    94   (2)| 00:00:01 |        |      |            |

      |  67 |        NESTED LOOPS                                          |                             |     1 |   243 |       |    93   (2)| 00:00:01 |        |      |            |

      |  68 |         NESTED LOOPS                                         |                             |     1 |   232 |       |    92   (2)| 00:00:01 |        |      |            |

      |  69 |          NESTED LOOPS                                        |                             |     1 |   204 |       |     9   (0)| 00:00:01 |        |      |            |

      |* 70 |           HASH JOIN                                          |                             |     1 |   188 |       |     8   (0)| 00:00:01 |        |      |            |

      |* 71 |            HASH JOIN                                         |                             |     1 |   162 |       |     6   (0)| 00:00:01 |        |      |            |

      |  72 |             TABLE ACCESS FULL                                | SYS_TEMP_0FD9D69A0_B77B4DD0 |     1 |   129 |       |     2   (0)| 00:00:01 |        |      |            |

      |  73 |             TABLE ACCESS BY INDEX ROWID BATCHED              | W_INT_ORG_D_TL              |    25 |   825 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 74 |              INDEX RANGE SCAN                                | W_INT_ORG_D_TL_U1           |    25 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |  75 |            TABLE ACCESS FULL                                 | SYS_TEMP_0FD9D699F_B77B4DD0 |   156 |  4056 |       |     2   (0)| 00:00:01 |        |      |            |

      |  76 |           TABLE ACCESS BY INDEX ROWID                        | W_USER_D                    |     1 |    16 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 77 |            INDEX UNIQUE SCAN                                 | W_USER_D_P1                 |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |* 78 |          TABLE ACCESS BY INDEX ROWID BATCHED                 | W_MCAL_DAY_D                |     1 |    28 |       |    92   (2)| 00:00:01 |        |      |            |

      |  79 |           BITMAP CONVERSION TO ROWIDS                        |                             |       |       |       |            |          |        |      |            |

      |  80 |            BITMAP AND                                        |                             |       |       |       |            |          |        |      |            |

      |  81 |             BITMAP MERGE                                     |                             |       |       |       |            |          |        |      |            |

      |  82 |              BITMAP KEY ITERATION                            |                             |       |       |       |            |          |        |      |            |

      |  83 |               TABLE ACCESS FULL                              | SYS_TEMP_0FD9D69A0_B77B4DD0 |     1 |     3 |       |     2   (0)| 00:00:01 |        |      |            |

      |* 84 |               BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_XCUSTOM1       |       |       |       |            |          |        |      |            |

      |  85 |             BITMAP MERGE                                     |                             |       |       |       |            |          |        |      |            |

      |  86 |              BITMAP KEY ITERATION                            |                             |       |       |       |            |          |        |      |            |

      |  87 |               TABLE ACCESS FULL                              | SYS_TEMP_0FD9D699F_B77B4DD0 |   156 |   468 |       |     2   (0)| 00:00:01 |        |      |            |

      |* 88 |               BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_F1             |       |       |       |            |          |        |      |            |

      |  89 |         TABLE ACCESS BY INDEX ROWID                          | W_PARTY_ORG_D               |     1 |    11 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 90 |          INDEX UNIQUE SCAN                                   | W_PARTY_ORG_D_P1            |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |  91 |        TABLE ACCESS BY INDEX ROWID                           | W_SUPPLIER_ACCOUNT_D        |     7 |   126 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 92 |         INDEX UNIQUE SCAN                                    | W_SPLR_ACCNT_D_P1           |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |* 93 |       INDEX UNIQUE SCAN                                      | W_EMPLOYEE_D_P1             |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |  94 |      TABLE ACCESS BY INDEX ROWID                             | W_EMPLOYEE_D                |    36 |   756 |       |     1   (0)| 00:00:01 |        |      |            |

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

       

      Predicate Information (identified by operation id):

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

       

         3 - filter(CASE "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END <=TO_DATE(' 2018-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND CASE

                    "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END >=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

                    "T2519709"."ADJUSTMENT_PERIOD_FLG"='N')

         7 - access("T2520043"."SUPPLIER_WID"="T2534878"."ROW_WID")

        11 - access("T2519877"."ROW_WID"="T2520043"."HOLD_REASON_WID")

        15 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

        19 - access("T2520043"."INVOICE_NUM"="T2972724"."PURCH_INVOICE_NUM" AND "T2520043"."SPLR_ACCT_WID"="T2972724"."SPLR_ACCT_WID")

        24 - filter("T2520043"."DELETE_FLG"='N')

        29 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

        30 - access("T2591329"."CURRENT_FLG"='Y')

        31 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

        34 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

        35 - access("T2520043"."HOLD_REASON_WID"="T2519877"."ROW_WID")

        45 - filter("PURCH_ORDER_NUM" IS NOT NULL)

        48 - filter("PURCH_ORDER_NUM" IS NULL AND "PURCH_ORDER_DT_WID"=0)

        52 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

        53 - access("T2591329"."CURRENT_FLG"='Y')

        57 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

        61 - filter("T2534878"."SUPPLIER_FLG"='Y')

        62 - filter(SUM(CASE  WHEN "C13"='Y' THEN 1 ELSE 0 END )>0)

        70 - access("C0"="C0" AND "C1"="C7")

        71 - access("DATASOURCE_NUM_ID"="C5" AND "INTEGRATION_ID"="C4")

        74 - access("LANGUAGE_CODE"='US')

        77 - access("T2519902"."ROW_WID"="C9")

        78 - filter("T2618846"."ADJUSTMENT_PERIOD_FLG"='N')

        84 - access("T2618846"."MCAL_CAL_WID"="C0" AND "C3"="T2618846"."MCAL_DAY_DT_WID")

             filter("T2618846"."MCAL_CAL_WID"="C0" AND "C0"="T2618846"."MCAL_CAL_WID")

        88 - access("T2618846"."MCAL_CAL_WID"="C0")

        90 - access("C2"="T2616936"."ROW_WID")

        92 - access("C8"="T2639505"."ROW_WID")

             filter("C1"="T2639505"."ROW_WID")

        93 - access("C6"="T2622263"."ROW_WID")

       

      Note

      -----

         - dynamic statistics used: dynamic sampling (level=2)

         - star transformation used for this statement

         - 1 Sql Plan Directive used for this statement

       

      Execution Plan:

      435 rows selected.

       

      Elapsed: 01:21:59.32

       

      Execution Plan

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

      Plan hash value: 3953956312

       

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

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

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

      |   0 | SELECT STATEMENT                                             |                             |     6 |  1692 |       | 68819   (1)| 00:00:03 |        |      |            |

      |   1 |  TEMP TABLE TRANSFORMATION                                   |                             |       |       |       |            |          |        |      |            |

      |   2 |   LOAD AS SELECT                                             | SYS_TEMP_0FD9D69A5_B77B4DD0 |       |       |       |            |          |        |      |            |

      |*  3 |    TABLE ACCESS FULL                                         | W_MCAL_DAY_D                |   156 |  4368 |       |  2121   (1)| 00:00:01 |        |      |            |

      |   4 |   PX COORDINATOR                                             |                             |       |       |       |            |          |        |      |            |

      |   5 |    PX SEND QC (RANDOM)                                       | :TQ10009                    |     1 |   426 |       | 66588   (1)| 00:00:03 |  Q1,09 | P->S | QC (RAND)  |

      |   6 |     LOAD AS SELECT (TEMP SEGMENT MERGE)                      | SYS_TEMP_0FD9D69A6_B77B4DD0 |       |       |       |            |          |  Q1,09 | PCWP |            |

      |*  7 |      HASH JOIN                                               |                             |     1 |   426 |       | 66588   (1)| 00:00:03 |  Q1,09 | PCWP |            |

      |   8 |       PX RECEIVE                                             |                             |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,09 | PCWP |            |

      |   9 |        PX SEND HYBRID HASH                                   | :TQ10008                    |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,08 | P->P | HYBRID HASH|

      |  10 |         STATISTICS COLLECTOR                                 |                             |       |       |       |            |          |  Q1,08 | PCWC |            |

      |* 11 |          HASH JOIN                                           |                             |     1 |   399 |       | 64360   (1)| 00:00:03 |  Q1,08 | PCWP |            |

      |  12 |           PX RECEIVE                                         |                             |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,08 | PCWP |            |

      |  13 |            PX SEND HYBRID HASH                               | :TQ10007                    |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,07 | P->P | HYBRID HASH|

      |  14 |             STATISTICS COLLECTOR                             |                             |       |       |       |            |          |  Q1,07 | PCWC |            |

      |* 15 |              HASH JOIN                                       |                             |     1 |   355 |       | 64355   (1)| 00:00:03 |  Q1,07 | PCWP |            |

      |  16 |               PX RECEIVE                                     |                             |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,07 | PCWP |            |

      |  17 |                PX SEND HYBRID HASH                           | :TQ10006                    |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,06 | P->P | HYBRID HASH|

      |  18 |                 STATISTICS COLLECTOR                         |                             |       |       |       |            |          |  Q1,06 | PCWC |            |

      |* 19 |                  HASH JOIN BUFFERED                          |                             |     1 |   330 |       | 64351   (1)| 00:00:03 |  Q1,06 | PCWP |            |

      |  20 |                   BUFFER SORT                                |                             |       |       |       |            |          |  Q1,06 | PCWC |            |

      |  21 |                    PX RECEIVE                                |                             | 24893 |  1555K|       |   580   (1)| 00:00:01 |  Q1,06 | PCWP |            |

      |  22 |                     PX SEND HYBRID HASH                      | :TQ10000                    | 24893 |  1555K|       |   580   (1)| 00:00:01 |        | S->P | HYBRID HASH|

      |  23 |                      STATISTICS COLLECTOR                    |                             |       |       |       |            |          |        |      |            |

      |* 24 |                       TABLE ACCESS BY INDEX ROWID BATCHED    | W_AP_HOLDS_F                | 24893 |  1555K|       |   580   (1)| 00:00:01 |        |      |            |

      |  25 |                        BITMAP CONVERSION TO ROWIDS           |                             |       |       |       |            |          |        |      |            |

      |  26 |                         BITMAP AND                           |                             |       |       |       |            |          |        |      |            |

      |  27 |                          BITMAP MERGE                        |                             |       |       |       |            |          |        |      |            |

      |  28 |                           BITMAP KEY ITERATION               |                             |       |       |       |            |          |        |      |            |

      |* 29 | ED                         TABLE ACCESS BY INDEX ROWID BATCH | W_INT_ORG_D                 |     9 |    81 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 30 |                             INDEX RANGE SCAN                 | W_INT_ORG_D_M10             |    20 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |* 31 |                            BITMAP INDEX RANGE SCAN           | W_AP_HOLDS_F_F11            |       |       |       |            |          |        |      |            |

      |  32 |                          BITMAP MERGE                        |                             |       |       |       |            |          |        |      |            |

      |  33 |                           BITMAP KEY ITERATION               |                             |       |       |       |            |          |        |      |            |

      |* 34 |                            TABLE ACCESS FULL                 | W_AP_HOLD_RELEASE_REASON_D  |   125 |   875 |       |     5   (0)| 00:00:01 |        |      |            |

      |* 35 |                            BITMAP INDEX RANGE SCAN           | W_AP_HOLDS_F_F1             |       |       |       |            |          |        |      |            |

      |  36 |                   PX RECEIVE                                 |                             |   757K|   192M|       | 65495   (4)| 00:00:03 |  Q1,06 | PCWP |            |

      |  37 |                    PX SEND HYBRID HASH                       | :TQ10005                    |   757K|   192M|       | 65495   (4)| 00:00:03 |  Q1,05 | P->P | HYBRID HASH|

      |  38 |                     VIEW                                     |                             |   757K|   192M|       | 65495   (4)| 00:00:03 |  Q1,05 | PCWP |            |

      |  39 |                      UNION-ALL                               |                             |       |       |       |            |          |  Q1,05 | PCWP |            |

      |  40 |                       HASH UNIQUE                            |                             | 84139 |  1561K|    20M|  3024   (2)| 00:00:01 |  Q1,05 | PCWP |            |

      |  41 |                        PX RECEIVE                            |                             | 84139 |  1561K|       |  3024   (2)| 00:00:01 |  Q1,05 | PCWP |            |

      |  42 |                         PX SEND HASH                         | :TQ10004                    | 84139 |  1561K|       |  3024   (2)| 00:00:01 |  Q1,04 | P->P | HASH       |

      |  43 |                          HASH UNIQUE                         |                             | 84139 |  1561K|    20M|  3024   (2)| 00:00:01 |  Q1,04 | PCWP |            |

      |  44 |                           PX BLOCK ITERATOR                  |                             |       |       |       |            |          |  Q1,04 | PCWC |            |

      |* 45 |                            BITMAP INDEX FAST FULL SCAN       | W_AP_XACT_F_XCUSTOM1        |       |       |       |            |          |  Q1,04 | PCWP |            |

      |  46 |                       PX SELECTOR                            |                             |       |       |       |            |          |  Q1,05 | PCWP |            |

      |  47 |                        HASH UNIQUE                           |                             |   672K|    15M|    25M| 62471   (1)| 00:00:03 |  Q1,05 | PCWP |            |

      |* 48 |                         TABLE ACCESS FULL                    | W_AP_XACT_F                 |   672K|    15M|       | 57725   (1)| 00:00:03 |  Q1,05 | PCWP |            |

      |  49 |               BUFFER SORT                                    |                             |       |       |       |            |          |  Q1,07 | PCWC |            |

      |  50 |                PX RECEIVE                                    |                             |     9 |   225 |       |     4   (0)| 00:00:01 |  Q1,07 | PCWP |            |

      |  51 |                 PX SEND HYBRID HASH                          | :TQ10001                    |     9 |   225 |       |     4   (0)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 52 |                  TABLE ACCESS BY INDEX ROWID BATCHED         | W_INT_ORG_D                 |     9 |   225 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 53 |                   INDEX RANGE SCAN                           | W_INT_ORG_D_M10             |    20 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |  54 |           BUFFER SORT                                        |                             |       |       |       |            |          |  Q1,08 | PCWC |            |

      |  55 |            PX RECEIVE                                        |                             |   125 |  5500 |       |     5   (0)| 00:00:01 |  Q1,08 | PCWP |            |

      |  56 |             PX SEND HYBRID HASH                              | :TQ10002                    |   125 |  5500 |       |     5   (0)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 57 |              TABLE ACCESS FULL                               | W_AP_HOLD_RELEASE_REASON_D  |   125 |  5500 |       |     5   (0)| 00:00:01 |        |      |            |

      |  58 |       BUFFER SORT                                            |                             |       |       |       |            |          |  Q1,09 | PCWC |            |

      |  59 |        PX RECEIVE                                            |                             |  8027 |   211K|       |  2228   (1)| 00:00:01 |  Q1,09 | PCWP |            |

      |  60 |         PX SEND HYBRID HASH                                  | :TQ10003                    |  8027 |   211K|       |  2228   (1)| 00:00:01 |        | S->P | HYBRID HASH|

      |* 61 |          TABLE ACCESS FULL                                   | W_PARTY_D                   |  8027 |   211K|       |  2228   (1)| 00:00:01 |        |      |            |

      |* 62 |   FILTER                                                     |                             |       |       |       |            |          |        |      |            |

      |  63 |    SORT GROUP BY                                             |                             |     6 |  1692 |       |   102   (2)| 00:00:01 |        |      |            |

      |  64 |     NESTED LOOPS                                             |                             |   103 | 29046 |       |    97   (2)| 00:00:01 |        |      |            |

      |  65 |      NESTED LOOPS                                            |                             |   103 | 29046 |       |    97   (2)| 00:00:01 |        |      |            |

      |  66 |       NESTED LOOPS                                           |                             |     3 |   783 |       |    94   (2)| 00:00:01 |        |      |            |

      |  67 |        NESTED LOOPS                                          |                             |     1 |   243 |       |    93   (2)| 00:00:01 |        |      |            |

      |  68 |         NESTED LOOPS                                         |                             |     1 |   232 |       |    92   (2)| 00:00:01 |        |      |            |

      |  69 |          NESTED LOOPS                                        |                             |     1 |   204 |       |     9   (0)| 00:00:01 |        |      |            |

      |* 70 |           HASH JOIN                                          |                             |     1 |   188 |       |     8   (0)| 00:00:01 |        |      |            |

      |* 71 |            HASH JOIN                                         |                             |     1 |   162 |       |     6   (0)| 00:00:01 |        |      |            |

      |  72 |             TABLE ACCESS FULL                                | SYS_TEMP_0FD9D69A6_B77B4DD0 |     1 |   129 |       |     2   (0)| 00:00:01 |        |      |            |

      |  73 |             TABLE ACCESS BY INDEX ROWID BATCHED              | W_INT_ORG_D_TL              |    25 |   825 |       |     4   (0)| 00:00:01 |        |      |            |

      |* 74 |              INDEX RANGE SCAN                                | W_INT_ORG_D_TL_U1           |    25 |       |       |     1   (0)| 00:00:01 |        |      |            |

      |  75 |            TABLE ACCESS FULL                                 | SYS_TEMP_0FD9D69A5_B77B4DD0 |   156 |  4056 |       |     2   (0)| 00:00:01 |        |      |            |

      |  76 |           TABLE ACCESS BY INDEX ROWID                        | W_USER_D                    |     1 |    16 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 77 |            INDEX UNIQUE SCAN                                 | W_USER_D_P1                 |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |* 78 |          TABLE ACCESS BY INDEX ROWID BATCHED                 | W_MCAL_DAY_D                |     1 |    28 |       |    92   (2)| 00:00:01 |        |      |            |

      |  79 |           BITMAP CONVERSION TO ROWIDS                        |                             |       |       |       |            |          |        |      |            |

      |  80 |            BITMAP AND                                        |                             |       |       |       |            |          |        |      |            |

      |  81 |             BITMAP MERGE                                     |                             |       |       |       |            |          |        |      |            |

      |  82 |              BITMAP KEY ITERATION                            |                             |       |       |       |            |          |        |      |            |

      |  83 |               TABLE ACCESS FULL                              | SYS_TEMP_0FD9D69A6_B77B4DD0 |     1 |     3 |       |     2   (0)| 00:00:01 |        |      |            |

      |* 84 |               BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_XCUSTOM1       |       |       |       |            |          |        |      |            |

      |  85 |             BITMAP MERGE                                     |                             |       |       |       |            |          |        |      |            |

      |  86 |              BITMAP KEY ITERATION                            |                             |       |       |       |            |          |        |      |            |

      |  87 |               TABLE ACCESS FULL                              | SYS_TEMP_0FD9D69A5_B77B4DD0 |   156 |   468 |       |     2   (0)| 00:00:01 |        |      |            |

      |* 88 |               BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_F1             |       |       |       |            |          |        |      |            |

      |  89 |         TABLE ACCESS BY INDEX ROWID                          | W_PARTY_ORG_D               |     1 |    11 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 90 |          INDEX UNIQUE SCAN                                   | W_PARTY_ORG_D_P1            |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |  91 |        TABLE ACCESS BY INDEX ROWID                           | W_SUPPLIER_ACCOUNT_D        |     7 |   126 |       |     1   (0)| 00:00:01 |        |      |            |

      |* 92 |         INDEX UNIQUE SCAN                                    | W_SPLR_ACCNT_D_P1           |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |* 93 |       INDEX UNIQUE SCAN                                      | W_EMPLOYEE_D_P1             |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |

      |  94 |      TABLE ACCESS BY INDEX ROWID                             | W_EMPLOYEE_D                |    36 |   756 |       |     1   (0)| 00:00:01 |        |      |            |

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

       

      Predicate Information (identified by operation id):

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

       

         3 - filter(CASE "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END <=TO_DATE(' 2018-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND CASE

                    "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END >=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

                    "T2519709"."ADJUSTMENT_PERIOD_FLG"='N')

         7 - access("T2520043"."SUPPLIER_WID"="T2534878"."ROW_WID")

        11 - access("T2519877"."ROW_WID"="T2520043"."HOLD_REASON_WID")

        15 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

        19 - access("T2520043"."INVOICE_NUM"="T2972724"."PURCH_INVOICE_NUM" AND "T2520043"."SPLR_ACCT_WID"="T2972724"."SPLR_ACCT_WID")

        24 - filter("T2520043"."DELETE_FLG"='N')

        29 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

        30 - access("T2591329"."CURRENT_FLG"='Y')

        31 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

        34 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

        35 - access("T2520043"."HOLD_REASON_WID"="T2519877"."ROW_WID")

        45 - filter("PURCH_ORDER_NUM" IS NOT NULL)

        48 - filter("PURCH_ORDER_NUM" IS NULL AND "PURCH_ORDER_DT_WID"=0)

        52 - filter("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y')

        53 - access("T2591329"."CURRENT_FLG"='Y')

        57 - filter("T2519877"."TYPE_FLG"='H' OR "T2519877"."TYPE_FLG"='U' OR "T2519877"."TYPE_FLG" IS NULL)

        61 - filter("T2534878"."SUPPLIER_FLG"='Y')

        62 - filter(SUM(CASE  WHEN "C13"='Y' THEN 1 ELSE 0 END )>0)

        70 - access("C0"="C0" AND "C1"="C7")

        71 - access("DATASOURCE_NUM_ID"="C5" AND "INTEGRATION_ID"="C4")

        74 - access("LANGUAGE_CODE"='US')

        77 - access("T2519902"."ROW_WID"="C9")

        78 - filter("T2618846"."ADJUSTMENT_PERIOD_FLG"='N')

        84 - access("T2618846"."MCAL_CAL_WID"="C0" AND "C3"="T2618846"."MCAL_DAY_DT_WID")

             filter("T2618846"."MCAL_CAL_WID"="C0" AND "C0"="T2618846"."MCAL_CAL_WID")

        88 - access("T2618846"."MCAL_CAL_WID"="C0")

        90 - access("C2"="T2616936"."ROW_WID")

        92 - access("C8"="T2639505"."ROW_WID")

             filter("C1"="T2639505"."ROW_WID")

        93 - access("C6"="T2622263"."ROW_WID")

       

      Note

      -----

         - dynamic statistics used: dynamic sampling (level=2)

         - star transformation used for this statement

         - 12 Sql Plan Directives used for this statement

       

       

      Statistics

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

             5049  recursive calls

           101864  db block gets

        598324100  consistent gets

        193440500  physical reads

          3170756  redo size

            34116  bytes sent via SQL*Net to client

              596  bytes received via SQL*Net from client

                6  SQL*Net roundtrips to/from client

              692  sorts (memory)

                0  sorts (disk)

              435  rows processed

       

      Trying to find out where the query is spending the time from the db perspective.

       

      Note: I did run the tuning advisor ... which is where the custom indexes came from. Plus, it stated that there were some indexes which were in a "stale state". I did run gather stats on them, but it didn't seem to have an impact.

       

      DB Version 12.1.0.2 (data warehouse)

       

      Regards,

       

      Charles

        • 1. Re: New data warehouse report has long running query
          John Thorton

          Looks like Siebel software to me.

           

          Posted  plans report run times under 1 minute.

          Do all tables & indexes have current  statistics?

          • 2. Re: New data warehouse report has long running query
            Charles M

            Hi John,

             

            We using Bi Apps ... but, I think some Siebel stuff is built-in to it?

             

            Yes, I am confused by the timings in the plan(s). It takes over an hour from SQL Developer and the same from SQLPlus, running on the db server. How is that possible? I believe that the ETL is supposed to gather stats on the objects (tables, indexes). It runs every night. But, I will confirm the statistics on these objects and reply back ...

             

            Regards,

             

            Charles

            • 3. Re: New data warehouse report has long running query
              Charles M

              Here's what I found for each of the tables involved in the query:

              select table_name, max(stats_update_time) from DBA_TAB_STATS_HISTORY
              where owner = '{owner}'
              --and table_name not like '%$%';
              and table_name in ('W_PARTY_D','W_PARTY_ORG_D','W_EMPLOYEE_D','W_SUPPLIER_ACCOUNT_D','W_USER_D','W_MCAL_DAY_D','W_INT_ORG_D_TL','W_AP_XACT_F')
              group by TABLE_NAME;
              

               

              W_AP_XACT_F             24-OCT-18 09.54.32.582824000 PM -04:00

              W_MCAL_DAY_D            24-OCT-18 09.37.55.806230000 PM -04:00

              W_PARTY_ORG_D           24-OCT-18 09.33.22.466703000 PM -04:00

              W_INT_ORG_D_TL          24-OCT-18 09.01.09.391006000 PM -04:00

              W_SUPPLIER_ACCOUNT_D    24-OCT-18 09.19.10.847614000 PM -04:00

              W_PARTY_D               24-OCT-18 09.33.45.785432000 PM -04:00

              W_EMPLOYEE_D            24-OCT-18 09.25.48.445798000 PM -04:00

              W_USER_D                24-OCT-18 08.56.54.209158000 PM -04:00

               

              Regards,

               

              Charles

              • 4. Re: New data warehouse report has long running query
                John Thorton

                Charles M wrote:

                 

                Here's what I found for each of the tables involved in the query:

                1. selecttable_name,max(stats_update_time)fromDBA_TAB_STATS_HISTORY
                2. whereowner='{owner}'
                3. --andtable_namenotlike'%$%';
                4. andtable_namein('W_PARTY_D','W_PARTY_ORG_D','W_EMPLOYEE_D','W_SUPPLIER_ACCOUNT_D','W_USER_D','W_MCAL_DAY_D','W_INT_ORG_D_TL','W_AP_XACT_F')
                5. groupbyTABLE_NAME;

                 

                W_AP_XACT_F 24-OCT-18 09.54.32.582824000 PM -04:00

                W_MCAL_DAY_D 24-OCT-18 09.37.55.806230000 PM -04:00

                W_PARTY_ORG_D 24-OCT-18 09.33.22.466703000 PM -04:00

                W_INT_ORG_D_TL 24-OCT-18 09.01.09.391006000 PM -04:00

                W_SUPPLIER_ACCOUNT_D 24-OCT-18 09.19.10.847614000 PM -04:00

                W_PARTY_D 24-OCT-18 09.33.45.785432000 PM -04:00

                W_EMPLOYEE_D 24-OCT-18 09.25.48.445798000 PM -04:00

                W_USER_D 24-OCT-18 08.56.54.209158000 PM -04:00

                 

                Regards,

                 

                Charles

                were the EXPLAIN PLANS generated from same database the reports were run against?

                • 5. Re: New data warehouse report has long running query
                  Charles M

                  Here is a list of the indexes (from the tables involved) where the last_analyzed date is prior to 10/24/18:

                  select index_name, table_name, last_analyzed from DBA_IND_STATISTICS
                  where table_name in ('W_PARTY_D','W_PARTY_ORG_D','W_EMPLOYEE_D','W_SUPPLIER_ACCOUNT_D','W_USER_D','W_MCAL_DAY_D','W_INT_ORG_D_TL','W_AP_XACT_F')
                  order by 3 asc;
                  

                   

                  W_USER_D_N1           W_USER_D               16-SEP-18

                  W_USER_D_U1           W_USER_D               16-SEP-18

                  W_USER_D_P1           W_USER_D               16-SEP-18

                  W_USER_D_M6           W_USER_D               16-SEP-18

                  W_INT_ORG_D_TL_U1     W_INT_ORG_D_TL         16-SEP-18

                  W_INT_ORG_D_TL_P1     W_INT_ORG_D_TL         16-SEP-18

                  W_SPLR_ACCNT_D_M20    W_SUPPLIER_ACCOUNT_D   16-SEP-18

                  W_SPLR_ACCNT_D_U1     W_SUPPLIER_ACCOUNT_D   16-SEP-18

                  W_SPLR_ACCNT_D_P1     W_SUPPLIER_ACCOUNT_D   16-SEP-18

                  W_PARTY_ORG_D_U1      W_PARTY_ORG_D          16-SEP-18

                  W_PARTY_ORG_D_P1      W_PARTY_ORG_D          16-SEP-18

                  W_PARTY_D_FI1         W_PARTY_D              16-SEP-18

                  W_PARTY_D_T2          W_PARTY_D              16-SEP-18

                  W_PARTY_D_P1          W_PARTY_D              16-SEP-18

                  W_PARTY_D_T1          W_PARTY_D              16-SEP-18

                  W_PARTY_D_U1          W_PARTY_D              16-SEP-18

                  W_AP_XACT_F_M99       W_AP_XACT_F            03-OCT-18

                  W_AP_XACT_F_M1        W_AP_XACT_F            03-OCT-18

                  W_AP_XACT_F_M2        W_AP_XACT_F            03-OCT-18

                  W_AP_XACT_F_M3    W_AP_XACT_F    03-OCT-18

                  W_AP_XACT_F_M7    W_AP_XACT_F    03-OCT-18

                  W_AP_XACT_F_U1    W_AP_XACT_F    03-OCT-18

                  W_AP_XACT_F_M8    W_AP_XACT_F    03-OCT-18

                  W_AP_XACT_F_U2    W_AP_XACT_F    03-OCT-18

                  W_MCAL_DAY_D_XCUSTOM1    W_MCAL_DAY_D    22-OCT-18

                  W_AP_XACT_F_XCUSTOM1    W_AP_XACT_F    22-OCT-18

                  • 6. Re: New data warehouse report has long running query
                    Mark D Powell

                    CharlesM, does your shop use the standard Oracle statistics update task or a home grown one?  If standard or if the home grown task relies on DBMS_STATS defaults what is the setting for CASCADE?  For that matter it probably would not hurt to list all the DBMS_STAT parameter defaults in use.

                    - -

                    HTH -- Mark D Powell --

                    • 7. Re: New data warehouse report has long running query
                      Charles M

                      Hi Mark,

                       

                      Thanks for the reply. Any/all of the statistics gathering steps are done via the ETL process - we use Oracle Data Intgerator (ODI). I'll need to dig in there to see what it is doing.

                       

                      Charles

                      • 8. Re: New data warehouse report has long running query
                        Charles M

                        Part 2:

                        PLAN_TABLE_OUTPUT

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

                        SQL_ID  7j19qh91m283h, child number 0

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

                        WITH SAWITH0 AS (select /*+ gather_plan_statistics */ sum(case  when

                        T2520043.ACTIVE_HOLD_STATUS_FLAG = 'Y' then 1 else 0 end ) as c1,

                        sum(case  when T2520043.ACTIVE_HOLD_STATUS_FLAG = 'Y' then

                        T2520043.DOC_INVOICE_AMT else 0 end ) as c2,      T2520043.INVOICE_NUM

                        as c3,      T2972724.PO_NUM as c4,      T2519902.FULL_NAME as c5,

                        case  when T2519709.ROW_WID = 0 then NULL else T2519709.MCAL_DAY_DT end

                        as c6,      case  when T2618846.ROW_WID = 0 then NULL else

                        T2618846.MCAL_DAY_DT end  as c7,      T2522555.ORG_NAME as c8,

                        T2622263.FULL_NAME as c9,      T2639505.SPLR_ACCT_NAME as c10,

                        T2534878.NAME as c11,      T2616936.SUPPLIER_NUM as c12,

                        T2591329.ROW_WID as c13,      T2618846.ROW_WID as c14,

                        T2519709.ROW_WID as c15,      T2519877.W_HOLD_TYPE as c16,

                        T2519877.HOLD_CODE as c17,      T2519877.DATASOURCE_NUM_ID as c18 from

                            W_PARTY_D T2534878 /* Dim_W_PARTY_D_Supplier */ ,

                        W_PARTY_ORG_D T2616936 /* Dim_W_PARTY_ORG_D_Supplier */

                         

                        Plan hash value: 3953956312

                         

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

                        | Id  | Operation                                                    | Name                        | Starts | E-Rows | A-Rows |  A-Time  | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

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

                        |  0 | SELECT STATEMENT                                            |                            |      1 |        |    435 |00:09:24.61 |    598M|    193M|    129 |      |      |          |        |

                        |  1 |  TEMP TABLE TRANSFORMATION                                  |                            |      1 |        |    435 |00:09:24.61 |    598M|    193M|    129 |      |      |          |        |

                        |  2 |  LOAD AS SELECT                                            |                            |      1 |        |      0 |00:00:00.09 |    7763 |      2 |      2 |  1040K|  1040K|          |        |

                        |*  3 |    TABLE ACCESS FULL                                        | W_MCAL_DAY_D                |      1 |    156 |    248 |00:00:00.06 |    7757 |      0 |      0 |      |      |          |        |

                        |  4 |  PX COORDINATOR                                            |                            |      1 |        |    253 |00:01:33.54 |    9657 |  8244 |      0 |      |      |          |        |

                        |  5 |    PX SEND QC (RANDOM)                                      | :TQ10009                    |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  6 |    LOAD AS SELECT (TEMP SEGMENT MERGE)                      |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |  1040K|  1040K| 1040K (0)|        |

                        |*  7 |      HASH JOIN                                              |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |  809M|    15M| 4542K (0)|    1024 |

                        |  8 |      PX RECEIVE                                            |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  9 |        PX SEND HYBRID HASH                                  | :TQ10008                    |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  10 |        STATISTICS COLLECTOR                                |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 11 |          HASH JOIN                                          |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |  1653M|    33M| 9336K (0)|        |

                        |  12 |          PX RECEIVE                                        |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  13 |            PX SEND HYBRID HASH                              | :TQ10007                    |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  14 |            STATISTICS COLLECTOR                            |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 15 |              HASH JOIN                                      |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |  1793M|    34M|  10M (0)|        |

                        |  16 |              PX RECEIVE                                    |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  17 |                PX SEND HYBRID HASH                          | :TQ10006                    |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  18 |                STATISTICS COLLECTOR                        |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 19 |                  HASH JOIN BUFFERED                          |                            |      0 |      1 |      0 |00:00:00.01 |      0 |      0 |      0 |  8318K|  2628K| 3549K (0)|        |

                        |  20 |                  BUFFER SORT                                |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 | 18432 | 18432 |12288  (0)|        |

                        |  21 |                    PX RECEIVE                                |                            |      0 |  24898 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  22 |                    PX SEND HYBRID HASH                      | :TQ10000                    |      0 |  24898 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  23 |                      STATISTICS COLLECTOR                    |                            |      1 |        |  27667 |00:00:00.09 |    1041 |    185 |      0 |      |      |          |        |

                        |* 24 |                      TABLE ACCESS BY INDEX ROWID BATCHED    | W_AP_HOLDS_F                |      1 |  24898 |  27667 |00:00:00.07 |    1041 |    185 |      0 |      |      |          |        |

                        |  25 |                        BITMAP CONVERSION TO ROWIDS          |                            |      1 |        |  27667 |00:00:00.01 |    167 |    10 |      0 |      |      |          |        |

                        |  26 |                        BITMAP AND                          |                            |      1 |        |      1 |00:00:00.01 |    167 |    10 |      0 |      |      |          |        |

                        |  27 |                          BITMAP MERGE                        |                            |      1 |        |      1 |00:00:00.01 |      17 |      3 |      0 |  1024K|  512K|14336  (0)|        |

                        |  28 |                          BITMAP KEY ITERATION              |                            |      1 |        |    42 |00:00:00.01 |      17 |      3 |      0 |      |      |          |        |

                        |* 29 | ED                        TABLE ACCESS BY INDEX ROWID BATCH | W_INT_ORG_D                |      1 |      9 |    10 |00:00:00.01 |      4 |      0 |      0 |      |      |          |        |

                        |* 30 |                            INDEX RANGE SCAN                | W_INT_ORG_D_M10            |      1 |    20 |    20 |00:00:00.01 |      1 |      0 |      0 |      |      |          |        |

                        |* 31 |                            BITMAP INDEX RANGE SCAN          | W_AP_HOLDS_F_F11            |    10 |        |    42 |00:00:00.01 |      13 |      3 |      0 |      |      |          |        |

                        |  32 |                          BITMAP MERGE                        |                            |      1 |        |      1 |00:00:00.01 |    150 |      7 |      0 |  1024K|  512K|28672  (0)|        |

                        |  33 |                          BITMAP KEY ITERATION              |                            |      1 |        |    136 |00:00:00.01 |    150 |      7 |      0 |      |      |          |        |

                        |* 34 |                            TABLE ACCESS FULL                | W_AP_HOLD_RELEASE_REASON_D  |      1 |    125 |    125 |00:00:00.01 |      21 |      3 |      0 |      |      |          |        |

                        |* 35 |                            BITMAP INDEX RANGE SCAN          | W_AP_HOLDS_F_F1            |    125 |        |    136 |00:00:00.01 |    129 |      4 |      0 |      |      |          |        |

                        |  36 |                  PX RECEIVE                                |                            |      0 |    752K|      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  37 |                    PX SEND HYBRID HASH                      | :TQ10005                    |      0 |    752K|      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  38 |                    VIEW                                    |                            |      0 |    752K|      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  39 |                      UNION-ALL                              |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  40 |                      HASH UNIQUE                            |                            |      0 |  84149 |      0 |00:00:00.01 |      0 |      0 |      0 |  8587K|  2723K| 3066K (0)|        |

                        |  41 |                        PX RECEIVE                            |                            |      0 |  84149 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  42 |                        PX SEND HASH                        | :TQ10004                    |      0 |  84149 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  43 |                          HASH UNIQUE                        |                            |      0 |  84149 |      0 |00:00:00.01 |      0 |      0 |      0 |  7122K|  2924K| 3125K (0)|        |

                        |  44 |                          PX BLOCK ITERATOR                  |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 45 |                            BITMAP INDEX FAST FULL SCAN      | W_AP_XACT_F_XCUSTOM1        |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  46 |                      PX SELECTOR                            |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  47 |                        HASH UNIQUE                          |                            |      0 |    668K|      0 |00:00:00.01 |      0 |      0 |      0 |  966M|    20M|          |        |

                        |* 48 |                        TABLE ACCESS FULL                    | W_AP_XACT_F                |      0 |    668K|      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  49 |              BUFFER SORT                                    |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |  576K|  463K| 2048  (0)|        |

                        |  50 |                PX RECEIVE                                    |                            |      0 |      9 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  51 |                PX SEND HYBRID HASH                          | :TQ10001                    |      0 |      9 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 52 |                  TABLE ACCESS BY INDEX ROWID BATCHED        | W_INT_ORG_D                |      1 |      9 |    10 |00:00:00.01 |      4 |      0 |      0 |      |      |          |        |

                        |* 53 |                  INDEX RANGE SCAN                          | W_INT_ORG_D_M10            |      1 |    20 |    20 |00:00:00.01 |      1 |      0 |      0 |      |      |          |        |

                        |  54 |          BUFFER SORT                                        |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |  576K|  463K| 2048  (0)|        |

                        |  55 |            PX RECEIVE                                        |                            |      0 |    125 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  56 |            PX SEND HYBRID HASH                              | :TQ10002                    |      0 |    125 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 57 |              TABLE ACCESS FULL                              | W_AP_HOLD_RELEASE_REASON_D  |      1 |    125 |    125 |00:00:00.01 |      21 |      0 |      0 |      |      |          |        |

                        |  58 |      BUFFER SORT                                            |                            |      0 |        |      0 |00:00:00.01 |      0 |      0 |      0 |  576K|  463K| 4096  (0)|        |

                        |  59 |        PX RECEIVE                                            |                            |      0 |  8027 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |  60 |        PX SEND HYBRID HASH                                  | :TQ10003                    |      0 |  8027 |      0 |00:00:00.01 |      0 |      0 |      0 |      |      |          |        |

                        |* 61 |          TABLE ACCESS FULL                                  | W_PARTY_D                  |      1 |  8027 |  8027 |00:00:00.01 |    8067 |  8058 |      0 |      |      |          |        |

                        |* 62 |  FILTER                                                    |                            |      1 |        |    435 |00:07:49.70 |    597M|    193M|      0 |      |      |          |        |

                        |  63 |    SORT GROUP BY                                            |                            |      1 |      6 |  2254 |00:07:49.70 |    597M|    193M|      0 |  690K|  648K|  613K (0)|        |

                        |  64 |    NESTED LOOPS                                            |                            |      1 |    109 |  5920 |01:20:42.54 |    597M|    193M|      0 |      |      |          |        |

                        |  65 |      NESTED LOOPS                                            |                            |      1 |    109 |  5920 |01:20:42.15 |    597M|    193M|      0 |      |      |          |        |

                        |  66 |      NESTED LOOPS                                          |                            |      1 |      3 |  5920 |01:20:42.01 |    597M|    193M|      0 |      |      |          |        |

                        |  67 |        NESTED LOOPS                                          |                            |      1 |      1 |  5920 |01:20:41.55 |    597M|    193M|      0 |      |      |          |        |

                        |  68 |        NESTED LOOPS                                        |                            |      1 |      1 |  5920 |01:20:40.87 |    597M|    193M|      0 |      |      |          |        |

                        |  69 |          NESTED LOOPS                                        |                            |      1 |      1 |  5920 |00:00:01.56 |  40308 |  32665 |      0 |      |      |          |        |

                        |* 70 |          HASH JOIN                                          |                            |      1 |      1 |  5920 |00:00:01.31 |  32685 |  32646 |      0 |    18M|  4082K|  19M (0)|        |

                        |* 71 |            HASH JOIN                                        |                            |      1 |      1 |  68006 |00:00:01.16 |  32675 |  32644 |      0 |    13M|  2125K|  16M (0)|        |

                        |  72 |            TABLE ACCESS FULL                                | SYS_TEMP_0FD9D69D4_B77B4DD0 |      1 |      1 |  68006 |00:00:00.13 |  32671 |  32641 |      0 |      |      |          |        |

                        |  73 |            TABLE ACCESS BY INDEX ROWID BATCHED              | W_INT_ORG_D_TL              |      1 |    25 |    25 |00:00:00.02 |      4 |      3 |      0 |      |      |          |        |

                        |* 74 |              INDEX RANGE SCAN                                | W_INT_ORG_D_TL_U1          |      1 |    25 |    25 |00:00:00.01 |      1 |      0 |      0 |      |      |          |        |

                        |  75 |            TABLE ACCESS FULL                                | SYS_TEMP_0FD9D69D3_B77B4DD0 |      1 |    156 |    248 |00:00:00.01 |      10 |      2 |      0 |      |      |          |        |

                        |  76 |          TABLE ACCESS BY INDEX ROWID                        | W_USER_D                    |  5920 |      1 |  5920 |00:00:00.22 |    7623 |    19 |      0 |      |      |          |        |

                        |* 77 |            INDEX UNIQUE SCAN                                | W_USER_D_P1                |  5920 |      1 |  5920 |00:00:00.11 |    1703 |      6 |      0 |      |      |          |        |

                        |* 78 |          TABLE ACCESS BY INDEX ROWID BATCHED                | W_MCAL_DAY_D                |  5920 |      1 |  5920 |01:19:20.60 |    597M|    193M|      0 |      |      |          |        |

                        |  79 |          BITMAP CONVERSION TO ROWIDS                        |                            |  5920 |        |  5920 |01:19:20.41 |    597M|    193M|      0 |      |      |          |        |

                        |  80 |            BITMAP AND                                        |                            |  5920 |        |  5920 |01:19:20.33 |    597M|    193M|      0 |      |      |          |        |

                        |  81 |            BITMAP MERGE                                    |                            |  5920 |        |  5920 |01:17:56.01 |    596M|    193M|      0 |  7836K|  979K| 8368K (0)|        |

                        |  82 |              BITMAP KEY ITERATION                            |                            |  5920 |        |    402M|01:06:24.38 |    596M|    193M|      0 |      |      |          |        |

                        |  83 |              TABLE ACCESS FULL                              | SYS_TEMP_0FD9D69D4_B77B4DD0 |  5920 |      1 |    402M|00:25:50.86 |    193M|    193M|      0 |      |      |          |        |

                        |* 84 |              BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_XCUSTOM1      |    402M|        |    402M|00:33:37.82 |    402M|      5 |      0 |      |      |          |        |

                        |  85 |            BITMAP MERGE                                    |                            |  5920 |        |  5920 |00:01:24.10 |    1648K|      8 |      0 |  1024K|  512K|  929K (0)|        |

                        |  86 |              BITMAP KEY ITERATION                            |                            |  5920 |        |  3805K|00:00:22.51 |    1648K|      8 |      0 |      |      |          |        |

                        |  87 |              TABLE ACCESS FULL                              | SYS_TEMP_0FD9D69D3_B77B4DD0 |  5920 |    156 |  1468K|00:00:03.34 |  59200 |      0 |      0 |      |      |          |        |

                        |* 88 |              BITMAP INDEX RANGE SCAN                        | W_MCAL_DAY_D_F1            |  1468K|        |  3805K|00:00:09.84 |    1589K|      8 |      0 |      |      |          |        |

                        |  89 |        TABLE ACCESS BY INDEX ROWID                          | W_PARTY_ORG_D              |  5920 |      1 |  5920 |00:00:00.51 |    9242 |    278 |      0 |      |      |          |        |

                        |* 90 |          INDEX UNIQUE SCAN                                  | W_PARTY_ORG_D_P1            |  5920 |      1 |  5920 |00:00:00.22 |    3322 |      4 |      0 |      |      |          |        |

                        |  91 |        TABLE ACCESS BY INDEX ROWID                          | W_SUPPLIER_ACCOUNT_D        |  5920 |      7 |  5920 |00:00:00.85 |    9374 |    320 |      0 |      |      |          |        |

                        |* 92 |        INDEX UNIQUE SCAN                                    | W_SPLR_ACCNT_D_P1          |  5920 |      1 |  5920 |00:00:00.13 |    3454 |    40 |      0 |      |      |          |        |

                        |* 93 |      INDEX UNIQUE SCAN                                      | W_EMPLOYEE_D_P1            |  5920 |      1 |  5920 |00:00:00.28 |    4020 |    65 |      0 |      |      |          |        |

                        |  94 |      TABLE ACCESS BY INDEX ROWID                            | W_EMPLOYEE_D                |  5920 |    39 |  5920 |00:00:00.16 |    5920 |    229 |      0 |      |      |          |        |

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

                         

                        Predicate Information (identified by operation id):

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

                         

                          3 - filter((CASE "T2519709"."ROW_WID" WHEN 0 THEN NULL ELSE "T2519709"."MCAL_DAY_DT" END <=TO_DATE(' 2018-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND CASE "T2519709"."ROW_WID" WHEN 0 THEN

                                      NULL ELSE "T2519709"."MCAL_DAY_DT" END >=TO_DATE(' 2018-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2519709"."ADJUSTMENT_PERIOD_FLG"='N'))

                          7 - access("T2520043"."SUPPLIER_WID"="T2534878"."ROW_WID")

                          11 - access("T2519877"."ROW_WID"="T2520043"."HOLD_REASON_WID")

                          15 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

                          19 - access("T2520043"."INVOICE_NUM"="T2972724"."PURCH_INVOICE_NUM" AND "T2520043"."SPLR_ACCT_WID"="T2972724"."SPLR_ACCT_WID")

                          24 - filter("T2520043"."DELETE_FLG"='N')

                          29 - filter(("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y'))

                          30 - access("T2591329"."CURRENT_FLG"='Y')

                          31 - access("T2520043"."PAYABLES_ORG_WID"="T2591329"."SCD1_WID")

                          34 - filter((INTERNAL_FUNCTION("T2519877"."TYPE_FLG") OR "T2519877"."TYPE_FLG" IS NULL))

                          35 - access("T2520043"."HOLD_REASON_WID"="T2519877"."ROW_WID")

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

                              filter("PURCH_ORDER_NUM" IS NOT NULL)

                          48 - filter(("PURCH_ORDER_NUM" IS NULL AND "PURCH_ORDER_DT_WID"=0))

                          52 - filter(("T2591329"."PAYABLES_ORG_FLG"='U' OR "T2591329"."PAYABLES_ORG_FLG"='Y'))

                          53 - access("T2591329"."CURRENT_FLG"='Y')

                          57 - filter((INTERNAL_FUNCTION("T2519877"."TYPE_FLG") OR "T2519877"."TYPE_FLG" IS NULL))

                          61 - filter("T2534878"."SUPPLIER_FLG"='Y')

                          62 - filter(SUM(CASE  WHEN "C13"='Y' THEN 1 ELSE 0 END )>0)

                          70 - access("C0"="C0" AND "C1"="C7")

                          71 - access("DATASOURCE_NUM_ID"="C5" AND "INTEGRATION_ID"="C4")

                          74 - access("LANGUAGE_CODE"='US')

                          77 - access("T2519902"."ROW_WID"="C9")

                          78 - filter("T2618846"."ADJUSTMENT_PERIOD_FLG"='N')

                          84 - access("T2618846"."MCAL_CAL_WID"="C0" AND "C3"="T2618846"."MCAL_DAY_DT_WID")

                              filter(("T2618846"."MCAL_CAL_WID"="C0" AND "C0"="T2618846"."MCAL_CAL_WID"))

                          88 - access("T2618846"."MCAL_CAL_WID"="C0")

                          90 - access("C2"="T2616936"."ROW_WID")

                          92 - access("C8"="T2639505"."ROW_WID")

                              filter("C1"="T2639505"."ROW_WID")

                          93 - access("C6"="T2622263"."ROW_WID")

                         

                        Note

                        -----

                          - dynamic statistics used: dynamic sampling (level=2)

                          - star transformation used for this statement

                          - 12 Sql Plan Directives used for this statement

                        • 9. Re: New data warehouse report has long running query
                          Charles M

                          Hi Mark,

                           

                          Here is what I found for 'table maintenance' on one particular task (for the W_USER_D table):

                          ---if data has been loaded since last Stats Date then Gather Stats

                            IF (V_LAST_STATS_DATE<V_LAST_DML_DATE) THEN

                              DBMS_STATS.GATHER_TABLE_STATS(

                                OWNNAME => '{owner} --I'm omitting this!',

                                TABNAME => 'W_USER_D',

                                ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,

                                DEGREE => DBMS_STATS.DEFAULT_DEGREE,

                                METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO',

                                CASCADE => FALSE

                              );

                              v_EVENT_RESULT:= 'SUCCESS';

                            ELSE

                              v_EVENT_RESULT:= 'SUCCESS STATS SKIPPED SINCE NO DML SINCE LAST STATS ';

                            END IF ;

                          • 10. Re: New data warehouse report has long running query
                            Mark D Powell

                            CharlesM, the setting of CASCADE FALSE would not update the index statistics when the table statistics are updated.  I would think it would be desirable to update the index statistics at the same time the table statistics are updated.

                            - -

                            If you manually collect statistics on the indexes that are old does the plan change?  If yes, is the change for the better?

                            - -

                            Not knowing the data and not taking the time to review the plan in detail it is had to say but sometimes the query plan is optimal and what you get is a good as it can be.

                            - -

                            Being that parallelism is in use is the DOP reasonable?   Not too high so that excessive time is spent putting the data back together and not so low that a few more slaves would not result in a significant improvement.

                            - -

                            You may want to review your database parallelism settings.

                            - -

                            HTH -- Mark D Powell --

                            1 person found this helpful
                            • 11. Re: New data warehouse report has long running query
                              Charles M

                              Thanks Mark. I will be working on the above points. I will reply back with the results.

                               

                              One thing (warning, dumb question ...) you said:

                              For that matter it probably would not hurt to list all the DBMS_STAT parameter defaults in use.

                               

                              How do you get this info in 12c (12.1.0.2)?

                               

                              I referenced our production instance, and found that we don't have the same stale/outdated indexes. I'm wondering if the db is collecting these on its own/as part of a scheduled job? If so, they are not the same between our instances ...

                               

                              Regards,

                               

                              Charles

                              • 12. Re: New data warehouse report has long running query
                                AJ

                                Charles M skrev:

                                 

                                Thanks Mark. I will be working on the above points. I will reply back with the results.

                                 

                                One thing (warning, dumb question ...) you said:

                                For that matter it probably would not hurt to list all the DBMS_STAT parameter defaults in use.

                                 

                                How do you get this info in 12c (12.1.0.2)?

                                 

                                I referenced our production instance, and found that we don't have the same stale/outdated indexes. I'm wondering if the db is collecting these on its own/as part of a scheduled job? If so, they are not the same between our instances ...

                                 

                                Regards,

                                 

                                Charles

                                Take a look at SRDC - How to Collect Standard Information for an Issue with DBMS_STATS (Doc ID 2335968.1)  and "2) Collect information regarding the DBMS_STATS Configuration"

                                 

                                Regards,

                                AJ

                                1 person found this helpful
                                • 13. Re: New data warehouse report has long running query

                                  Here's what I found for each of the tables involved in the query:

                                  What query? You haven't posted ANY query.

                                   

                                  Think about it - you have a problem with a query but you don't actually post the query.

                                   

                                  I suggest you follow the advice you were given in your other thread from two days ago

                                  Link to performance tuning/troubleshooting discussion

                                   

                                  You were provided the link to the FAQ about how to post a tuning request but it doesn't seem like you read or followed it.

                                   

                                  At a MINIMUM you need to provide:

                                   

                                  1. the actual query

                                  2. the ddl for the tables and indexes involved

                                  3. info about the statistics - are they current? how were they collected

                                  4. the actual execution plan

                                  5. rowcounts for the tables and query predicates

                                   

                                  We can't help tune a query without seeing the query and having the other info.

                                  • 14. Re: New data warehouse report has long running query
                                    jgarry

                                    Just asking since it isn't obvious from what is posted; does it flush the monitoring info before 'table maintenance', rather than (not) waiting for it to be flushed? https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68568 

                                    1 2 Previous Next