Home » SQL & PL/SQL » SQL & PL/SQL » P-Code in Oracle (10 G)
P-Code in Oracle [message #423799] Mon, 28 September 2009 06:34 Go to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
madhuottapalam
Messages: 10
Registered: September 2009
Junior Member
pablolee wrote on Mon, 28 September 2009 06:54
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.


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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous message
madhuottapalam
Messages: 10
Registered: September 2009
Junior Member
pablolee wrote on Mon, 28 September 2009 07:59
As 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...
Previous Topic: Forall - need an example with multiple row
Next Topic: ORA_ROWSCN NUll for first row
Goto Forum:
  


Current Time: Sat Dec 03 08:15:45 CST 2016

Total time taken to generate the page: 0.10522 seconds