Home » RDBMS Server » Performance Tuning » package size (oracle 11.2.g OS windows, )
package size [message #607452] Thu, 06 February 2014 14:35 Go to next message
anncao
Messages: 74
Registered: August 2013
Member
Is it good to have a big package or separate into a couple when it gets big, under condition that they are all related business category of functions or procedures.
How big is big?
Any size limitation that could impact performance?

Thanks,
Re: package size [message #607453 is a reply to message #607452] Thu, 06 February 2014 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member

SQL> select owner, name, max(line) from dba_source where line = (select max(line) from dba_source) group by owner, name;

OWNER                          NAME                            MAX(LINE)
------------------------------ ------------------------------ ----------
SYSMAN                         MGMT_JOB_ENGINE                     23467

Re: package size [message #607455 is a reply to message #607452] Thu, 06 February 2014 14:57 Go to previous messageGo to next message
John Watson
Messages: 4370
Registered: January 2010
Location: Global Village
Senior Member
If you are into extreme tuning, you probably should not group code depending on it being related in business terms. When you invoke one function or procedure, the entire package containing that code block is loaded into the library cache. That may include objects that are needed only once a month (or once a year). So you are wasting a lot of library cache memory. Better to place frequently executed code from ALL business categories into one huge package; load this package at instance startup up by using a startup trigger that invokes some dummy function. And keep all the rarely executed code as non-packed objects.
Would I do that? No way.
Re: package size [message #607458 is a reply to message #607455] Thu, 06 February 2014 15:45 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Thanks,
John, when you say would I do that, no way, what do you mean?
BlackSwan, does that mean MGMT_JOB_ENGINE can handle as maximum lines 23467 for packages? I tried search MGMT_JOB_ENGNINE, cannot find the definition online.
Re: package size [message #607459 is a reply to message #607458] Thu, 06 February 2014 15:54 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
I am sorry that the clue locker is empty at your site.

what is the result when you run same SQL against your database (as SYS)?
Re: package size [message #607460 is a reply to message #607458] Thu, 06 February 2014 16:04 Go to previous messageGo to next message
John Watson
Messages: 4370
Registered: January 2010
Location: Global Village
Senior Member
anncao wrote on Thu, 06 February 2014 21:45
Thanks,
John, when you say would I do that, no way, what do you mean?
Please accept my apologies for using language that was open to misinterpretation. I meant that I would not recommend such an approach.
Re: package size [message #607521 is a reply to message #607452] Sat, 08 February 2014 00:44 Go to previous message
Kevin Meade
Messages: 1924
Registered: December 1999
Location: Connecticut USA
Senior Member
This is a really old debate. How big should a piece of code be? It is not just an Oracle question of course, and there are many considerations. Consider however the evolution of technology in the IT space. In the beginning machines were expensive and using every bit of it was key to success. Today though people are expensive. So in most things IT, emphasis on improving the efficiency of people should take priority over improving the efficiency of machines.

With this in mind, I would suggest to you that size does not matter. Or maybe more correctly size matters far less than several other more key factors. Consider the plight of the maintenance developer. This person must walk code to figure out what it does. This job is made much easier if the walking of code does not require page flipping. A piece of code where logic "flows" is best. Flows generally means reads from the top down without having to look in other places for what you need. For example.

declare
   cursor c1 is (select <lots of columns> from <lots of table> where <complex predicates>);
   r1 c1%rowtype;
...
begin
   open c1;
   fetch c1 into r1;
   loop
      when c1%notfound then exit;
      <do stuff>;
   end loop;
   close c1;
end;
/


begin
   for r1 in (select <lots of columns> from <lots of table> where <complex predicates>) loop
      <do stuff>;
   end loop;
end;
/

In a real world package the first example here would have the SQL driving the loop possibly several hundred to several thousand lines away from the logic processing its data. In the second example it is right there. No need to flip pages to find it. A piece of code where logic flows is way better than one in which it does not.

What this means to me is size does not matter so much. Getting my code to flow is more important. Indeed, I have many times written packages that contain 20 thousand lines of code or more and been told it was very easy to maintain because of its logical construction and good documentation.

Blackswan's query is an interesting one. It shows you that Oracle had no qualms about writing a 23 thousand line package.

In any event, worry more about maintenance, understanding, and communication, and size won't matter to anyone. Performance should be measured in people terms these days. If you really want to work it, then buy yourself a 27 inch monitor and try keeping your pl/sql routines on one page. Use portrait mode if you want to but all on one page would be good modularization. This does not answer the question of how big a package should be, but it gives a good example of how people needs are important and can be met in a realistic way. I went extreme and bought three 27 inch monitors on which I code/test/document one of each screen. 27 inch is almost too big cause I have to move my head from left to right to see the different screens but I manage. In the end though, modularity is about "idea" not "number of lines". Implement an idea. Some ideas are big and so require more code than others. One should not artificially split an idea into to halves just because you have exceeded some arbitrary "number of lines" measure.

Good luck. Kevin

[Updated on: Sat, 08 February 2014 00:54]

Report message to a moderator

Previous Topic: result cache physical reads 0 on the first execution.What could be the reason
Next Topic: Tuning query without where clause
Goto Forum:
  


Current Time: Wed Jul 23 05:41:18 CDT 2014

Total time taken to generate the page: 0.31553 seconds