OCM_Session6_3_Manage Instance Memory Structures
[*]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 where2type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')3and SHARABLE_MEM>=51200;
这条语句能列出在内存中占用超过50KB的包，存储过程，触发器和函数。
SQL> show userUSER is "SYS"SQL> create or replace view large_proc as2select * from v$DB_OBJECT_CACHE where3type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')4and 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 Area536870912 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
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]