| P-Code in Oracle [message #423799] |
Mon, 28 September 2009 06:34  |
madhuottapalam
Messages: 10
Registered: September 2009
|
Junior Member |
|
|
Hi All,
This is my first post in the forum and i am sure i will have many more going forward. I am a SQL Server Dev/DBA with 12+ years of exp and also i am a moderator in MSDN forums and MVP in SQL Server.
Currently i am working in a Oracle project and i was trying to map the architecure of Oracle to SQL Server equivalent. I have question regarding P-Code in PL/SQL.
In the following link
books.google.co.in/books?id=sIJDjNUUHAoC&pg=RA2-PA400&lpg=RA2-PA400&dq=p+code+pl+sql&source=bl&ots=xQHCtebNqe& ;sig=2hnlJ57F-H_tt38gcxrO59junYc&hl=en&ei=mJzASpXgAZrW7AOF_qGJAQ&sa=X&oi=book_result&ct=result&resnum=1#v=one page&q=p%20code%20pl%20sql&f=false
It is mentioned that, When the subprogram is called the P-Code is read from the Disk if necessary. My question is, i understand what is pcode, but does this store in Disk or it stores in SGA. If i think about p-code equivalent in SQL Server, it is execution plan stored in Procedure cache. But in sql server, you dont stores execution plan in Disk it is only in cache and once the cache is aged out , it has to be recompiled and put again in cache.
Could you please confirm, whether P-code is stored in disk or not? if yes, which system object expose this?
thanks
Madhu K Nair
|
|
|
|
| Re: P-Code in Oracle [message #423802 is a reply to message #423799] |
Mon, 28 September 2009 06:54  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
madhuottapalam wrote on Mon, 28 September 2009 12:34
If i think about p-code equivalent in SQL Server, it is execution plan stored in Procedure cache.
Wrong, it is not an execution plan. It is the compiled version of the procedure. Yes it is stored on disk and read into memory when required. Where can you see it? i don't know, neither do I care 'cos I could understand it anyway.
|
|
|
|
| Re: P-Code in Oracle [message #423804 is a reply to message #423799] |
Mon, 28 September 2009 07:18 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
One very important thing to understand is the difference between stored procedures in SQLServer and Oracle.
In Oracle these are purely pieces of runnable code, comparable to procedures/methods in other programming language.
In SQLServer I have seen stored procedures that were no more than stored queries.
In general, Oracle's SPs do not return resultsets.
Stored execution plans when talking about Oracle SP does not make sense for this.
|
|
|
|
| Re: P-Code in Oracle [message #423807 is a reply to message #423802] |
Mon, 28 September 2009 07:22 |
madhuottapalam
Messages: 10
Registered: September 2009
|
Junior Member |
|
|
pablolee wrote on Mon, 28 September 2009 06:54madhuottapalam wrote on Mon, 28 September 2009 12:34
If i think about p-code equivalent in SQL Server, it is execution plan stored in Procedure cache.
Wrong, it is not an execution plan. It is the compiled version of the procedure. Yes it is stored on disk and read into memory when required. Where can you see it? i don't know, neither do I care 'cos I could understand it anyway.
Thanks for the quick answer... Ok... let me understand, in sql server , when the query is submitted to DB engine let us say for the first time, the optimizer parse it, bind it and prepare many cost based execution plan according to the statistics and other info available on the tables. The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans.
All these plans are stored in Procedure cache.
Now in oracle if p-code is not execution plan could you please explain me step by step how the process happens. or let me expalin please confirm it.
(a) When a query is submitted it is parsed/bind /prepare pcode -
(b) Now the compiled pcode is stored in disk and also in SGA
(c) Now from this p-code the execution plan is generated (cost based or role based) and the optimizer choose one plan and submit to DB engine
(d) If the plan is aged out, when the next time the same query is ran, it get the p-code from disk and no need to compile (parse/bind/p-code generation) again and hence you save time.
If the above stpes are correct SQL Server have no equivalent of P-code.
Could you please confirm the stpes
thanks once again
Madhu K Nair
|
|
|
|
| Re: P-Code in Oracle [message #423816 is a reply to message #423807] |
Mon, 28 September 2009 07:59 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
As Frank has intimated in the post above, you appear to be mixing up stored procedures with queries. They are not the same thing.
|
|
|
|
| Re: P-Code in Oracle [message #423818 is a reply to message #423816] |
Mon, 28 September 2009 08:08 |
madhuottapalam
Messages: 10
Registered: September 2009
|
Junior Member |
|
|
pablolee wrote on Mon, 28 September 2009 07:59As Frank has intimated in the post above, you appear to be mixing up stored procedures with queries. They are not the same thing.
i am understanding now... In SQL Server whether it is query or SP it is compiled by statement by statement... In Oracle, is it that, you have p-code only for PL/SQL block? When you run a SQL Statement in oracle does it not create p-code?
Please bear with me if I am asking too many non-sense questions...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
