MANAGING INSTANCE IN ORACLE 10G

INCREASING THE SIZE OF OF SGA AND PGA
ALTER SYSTEM SET SGA_MAX_SIZE=1024M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=1024M SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=512M SCOPE=SPFILE;
ALTER SYTEM SET WORK_AREA_SIZE_POLICY=AUTO SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

CHECKING THE VALUE OF THE PARAMETERS
SQL> SHOW PARAMETER SGA_MAX_SIZE;
SQL> SHOW PARAMETER SGA_TARGET;
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET;
SQL> SHOW PARAMETER WORKAREA_SIZE_POLICY;

KEEPING FREQUENTLY USED AND LARGE PL/SQL PROCEDURES IN BUFFER POOL
STEP 1: Run the script as sysdba (ONE TIME)
TO KEEP PROCEDURES IN BUFFER POOL
SQL> @ E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\DBMSPOOL.SQL

STEP 2: Keep the procedures in Shared Pool
EXECUTE DBMS_SHARED_POOL.KEEP(‘ORCL.PROCEDURE1’);
EXECUTE DBMS_SHARED_POOL.KEEP(‘ORCL.PROCEDURE12’);

Check v$db_object_cache view to see how much memory is consumed by each package;
SELECT SUM(SHARABLE_MEM)/(1024*1024) SPACE_IN_MB FROM V$DB_OBJECT_CACHE WHERE KEPT='YES';

SELECT NAME,LOADS,PINS,EXECUTIONS,KEPT FROM V$DB_OBJECT_CACHE WHERE TYPE='PROCEDURE';