slow package compilation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: slow package compilation

  1. #1
    Join Date
    May 2008
    Posts
    5

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    trace it - see what happens

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    May 2008
    Posts
    5
    Quote 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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width