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!

Tune one sql

Discussion in 'SQL PL/SQL' started by 13478, Sep 29, 2016.

  1. 13478

    13478 Active Member

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

    I have a expensive sql, it runs 15 minutes

    UPDATE MY_WORLD TJ1 SET TJ1.BOUNTY_DT =
    (SELECT TJ2.DOG_DT FROM MY_WORLD TJ2 WHERE TJ1.CREDIT_ID = TJ2.UID_HIGH)
    WHERE TJ1.CREDIT_ID IS NOT NULL;


    now I change it to

    MERGE /*+ parallel */ INTO MY_WORLD TJ1
    USING MY_WORLD TJ2
    on (TJ1.CREDIT_ID = TJ2.UID_HIGH)
    WHEN matched THEN UPDATE /*+ parallel */ SET TJ1.BOUNTY_DT = TJ2.DOG_DT WHERE TJ1.CREDIT_ID IS NOT NULL;


    or


    UPDATE /*+ parallel */ MY_WORLD TJ1 SET BOUNTY_DT = DOG_DT where CREDIT_ID = UID_HIGH and CREDIT_ID IS NOT NULL;


    do I change correctly, or which one should be better to tune this sql?

    Thank you very much
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    How many rows of conditional : CREDIT_ID IS NOT NULL?
    1) The condition WHERE TJ1.CREDIT_ID IS NOT NULL is superfluous.
    PDML in MERGE operator doesn't work
    2) PDML can not work. There are restrictions
    3) May be to use dbms_parallel_execution ?
     
    Last edited: Sep 29, 2016
  3. 13478

    13478 Active Member

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

    hmm, thank you

    Because I saw the original slow query, in fact, just update and self join same table ( MY_WORLD), so is that OK to rewrite the sql like:

    UPDATE /*+ parallel */ MY_WORLD SET BOUNTY_DT = DOG_DT where CREDIT_ID = UID_HIGH and CREDIT_ID IS NOT NULL;

    could you help me to confirm?

    Thank you very much.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tabe MY_WORLD is partitioned ?
    If this table is flat , then PDM is not working.

    p.s. My name is Sergey.
     
    Last edited: Sep 29, 2016
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Yeah, good suggestion.
    I am running a count for how many row are null..(?)

    thank you
     
  6. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    yeah, it a huge partitioned table, size around 100G.

    thank you
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Condition in WHERE , I suppose, incorrect.
    Write what records you need to select?
    What is partioned key ?
    P.S.
    Code (SQL):
    ALTER SESSION enable parallel dml;
    UPDATE /*+ parallel(your dop) */ ....
     
    Last edited: Sep 29, 2016
  8. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    I got it tune now,
    now it is 83% quicker.

    thank you very much for discussion.
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    I am glad that you achieved result.
    But,it is very interesting that there are 83 percent quicker )))
    Can you provide plans of queries with execution statistics: before optimization and after optimization?
    Which is results: how changed the plan, how changed the DB_TIME and etc ?
    For analyse plan of your query is possible to use v$active_session_history/v$sql_plan_statistics and etc ... or EM.