1 2 Previous Next 28 Replies Latest reply on Feb 6, 2008 6:58 PM by Billy~Verreynne

    perfomance points in packages

      Hi all
      sorry for asking an old question, just hoping to get some new answers, i recently went for an interview and i was asked "what are the advantage of packages?", i said
      3.When you call a packaged subprogram for the first time, the whole package is loaded into memory. Therefore, subsequent calls to related subprograms in the package require no disk I/O
      4.packages stop cascading dependencies and so avoid unnecessary recompiling

      the interviewer said i'm missing one vital point in performace perspective and refused to tell that,
      hi gurus can you kindly tell me what are the other points that can be added in the list of "advantages of packages in performance perspective"

      Thank u
        • 1. Re: perfomance points in packages
          Hmmm...Not sure but may be he is expecting about Session persistence. That is, persistent data of the package can improve the response time of the queries by caching static data, thus avoiding repeated queries for same information.
          • 2. Re: perfomance points in packages
            Thank you chaitanya...
            is there anyother points.....???
            • 3. Re: perfomance points in packages
              There are no "vital" performance improvements caused by using packages.

              If there was - Oracle would have recommended only using packages, there would have Metalink notes on the subject, and there would have been a desupport notice that procedure should not be used for new development, as it is a performance problem, and will be deprecated.

              That interviewer is full of it. This is a typical nonsense question that some, who are experts in their own little minds, like to ask in interviews. Never mind Oracle, he does not seem to know the first thing about software engineering fundamentals.

              Procedures are tools. Packages are tools. (and you get them in all kinds of shapes in numerous programming languages)

              There are reasons for using one in some cases, and the other in other cases. Performance considerations are not relevant as that is not the deciding factor of whether to choose procedures or packages to perform a specific task.

              Comparing procedures vs packages is just plain stupid as it is not a case of one versus the other. It is a case of choosing the right tool for the job.

              An interviewer that knows his craft, would have asked you when would you use procedures and when would you packages. He may even have given you a sample case and asked you to explain your reasons for choosing whatever tool you think best to address this.

              Why? Because it is by far more important to understand how the applicant reasons, thinks, and approaches problems, then asking questions and expecting pat answers in return.
              • 4. Re: perfomance points in packages
                wow...billy... thats really wonderful perspective....it hasn't come to my mind.. :)

                billy can you tell me when to use procedures and when to packages.

                Thanks a lot...
                • 5. Re: perfomance points in packages
                  is there anyother points.....???
                  Nothing more from me, but using packages is highly recommended by many experts like Tom Kyte and Steve Feuerstein, for various known benefits and maintenance reasons of applications.

                  Go through the following, It can help you:

                  • 6. Re: perfomance points in packages
                    > an you tell me when to use procedures and when to packages.

                    I use procedures in PL/SQL are typically as wrappers - or where the code is basic and straight to the point, and does not need anything more but a single call. I use packages typically as Application Programming Interfaces (APIs).

                    For example, instead of calling raise_application_error directly, one can create a wrapper procedure for it called RaiseError. This wrapper does the actual raise of the exception - and it can do numerous other functions too. Like logging the error to DBMS_OUTPUT if debugging is enabled. Or logging it to a log table using an autonomous transaction. Or record the PL/SQL call stack. Etc.

                    It allows one to take a basic system call, and wrap it into something more powerful and flexible. Something that you now control.. (you do not control what raise_application_error does, but you can control what RaiseError does)

                    A package on the other hand is like a full blown program. It can contain numerous procedures and functions. Static variables. It can show an interface to the caller, but hide the actual implementation of that interface. This is typically used in PL/SQL to create APIs - Application Programming Interfaces. DBMS_JOB is such an example. An API to manage background jobs. DBMS_SQL is an API for creating cursors. Etc.

                    I see this as the basic difference between procedure as a tool and packages as a tool.

                    What do I see as the guideline? Writing good software is mostly about modularisation... as that is the fundamental cornerstone of a good design. A good design will be using a lot of reusable blocks. Think Lego. With Lego blocks you can build anything from robots to spaceships to cars and houses - using the same set of blocks.

                    And that is what software engineering is essentially about. Designing and writing "blocks of code" that is reusable. Simple blocks are procedure. More complex blocks are packages.

                    A reusable code block does one thing only and does it well. And with this approach, once you start the designing of the code unit, you quickly discovered which of the tools, procedure or package, best suit that design - is the best tool for creating that "lego block of code" that can be used and re-used again and again.
                    • 7. Re: perfomance points in packages
                      Hi billy...
                      thanks a lot... thats really a great effort...
                      billy can you just see whether my understanding is correct

                      Stand alone procedures: can be used
                      --when the process we are going to do is small
                      --if there is no calling of this procedure from some other block

                      packages: can be used

                      -- when the process is huge
                      -- calling of procedures or functions (i.e) ( code resuablity)

                      kindly correct me if i'm wrong...

                      Thanks a lot
                      • 8. Re: perfomance points in packages
                        That's a fair approach. Not exactly sure what you mean with "if there is no calling of this procedure from some other block" - as having a procedure and not calling it from anywhere else, means that there is no reason for the existence of that procedure in the first place.

                        Utility and wrapper procedures are usually called from almost every single package one has (e.g. raising an exception, logging a debug message, tokenising a string, etc).
                        • 9. Re: perfomance points in packages
                          Hi billy
                          i meant, if there is no resuablity of the code (i.e) if its being executed only once in the flow, we can go for procedures. Am i correct.

                          Thank u
                          • 10. Re: perfomance points in packages
                            No. Procedures are basically used to create basic building blocks to extend the language - making it more flexible and powerful. And these are most definitely re-usable code.

                            Here is a basic example of how procedures are used in PL/SQL to extend the language by creating procedures to serve as "new language commands":

                            SQL> -- a wrapper procedure that wraps raise_application_error
                            SQL> create or replace procedure RaiseError( errNo number, errMsg varchar2 ) is
                            2 begin
                            3 -- for debugging we can turn the following on: DBMS_OUTPUT.put_line( 'Error '||errNo||': '||errMsg );
                            4 -- we can also log the error to a log table via atonomous transaction, etc.
                            5 raise_application_error( -20000-errNo , errMsg );
                            6 end;
                            7 /

                            Procedure created.

                            SQL> -- a utillity procedure to introduce a new command in PL/SQL
                            SQL> create or replace procedure Assert( condition boolean, errNo number, errMsg varchar2 ) is
                            2 begin
                            3 if not condition then
                            4 RaiseError( errNo, errMsg );
                            5 end if;
                            6 end;
                            7 /

                            Procedure created.

                            SQL> create or replace type TMessageList is table of varchar2(200);
                            2 /

                            Type created.

                            SQL> -- now in a package, I can use these procedures/language extensions
                            SQL> create or replace package sampleP as
                            3 procedure DoSomething1( d date );
                            5 procedure DoSomething2( n number );
                            6 end;
                            7 /

                            Package created.

                            SQL> create or replace package body sampleP as
                            3 -- I define my error codes
                            4 ERR_DATE_IS_NULL constant number := 1;
                            5 ERR_DATE_INVALID constant number := 2;
                            6 ERR_EMP_TABLE_IS_EMPTY constant number := 3;
                            8 -- .. and a corresponding message for each error
                            9 ERROR_MESSAGE constant TMessageList := new TMessageList(
                            10 'The date may not be null.',
                            11 'The date may not be in the future.',
                            12 'The EMP table is empty.'
                            13 );
                            17 procedure DoSomething1( d date ) is
                            18 begin
                            19 -- the date may not be null
                            20 assert(
                            21 d is not null,
                            22 ERR_DATE_IS_NULL,
                            23 ERROR_MESSAGE( ERR_DATE_IS_NULL )
                            24 );
                            26 -- the date may not be in the future
                            27 assert(
                            28 d <= SYSDATE,
                            29 ERR_DATE_INVALID,
                            30 ERROR_MESSAGE( ERR_DATE_INVALID )
                            31 );
                            33 -- rest of the code for the proc...
                            34 end;
                            36 procedure DoSomething2( n number ) is
                            37 cnt integer;
                            38 begin
                            39 -- we do assertions to check our parameters, etc.
                            40 --
                            41 -- somewhere in our proc we select against EMP,
                            42 -- but the EMP table is empty and throws no data found
                            43 raise NO_DATA_FOUND;
                            45 -- more code...
                            47 exception when NO_DATA_FOUND then
                            48 -- our exception handler catches the exception and turns it
                            49 -- into a meaningful error
                            50 RaiseError( ERR_EMP_TABLE_IS_EMPTY, ERROR_MESSAGE(ERR_EMP_TABLE_IS_EMPTY) );
                            51 end;
                            53 begin
                            54 null;
                            55 end;
                            56 /

                            Package body created.

                            SQL> var d varchar2(20)
                            SQL> exec sampleP.DoSomething1( :d )
                            BEGIN sampleP.DoSomething1( :d ); END;

                            ERROR at line 1:
                            ORA-20001: The date may not be null.

                            SQL> exec :d := SYSDATE+1;

                            PL/SQL procedure successfully completed.

                            SQL> exec sampleP.DoSomething1( :d )
                            BEGIN sampleP.DoSomething1( :d ); END;

                            ERROR at line 1:
                            ORA-20002: The date may not be in the future.

                            SQL> exec sampleP.DoSomething2( 100 )
                            BEGIN sampleP.DoSomething2( 100 ); END;

                            ERROR at line 1:
                            ORA-20003: The EMP table is empty.

                            • 11. Re: perfomance points in packages
                              Hi billy
                              Thanks a lot for replying, But i'm little confused, billy if the question is whats the exact scenario to use a package and whats the exact scenario to use a proceudre, what will be your sharp answer for that billy.

                              Thank u
                              • 12. Re: perfomance points in packages
                                Sven W.
                                I was thinking if I could come up with other performance points for packages vs. procedures. There are no good ones left afaik. Maybe next time ask the interviewer about the exact database version he is refering to. Could always be that there is something new.

                                Result cache would be functions only.
                                Also packaged variables do not exists in procedures, so no sense in comparing that.
                                Invoker and Definer rights should have no performance implications. And I'm not sure if the can be used in stand alone packages.

                                As to when to use a packaged procedure compared to a stand alone procedure I would always go for the packaged version. I sometimes write standalone functions, mostly for the same reason that billy wrote. But never had the need for a standalone procedure (other than <strike>laziness</strike> programming efficiency).

                                Message was edited by:
                                Sven W.
                                • 13. Re: perfomance points in packages
                                  > if the question is whats the exact scenario to use a package and
                                  whats the exact scenario to use a proceudre, what will be your sharp

                                  My answer would be - describe the exact scenario. Show me the code. Show the program's design. The scenario will dictate which tools to be used.

                                  There are no ready-made-off-the-shelve answers for when to use procedures and when to packages. The basic software life cycle is:
                                  a) user specifications (we have a need)
                                  b) functional specifications (define the need formally)
                                  c) technical specifications (how are we going to address the need technically?)
                                  d) development (creating the actual solution to satisfy the need)
                                  e) implementation (rolling it out)
                                  f) repeat

                                  So in order to comment on what to use, package or procedure, I need to be at step d - writing the code. And the technical specifications and design will dictate what is the best to use for that particular cog in the solution wheel.

                                  As I said originally - this question is stupid. It does not make sense. There is insufficient data to formulate a response. There are no pat answers to this. I would tell the interviewer, "Show me the code. Show the program design.".

                                  Then we can discuss about whether a procedure or package need to be used and exactly WHY the one would the more appropriate tool to use.

                                  PS. It is like asking what is better to use, a hammer or screwdriver? You can only say when you know whether you need to drive in nails or screws. In other words, you need to know WHAT the problem is before deciding on what tool to use on that problem.
                                  • 14. Re: perfomance points in packages
                                    package is designed to manage procedures and functions more easily :

                                    - consider you have 5000 procedures and functions in your application and you have 15 guys working at them, you should use package to facilite your code source management

                                    - if your application use only 5 procedures and functions, you might not to use package

                                    your interviewer should be a commercial guy who does not understand the difference between a tool and the way the tool is drived...what is coded in a procedure or function determines his performance, despite you have putted it within a package or without.

                                    1 2 Previous Next