OCM_Session6_3_Manage Instance Memory Structures

最新推荐文章于 2021-07-01 18:13:39 发布

原创最新推荐文章于 2021-07-01 18:13:39 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

OCM experiment 专栏收录该内容

42 篇文章

订阅专栏

本博客详细介绍了如何通过创建视图和调整参数来管理内存结构，并优化Oracle实例配置，包括设置最大SGA、Java池大小和PGA限制。

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