Oracle report takes forever to run

[CBK]

I have a couple of reports that used to run fine, now for some reason they take forever (if at all) to run.

I am using Oracle 8.1.7 and have already examined the tables and indexes involved in the processing. I even dropped and recreated the indexes.

Any clues??

 

[sem]

Can you examine V$SESSION [and V$SQLTEXT] to get current cursors opened by that sessions?

Regards, Dima

 

[CBK]

I queried v$session and came up with some data, but I am not sure what I am looking at

 

[sem]

Oops, was not too precise

You may get text of query by

select SQL_TEXT
from SESSION S, V$SQLTEXT T
where S.SQL_ADDRESS= T.ADDRESS
AND S.SQL_HASH_VALUE=T.HASH_VALUE
AND AUDSID=<add correct value here>
ORDER BY ADDRESS, PIECE

Then you may use EXPLAIN PLAN and get plan for these statements.
Recreating indexes is not very good idea, because you loose statistics thus making your CBO blind.

You may also investigate V$LOCK view to check whether those reports don't wait for locked resources.

Regards, Dima

 

[CBK]

Thanks, but I am not getting any row returned with your query. I double-checked the audsid to verify it is still in v$session but nothing is coming back.

 

[subrak30]

Try querying the V$session_longops view.