Process Running Foreever
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Process Running Foreever

  1. #1
    Join Date
    Mar 2001
    Posts
    15

    Process Running Foreever

    Hi, I have a process which has been running as expected since long.

    Recently, I modified a table which is used by the process adding 2 additional columns.

    The process inserts into this table, and hence the procedure insert statement was modified accordingly to incorporate this change.

    After this change the process is running foreever.

    Any clue please?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Trace the session and tkprof it.
    Then check the bottleneck SQL.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Mar 2001
    Posts
    15
    I have the sql. The only thing I noticed that it has high logical reads.

    However, It is not clear to me adding 2 columns will cause such impact.

    I know there are some row chaining on that table; but that existed even before the modification.

    I can always modify and tune the sql, but that is not the solution at this moment.

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    How high is that. I guess the cost of SQL is less. Seems some index it should not use which it's using now and that's giving problem. I guess your CPU elapsed will be also close to total elapsed here. Look into all the indexes and check the cardinality, clustering_factor. Buffer gets SQL take s time to tune.

    Did you analyze the table after adding two columns BTW?
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Mar 2001
    Posts
    15
    It is not an index problem for sure. The new columns are not used in any select statement.

    Very important thing is the the the SQL which is stuck is not using this new columns at all. However, the table which is modified is being used for select
    (excluding the new columns) and the output in inserted into other table which not touched at all.

    Cost of SQL is low ( 6).

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Did you analyze the table after adding the columns.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The process inserts into this table, and hence the procedure insert statement was modified accordingly to incorporate this change.
    Does the procedure update old rows or only insert new rows?

    Do you use LMT with ASSM?

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