Process Running Foreever

[aghosha]

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

[malay_biswal]

Trace the session and tkprof it.
Then check the bottleneck SQL.

[aghosha]

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.

[malay_biswal]

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?

[aghosha]

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).

[malay_biswal]

Did you analyze the table after adding the columns.

[tamilselvan]

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?