Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions


Procedure Compile takes time - around 4 mins

0614 Member Posts: 121 Red Ribbon

Hi Folks,

I have faced a very wried case today, procedure compiles taking time every time and 4 mins time.

i have checked all the possible ways like

o library cache locks on the dependent objects

its not accessing at the time

no dbms_jobs on the procedure

can you please help on the what could be reason ?


  • Fabricio_Jorge
    Fabricio_Jorge Member Posts: 337 Bronze Badge


    confirm if there are no other sessions using the procedure, at the V$SESSION. One possibility here is that while you are trying to compile the procedure, another session is using it in parallel.

  • 0614
    0614 Member Posts: 121 Red Ribbon

    @Fabricio_Jorge ,

    Thanks for the reply ,

    none of the sessions using it in parallel, but we have tried staging environments like (not used environment ).

    could you check please what could be the reason ?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,856 Gold Crown

    taking time every time

    Do it one more time, but enable extended sql_trace for the session with wait state tracing enabled, then run the trace file through tkprof (sorting by elapsed time, maybe) to see where most of the time goes.

    I'm not a regular user of SQL Developer so don't know the best way of enabling the trace and reading the trace file - and the method ought to vary with version of Oracle, which you didn't mention.

    If you can't do it through trace files, and have access to the ASH data (needs licence) then you could start a session, check the session_id and serial, then compile and query ASH to see where your time went; or you could query v$sesstat/v$statname for your session, and v$session_event/v$statname for your session to find out what work you did.


    Jonathan Lewis