1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query optimalization

Discussion in 'SQL PL/SQL' started by mabyna, Dec 3, 2013.

  1. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    I need to optimalize the query. That query was fast and only one thing was changed (and what therefore caused the performance-breakdown about 500%). That thing is adding the field to the table which is used in query. But SQL doesnt uses this table directly, but via the view (view is a simple 1:1- view with no logic). The new field in a table is part of primary key which contains 8 fields. This new field is not use in the sql query.

    Can you please advice me, what could cause performance breakdown?

    Thanks,

    mabyna
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Without a query to look at....the easiest way to optimize this would be to undo the change that was made.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For a start it is necessary to show the request text , the plan of request which is carried out very slowly.

    P.S.
    It would be very good if you showed the ASH-report and AWR-report
     
  4. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Here is a query. Unfortunatelly I havent got the plan and AWR/ASH report yet, but tomorrow I will post you the plan and maybe AWR/ASH report too (if I will have permissions for it).

    The view, which table was changed is highlighted.

    makeup-top.prodejce.cz/sql.[j][p][g]

    (remove the brackets [] in adress)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where are we to find this file? You haven't uploaded it anywhere as far as we know and there is no web address (URL) directing us to an outside site where this file can be viewed.

    We can't help you if you won't provide enough information to see what you are doing.
     
  6. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Adress is in my last post, just remove the brackets []. Do you see it?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The text is SO small it is difficult to read. Also with it being a JPG file I cannot copy the text out to put it in Notepad or another text editor. Is there some OTHER way you can post this information, like post the actual SCRIPT instead of some JPG image?
     
  8. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Ok, now it is in another format:

    makeup-top.prodejce.cz/sql.[q][l]

    (remove the brackets [] in adress)
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would also be helpful to have the execution plan, which you can generate:

    Code (SQL):

    SET autotrace ON
    <run your query here>
     
    The output will look like this:

    Code (SQL):
    SQL> SELECT * FROM oprid_sec a, emp_app_sec b WHERE a.oprid = :a1
      2    AND a.career = b.career
      3    AND a.org = b.org
      4    AND b.latest_row = 'Y';
         OPRID CAREER               ORG                       OPRID CAREER               ORG               L
    ---------- -------------------- -------------------- ---------- -------------------- -------------------- -
            44 Career 44            OrgNo9                       44 Career 44            OrgNo9               Y

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 331752133
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name          | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |               |     1 |    46 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |               |       |       |            |          |
    |   2 |   NESTED LOOPS               |               |     1 |    46 |     4   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL         | OPRID_SEC     |     1 |    22 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN          | EMP_APP_SEC_I |     1 |       |     0   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS BY INDEX ROWID| EMP_APP_SEC   |     1 |    24 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       3 - FILTER("A"."OPRID"=TO_NUMBER(:A1))
       4 - access("A"."CAREER"="B"."CAREER" AND "A"."ORG"="B"."ORG" AND
                  "B"."LATEST_ROW"='Y')
     
    Note
    -----
       - automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             10  consistent gets
              0  physical reads
              0  redo SIZE
            967  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
     
    SQL>
    This was run on Oracle 11.2.0.3, but it will work on most releases of Oracle.
     
  10. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Ok, thank you for instructions, I will send you the execution plan tomorrow.
     
  11. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    So here is an execution plan:

    -----------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 2194 | 112 (6)| 00:00:02 |
    | 1 | SORT ORDER BY | | 1 | 2194 | 112 (6)| 00:00:02 |
    | 2 | NESTED LOOPS OUTER | | 1 | 2194 | 111 (5)| 00:00:02 |
    | 3 | NESTED LOOPS | | 1 | 140 | 109 (5)| 00:00:02 |
    | 4 | NESTED LOOPS | | 1 | 121 | 108 (5)| 00:00:02 |
    | 5 | NESTED LOOPS | | 1 | 89 | 107 (5)| 00:00:02 |
    | 6 | VIEW | | 1 | 69 | 105 (5)| 00:00:02 |
    | 7 | HASH GROUP BY | | 1 | 18 | 105 (5)| 00:00:02 |
    | 8 | VIEW | | 1 | 18 | 105 (5)| 00:00:02 |
    | 9 | HASH GROUP BY | | 1 | 52 | 105 (5)| 00:00:02 |
    | 10 | VIEW | | 1 | 52 | 105 (5)| 00:00:02 |
    | 11 | HASH GROUP BY | | 1 | 81 | 105 (5)| 00:00:02 |
    |* 12 | HASH JOIN | | 1 | 81 | 104 (4)| 00:00:02 |
    |* 13 | HASH JOIN | | 1 | 37 | 7 (15)| 00:00:01 |
    |* 14 | TABLE ACCESS FULL | T5103D_VERSION | 1 | 19 | 3 (0)| 00:00:01 |
    |* 15 | TABLE ACCESS FULL | T5103D_KEYFIGURE_MAP | 296 | 5328 | 3 (0)| 00:00:01 |
    | 16 | VIEW | | 9 | 396 | 97 (4)| 00:00:02 |
    | 17 | UNION-ALL | | | | | |
    | 18 | HASH GROUP BY | | 6 | 330 | 91 (4)| 00:00:02 |
    | 19 | NESTED LOOPS | | 6 | 330 | 90 (3)| 00:00:02 |
    | 20 | VIEW | | 1 | 5 | 6 (17)| 00:00:01 |
    | 21 | HASH UNIQUE | | 1 | 92 | 6 (17)| 00:00:01 |
    | 22 | NESTED LOOPS | | 1 | 92 | 5 (0)| 00:00:01 |
    |* 23 | TABLE ACCESS FULL | T0000D_COMPANY_INFORM | 1 | 60 | 4 (0)| 00:00:01 |
    |* 24 | INDEX RANGE SCAN | XMETA_USER_COMP_BD_AUTH_I00 | 1 | 32 | 1 (0)| 00:00:01 |
    | 25 | VIEW PUSHED PREDICATE | | 1 | 50 | 84 (2)| 00:00:02 |
    | 26 | SORT GROUP BY | | 1 | 42 | 84 (2)| 00:00:02 |
    | 27 | TABLE ACCESS BY INDEX ROWID| T5103F_FACT_VALUE | 8 | 336 | 83 (0)| 00:00:01 |
    |* 28 | INDEX SKIP SCAN | T5103F_FACT_VALUE_PK | 3 | | 82 (0)| 00:00:01 |
    | 29 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
    | 30 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
    | 31 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
    |* 32 | TABLE ACCESS BY INDEX ROWID | T5103D_KEYFIGURE | 1 | 20 | 2 (0)| 00:00:01 |
    |* 33 | INDEX RANGE SCAN | X5103_KEYFIGURE_PK | 1 | | 1 (0)| 00:00:01 |
    | 34 | TABLE ACCESS BY INDEX ROWID | T5103D_TEXT | 1 | 32 | 1 (0)| 00:00:01 |
    |* 35 | INDEX UNIQUE SCAN | X5103_TEXT_PK | 1 | | 0 (0)| 00:00:01 |
    |* 36 | TABLE ACCESS BY INDEX ROWID | T5103D_VERSION | 1 | 19 | 1 (0)| 00:00:01 |
    |* 37 | INDEX UNIQUE SCAN | X5103_VERSION_PK | 1 | | 0 (0)| 00:00:01 |
    | 38 | VIEW PUSHED PREDICATE | | 1 | 2054 | 2 (0)| 00:00:01 |
    | 39 | SORT GROUP BY | | 1 | 34 | 2 (0)| 00:00:01 |
    |* 40 | FILTER | | | | | |
    | 41 | INLIST ITERATOR | | | | | |
    | 42 | TABLE ACCESS BY INDEX ROWID | T5103F_FACT_COMMENT | 1 | 34 | 2 (0)| 00:00:01 |
    |* 43 | INDEX UNIQUE SCAN | X5103_FACTCOMMENT_PK | 1 | | 1 (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    12 - access("T"."KEY_FIGURE_SUB"="F"."KEY_FIGURE")
    13 - access("T"."VERSION_ID"="VERSION_ID")
    14 - filter("FISCALYEAR_V5"("VERSION_VALFROM")||CASE "FISCALPERI_V5"("VERSION_VALFROM") WHEN '012' THEN '999' ELSE
    "FISCALPERI_V5"("VERSION_VALFROM") END <='2013006' AND "FISCALYEAR_V5"("VERSION_VALTO")||CASE
    "FISCALPERI_V5"("VERSION_VALTO") WHEN '012' THEN '999' ELSE "FISCALPERI_V5"("VERSION_VALTO") END >='2013006')
    15 - filter("T"."KEY_FIG_VIEW"=1 AND "T"."KEY_FIGURE"<>2022 AND "T"."KEY_FIGURE"<>2040 AND "T"."KEY_FIGURE"<>2063)
    23 - filter(SUBSTR("APPLICAT_FLAG",7,1)='1' AND SUBSTR("COMP_CODE",1,2)<>'99' AND "BUSIDIV"<>'HA' AND
    "SUBGROUP_NAME"='Group' AND SUBSTR("FISCALYEAR_V5"("COMP_VALFROM"),1,4)||CASE WHEN
    (SUBSTR("FISCALPERI_V5"("COMP_VALFROM"),1,3)='001') THEN '000' ELSE SUBSTR("FISCALPERI_V5"("COMP_VALFROM"),1,3) END
    <='2013006' AND SUBSTR("FISCALYEAR_V5"("COMP_VALTO"),1,4)||CASE WHEN
    (SUBSTR("FISCALPERI_V5"("COMP_VALTO"),1,3)='012') THEN '999' ELSE SUBSTR("FISCALPERI_V5"("COMP_VALTO"),1,3) END
    >='2013006')
    24 - access("APPLICATION"='5103 Key Figures' AND "T"."USER_ID"='GAJETH1')
    filter(("COMP_CODE"="T"."COMP_CODE" OR ("T"."COMP_CODE"='all' OR "T"."COMP_CODE"='sum')) AND
    ("BUSIDIV"="T"."BUSIDIV" OR "T"."BUSIDIV"='al'))
    28 - access("COMP_CODE"="C"."COMP_CODE" AND "BUSIDIV"="C"."BUSIDIV")
    filter("COMP_CODE"="C"."COMP_CODE" AND "BUSIDIV"="C"."BUSIDIV" AND ("FISC_YEAR"||"PERIOD"='2013005' OR
    "FISC_YEAR"||"PERIOD"='2013006' OR "FISC_YEAR"||"PERIOD"='2013012') AND (("FACTTYPE_ID"<>1 OR "FACTTYPE_ID"=1 AND
    "PERIOD"<>'012') AND "VERSION_ID"=5 OR "PERIOD"='012' AND "FACTTYPE_ID"=1 AND "VERSION_ID"=5))
    32 - filter("T"."KEY_FIG_AREA"=0 OR "T"."KEY_FIG_AREA"=1)
    33 - access("T"."KEY_FIGURE"="F"."KEY_FIGURE" AND "T"."KEY_FIG_VIEW"=3)
    filter("T"."KEY_FIGURE"<>2022 AND "T"."KEY_FIGURE"<>2040 AND "T"."KEY_FIGURE"<>2063)
    35 - access("T"."TEXT_ID"="T"."KEY_FIG_TEXT_ID" AND "T"."LANG_CODE"='EN')
    36 - filter("FISCALYEAR_V5"("VERSION_VALFROM")||CASE "FISCALPERI_V5"("VERSION_VALFROM") WHEN '012' THEN '999' ELSE
    "FISCALPERI_V5"("VERSION_VALFROM") END <='2013006' AND "FISCALYEAR_V5"("VERSION_VALTO")||CASE
    "FISCALPERI_V5"("VERSION_VALTO") WHEN '012' THEN '999' ELSE "FISCALPERI_V5"("VERSION_VALTO") END >='2013006')
    37 - access("T"."VERSION_ID"="VERSION_ID")
    40 - filter("F"."KEY_FIGURE"<>2063 AND "F"."KEY_FIGURE"<>2040 AND "F"."KEY_FIGURE"<>2022)
    43 - access("KEY_FIGURE"="F"."KEY_FIGURE" AND "FISC_YEAR"='2013' AND "PERIOD"='006' AND ("COMP_CODE"='all' OR
    "COMP_CODE"='all_ind') AND "BUSIDIV"='al' AND "YTD_FLAG"='C')
    filter("KEY_FIGURE"<>2022 AND "KEY_FIGURE"<>2040 AND "KEY_FIGURE"<>2063)
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    why you don't show sql-query text? it is secret? ))

    p.s.
    plan points require attention: 12 - 15 , 27 -28, 29 -31 and etc...
     
  13. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Download link for sql query text is on previous page. But I post it again as text.


    Code (SQL):


    SELECT  k.key_figure, k.text_bez,
        f.bud, f.act, CASE k.key_figure WHEN 2054 THEN NULL ELSE f.fc END fc,
        f.act-f.bud dev_bud, CASE f.bud WHEN 0 THEN 0 ELSE CASE WHEN f.bud<0 THEN 1-f.act/f.bud ELSE f.act/f.bud-1 END END dev_bud_pc,
        CASE k.key_figure WHEN 2054 THEN NULL ELSE f.act-f.fc END dev_fc,
        CASE k.key_figure WHEN 2054 THEN NULL ELSE CASE f.fc WHEN 0 THEN 0 ELSE CASE WHEN f.fc<0 THEN 1-f.act/f.fc ELSE f.act/f.fc-1 END END END dev_fc_pc,
        f.bud12, f.fc12,
        f.fc12-f.bud12 dev12, CASE f.bud12 WHEN 0 THEN 0 ELSE CASE WHEN f.bud12<0 THEN 1-f.fc12/f.bud12 ELSE f.fc12/f.bud12-1 END END dev12_pc,
        t.comm, t.comm12
    FROM (
        SELECT  kk.key_figure, tt.text_bez, kk.sortnr
        FROM    report.v5103d_text tt INNER JOIN
            report.v5103d_keyfigure kk INNER JOIN
            report.v5103d_version vv
        ON  kk.version_id=vv.version_id AND
            '2013006' BETWEEN vv.fyperi_valfrom AND vv.fyperi_valto
        ON  tt.text_id=kk.key_fig_text_id AND
            tt.lang_code='EN'
        WHERE   kk.key_fig_view=3 AND kk.key_fig_area IN (0,1) AND
            kk.key_figure NOT IN (2022,2040,2063)
    ) k INNER JOIN (
        SELECT  f.key_figure,
            SUM(CASE WHEN f.fy_per = '2013006' AND f.facttype_id = 1 THEN f.fact_value ELSE 0 END) bud,
            SUM(CASE WHEN f.fy_per = '2013006' AND f.facttype_id = 2 THEN CASE WHEN f.key_figure=2041 AND substr(f.fy_per,5,3)='000' THEN 0 ELSE f.fact_value END ELSE 0 END) act,
            SUM(CASE WHEN f.fy_per = '2013005' AND f.facttype_id = 3 THEN f.fact_value ELSE 0 END) fc, SUM(CASE WHEN f.fy_per = '2013012' AND f.facttype_id = 1 THEN f.fact_value ELSE 0 END) bud12,
                  SUM(CASE WHEN f.fy_per < '2013011' THEN CASE WHEN f.fy_per = '2013006' AND f.facttype_id = 4 THEN f.fact_value ELSE 0 END ELSE CASE WHEN f.fy_per = '2013011' AND f.facttype_id = 4 THEN f.fact_value ELSE 0 END END) fc12
        FROM (
            SELECT  key_figure, facttype_id, fy_per,
                SUM(CASE typ
                    WHEN 'sum' THEN f1
                    WHEN 'frac' THEN CASE f2 WHEN 0 THEN 0 ELSE f1/f2 END
                    WHEN 'mult' THEN f1*f2
                END) fact_value
            FROM (
                SELECT  m.key_figure, f.facttype_id, f.fy_per,
                    CASE
                        WHEN m.calc_type = '-' THEN 'sum'
                        WHEN m.calc_type IN ('a','b') THEN 'frac'
                        WHEN m.calc_type IN ('c','d') THEN 'mult'
                    END typ,
                    SUM(CASE WHEN m.calc_type IN ('-','a','c')
                        THEN f.fact_value*m.sum_factor ELSE NULL END) f1,
                    SUM(CASE WHEN m.calc_type IN ('b','d')
                        THEN f.fact_value*m.sum_factor ELSE NULL END) f2
                FROM (
                    SELECT  mm.key_figure, mm.key_figure_sub, mm.sum_factor, mm.calc_type
                    FROM    report.v5103d_keyfigure_map mm INNER JOIN
                        report.v5103d_version vv
                    ON  mm.version_id = vv.version_id AND
                        '2013006' BETWEEN vv.fyperi_valfrom AND vv.fyperi_valto
                    WHERE   mm.key_fig_view = 1
                ) m INNER JOIN (
                    SELECT  f.key_figure, f.facttype_id, f.fisc_year||f.period fy_per, SUM(f.fact_value) fact_value
                    FROM (
                            SELECT  DISTINCT c.comp_code, c.busidiv, c.region_desc
                            FROM (
                                SELECT  comp_code, busidiv, region_desc
                                FROM    report.v0000d_company_inform c
                                WHERE   '2013006' BETWEEN c.fyperi_valfrom AND c.fyperi_valto AND
                                    substr(c.applicat_flag,7,1)='1' AND subgroup_name='Group'
                            ) c
                            INNER JOIN dwmeta.v5103d_kf_user_auth a
                            ON  (c.comp_code=a.comp_code OR a.comp_code IN('all','sum')) AND
                                (c.busidiv=a.busidiv OR a.busidiv='al')
                            WHERE 1=1 AND c.busidiv!='HA' AND substr(c.comp_code,1,2)!='99'
                        ) c INNER JOIN (
                            SELECT  key_figure, comp_code, busidiv, fisc_year, period, facttype_id,
                                round(SUM(fact_value_eur),15) fact_value
                            FROM    report.v5103f_fact_value
                            WHERE   ((version_id=5 AND ((facttype_id!=1) OR (facttype_id=1 AND period!='012'))) OR (version_id=5 AND period='012' AND facttype_id=1)) AND
                                fisc_year||period IN ('2013005','2013006','2013006','2013012')
                            GROUP BY key_figure, fisc_year, period, facttype_id, comp_code, busidiv
                        ) f
                    ON  c.comp_code = f.comp_code AND c.busidiv = f.busidiv
                    GROUP BY f.key_figure, f.facttype_id, f.fisc_year, f.period
                    UNION ALL
                    SELECT  2025, 2, '2013005', 0 FROM dual
                    UNION ALL
                    SELECT  2090, 2, '2013005', 0 FROM dual
                    UNION ALL
                    SELECT  2041, 2, '2013005', 0 FROM dual
                ) f
                ON  m.key_figure_sub = f.key_figure
                GROUP BY m.key_figure, f.facttype_id, f.fy_per,
                    CASE
                        WHEN m.calc_type = '-' THEN 'sum'
                        WHEN m.calc_type IN ('a','b') THEN 'frac'
                        WHEN m.calc_type IN ('c','d') THEN 'mult'
                    END
            )
            GROUP BY key_figure, facttype_id, fy_per
        ) f
        GROUP BY f.key_figure
    ) f
    LEFT JOIN (
        SELECT  key_figure,
            MAX(CASE WHEN substr(comp_code,-4,4)!='_ind' OR substr(comp_code,-4,4) IS NULL THEN comment1 END) comm,
            MAX(CASE WHEN substr(comp_code,-4,4)!='_ind' OR substr(comp_code,-4,4) IS NULL THEN comment2 END) comm12
        FROM    stman.t5103f_fact_comment
        WHERE   comp_code IN ('all','all_ind') AND busidiv='al' AND
            fisc_year='2013' AND period='006' AND ytd_flag = 'C'
        GROUP BY key_figure
    ) t
    ON  f.key_figure=t.key_figure
    ON  k.key_figure=f.key_figure
    ORDER BY k.sortnr;

     
     
  14. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    The problematic table is report.v5103f_fact_value. To this table was add the field that is a part of primary key which contains 8 fields and then the performance goes down. The new field is not used in this query but nevertheless its adding causes the performance problem.
     
  15. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    what order of a new field in the T5103F_FACT_VALUE_PK index?

    ...you can show ddl of the table report.v5103f_fact_value and its indexes?
     
  16. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Code (SQL):
    CREATE TABLE "T5103F_FACT_VALUE"
       (    "KEY_FIGURE" NUMBER(4,0) NOT NULL ENABLE,
        "FISC_YEAR" VARCHAR2(4 BYTE) NOT NULL ENABLE,
        "PERIOD" VARCHAR2(3 BYTE) NOT NULL ENABLE,
        "COMP_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE,
        "BUSIDIV" VARCHAR2(2 BYTE) NOT NULL ENABLE,
        "VERSION_ID" NUMBER(1,0) NOT NULL ENABLE,
        "FACTTYPE_ID" NUMBER(2,0) NOT NULL ENABLE,
        "FACT_VALUE" FLOAT(126) NOT NULL ENABLE,
        "FACT_VALUE_EUR" FLOAT(126),
        "FACT_VALUE_SEL" FLOAT(126),
        "FACT_VALUE_SEL_EUR" FLOAT(126),
        "FACT_VALUE_SHR" FLOAT(126),
        "FACT_VALUE_SHR_EUR" FLOAT(126),
        "FACT_VALUE_SEL_SHR" FLOAT(126),
        "FACT_VALUE_SEL_SHR_EUR" FLOAT(126),
        "QUELLE" VARCHAR2(8 BYTE) NOT NULL ENABLE
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" ;
     

    There is only one index which is unique. Order fields in index:


    KEY_FIGURE
    FISC_YEAR
    PERIOD
    COMP_CODE
    BUSIDIV
    VERSION_ID
    FACTTYPE_ID
    QUELLE - this is new column that causes problem
     
  17. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    This is another example of :

    select <stuff>
    from (select <stuff>
    from (select <stuff>
    from (select <stuff>
    from <here>
    where <stuff>)
    where <stuff>)
    where <stuff>)
    where <stuff>;

    ...this was in a previous post where the only difference was :

    Select * from select * from select * from...

    I would be REALLY interested in looking at a DB model that is forcing you into writing such a select statement. I tried dumping the query into SQL Developer just to try wrapping my head around it....and I get lost around the 15th CASE statement...ugh!

    The only reason I can think of to do something like this is you wanted a dynamic query that did everything rather than multiple queries to do specific things. So you cut down on code that must be maintained yet now have a monster query that can't be maintained.

    Is this typical of the industry now? Have I missed something completely?

    I know this does nothing to answer the question...I'm just really puzzled as to how anybody could come up with something like this query.


    CJ
     
  18. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    first of all it is necessary to optimize logic of request, logic of conditions of a choice, logic of measure calculations....

    p.s.
    ...step 27 and 28 plan...
    index_skip_scan is used. it is the worst method of index access (in the majority of cases).

    it occurs because access on fields of a key of COMP_CODE and BUSIDIV and according to their order - index_skip_scan.
    Other fields of a key don't participate because participate in expressions: ("FISC_YEAR" || "PERIOD" = '2013005' OR
    "FISC_YEAR" || "PERIOD" = '2013006' OR... )
    ... it was possible to use conditions of such type: (fisc_year, period) in ( ('2013','005'),('2013','006') , ... )

    ... in this request there are many conditions which shall be optimized...

    as possible option: it is necessary to recreate an index according to selectivity of fields and to arrange them in a necessary order.
     
  19. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thank you. So I trying now to find the slowest parts of code. This is the one of them:


    Code (SQL):

        SELECT  f.key_figure, f.facttype_id, f.fisc_year||f.period fy_per--, sum(f.fact_value) fact_value
                    FROM (
                            SELECT  DISTINCT c.comp_code, c.busidiv, c.region_desc
                            FROM (
                                SELECT  comp_code, busidiv, region_desc
                                FROM    report.v0000d_company_inform c
                                WHERE   '2013006' BETWEEN c.fyperi_valfrom AND c.fyperi_valto AND
                                    substr(c.applicat_flag,7,1)='1' AND subgroup_name='Group'
                            ) c
                            INNER JOIN dwmeta.v5103d_kf_user_auth a
                            ON  (c.comp_code=a.comp_code OR a.comp_code IN('all','sum')) AND
                                (c.busidiv=a.busidiv OR a.busidiv='al')
                            WHERE 1=1 AND c.busidiv!='HA' AND substr(c.comp_code,1,2)!='99'
                        ) c INNER JOIN (
                            SELECT key_figure, comp_code, busidiv, fisc_year, period, facttype_id, --fact_value_eur--,
                                round(SUM(fact_value_eur),15) fact_value
                            FROM    report.v5103f_fact_value fv
                WHERE fisc_year||period IN ('2013005','2013006','2013006','2013012') AND ((version_id=5 AND ((facttype_id!=1) OR (facttype_id=1 AND (period!='012' OR (period='012' AND version_id=5))) )))
                            GROUP BY busidiv, comp_code, key_figure, fisc_year, period, facttype_id
                        ) f
                    ON  c.comp_code = f.comp_code AND c.busidiv = f.busidiv
                    GROUP BY f.fisc_year, f.period, f.key_figure, f.facttype_id ;
     
    Slowdown is caused the LAST GROUP BY clause. I added new index to table report.v5103f_fact_value with columns FISC_YEAR, KEY_FIGURE, FACTTYPE_ID and PERIOD. But Oracle doesnt use that index and use the index of primary key (BUSIDIV, COMP_CODE, KEY_FIGURE, FISC_YEAR, PERIOD, VERSION_ID, FACTTYPE_ID, QUELLE).


    If I trying the simplier select, Oracle use that index.

    Code (SQL):
     SELECT f.key_figure, f.facttype_id, f.fisc_year||f.period fy_per
    FROM report.v5103f_fact_value f
    GROUP BY f.fisc_year, f.period, f.key_figure, f.facttype_id;
    Can you please help me where is the problem? Is any posibility how can I modify the query, or can I use some hint to force using that index (the hint /*+ INDEX(fv REPORT.T5103F_FACT_VALUE_INDEX1) */ does not work )?

    Thank you
     
  20. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation