-
slow package compilation
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
-
trace it - see what happens
-
Either somebody is executing the package or your session died.
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.
-
Originally Posted by PAVB
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
-
Prove it.
It wouldn't be the first time I see a sleeping session locking a piece of code.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|