slow package compilation

[dba_sabya_niit]

Hi

A pl sql package is taking 3 hours to compile in a 10g database. There is no library cache load/pin lock waits as so one is working in the database while compiling. The wait event seen is db_file_sequential_read. Total number of line of code is around 4K and mainly contains lots of update statements on a table with 150 columns. The statement written is trying to update each field of the table with value based on calculation and this construct is repeated 8 to 10 times.If some of the update statements are removed then speed of the compilation increases (infact if all the update statements are removed then it is compiled under less than 3 secs.). I tried to find out what is running behind by quering v$sqlarea with the hash_value from v$session but there is no entry in that view.
The cache related parameters in the database are given below :-

db_cache_size=1300M
db_keep_cache_size=200M
pga_aggregate_target=800M
shared_pool_size=700M
shared_pool_reserved_size=120M
log_buffer=10M

Thanks
Sabya

[davey23uk]

trace it - see what happens

[PAVB]

Either somebody is executing the package or your session died.

[dba_sabya_niit]

Either somebody is executing the package or your session died.

Hi PAVB

Neither the session has died nor somebody is executing the package as i have already mentioned that nobody is working in the database when I am compiling the package

[PAVB]

Prove it.
It wouldn't be the first time I see a sleeping session locking a piece of code.