8 Replies Latest reply: Oct 7, 2013 11:11 AM by Frank Kulash RSS

    Functions compile each time they get called

    989520

      Hi

      I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

      Thanks in advance

        • 1. Re: Functions compile each time they get called
          sb92075

          989520 wrote:

           

          Hi

          I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

          Thanks in advance

           

          You've come to know a falsehood & nobody documents things that are not true.

          • 3. Re: Functions compile each time they get called
            rp0428
            I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

            Can you 'please enlighten us' as to how you came to know that 'functions compile each time they get called'?

            • 4. Re: Functions compile each time they get called
              Lalit Kumar B

              989520 wrote:

               

              Hi

              I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

               

              Only in case the function(or any object for that matter)is in INVALID state, then the FIRST TIME you call the object, it will recompile itself.

               

              Since you say that functions compile each time they are called, well, that's ABSOLUTELY INCORRECT. If you found that piece of incorrect information from any source, then please don't read anything from anywhere other than documentation.

               

              Regards,

              Lalit

              • 5. Re: Functions compile each time they get called
                Solomon Yakobson

                989520 wrote:

                 

                Hi

                I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

                 

                I think you misread it. Miost likely whatever you read stated standalone procedure/function is loaded in session memory each time it is called. Package  procedure/function is not, since package is loaded in session memory once only when session references package first time.

                 

                SY.

                • 6. Re: Functions compile each time they get called
                  Lalit Kumar B

                  SolomonYakobson wrote:

                   

                  989520 wrote:

                   

                  Hi

                  I come to know that functions compile each time they get called. Can you please enlight in more details or refer some useful article on this.

                   

                  I think you misread it. Miost likely whatever you read stated standalone procedure/function is loaded in session memory each time it is called. Package  procedure/function is not, since package is loaded in session memory once only when session references package first time.

                   

                   

                   

                  I don't think that compiling a PL/SQL object has anything to do with loading it into the session memory. I hope OP is aware of the difference between these two.

                   

                  @SY - My post has nothing to do with your clarification on what OP might be thinking, you might be closer to OP's thought. I am just contemplating on a broader picture.

                  Regards,

                  Lalit

                  • 7. Re: Functions compile each time they get called
                    989520

                    Thanks to all of you for the response

                     

                    Hopefully, I did not misunderstand the comment https://forums.oracle.com/thread/203446 at Dec 13, 2006 5:15 AM

                     

                    It is old thread, but I believe it was not there in any version. I come to this link while searching google after one person strongly claimed the same and even forced a developer (mainly from java stream) to change the function to a procedure with out parameter.

                    • 8. Re: Functions compile each time they get called
                      Frank Kulash

                      Hi,

                       

                       

                       

                      989520 wrote:

                       

                      Thanks to all of you for the response

                       

                      Hopefully, I did not misunderstand the comment https://forums.oracle.com/thread/203446 at Dec 13, 2006 5:15 AM

                       

                      It is old thread, but I believe it was not there in any version. I come to this link while searching google after one person strongly claimed the same and even forced a developer (mainly from java stream) to change the function to a procedure with out parameter.

                      Are you talking about reply #5 in that thread https://forums.oracle.com/message/1598468#1598468

                      which says:

                      There is one more difference between stored procedure and functions that stored procedures compiled only once and can be called again and again without being compiled each time, this improves performance and saves time, on the other hand functions compile each time they get called

                      ?  Whoever wrote that probably meant anonymous blocks rather than functions.  This was questioned immediately in the same thread.

                      Functions, like procedures, can be (and almost always are) compiled once and run again and again without being compiled each time.