- 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
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
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