Sql Tuning for DMLs
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Sql Tuning for DMLs

  1. #1
    Join Date
    Oct 2006
    Posts
    175

    Sql Tuning for DMLs

    Hello all,
    I have an insert statement as Insert into T2(a,b,c) select (a,b,sum(c)) from T1. T1 has 1 million+ rows. The select statement itself takes 20 seconds. I don't have any indexes on T1. I was wondering if I could the make insert statement faster. I have few doubts regarding the same. Does /*+append*/ really help here as I read somewhere this hint just appends the new rows into the table thus increasing the HWM, ultimately, make the FTS slower later on. And what hints would be appropriate with DMLs like these, where there is no joins in the select statement (just one table)?

    Appreciate your valuable inputs,
    gtcol

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    You know the problem, no index. Why don't you create one after checking explain plan. Regarding Append what ever you heard is true.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Oct 2006
    Posts
    175
    Thanks Malay for your advice,
    Beside creating indexes on the table, what could be other options in making DMLs faster? I am using parallel hints in few select statements and it seems to help somewhat. But I have a lot modules that we run in parallel. So wouldn't using parallel hints here excessively consume lots of resources and slow down other modules in adverse? I need to get through this doubt. Please suggest.

    Thanks,
    gtcol

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    can u post your sql with explain plan.
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Oct 2006
    Posts
    175
    Here is the DML with its explain plan:

    Without parallel hint:
    INSERT INTO HF01.PAIDBYLVL4NSPEC
    (
    nNwkID,
    InNwk,
    PaidAmt,
    BilledAmt ,
    EmpPaidAmt,
    PaidOrIncurred,
    YR,
    MTH,
    RefDate
    )
    SELECT
    nNwkID,
    InNwk,
    SUM( NVL( PaidAmt, 0 )) AS PaidAmt,
    SUM( NVL( BilledAmt, 0 )) AS BilledAmt,
    SUM( NVL( EmpPaidAmt, 0 )) AS EmpPaidAmt,
    PaidOrIncurred,
    YR,
    MTH,
    RefDate
    FROM
    HF01.PAIDBYNWKSPEC
    GROUP BY
    nNwkID,
    InNwk,
    PaidOrIncurred,
    YR,
    MTH,
    Refdate
    --------------------------------------------------------------------------

    INSERT STATEMENT Optimizer=ALL_ROWS (Cost=104,821 Card=5,194,253 bytes=389,568,975)
    SORT (GROUP BY) (Cost=104,821 Card=5,194,253 bytes=389,568,975)
    TABLE ACCESS (FULL) of "PAIDBYNWKSPEC" #2 TABLE Optimizer=ANALYZED (Cost=13,164 Card=5,194,253 bytes=389,568,975)
    --------------------------------------------------------------------------
    With Parallel hint:
    INSERT INTO HF01.PAIDBYLVL4NSPEC
    (
    nNwkID,
    InNwk,
    PaidAmt,
    BilledAmt ,
    EmpPaidAmt,
    PaidOrIncurred,
    YR,
    MTH,
    RefDate
    )
    SELECT /*+parallel(PAIDBYNWKSPEC)*/
    nNwkID,
    InNwk,
    SUM( NVL( PaidAmt, 0 )) AS PaidAmt,
    SUM( NVL( BilledAmt, 0 )) AS BilledAmt,
    SUM( NVL( EmpPaidAmt, 0 )) AS EmpPaidAmt,
    PaidOrIncurred,
    YR,
    MTH,
    RefDate
    FROM
    HF01.PAIDBYNWKSPEC
    GROUP BY
    nNwkID,
    InNwk,
    PaidOrIncurred,
    YR,
    MTH,
    Refdate
    --------------------------------------------------------------------------
    INSERT STATEMENT Optimizer=ALL_ROWS (Cost=7,276 Card=5,194,253 bytes=389,568,975)
    PX COORDINATOR
    PX SEND (QC (RANDOM)) of ":TQ10001" (Cost=7,276 Card=5,194,253 bytes=389,568,975)
    SORT (GROUP BY) (Cost=7,276 Card=5,194,253 bytes=389,568,975)
    PX RECEIVE (Cost=911 Card=5,194,253 bytes=389,568,975)
    PX SEND (HASH) of ":TQ10000" (Cost=911 Card=5,194,253 bytes=389,568,975)
    PX BLOCK (ITERATOR) (Cost=911 Card=5,194,253 bytes=389,568,975)
    TABLE ACCESS (FULL) of "PAIDBYNWKSPEC" #2 TABLE Optimizer=ANALYZED (Cost=911 Card=5,194,253 bytes=389,568,975)
    --------------------------------------------------------------------------

    Thanks,
    gtcol

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    you are selecting all the records from PAIDBYNWKSPEC table as there is no predicates. Is that your business requirement. If so forget adding any index. Then the way how to make it fast, hmm .. may be you need to talk to the system administrator how the disk structures are and you can move this table to the least busy disk to reduce I/O.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Oct 2006
    Posts
    175
    Yes, this is the business requirement for us for the time being. Actually, i was just concerned if we could make it any faster. And also about cost, for some sqls, execution time doesn't tend to come down even when the cost come down immensely. Isn't cost the indication for execution time? If it is not, then how can we test the execution time for long running queries, without executing them? Could you kindly suggest anything for this?

    Thanks
    gtcol

  8. #8
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Cost is a good indicator but not always cost less means the query will run faster. Regarding execution time of query without executing , no you can not do that. But yf the query is taking long time you can query v$session_longops to know how long it may take.

    If you are desparate to make this querry faster, then place the table in KEEP pool and then see.
    http://www.perf-engg.com
    A performance engineering forum

  9. #9
    Join Date
    Oct 2006
    Posts
    175
    Thanks Malay,
    It pretty much clears few clouds from my mind. About Keep Pool, I am using it for few frequently queried tables. Will have to look into this approach also, if need arises for this table.
    Thanks once again for your valuable input.
    gtcol

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Sorry for barging in, may I ask a question?

    You say... "insert takes 20 seconds"

    I ask... how often do you run this process?... in a daily basis, once an hour? once a minute?

    Bottom line is, why 20 seconds is such an issue?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width