OCM_Session6_3_Manage Instance Memory Structures

zz22

　　

• 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 througha 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$kglobhttp://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.
TYPEVARCHAR2(28)Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINKSHARABLE_MEMNUMBERAmount 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 userUSER 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 SYNONYMhttp://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     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 500Msga_target                           big integer 500MSQL> alter system set sga_target=512m scope=spfile;
System altered.

SQL> startup forceORACLE instance started.
Total System Global Area  536870912 bytesFixed Size                  1220432 bytesVariable Size             150995120 bytesDatabase Buffers          381681664 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened.SQL> show parameter sga
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 512M --sga_max_size 自动升高sga_target                           big integer 512MSQL>

• 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     0java_pool_size                       big integer 0java_soft_sessionspace_limit         integer     0SQL> alter system set java_pool_size=200m;
System altered.
SQL> show parameter java
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------java_max_sessionspace_size           integer     0java_pool_size                       big integer 200Mjava_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 107374182SQL> alter system set pga_aggregate_target=150m;
System altered.
SQL> show parameter pga
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target                 big integer 150M　　

         版权声明：本文为博主原创文章，未经博主允许不得转载。