The register_student and Update student procedures are grouped together in a package

Oracle and Expert Systems Technology

Oracle - Pinning Packages in the Shared Pool Library Cache

Based on my personal DBA experience, I highly recommend that you store all SQL in packages in you have enough shared_pool memory. Storing SQL in packages has several benefits.  It ensures that all SQL is uniform and reusable, and it makes the SQL "pin-able".

To prevent paging, packages can be marked as nonswappable. Marking a package as nonswappable tells a database that after the package is initially loaded into the shared pool, the package must always remain in memory. This procedure is sometimes called pinning or memory fencing.

Oracle provides a procedure called dbms_shared_pool.keep to pin a package, and packages can be unpinned with dbms_shared_pool.unkeep.

Only packages can be pinned. Stored procedures cannot be pinned unless they are placed into a package.

The choice of whether to pin a package in memory is a function of the size of the object and the frequency of its use. Very large packages that are called frequently might benefit from pinning, but any difference might go unnoticed because the frequent calls to the procedure have kept it loaded into memory anyway. Therefore, because the object never pages out in the first place, pinning has no effect. Also, the way procedures are grouped into packages can have some influence. Some Oracle DBAs identify high-impact procedures and group them into a single package, and then pin this package in the shared pool library cache.

In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure, and trigger that can be used by the applications. However, reality dictates that the shared pool cannot grow indefinitely, and wise choices must be made in terms of which packages are pinned.

Unix users might want to add code to their database startup script ensure that the packages are re-pinned after each database startup, guaranteeing that all packages are re-pinned with each bounce of the box. A script might look like this:

ORACLE_SID=mydata
export ORACLE_SID
su oracle -c "/usr/oracle/bin/svrmgrl /

EXECUTE dbms_shared_pool.keep['DBMS_ALERT'];
EXECUTE dbms_shared_pool.keep['DBMS_DDL'];
EXECUTE dbms_shared_pool.keep['DBMS_DESCRIBE'];
EXECUTE dbms_shared_pool.keep['DBMS_LOCK'];

exit;
!"

The database administrator also needs to remember to run pin.sqlwhenever restarting a database. This is done by reissuing the PIN command from inside SQL*DBA immediately after the database has been restarted.

If you have large procedures or large anonymous PL/SQL blocks in your application, you may also want to put these into packages and pin them in the shared pool.  You can determine what large stored objects are in the shared pool by selecting from the v$db_object_cache fixed view. This will also tell you which objects have been marked kept. This can be done with the following query:

select * from v$db_object_cache where sharable_mem > 10000;

If you have plenty of free memory in the shared pool and you wish to mark all packages in the system 'kept', you can execute the following PL/SQL snippet:

declare
   own varchar2[100];
   nam varchar2[100];  

cursor pkgs is  

select
   owner,
   object_name
from
   dba_objects
where
   object_type = 'PACKAGE';

begin open pkgs;
   loop fetch pkgs into own, nam; |
   exit when pkgs%notfound;
   dbms_shared_pool.keep[own || '.' || nam, 'P'];
end loop; 

end;

Now, let's look at how we can identify packages that should be pinned.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

I am new to creating packages. I have 2 functions and 1 procedure. They all work individually and compile with no errors. When I throw them together in a package I get the error, "Encountered the create symbol" referencing the create used for the package body. Can anybody explain to me what I am doing wrong?

CREATE OR REPLACE PACKAGE STUDENT_PKG IS 

  FUNCTION CALCULATE_GPA
    [STUDENT_ID1 IN NUMBER]
      RETURN NUMBER;

  FUNCTION CALCULATE_HOURS 
[STUDENT_ID1 IN NUMBER] 
  RETURN NUMBER;

  PROCEDURE UPDATE_STUDENTS;
END;  

CREATE OR REPLACE PACKAGE BODY STUDENT_PKG IS

FUNCTION CALCULATE_GPA
  [STUDENT_ID1 IN NUMBER]
  RETURN NUMBER
    IS 
GPA Number[3,2];
STUDENT_ID2 NUMBER[6];
BEGIN
Select To_Char[NVL[Sum[Points]/Sum[Credit_Hours],0],'0.99'] GPA INTO GPA
  from
    [Select Enrollment.Student_ID, Enrollment.Grade, Grade_Value*Credit_Hours Points, Credit_hours, Enrollment.Course_Number
      from Enrollment 
        left join Grade on Grade.Grade=Enrollment.Grade
        left join Section on [Enrollment.Term_Code=Section.Term_Code and
                    Enrollment.Subject_Code=Section.Subject_code and
                    Enrollment.Course_Number=Section.Course_Number and
                    Enrollment.Section=Section.Section]
        left join Course on  [Section.Subject_Code=Course.Subject_Code and
                    Section.Course_Number=Course.Course_Number]] First
Where Student_Id=Student_ID1                   
    group by Student_ID;
RETURN GPA;
END CALCLUATE_GPA; 

FUNCTION CALCULATE_HOURS 
[STUDENT_ID1 IN NUMBER] 
  RETURN NUMBER AS
Credit_Hours1 Number[2];
BEGIN
  Select SUM[CASE WHEN GRADE IS NULL THEN 0 ELSE CREDIT_HOURS END] into Credit_Hours1
    from Course
      join Enrollment on course.course_number=Enrollment.course_number
        where Student_id=Student_Id1
          GROUP BY STUDENT_ID;
  RETURN Credit_Hours1;
END CALCULATE_HOURS;

PROCEDURE UPDATE_STUDENTS AS 
GPA1 NUMBER [3,2];
CREDIT_HOURS1 NUMBER[2];
STUDENT_ID1 NUMBER[6]:=333333;
BEGIN
  GPA1:=CALCULATE_GPA[STUDENT_ID1];
  CREDIT_HOURS1:=CALCULATE_HOURS[STUDENT_ID1];
    UPDATE STUDENT
      SET GPA=GPA1,
      CREDIT_HOURS=CREDIT_HOURS1
      WHERE STUDENT_ID=STUDENT_ID1;
END UPDATE_STUDENTS;

END STUDENT_PKG;

asked May 2, 2015 at 20:06

1

You need to terminate the PACKAGE and PACKAGE BODY blocks with a slash [/] like this:

CREATE OR REPLACE PACKAGE STUDENT_PKG IS
    /* Header declarations */
END;  
/
CREATE OR REPLACE PACKAGE BODY STUDENT_PKG IS
    /* PKG BODY here */
END STUDENT_PKG;
/

If you're running the code from SQLDeveloper, you should choose the "Run as script" option. This thread gives an in-depth explanation about when/why the slash is required.

answered May 2, 2015 at 22:57

Mick MnemonicMick Mnemonic

7,7182 gold badges25 silver badges30 bronze badges

What is the difference between procedure and package?

What are stored functions procedures and packages?

How can we create procedure inside package in Oracle?

What is package specification and package body in Oracle?