OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » Procedure Vs Pakage

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Procedure Vs Pakage [message #287411]

Wed, 12 December 2007 03:25

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?

Report message to a moderator

Re: Procedure Vs Pakage [message #287412 is a reply to message #287411]

Wed, 12 December 2007 03:29

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.

Report message to a moderator

Re: Procedure Vs Pakage [message #287413 is a reply to message #287411]

Wed, 12 December 2007 03:29

Maaher
Messages: 7065
Registered: December 2001

Senior Member

That does not make sense. What did you answer?

MHE

Report message to a moderator

Re: Procedure Vs Pakage [message #287417 is a reply to message #287413]

Wed, 12 December 2007 03:45

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?.

Report message to a moderator

Re: Procedure Vs Pakage [message #287426 is a reply to message #287417]

Wed, 12 December 2007 03:57

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

Report message to a moderator

Re: Procedure Vs Pakage [message #287436 is a reply to message #287411]

Wed, 12 December 2007 04:24

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

Frank
Messages: 7901
Registered: March 2000

Senior Member

Sorry, I fail to see the point you are trying to make with that quote..

Report message to a moderator

Re: Procedure Vs Pakage [message #287453 is a reply to message #287411]

Wed, 12 December 2007 05:00

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

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

Michel Cadot
Messages: 68716
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

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.

Report message to a moderator

Previous Topic:	Insert Column Dynamically to a Table from another one
Next Topic:	Need 10g RAC training

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 18:05:06 CST 2024