tailieunhanh - Oracle Built−in Packages- P117

Oracle Built−in Packages- P117: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk SIZE K KEPT NAME 180 PACKAGE 78 YES CVAR PACKAGE 74 SELECT JOB FROM JOBS RUNNING WHERE JOB b1 0F884588 518752523 CURSOR 71 YES INSTAT PACKAGE BODY 62 YES BGPROC PACKAGE BODY PL SQL procedure successfully completed. SQL execute 0F884588 518752523 C PL SQL procedure successfully completed. SQL execute 50 SIZE K KEPT NAME SIZE K KEPT NAME 180 PACKAGE 78 YES CVAR PACKAGE 74 YES 1 SELECT JOB FROM JOBS RUNNING WHERE JOB b1 0F884588 518752523 CURSOR 71 YES INSTAT PACKAGE BODY 62 YES BGPROC PACKAGE BODY PL SQL procedure successfully completed. After the cursor is pinned the second call to the SIZES procedure indicates this by showing YES in the KEPT output column. It is interesting and somewhat confusing that such a simple SELECT statement results in a cursor that uses 74K of shared pool memory. The DBA_KEEPSIZES view DBA_KEEPSIZES is a view that makes available the size PL SQL objects will occupy in the shared pool when kept using the procedure. This view can be used by the DBA to plan for shared pool memory requirements of large PL SQL objects. The actual view definition contained in the file follows CREATE OR REPLACE VIEW DBA_KEEPSIZES totsize owner name AS SELECT TRUNC SUM parsed_size SUM code_size 1000 owner name FROM dba_object_size WHERE TYPE IN PACKAGE PROCEDURE FUNCTION PACKAGE BODY TRIGGER GROUP BY owner name The columns for DBA_KEEPSIZES are defined in the following table. Column Datatype Description TOTSIZE NUMBER Size in shared pool if object kept via KEEP OWNER VARCHAR2 30 Schema of the stored PL SQL object NAME VARCHAR2 30 Name of the stored PL SQL object You can query DBA_KEEPSIZES to get an idea of which packages procedures and functions are relatively DBMS_SHARED_POOL Examples 571 Appendix A What s on the Companion Disk large and thus may