Compile the stored procedures as interpreted

Multi tool use
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
add a comment |
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
INTERPRETED
is the "normal" mode, at least in my experience, andNATIVE
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 betweennative
andinterpreted
compilation.
– APC
Dec 29 '18 at 21:27
add a comment |
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
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
sql oracle plsql
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, andNATIVE
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 betweennative
andinterpreted
compilation.
– APC
Dec 29 '18 at 21:27
add a comment |
INTERPRETED
is the "normal" mode, at least in my experience, andNATIVE
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 betweennative
andinterpreted
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
add a comment |
2 Answers
2
active
oldest
votes
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.
"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
add a comment |
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]
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
"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
add a comment |
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.
"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
add a comment |
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.
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.
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
add a comment |
"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
add a comment |
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]
add a comment |
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]
add a comment |
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]
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]
edited Dec 29 '18 at 21:52
answered Dec 29 '18 at 21:35


SimonareSimonare
9,88411737
9,88411737
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
INTERPRETED
is the "normal" mode, at least in my experience, andNATIVE
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
andinterpreted
compilation.– APC
Dec 29 '18 at 21:27