Home » Other » Training & Certification » Procedure Vs Pakage
Procedure Vs Pakage [message #287411] Wed, 12 December 2007 03:25 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Its one of the interview question

Which one is faster and why procedure or package?
Re: Procedure Vs Pakage [message #287412 is a reply to message #287411] Wed, 12 December 2007 03:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
A PACKAGE can consist of a number of PROCEDURES and/or FUNCTIONS. But in terms of performance I don't see any difference at all.
Re: Procedure Vs Pakage [message #287413 is a reply to message #287411] Wed, 12 December 2007 03:29 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
That does not make sense. What did you answer?

MHE
Re: Procedure Vs Pakage [message #287417 is a reply to message #287413] Wed, 12 December 2007 03:45 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
According to me procedure is the fast

When we incorporate two procedure with same name but different in number of parameters in a package the execution time may differ i am not having practical knoweledge for the above.

If any one having correct me Maaher?.
Re: Procedure Vs Pakage [message #287426 is a reply to message #287417] Wed, 12 December 2007 03:57 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I don't think so, Oracle has to look up the signature of the procedure, just as it has to look up the signature of a packaged procedure. The only difference I can think of is that a package is loaded as a whole. I fail to see why that would make a procedure faster. I'd go for packages.

you might find this link interesting: Picking your packages. And even then, I don't think I fully agree with Feuerstein on 'when not to use packages'.

MHE
Re: Procedure Vs Pakage [message #287436 is a reply to message #287411] Wed, 12 December 2007 04:24 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

The Link

Quote:

performance issue April 28, 2005 - 4pm US/Eastern Bookmark | Bottom | TopReviewer: Christine Guo from Virginia
I agree using package instead of stand alone stored procs. But are there any performance difference
between the two?

I worked with a query returning over 40 million records. There are two functions in this query.
When I tried to put the functions into a package, the performance slowed down dramatically. The
query calling a function returns half million rows in less then 1 minute, and the query calling a
function inside a package returns the half million rows in 6 minutes.
What might be the problem?
BTW, I also noticed that if I put an exception block in the function, it slows down the performance
a lot as well.
Your input would be greatly appreciated.



Followup April 28, 2005 - 4pm US/Eastern:

not that I don't believe you but...

set up a simple example, don't need your tables, just need to understand the difference between
your two implementations.


Kiran.

[Updated on: Wed, 12 December 2007 04:29] by Moderator

Report message to a moderator

Re: Procedure Vs Pakage [message #287443 is a reply to message #287436] Wed, 12 December 2007 04:35 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Sorry, I fail to see the point you are trying to make with that quote..
Re: Procedure Vs Pakage [message #287453 is a reply to message #287411] Wed, 12 December 2007 05:00 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You have to go through the whole thread in the link what i posted. I post it again :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537

Kiran.

[Updated on: Wed, 12 December 2007 05:02]

Report message to a moderator

Re: Procedure Vs Pakage [message #287455 is a reply to message #287411] Wed, 12 December 2007 05:06 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

[Updated on: Wed, 12 December 2007 15:59] by Moderator

Was there anything wrong in my post?

Kiran.

[Updated on: Wed, 12 December 2007 05:51]

Report message to a moderator

Re: Procedure Vs Pakage [message #287469 is a reply to message #287455] Wed, 12 December 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sorry, changed "code" tags to "quote" tags, I should have precised it.
Rationale: this is a quote (not code) and "code" block does not fold the lines and so they were very long ones.

Regards
Michel

[Updated on: Wed, 12 December 2007 05:41]

Report message to a moderator

Re: Procedure Vs Pakage [message #287472 is a reply to message #287411] Wed, 12 December 2007 05:53 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Oh yes. I thought that it might be the reason once. Anyway it was my mistake.

Thanks & Regards,
Kiran.
Previous Topic: Insert Column Dynamically to a Table from another one
Next Topic: Need 10g RAC training
Goto Forum:
  


Current Time: Tue Dec 03 17:34:45 CST 2024