7 Replies Latest reply: Dec 20, 2012 8:47 PM by Nikolay Savvinov RSS

    Performance query - global index related

    oralicious
      can someone who has worked with global indexes suggest improvements? Performance quite bad as one can see
      explain plan for
      SELECT TO_CHAR(:B3 , 'dd-Mon-yyyy') AS PLDATE, 
      'ExternalCCInterestReversal' AS ADJUSTMENTLABEL, 
      AC.PB_ACCOUNT_NAME AS ACCT, 
      'USD' AS CURRENCY, 
      ROUND(SUM(VAL.AMOUNT),2) AS AMOUNT, 
      '' AS REMARKLABEL, 
      '' AS BCODE, 0 AS QTY, 
      '' AS ACCRUAL_CATEGORY, 
      '' AS EXPENSEMONTH, 0 AS CASHMARK, 
      0 AS MARKLMV, 
      0 AS MARKSMV
      FROM (
                  SELECT ACC.ACCOUNT_ID, 
                          DECODE(ACC.OB_ACCOUNT_ID, NULL, ACC.ACCOUNT_ID, ACC.OB_ACCOUNT_ID) UPLOAD_ACCOUNT_ID, 
                          -SUM(NVL(TXN.USD_AMOUNT,0)) AS AMOUNT
                  FROM I_CASH_TRANSACTIONS TXN 
                  INNER JOIN VW_I_ACCOUNTS ACC ON ACC.ACCOUNT_ID = TXN.ACCOUNT_ID 
                  INNER JOIN I_BUSINESS_UNITS_LU BU ON BU.BUS_UNIT_LABEL = ACC.BUS_UNIT_LABEL 
                  INNER JOIN I_CHARGES_LU CH ON CH.CHARGE_ID = TXN.CHARGE_ID 
                  INNER JOIN I_STATUS_CODES_LU SC ON SC.STATUS_CD_ID = TXN.STATUS_CD_ID 
                  INNER JOIN I_CHARGE_TYPES_LU CTYPE ON CTYPE.CHARGE_TYPE_ID = CH.CHARGE_TYPE_ID 
                  INNER JOIN I_CHARGE_PAY_PERIODS PP ON PP.CHARGE_PAY_PERIOD_ID = TXN.CHARGE_PAY_PERIOD_ID
                  WHERE CTYPE.CHARGE_TYPE = 'EXTERNAL_CAP_CHARGE' 
                  AND (:B2 = 0 OR BU.BUS_UNIT_ID = :B2 ) 
                  AND SC.STATUS_CODE_LABEL = 'ALLOCATED' 
                  AND TO_CHAR(PP.END_DATE, 'mm-yyyy') = TO_CHAR(:B1 , 'mm-yyyy') 
                  GROUP BY ACC.ACCOUNT_ID, ACC.OB_ACCOUNT_ID ) VAL, 
                  I_ACCOUNTS AC
      WHERE AC.ACCOUNT_ID = VAL.UPLOAD_ACCOUNT_ID 
      GROUP BY AC.PB_ACCOUNT_NAME;
      
      SET LINESIZE 180
      SET PAGESIZE 0
      SELECT *
      FROM   TABLE(DBMS_XPLAN.DISPLAY);
      
      
      ------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                             |                           |     1 |    43 |   227K  (1)|999:59:59 |       |       |
      |   1 |  HASH GROUP BY                               |                           |     1 |    43 |   227K  (1)|999:59:59 |       |       |
      |   2 |   NESTED LOOPS                               |                           |       |       |            |          |       |       |
      |   3 |    NESTED LOOPS                              |                           |     1 |    43 |   227K  (1)|999:59:59 |       |       |
      |   4 |     VIEW                                     |                           |     1 |    26 |   227K  (1)|999:59:59 |       |       |
      |   5 |      HASH GROUP BY                           |                           |     1 |   139 |   227K  (1)|999:59:59 |       |       |
      |   6 |       NESTED LOOPS                           |                           |       |       |            |          |       |       |
      |   7 |        NESTED LOOPS                          |                           |     1 |   139 |   227K  (1)|999:59:59 |       |       |
      |*  8 |         HASH JOIN                            |                           |     5 |   630 |   227K  (1)|999:59:59 |       |       |
      |*  9 |          VIEW                                | index$_join$_009          |     4 |    56 |     4  (25)| 00:01:36 |       |       |
      |* 10 |           HASH JOIN                          |                           |       |       |            |          |       |       |
      |* 11 |            INDEX RANGE SCAN                  | I_ISCL_STCDTYPE_LABEL     |     4 |    56 |     2   (0)| 00:00:55 |       |       |
      |  12 |            INDEX FAST FULL SCAN              | PK_STATUS_CODES_LU        |     4 |    56 |     1   (0)| 00:00:35 |       |       |
      |* 13 |          HASH JOIN                           |                           |   354 | 39648 |   227K  (1)|999:59:59 |       |       |
      |  14 |           VIEW                               | index$_join$_007          |  1626 | 13008 |    11  (10)| 00:04:47 |       |       |
      |* 15 |            HASH JOIN                         |                           |       |       |            |          |       |       |
      |  16 |             INDEX FAST FULL SCAN             | PK_CHARGES_LU             |  1626 | 13008 |     5   (0)| 00:02:17 |       |       |
      |  17 |             INDEX FAST FULL SCAN             | IF1_CHARGES_LU            |  1626 | 13008 |     8   (0)| 00:03:25 |       |       |
      |  18 |           NESTED LOOPS                       |                           |       |       |            |          |       |       |
      |  19 |            NESTED LOOPS                      |                           |   668K|    66M|   227K  (1)|999:59:59 |       |       |
      |* 20 |             HASH JOIN                        |                           |    23 |  2047 |    51   (4)| 00:23:13 |       |       |
      |* 21 |              VIEW                            | index$_join$_020          |     1 |    19 |     3  (34)| 00:01:09 |       |       |
      |* 22 |               HASH JOIN                      |                           |       |       |            |          |       |       |
      |  23 |                INDEX FAST FULL SCAN          | PK_IBUL__BUS_UNIT_ID      |     1 |    19 |     1   (0)| 00:00:35 |       |       |
      |* 24 |                INDEX FAST FULL SCAN          | UI_IBUL__BUS_UNIT_LABEL   |     1 |    19 |     1   (0)| 00:00:35 |       |       |
      |* 25 |              HASH JOIN                       |                           |   660 | 46200 |    48   (3)| 00:21:51 |       |       |
      |  26 |               TABLE ACCESS FULL              | I_TING_SS_LU              |   712 |  5696 |     6   (0)| 00:02:44 |       |       |
      |* 27 |               HASH JOIN                      |                           |   720 | 44640 |    42   (3)| 00:18:54 |       |       |
      |  28 |                NESTED LOOPS                  |                           |       |       |            |          |       |       |
      |  29 |                 NESTED LOOPS                 |                           |   720 | 36000 |    18   (0)| 00:08:12 |       |       |
      |  30 |                  NESTED LOOPS                |                           |     1 |    43 |     5   (0)| 00:02:17 |       |       |
      |  31 |                   TABLE ACCESS BY INDEX ROWID| I_ACC_ACRONYM_GRPS_LU     |     1 |    18 |     1   (0)| 00:00:28 |       |       |
      |* 32 |                    INDEX UNIQUE SCAN         | AK1_ACC_ACRONYM_GRPS_LU   |     1 |       |     0   (0)| 00:00:01 |       |       |
      |* 33 |                   TABLE ACCESS FULL          | I_CHARGE_TYPES_LU         |     1 |    25 |     4   (0)| 00:01:50 |       |       |
      |* 34 |                  INDEX RANGE SCAN            | IF1_ACC_ACRONYMS          |   720 |       |     1   (0)| 00:00:28 |       |       |
      |  35 |                 TABLE ACCESS BY INDEX ROWID  | I_ACC_ACRONYMS            |   720 |  5040 |    13   (0)| 00:05:56 |       |       |
      |  36 |                TABLE ACCESS FULL             | I_ACCOUNTS                |  3473 | 41676 |    23   (0)| 00:10:29 |       |       |
      |* 37 |             INDEX RANGE SCAN                 | I_ICTR_ACCOUNT_ID_3       | 48491 |       |   228   (0)| 01:43:47 |       |       |
      |  38 |            TABLE ACCESS BY GLOBAL INDEX ROWID| I_CASH_TRANSACTIONS       | 29516 |   432K| 16091   (0)|122:03:50 | ROWID | ROWID |
      |* 39 |         INDEX UNIQUE SCAN                    | PK_CHARGE_PAY_PERIODS     |     1 |       |     1   (0)| 00:00:28 |       |       |
      |* 40 |        TABLE ACCESS BY INDEX ROWID           | I_CHARGE_PAY_PERIODS      |     1 |    13 |     2   (0)| 00:00:55 |       |       |
      |* 41 |     INDEX UNIQUE SCAN                        | PK_ACCOUNTS               |     1 |       |     0   (0)| 00:00:01 |       |       |
      |  42 |    TABLE ACCESS BY INDEX ROWID               | I_ACCOUNTS                |     1 |    17 |     1   (0)| 00:00:28 |       |       |
      ------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         8 - access("SC"."STATUS_CD_ID"="TXN"."STATUS_CD_ID")
         9 - filter("SC"."STATUS_CODE_LABEL"='ALLOCATED')
        10 - access(ROWID=ROWID)
        11 - access("SC"."STATUS_CODE_LABEL"='ALLOCATED')
        13 - access("CTYPE"."CHARGE_TYPE_ID"="CH"."CHARGE_TYPE_ID" AND "CH"."CHARGE_ID"="TXN"."CHARGE_ID")
        15 - access(ROWID=ROWID)
        20 - access("TS"."BUS_UNIT_ID"="BU"."BUS_UNIT_ID")
        21 - filter("BUS_UNIT_ID"=TO_NUMBER(:B2) OR TO_NUMBER(:B2)=0)
        22 - access(ROWID=ROWID)
        24 - filter("BU"."BUS_UNIT_LABEL" IS NOT NULL)
        25 - access("ACC"."TING_STRATEGY_ID"="TS"."TING_STRATEGY_ID")
        27 - access("ACC"."ACCOUNT_ID"="ACR"."ACCOUNT_ID")
        32 - access("GRP"."ACRONYM_GROUP_LABEL"='SIG_ACRONYM')
        33 - filter("CTYPE"."CHARGE_TYPE"='EXTERNAL_CAP_CHARGE')
        34 - access("ACR"."ACRONYM_GRP_ID"="GRP"."ACRONYM_GRP_ID")
        37 - access("ACC"."ACCOUNT_ID"="TXN"."ACCOUNT_ID")
        39 - access("PP"."CHARGE_PAY_PERIOD_ID"="TXN"."CHARGE_PAY_PERIOD_ID")
        40 - filter(TO_CHAR(INTERNAL_FUNCTION("PP"."END_DATE"),'MM-YYYY')=TO_CHAR(TO_NUMBER(:B1),'MM-YYYY'))
        41 - access("AC"."ACCOUNT_ID"="VAL"."UPLOAD_ACCOUNT_ID")
        • 1. Re: Performance query - global index related
          sb92075
          in the EXPLAIN PLAN about half the values in ROWS column show a value of 1.
          How many actual rows exist for these?
          Are statistics current & accurate for all tables & indexes involved?
          • 2. Re: Performance query - global index related
            JohnWatson
            sb92075 wrote:
            in the EXPLAIN PLAN about half the values in ROWS column show a value of 1.
            How many actual rows exist for these?
            Are statistics current & accurate for all tables & indexes involved?
            Most of the 1s are for index unique scans, so there can be only 1 or zero rows returned. No problem with the statistics there.

            But there are two exceptions, at operation IDs 23 and 24. @OP, will those operations (the index fast full scans) really return only one row?
            • 3. Re: Performance query - global index related
              Dom Brooks
              See template tuning threads:
              [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
              [url https://forums.oracle.com/forums/thread.jspa?messageID=1812597]When your query takes too long


              Explain plan is generally only a starter for a performance tuning threads for at least two reasons:
              1. Bind variables aren't peeked and
              2. All bind variables are strings.

              Furthermore, an explain plan just gives you estimates.

              What you need to the actual execution plan metrics a) to get the actual execution plan (which may not be the same as explain plan suggests) and b) so you can compare estimates to actuals and see if the root cause is inaccurate estimates.

              The threads above talk through all this and more.


              Sb92075 has already pointed out that a lot of your estimates say "1" - this is often an indicator of statistics issues.

              Note also that you are explicitly converting a date column to a string. Rarely a good idea
              AND TO_CHAR(PP.END_DATE, 'mm-yyyy') = TO_CHAR(:B1 , 'mm-yyyy')
              Better to keep columns in their appropriate data type which enables them to use indexes where relevant/appropriate.
              • 4. Re: Performance query - global index related
                oralicious
                Just double checked there and some have 100s of rows, some have 100s of thousands of rows, stats are showing within 1% accuracy, all having rerun within last 2 days.

                The partitioned table has 102 million rows. stats gathered and are accurate on table and indexes. blevels are low.

                Ora version 11.2.0.2

                edit: just noticed replies from dom and john. I'll get to these.

                Edited by: 961469 on Dec 20, 2012 6:53 AM
                • 5. Re: Performance query - global index related
                  oralicious
                  >
                  Note also that you are explicitly converting a date column to a string. Rarely a good idea
                  AND TO_CHAR(PP.END_DATE, 'mm-yyyy') = TO_CHAR(:B1 , 'mm-yyyy')
                  Better to keep columns in their appropriate data type which enables them to use indexes where relevant/appropriate.
                  I do take your point, Ive mentioned to the developer that index that is on the end_date will never be used with the code as it is.

                  that aside, how is it possible to compare 2 fields of date type when you only want to compare month and year and want to leave the fields as date format?

                  e.g. if we take the month out of the field for comparison, its no longer a date, its just a string of JAN, FEB....
                  • 6. Re: Performance query - global index related
                    Dom Brooks
                    that aside, how is it possible to compare 2 fields of date type when you only want to compare month and year and want to leave the fields as date format?
                    Convert it to a date and use it with range predicates.

                    So, let's assume it's passed as a string datatype, e.g. 'FEB-2012' then:
                    AND pp.end_date >= TO_DATE(:B1,'MON-YYYY')
                    AND pp.end_date  < ADD_MONTHS(TO_DATE(:B1,'MON-YYYY'),1)
                    If it's there's no year, then the process is just the same but with a different format mask.

                    And if the parameter is not a string datatype, then treat it as a date.
                    • 7. Re: Performance query - global index related
                      Nikolay Savvinov
                      Hi,

                      1) you're saying "performance is quite bad as one can see", but actually one can't see since you haven't provided any information about the actual performance. what you posted is optimizer timings. can we trust them? well, if you think that 1 read indeed takes about 30 seconds (which for any realistic database is closer to a few milliseconds, or even microseconds if the read comes from the cache) then yes, otherwise no
                      2) we do see that according to the optimizer estimates retrieving a comparatively small number of rows requires a large number of reads (227k), so the plan does seem to be inefficient
                      3) however, if we trust the optimizer's judgement regarding efficiency of the plan, then it would be reasonable to trust it that it's the best plan possible
                      4) the problem with the plan, whatever it is, has little to do with global indexes. any index operation inside a nested loop can have an arbitrarily high cost, if you run it enough times
                      5) btw the TABLE ACCESS BY GLOBAL ROWID operation is estimated to have cost of 16091 to retrieve 29516 rows -- with default optimizer settings I would expect your numbers to be the same, so the optimizer settings probably have non-default values; can you post optimizer_index_cost_adj and optimizer_index_caching?
                      6) but above all else, post an actual plan with rowsource stats using ALTER SESSION SET STATISTICS_LEVEL = ALL and dbms_xplan.display_cursor(...,...,'iostats last')

                      Best regards,
                      Nikolay