| |
 |
|
Oracle dbms_describe
Oracle Database Tips by Donald Burleson |
Using Oracle dbms_describe
Oracle
Corporation recommends that you always pin
the STANDARD, DBMS_STANDARD, DBMS_UTILITY,
DBMS_DESCRIBE, and DBMS_OUTPUT packages in
the shared pool. You can use the following
code:
connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');
You can write a standard procedure to pin
all of the recommended Oracle packages into
the shared pool. Here is the script:
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');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('DBMS_PIPE');
execute dbms_shared_pool.keep('DBMS_SESSION');
execute
dbms_shared_pool.keep('DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');
Here's
the output of the memory.sql script:
SQL> @memory
OWNER TYPE NAME EXECUTIONS MEM_USED Kept
----- ---- ---- ---------- -------- ----
SYS PACKAGE STANDARD 867,600 151,963 YES
SYS PACKAGE BODY STANDARD 867,275 30,739 YES
SYS PACKAGE DBMS_ALERT 502,126 3,637 NO
SYS PACKAGE BODY DBMS_ALERT 433,607 20,389
NO
SYS PACKAGE DBMS_LOCK 432,137 3,140 YES
SYS PACKAGE BODY DBMS_LOCK 432,137 10,780
YES
SYS PACKAGE DBMS_PIPE 397,466 3,412 NO
SYS PACKAGE BODY DBMS_PIPE 397,466 5,292 NO
HRIS PACKAGE S3425_PACKAGE 285,700 3,776 YES
SYS PACKAGE DBMS_UTILITY 284,694 3,311 NO
SYS PACKAGE BODY DBMS_UTILITY 284,694 6,159
NO
HRIS PACKAGE HRS_COMN_PACKAGE 258,657 3,382
NO
HRIS PACKAGE BODY S125_PACKAGE 248,857
30,928 NO
HRIS PACKAGE BODY HRS_COM_PACKAGE 242,155
8,638 NO
HRIS PACKAGE GTS_SNAP_UTILITY 168,978 11,056
NO
HRIS PACKAGE BODY GTS_SNAP_UTILITY 89,623
3,232 NO
SYS PACKAGE DBMS_STANDARD 18,953 14,696 NO
SYS PACKAGE BODY DBMS_STANDARD 18,872 3,432
NO
KIS PROCEDURE RKA_INSERT 7,067 4,949 NO
HRIS PACKAGE HRS_PACKAGE 5,175 3,831 NO
HRIS PACKAGE BODY HRS_PACKAGE 5,157 36,455
NO
SYS PACKAGE DBMS_DESCRIBE 718 12,800 NO
HRIS PROCEDURE CHECK_APP_ALERT 683 3,763 NO
SYS PACKAGE BODY DBMS_DESCRIBE 350 9,880 NO
SYS PACKAGE DBMS_SESSION 234 3,351 NO
SYS PACKAGE BODY DBMS_SESSION 165 4,543 NO
GIANT PROCEDURE CREATE_SESS_RE 62 7,147 NO
HRIS PROCEDURE INIT_APP_ALERT 6 10,802 NO
Oracle
Corporation recommends that you always pin
the STANDARD, DBMS_STANDARD, DBMS_UTILITY,
DBMS_DESCRIBE, and DBMS_OUTPUT packages in
the shared pool. You can use the following
code:
connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');
Because
of their frequent usage, Oracle recommends
that the standard, dbms_standard,
dbms_utility, dbms_describe, and dbms_output
packages always be pinned in the shared
pool. The following snippet demonstrates how
a stored procedure called sys.standard can
be pinned:
Connect system/manager as sysdba;
|
|
|
|
|
|
and include the URL for the page.
