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!

Sql tuning for binary table

Discussion in 'SQL PL/SQL' started by akika, Apr 12, 2018.

  1. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,

    I have attached scripts and same is taking too much time to be processed.
    Any idea how can same be optimized for parallel run or execute several times.
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please define 'too much time' as it's not clear what you consider that to be.

    Parallel isn't a time-saver, it's just as often a time sink as parallel slaves need to work through a coordinator. Read here:

    Parallel Universe
    Parallel Reality
     
  3. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Got 1000k records in the table with id and blob record fields
    And it's taking around 8hrs to be executed.
    Is there a better way to run it into several splits or use a trigger?
    I need to gain in time..
    Can existing proc be modified to cater for that conversion in less time?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have not provided sufficient information to recommend any course of action. An execution plan needs to be posted to know what Oracle is doing with the query -- get the sql_id and use dbms_xplan.display_cursor to get the plan then post it here.
     
  5. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    HI, I've amended the package having 2 procedures in it..
    1 procedure will select * IDs in the blob table order by 1 and split it into a specific range.
    Split table contains ID , startRangeId and EndRangeID, sequenceID.

    In 2nd procedure, have a cursor that takes startRangeID and EndRangeID.
    Will then run the split range in parallel.

    I would like to know how can i parameterised the table name in the select & update section instead of hardcoding the table name. (procedure attached).

    Thxs.