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!

A sql tuning.

Discussion in 'SQL PL/SQL' started by 13478, Dec 19, 2017.

  1. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    I have an original sql which run 100 hours, to improve the performance, I created a function based index, and oracle optimizer started to use that. Besides this, I wonder if there is another way to improve the performance , for example, re-write this sql...any recommendations are welcome.
    Thank you very much.

    --the original sql

    create table TEMP_ABC_IQ as (select t1.strt_doc_id, t1.uidy, t1.crea_amnd_num, t1.strt_amnd_num, t2.sys_dt_tm, t2.amnd_num from MF_FOOT_NVBC t1 INNER JOIN

    MF_EQ_BMD T2 ON t2.UIDY LIKE '&1495' || substr(t1.STRT_DOC_ID, 6) || '%' AND NVL(t2.LAST_AMND_NUM, 'NA2145NA') = NVL(t1.CREA_AMND_NUM, 'NA2145NA')
    where t1.strt_doc_id like '&1436%'
    AND exists (select (1) from MF_FOOT_NVBC t2 where t1.strt_doc_id = t2.strt_doc_id AND CREA_AMND_NUM IS NULL)
    and (crea_amnd_num is null or not exists (select (1) from MF_FOOT_NVBC t3
    where t1.strt_doc_id = t3.strt_doc_id and t3.uidy = '&2950&'||T1.STRT_DOC_ID||'&'|| (select t4.amnd_num from MF_EQ_BMD t4
    where substr(T4.UIDY,6,instr(T4.UIDY,'&', 1,5)-instr(T4.UIDY,'&',1,2)+1) = SUBSTR(T1.STRT_DOC_ID,6) and t4.sys_dt_tm = (SELECT max(sys_dt_tm)
    FROM MF_EQ_BMD t5 WHERE t4.dtyp_id =t5.dtyp_id and t4.DOC_NUM = t5.DOC_NUM group by substr(t5.uidy,1, instr(t5.uidy,'&',1,5)))))));


    --original sql's explain plan

    ------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
    ------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 20M| 3098M| | 3948 (36)|
    |* 1 | FILTER | | | | | |
    |* 2 | HASH JOIN | | 27M| 4190M| 2112K| 3948 (36)|
    | 3 | TABLE ACCESS FULL | MF_EQ_BMD | 35998 | 1687K| | 1009 (1)|
    |* 4 | HASH JOIN RIGHT SEMI | | 21683 | 2371K| | 1325 (1)|
    |* 5 | TABLE ACCESS FULL | MF_FOOT_NVBC | 21875 | 747K| | 663 (1)|
    |* 6 | TABLE ACCESS FULL | MF_FOOT_NVBC | 30151 | 2267K| | 663 (1)|
    |* 7 | TABLE ACCESS BY INDEX ROWID| MF_FOOT_NVBC | 1 | 73 | | 3 (0)|
    |* 8 | INDEX UNIQUE SCAN | PK_WKLD_ASGT | 1 | | | 2 (0)|
    |* 9 | FILTER | | | | | |
    |* 10 | TABLE ACCESS FULL | MF_EQ_BMD | 360 | 27000 | | 1009 (1)|
    | 11 | HASH GROUP BY | | 1 | 72 | | 1010 (1)|
    |* 12 | TABLE ACCESS FULL | MF_EQ_BMD | 1 | 72 | | 1009 (1)|
    ------------------------------------------------------------------------------------------

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

    1 - filter("T1"."CREA_AMND_NUM" IS NULL OR NOT EXISTS (SELECT 0 FROM
    "MF_FOOT_NVBC" "T3" WHERE "T3"."UIDY"='&2950&'||:B1||'&'|| (SELECT
    "T4"."AMND_NUM" FROM "MF_EQ_BMD" "T4" WHERE "T4"."SYS_DT_TM"= (SELECT
    MAX("SYS_DT_TM") FROM "MF_EQ_BMD" "T5" WHERE "T5"."DOC_NUM"=:B2 AND
    "T5"."DTYP_ID"=:B3 GROUP BY SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))) AND
    SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&',1,2)+1)=SUBS
    TR:)B4,6)) AND "T3"."STRT_DOC_ID"=:B5))
    2 - access(NVL("T2"."LAST_AMND_NUM",'NA2145NA')=NVL("T1"."CREA_AMND_NUM",'NA214
    5NA'))
    filter("T2"."UIDY" LIKE '&1495'||SUBSTR("T1"."STRT_DOC_ID",6)||'%')
    4 - access("T1"."STRT_DOC_ID"="T2"."STRT_DOC_ID")
    5 - filter("CREA_AMND_NUM" IS NULL AND "T2"."STRT_DOC_ID" LIKE '&1436%')
    6 - filter("T1"."STRT_DOC_ID" LIKE '&1436%')
    7 - filter("T3"."STRT_DOC_ID"=:B1)
    8 - access("T3"."UIDY"='&2950&'||:B1||'&'|| (SELECT "T4"."AMND_NUM" FROM
    "MF_EQ_BMD" "T4" WHERE "T4"."SYS_DT_TM"= (SELECT MAX("SYS_DT_TM") FROM
    "MF_EQ_BMD" "T5" WHERE "T5"."DOC_NUM"=:B2 AND "T5"."DTYP_ID"=:B3 GROUP BY
    SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))) AND
    SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&',1,2)+1)=SUBS
    TR:)B4,6)))
    9 - filter("T4"."SYS_DT_TM"= (SELECT MAX("SYS_DT_TM") FROM "MF_EQ_BMD" "T5"
    WHERE "T5"."DOC_NUM"=:B1 AND "T5"."DTYP_ID"=:B2 GROUP BY
    SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))))
    10 - filter(SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&
    ',1,2)+1)=SUBSTR:)B1,6))
    12 - filter("T5"."DOC_NUM"=:B1 AND "T5"."DTYP_ID"=:B2)




    --change to


    create index tem_index_1 on MF_EQ_BMD("DOC_NUM","DTYP_ID",NVL("LAST_AMND_NUM",'NA2145NA')) parallel 8;

    create table TEMP_ABC_IQ nologging parallel (degree 8) as (select t1.strt_doc_id, t1.uidy, t1.crea_amnd_num, t1.strt_amnd_num, t2.sys_dt_tm, t2.amnd_num from

    MF_FOOT_NVBC t1 INNER JOIN MF_EQ_BMD T2 ON t2.UIDY LIKE '&1495' || substr(t1.STRT_DOC_ID, 6) || '%' AND NVL(t2.LAST_AMND_NUM, 'NA2145NA') = NVL

    (t1.CREA_AMND_NUM, 'NA2145NA') where t1.strt_doc_id like '&1436%' AND exists (select (1) from MF_FOOT_NVBC t2 where t1.strt_doc_id = t2.strt_doc_id AND

    CREA_AMND_NUM IS NULL) and (crea_amnd_num is null or not exists (select (1) from MF_FOOT_NVBC t3 where t1.strt_doc_id = t3.strt_doc_id and t3.uidy =

    '&2950&'||T1.STRT_DOC_ID||'&'|| (select t4.amnd_num from MF_EQ_BMD t4 where substr(T4.UIDY,6,instr(T4.UIDY,'&', 1,5)-instr(T4.UIDY,'&',1,2)+1) =
    SUBSTR(T1.STRT_DOC_ID,6) and t4.sys_dt_tm = (SELECT max(sys_dt_tm)
    FROM MF_EQ_BMD t5 WHERE t4.dtyp_id =t5.dtyp_id and t4.DOC_NUM = t5.DOC_NUM group by substr(t5.uidy,1, instr(t5.uidy,'&',1,5)))))));

    Drop index tem_index_1;

    --the new explain plan (after creating index)

    ------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
    ------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 25545 | 4440K| | 2597 (1)|
    |* 1 | FILTER | | | | | |
    |* 2 | HASH JOIN RIGHT SEMI | | 34557 | 6006K| | 2597 (1)|
    |* 3 | TABLE ACCESS FULL | MF_FOOT_NVBC | 21875 | 747K| | 663 (1)|
    |* 4 | HASH JOIN | | 48053 | 6710K| 2624K| 1935 (1)|
    | 5 | NESTED LOOPS | | 48053 | 6710K| 2624K| 1935 (1)|
    | 6 | NESTED LOOPS | | | | | |
    | 7 | STATISTICS COLLECTOR | | | | | |
    |* 8 | TABLE ACCESS FULL | MF_FOOT_NVBC | 30151 | 2267K| | 663 (1)|
    |* 9 | INDEX RANGE SCAN | PK_IQ_AMD | | | | |
    |* 10 | TABLE ACCESS BY INDEX ROWID | MF_EQ_BMD | 2 | 132 | | 1009 (1)|
    | 11 | TABLE ACCESS FULL | MF_EQ_BMD | 35998 | 2320K| | 1009 (1)|
    |* 12 | TABLE ACCESS BY INDEX ROWID | MF_FOOT_NVBC | 1 | 73 | | 3 (0)|
    |* 13 | INDEX UNIQUE SCAN | PK_WKLD_ASGT | 1 | | | 2 (0)|
    |* 14 | FILTER | | | | | |
    |* 15 | TABLE ACCESS FULL | MF_EQ_BMD | 360 | 27000 | | 1009 (1)|
    | 16 | HASH GROUP BY | | 1 | 72 | | 4 (25)|
    | 17 | TABLE ACCESS BY INDEX ROWID BATCHED| MF_EQ_BMD | 1 | 72 | | 3 (0)|
    |* 18 | INDEX RANGE SCAN | TEM_INDEX_1 | 1 | | | 2 (0)|
    ------------------------------------------------------------------------------------------------------

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

    1 - filter("T1"."CREA_AMND_NUM" IS NULL OR NOT EXISTS (SELECT 0 FROM "MF_FOOT_NVBC" "T3"
    WHERE "T3"."UIDY"='&2950&'||:B1||'&'|| (SELECT "T4"."AMND_NUM" FROM "MF_EQ_BMD" "T4" WHERE
    "T4"."SYS_DT_TM"= (SELECT MAX("SYS_DT_TM") FROM "MF_EQ_BMD" "T5" WHERE "T5"."DTYP_ID"=:B2 AND
    "T5"."DOC_NUM"=:B3 GROUP BY SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))) AND
    SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&',1,2)+1)=SUBSTR:)B4,6))
    AND "T3"."STRT_DOC_ID"=:B5))
    2 - access("T1"."STRT_DOC_ID"="T2"."STRT_DOC_ID")
    3 - filter("CREA_AMND_NUM" IS NULL AND "T2"."STRT_DOC_ID" LIKE '&1436%')
    4 - access(NVL("LAST_AMND_NUM",'NA2145NA')=NVL("T1"."CREA_AMND_NUM",'NA2145NA'))
    filter("T2"."UIDY" LIKE '&1495'||SUBSTR("T1"."STRT_DOC_ID",6)||'%')
    8 - filter("T1"."STRT_DOC_ID" LIKE '&1436%')
    9 - access("T2"."UIDY" LIKE '&1495'||SUBSTR("T1"."STRT_DOC_ID",6)||'%')
    filter("T2"."UIDY" LIKE '&1495'||SUBSTR("T1"."STRT_DOC_ID",6)||'%')
    10 - filter(NVL("LAST_AMND_NUM",'NA2145NA')=NVL("T1"."CREA_AMND_NUM",'NA2145NA'))
    12 - filter("T3"."STRT_DOC_ID"=:B1)
    13 - access("T3"."UIDY"='&2950&'||:B1||'&'|| (SELECT "T4"."AMND_NUM" FROM "MF_EQ_BMD" "T4"
    WHERE "T4"."SYS_DT_TM"= (SELECT MAX("SYS_DT_TM") FROM "MF_EQ_BMD" "T5" WHERE
    "T5"."DTYP_ID"=:B2 AND "T5"."DOC_NUM"=:B3 GROUP BY
    SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))) AND
    SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&',1,2)+1)=SUBSTR:)B4,6)))
    14 - filter("T4"."SYS_DT_TM"= (SELECT MAX("SYS_DT_TM") FROM "MF_EQ_BMD" "T5" WHERE
    "T5"."DTYP_ID"=:B1 AND "T5"."DOC_NUM"=:B2 GROUP BY
    SUBSTR("T5"."UIDY",1,INSTR("T5"."UIDY",'&',1,5))))
    15 - filter(SUBSTR("T4"."UIDY",6,INSTR("T4"."UIDY",'&',1,5)-INSTR("T4"."UIDY",'&',1,2)+1)=SU
    BSTR:)B1,6))
    18 - access("T5"."DOC_NUM"=:B1 AND "T5"."DTYP_ID"=:B2)


    ###
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    For a start ...
    0) pls, provide here ddl-describes your tables from query

    1) may be to optimize of the logic your query

    2) disable of the Adaprive Plans in your slq select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */....
    3)try again run your query and provide here ,please formated , actual plan of the query with runtime-statistics ( you can used of the hint monitor for real-time monitoring : DBMS_SQLTUNE.report_sql_detail).

    Additional notes:
    1) for all tables used in search queries(in EXISTS clause ) believe that you need to create indexes.

    2)
    STRT_DOC_ID - what is that data type on this field
    3) perhaps,a few tables needs to partitioned..
     
    Last edited: Dec 19, 2017
  3. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
     
  4. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Krasnoslobodtsev_si,
    thank you very much for your response.

    now below sql is running 8 hours. it is better. after implemented some recommendations from sql tuning advisiro



    create table TEMP_ABC_IQ nologging parallel (degree 8) as select t1.strt_doc_id, t1.uidy, t1.crea_amnd_num, t1.strt_amnd_num, t2.sys_dt_tm, t2.amnd_num from
    MF_FOOT_NVBC t1 INNER JOIN MF_EQ_BMD T2 ON t2.UIDY LIKE '&1495' || substr(t1.STRT_DOC_ID, 6) || '%' AND NVL(t2.LAST_AMND_NUM, 'NA2145NA') = NVL
    (t1.CREA_AMND_NUM, 'NA2145NA')
    where t1.strt_doc_id like '&1436%'
    AND exists (select (1) from MF_FOOT_NVBC t2 where t1.strt_doc_id = t2.strt_doc_id AND CREA_AMND_NUM IS NULL)
    and (crea_amnd_num is null or not exists (select (1) from MF_FOOT_NVBC t3
    where t1.strt_doc_id = t3.strt_doc_id and t3.uidy = '&2950&'||T1.STRT_DOC_ID||'&'|| (select t4.amnd_num from MF_EQ_BMD t4
    where substr(T4.UIDY,6,instr(T4.UIDY,'&', 1,5)-instr(T4.UIDY,'&',1,2)+1) = SUBSTR(T1.STRT_DOC_ID,6) and t4.sys_dt_tm = (SELECT max(sys_dt_tm)
    FROM MF_EQ_BMD t5 WHERE t4.dtyp_id =t5.dtyp_id and t4.DOC_NUM = t5.DOC_NUM group by substr(t5.uidy,1, instr(t5.uidy,'&',1,5))))));

    now, I think, could we use "WITH x as select " approach.

    I modified as below:


    WITH A AS (SELECT max(sys_dt_tm)
    FROM MF_EQ_BMD t5 WHERE t4.dtyp_id =t5.dtyp_id and t4.DOC_NUM = t5.DOC_NUM group by substr(t5.uidy,1, instr(t5.uidy,'&',1,5)))
    create table TEMP_ABC_IQ nologging parallel (degree 8) as (select t1.strt_doc_id, t1.uidy, t1.crea_amnd_num, t1.strt_amnd_num, t2.sys_dt_tm, t2.amnd_num from
    MF_FOOT_NVBC t1 INNER JOIN MF_EQ_BMD T2 ON t2.UIDY LIKE '&1495' || substr(t1.STRT_DOC_ID, 6) || '%' AND NVL(t2.LAST_AMND_NUM, 'NA2145NA') = NVL
    (t1.CREA_AMND_NUM, 'NA2145NA')
    where t1.strt_doc_id like '&1436%'
    AND exists (select (1) from MF_FOOT_NVBC t2 where t1.strt_doc_id = t2.strt_doc_id AND CREA_AMND_NUM IS NULL)
    and (crea_amnd_num is null or not exists (select (1) from MF_FOOT_NVBC t3
    where t1.strt_doc_id = t3.strt_doc_id and t3.uidy = '&2950&'||T1.STRT_DOC_ID||'&'|| (select t4.amnd_num from MF_EQ_BMD t4
    where substr(T4.UIDY,6,instr(T4.UIDY,'&', 1,5)-instr(T4.UIDY,'&',1,2)+1) = SUBSTR(T1.STRT_DOC_ID,6) and t4.sys_dt_tm = (A)))));


    seems like I got some syntax error? (but I can't find what wrong?...) could you help me a little?

    (I also want to put hint /*+ push_subq */ in there)

    Thank you very much!!!







     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Can you clarify why you planning to use the hint PUSH_SUBQ?


    The use of the clause WITH provide to possibility to factorization of sub-query (+ materializtaion or not).
     
    Last edited: Dec 26, 2017