ORACLE9i_性能调优基础五(Sizing Other SGA structures)

最新推荐文章于 2021-04-12 21:57:16 发布

原创最新推荐文章于 2021-04-12 21:57:16 发布 · 607 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

31 篇文章

订阅专栏

本文深入探讨了数据库性能优化的关键技术，包括SGA组件、重做日志缓冲区、动态视图使用、调优指南、减少重做操作、监控Java池内存、设置SGA大小等，提供了一套全面的性能提升策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Granule

1.SGA components are allocated and deallocated in units of contiguous memory called granules

2.The size of a granule depends on the estimated total SGA, If the estimated SGA size if :

a.Less than or equal to 128MB then the granule size is 4MB

b.Greater than 128MB then the granule size is 16MB

Sizing Redo Log Buffer

1.Adjust the LOG_BUFFER parameter

2.Default_value:Either 512K or 128K * the value of CPU_COUNT, whichever is greater.

Using Dynamic Views

Tuning Guidlines

SQL> select * from v$sysstat where name like 'redo buffer%' or name like 'redo entries';                                     

STATISTIC# NAME                 CLASS      VALUE    STAT_ID
---------- --------------- ---------- ---------- ----------
       133 redo entries             2      43861 3488821837
       135 redo buffer all          2          0 1446958922
           ocation retries

SQL> select sid,event,seconds_in_wait,state FROM v$session_wait where event='log buffer space%';

Reducing Redo Operations

1.Direct Path loading without archiving does not generate redo.

2.Direct Path loading with archiving can use Nologging mode

3.Direct Load Insert can use Nologging mode

4.Some SQL statements can use Nologging mode.

Monitoring Java Pool Mem

Limit Java session memory useage:

1.JAVA_SOFT_SESSIONSPACE_LIMIT

2.JAVA_MAX_SESSIONSAPCE_SIZE

SQL> select * from v$sgastat where pool ='java pool';                       

POOL         NAME                 BYTES
------------ --------------- ----------
java pool    free memory        4194304

Sizing the SGA for JAVA

1.SHARED_POOL_SIZE:

a.8KB per loaded class

b.50MB for loading large JAR files

2.Configure Oracle Shared Server

3.JAVA_POOL_SIZE

24MB default

50MB for medium-sized Java application

Monitoring Dispatchers

1.Use the following dynamic views:

a.v$shared_server_monitor

b.v$dispatcher

c.v$dispatcher_rate

2.Identify contention for dispatchers by checking:

a.Busy rates

b.Dispatcher waiting time

3.Check for dispatcer contention

4.Add or remove dispatchers while the database is open

SQL> show parameter disp;                                                                                                    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (protocol=tcp)(dispatchers=3)
max_dispatchers                      integer     5

SQL> select sum(owned) "clinets",sum(busy)*100/(sum(busy)+sum(idle)) "Busy rate" from v$dispatcher;

   clinets  Busy rate
---------- ----------
         0 .000076756

SQL> select * from v$queue;                                                 

PADDR    TYPE           QUEUED       WAIT     TOTALQ
-------- ---------- ---------- ---------- ----------
00       COMMON              0          0          0
30E1A71C DISPATCHER          0          0          0
30E1ACD0 DISPATCHER          0          0          0
30E1B284 DISPATCHER          0          0          0

第一个是接受队列后三个是响应队列

SQL> select name, bytes, idle,busy,requests from v$shared_server;           

NAME      BYTES       IDLE       BUSY   REQUESTS
---- ---------- ---------- ---------- ----------
S000          0     572435          0          0
S001          0     572408          0          0
S002          0     572391          0          0
S003          0     572383          0          0
S004          0     572373          0          0