3. Manage Instance Memory Structures
3.1 Create a view owned by user SYS that lists the packages,procedures,triggers and functions that are in memory and occupy more than 50KB. The view should be named LARGE_PROC and visible to all users through
a public synonym named LARGE_PROC.
3.1 Create a view owned by user SYS that lists the packages,procedures,triggers and functions that are in memory and occupy more than 50KB. The view should be named LARGE_PROC and visible to all users through a public synonym named LARGE_PROC.
SYS用戶創建列出占用了內存超過50KB的包，存儲過程，觸發器和函數的視圖，這個視圖的名稱是LARGE_PROC，並且創建一個公共同義詞名為LARGE_PROC，所有用
V$DB_OBJECT_CACHE：底層視圖為想x$kglob
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1083.htm#REFRN30058
This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.
TYPE
VARCHAR2(28)
Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK
SHARABLE_MEM
NUMBER
Amount of sharable memory in the shared pool consumed by the object
SQL> select * from v$DB_OBJECT_CACHE where
2 type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
3 and SHARABLE_MEM>=51200;
這條語句能列出在內存中占用超過50KB的包，存儲過程，觸發器和函數。
SQL> show user
USER is "SYS"
SQL>
create or replace view large_proc as
2 select * from v$DB_OBJECT_CACHE where
3 type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
4 and SHARABLE_MEM>=51200;
View created.
創建公共同義詞
官方文檔參考：CREATE SYNONYM
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm#SQLRF01401
SQL>
grant select on sys.large_proc to public;
Grant succeeded.
SQL>
create public synonym large_proc for sys.large_proc;
Synonym created.
3.2 Set your maximum SGA to 512MB. Turn on Automatic Shared Memory Management. Restart the instance after specifying.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 500M
sga_target big integer 500M
SQL>
alter system set sga_target=512m scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220432 bytes
Variable Size 150995120 bytes
Database Buffers 381681664 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 512M --sga_max_size 自動升高
sga_target big integer 512M
SQL>
3.3 Your developers notify you that they will need the Java Pool set to a minimum of 200MB.
SQL> show parameter java
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_pool_size big integer 0
java_soft_sessionspace_limit integer 0
SQL>
alter system set java_pool_size=200m;
System altered.
SQL> show parameter java
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_pool_size big integer 200M
java_soft_sessionspace_limit integer 0
3.4 Limit the total amount of PGA that can be used on an instance-wide basis to 150MB.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 107374182
SQL>
alter system set pga_aggregate_target=150m;
System altered.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 150M
