Compile the stored procedures as interpreted

Multi tool use
Multi tool use












3















While reading a PLSQL book I have stumbled upon this piece of code :



ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED; 


Could you please explain how it differs from normal compilation? I was not able to find the answer in the book.



Thank you!










share|improve this question

























  • INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

    – Bob Jarvis
    Dec 29 '18 at 21:08






  • 2





    Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

    – APC
    Dec 29 '18 at 21:27
















3















While reading a PLSQL book I have stumbled upon this piece of code :



ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED; 


Could you please explain how it differs from normal compilation? I was not able to find the answer in the book.



Thank you!










share|improve this question

























  • INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

    – Bob Jarvis
    Dec 29 '18 at 21:08






  • 2





    Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

    – APC
    Dec 29 '18 at 21:27














3












3








3








While reading a PLSQL book I have stumbled upon this piece of code :



ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED; 


Could you please explain how it differs from normal compilation? I was not able to find the answer in the book.



Thank you!










share|improve this question
















While reading a PLSQL book I have stumbled upon this piece of code :



ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED; 


Could you please explain how it differs from normal compilation? I was not able to find the answer in the book.



Thank you!







sql oracle plsql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 30 '18 at 23:17









GMB

8,7472623




8,7472623










asked Dec 29 '18 at 21:01









Ginta AmbražaitėGinta Ambražaitė

185




185













  • INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

    – Bob Jarvis
    Dec 29 '18 at 21:08






  • 2





    Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

    – APC
    Dec 29 '18 at 21:27



















  • INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

    – Bob Jarvis
    Dec 29 '18 at 21:08






  • 2





    Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

    – APC
    Dec 29 '18 at 21:27

















INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

– Bob Jarvis
Dec 29 '18 at 21:08





INTERPRETED is the "normal" mode, at least in my experience, and NATIVE is rarely used. YMMV.

– Bob Jarvis
Dec 29 '18 at 21:08




2




2





Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

– APC
Dec 29 '18 at 21:27





Whenever you find something in this book (whatever its is) which the book doesn't explain it's probably worth trying to see what the actual Oracle PL/SQL documentation has to say about it. Each is a link to the section explaining the difference between native and interpreted compilation.

– APC
Dec 29 '18 at 21:27












2 Answers
2






active

oldest

votes


















0














Oracle supports two different compilation modes : INTERPRETED and NATIVE.



INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :




... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time




In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).



NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :




SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.




This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.



As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...



For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.






share|improve this answer


























  • "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

    – Bob Jarvis
    Dec 30 '18 at 0:53











  • @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

    – GMB
    Dec 30 '18 at 1:21













  • Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

    – Bob Jarvis
    Dec 30 '18 at 3:49





















0














Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.




Native compilation is the process by which a stored PL/SQL program is
compiled into native code (Pro *C) that does not need to be
interpreted at runtime. In comparison, code that is not natively
compiled is stored in an intermediate form that must be interpreted at
runtime.



PL/SQL native compilation provides the greatest performance gains for
computation-intensive procedural operations.




Reference : [1] [2]






share|improve this answer

























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53973307%2fcompile-the-stored-procedures-as-interpreted%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Oracle supports two different compilation modes : INTERPRETED and NATIVE.



    INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :




    ... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time




    In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).



    NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :




    SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.




    This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.



    As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...



    For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.






    share|improve this answer


























    • "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

      – Bob Jarvis
      Dec 30 '18 at 0:53











    • @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

      – GMB
      Dec 30 '18 at 1:21













    • Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

      – Bob Jarvis
      Dec 30 '18 at 3:49


















    0














    Oracle supports two different compilation modes : INTERPRETED and NATIVE.



    INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :




    ... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time




    In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).



    NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :




    SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.




    This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.



    As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...



    For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.






    share|improve this answer


























    • "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

      – Bob Jarvis
      Dec 30 '18 at 0:53











    • @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

      – GMB
      Dec 30 '18 at 1:21













    • Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

      – Bob Jarvis
      Dec 30 '18 at 3:49
















    0












    0








    0







    Oracle supports two different compilation modes : INTERPRETED and NATIVE.



    INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :




    ... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time




    In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).



    NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :




    SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.




    This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.



    As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...



    For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.






    share|improve this answer















    Oracle supports two different compilation modes : INTERPRETED and NATIVE.



    INTERPRETED is the default mode, that was introduced at the same time as stored PL/SQL (Oracle 7) :




    ... each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time




    In the code you showed, the INTERPRETED part is redundant, as it corresponds to the default compilation mode (unless the command is executed against a server whose default was changed).



    NATIVE was introduced in Oracle 9, then enhanced and simplified in the following versions :




    SQL modules (packages, triggers, procedures, function, and types) .. are compiled... into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.




    This results in faster execution times, as no interpretation is needed at runtime : improvement factor was forecasted from 1.04 to 2.4 in Oracle 10g.



    As a conclusion : unless you have a good reason, you want to use NATIVE over INTERPRETED...



    For more details and a possible migration path, read this Oracle 10g FAQ paper and this post on Oracle blog.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 30 '18 at 1:14

























    answered Dec 29 '18 at 21:41









    GMBGMB

    8,7472623




    8,7472623













    • "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

      – Bob Jarvis
      Dec 30 '18 at 0:53











    • @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

      – GMB
      Dec 30 '18 at 1:21













    • Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

      – Bob Jarvis
      Dec 30 '18 at 3:49





















    • "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

      – Bob Jarvis
      Dec 30 '18 at 0:53











    • @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

      – GMB
      Dec 30 '18 at 1:21













    • Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

      – Bob Jarvis
      Dec 30 '18 at 3:49



















    "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

    – Bob Jarvis
    Dec 30 '18 at 0:53





    "Interpreted code is converted to machine code suitable for your processor"? Aside from the OraFAQ blog you cited, do you have a ref to official Oracle docs which confirm this? My understanding (and the numbers cited in the OraFAQ blog post seem to indicate it) is that interpreted code is compiled to virtual machine code and is then executed by the VM. My recollection is that Oracle chose the Ada VM, initially, at least, as the guys who developed PL/SQL really liked Ada, and if you squint at it just right in bad light PL/SQL does look a lot like Ada v1.

    – Bob Jarvis
    Dec 30 '18 at 0:53













    @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

    – GMB
    Dec 30 '18 at 1:21







    @BobJarvis : you may be right... I pulled out a more official documentation end edited the quotes in my answer... Thanks !

    – GMB
    Dec 30 '18 at 1:21















    Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

    – Bob Jarvis
    Dec 30 '18 at 3:49







    Not a problem. The OraFAQ blog post could be correct given that A) Sun Microsystems bought Anamorphic Systems, which had taken technology from the Self project and produced the HotSpot VM for Smalltalk, which Sun used to develop a just-in-time Java bytecode->native translator, B) Oracle bought Sun, acquiring both Java and the HotSpot JIT VM, and C) applying JIT technology to PL/SQL would be a logical step to take. However, in many years of following Oracle and PL/SQL I've never heard they took that step. Confirmation would be good to have.

    – Bob Jarvis
    Dec 30 '18 at 3:49















    0














    Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.




    Native compilation is the process by which a stored PL/SQL program is
    compiled into native code (Pro *C) that does not need to be
    interpreted at runtime. In comparison, code that is not natively
    compiled is stored in an intermediate form that must be interpreted at
    runtime.



    PL/SQL native compilation provides the greatest performance gains for
    computation-intensive procedural operations.




    Reference : [1] [2]






    share|improve this answer






























      0














      Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.




      Native compilation is the process by which a stored PL/SQL program is
      compiled into native code (Pro *C) that does not need to be
      interpreted at runtime. In comparison, code that is not natively
      compiled is stored in an intermediate form that must be interpreted at
      runtime.



      PL/SQL native compilation provides the greatest performance gains for
      computation-intensive procedural operations.




      Reference : [1] [2]






      share|improve this answer




























        0












        0








        0







        Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.




        Native compilation is the process by which a stored PL/SQL program is
        compiled into native code (Pro *C) that does not need to be
        interpreted at runtime. In comparison, code that is not natively
        compiled is stored in an intermediate form that must be interpreted at
        runtime.



        PL/SQL native compilation provides the greatest performance gains for
        computation-intensive procedural operations.




        Reference : [1] [2]






        share|improve this answer















        Below description summarizes Native vs Interpreted Compilation. Interpreted Compilation is default compilation on Oracle DB. Natively compiled code does not need to be interpreted at runtime thus it executes faster.




        Native compilation is the process by which a stored PL/SQL program is
        compiled into native code (Pro *C) that does not need to be
        interpreted at runtime. In comparison, code that is not natively
        compiled is stored in an intermediate form that must be interpreted at
        runtime.



        PL/SQL native compilation provides the greatest performance gains for
        computation-intensive procedural operations.




        Reference : [1] [2]







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 29 '18 at 21:52

























        answered Dec 29 '18 at 21:35









        SimonareSimonare

        9,88411737




        9,88411737






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53973307%2fcompile-the-stored-procedures-as-interpreted%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            65tT6n C LopuMm I5 V,ZOcmMG2mv6iwaXzdUlYGqj0zJsaLSCb9
            Ji7Vlg7HNhhI,zsK2bFnvh98pAxzNRC,B coZ2ITf513SkH,5eXx IxY 4N WlnrXkjZyvJZjDVo 1T4CGIqGJZjKz zVoieJWwBmDmYdG

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas