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
    Ok, thank you, but hint for index does not work yet. Object report.v5103f_fact_value is view, so I rewrite the hint as follows: /* + INDEX(fv.r T5103F_FACT_VALUE_INDEX8) */.

    The "fv" is alias for view and "r" is alias for table in view definition. But it still does not work. I didn't find any reason, why the optimizer ignore this hint.

    I partially solve the problem with group by described above (I simplified query and change the index definition). But I have problem with field from another table, which causes that the optimizer don't use the index.

    Example:

    Code (SQL):


    ALTER SYSTEM FLUSH BUFFER_CACHE;
    ALTER SYSTEM FLUSH SHARED_POOL;  

    SET autotrace ON;
     
    SELECT   /* + INDEX(fv.r T5103F_FACT_VALUE_INDEX8) */ fv.fisc_year, fv.period, fv.facttype_id, fv.key_figure
    FROM report.v5103f_fact_value fv
        INNER JOIN report.v0000d_company_inform c
          ON c.comp_code = fv.comp_code AND c.busidiv = fv.busidiv
        INNER JOIN dwmeta.v5103d_kf_user_auth a
          ON    (c.comp_code=a.comp_code OR a.comp_code IN('all','sum'))
            --      and a.user_id = 'AAA'
    WHERE  
      (fisc_year,period) IN (('2013','005'),('2013','00'),('2013','012')) AND ((version_id=5 AND ((facttype_id!=1) OR (facttype_id=1 AND (period!='012' OR (period='012' AND version_id=5))) )))
       --and a.user_id='AAA'
    GROUP BY fv.fisc_year, fv.period, fv.facttype_id, fv.key_figure ;
     
    SET autotrace OFF;

     
    Index T5103F_FACT_VALUE_INDEX8 have these columns (in this order): (FISC_YEAR, PERIOD, FACTTYPE_ID, KEY_FIGURE, VERSION_ID, COMP_CODE, BUSIDIV)

    If I uncomment condition "and a.user_id = 'AAA'", optimizer does't use index T5103F_FACT_VALUE_INDEX8 but use index for primary key (BUSIDIV, COMP_CODE, KEY_FIGURE, FISC_YEAR, PERIOD, VERSION_ID, FACTTYPE_ID, QUELLE). It does not matter if is in FROM clause, or in WHERE clause.

    I need to find the reason, why the condition on table dwmeta.v5103d_kf_user_auth affects to using index on table T5103F_FACT_VALUE. I really don't understand it :confused:

    Thank you for some help.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    show the problem plan of query....

    you can show the text views : v0000d_company_inform and dwmeta.v5103d_kf_user_auth ....

    Links .....

    based :
    http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_transform.htm#TGSQL94896

    additional :
    https://blogs.oracle.com/optimizer/entry/optimizer_transformations_table_expansion

    https://blogs.oracle.com/optimizer/entry/or_expansion_transformation

    https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1

    https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle

    https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_2


    p.s.
    it really isn't good that there would be two indexes differing only in the one field.....

    can make the following updating :

    1) to create unique index on composition as primary key, but with a necessary order of fields
    2) to use the created unique index for primary key instead of the existing
    alter table modify constraint using index...

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#SQLRF01001
     
  3. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Ok, thank you. I changed the order fields in index for primary key a now it looks faster. But I have other problem. Yesterday I insert more data (1 milion) to biggest table REPORT.T5103F_FACT_VALUE and everything was OK. Execution time was little highter, but not so much. I am not sure, but I think, that the costs in execution plan were same. But today are very high (641 units, yesterday 137 units). Execution time is 3 times higher.

    Have you got any ideas about reason?

    I tried rebuild index, recompute its statistics and validate structure of index. But with no effect.

    Thank you
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    the reason can be not single...


    at first is necessary...

    1) show plan of problem sql-query...
    show the report ASH and AWR if you can
    it is desirable to study ash-report and awr-report...

    2) whether you use histograms?
    if you use histograms, then show the of sql-script for gathering of statistics of the table REPORT.T5103F_FACT_VALUE
     
  5. mabyna

    mabyna Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Here is an AWR report:

    WORKLOAD REPOSITORY report for


    Snap Id Snap Time Sessions Curs/Sess
    --------- ------------------- -------- ---------
    Begin Snap: 10557 12-Dec-13 07:00:05 73 .5
    End Snap: 10558 12-Dec-13 08:00:18 85 .9
    Elapsed: 60.22 (mins)
    DB Time: 3.63 (mins)

    Cache Sizes Begin End
    ~~~~~~~~~~~ ---------- ----------
    Buffer Cache: 2,384M 2,384M Std Block Size: 8K
    Shared Pool Size: 1,536M 1,536M Log Buffer: 7,676K

    Load Profile Per Second Per Transaction Per Exec Per Call
    ~~~~~~~~~~~~ --------------- --------------- ---------- ----------
    DB Time(s): 0.1 9.9 0.01 0.04
    DB CPU(s): 0.0 1.1 0.00 0.00
    Redo size: 575.6 94,541.8
    Logical reads: 161.3 26,484.5
    Block changes: 3.9 647.1
    Physical reads: 4.2 695.1
    Physical writes: 0.2 36.5
    User calls: 1.4 224.7
    Parses: 1.1 175.1
    Hard parses: 0.3 42.6
    W/A MB processed: 0.1 23.5
    Logons: 0.1 13.0
    Executes: 4.5 734.6
    Rollbacks: 0.0 0.3
    Transactions: 0.0

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait %: 100.00
    Buffer Hit %: 97.38 In-memory Sort %: 100.00
    Library Hit %: 91.24 Soft Parse %: 75.66
    Execute to Parse %: 76.16 Latch Hit %: 99.99
    Parse CPU to Parse Elapsd %: 38.73 % Non-Parse CPU: 82.06

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 45.46 19.26
    % SQL with executions>1: 96.99 61.90
    % Memory for SQL w/exec>1: 96.93 56.15
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    show plan of sql-query...
    show the ASH-report of a session which executed problem sql-query