主要用于数据库的调优工作:
1 查看SGA:
SQL> select * from v$sga
2 ;
NAME VALUE
-------------------- ----------
Fixed Size 1336960
Variable Size 281020800
Database Buffers 134217728
Redo Buffers 6094848
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
2 如何设置SGA自动管理?
(1)首先,找出SGA_TARGET的值:
SQL> select sum(value) from v$sga;
SUM(VALUE)
----------
422670336
(2)其次,得到有效内存的大小:
SQL> select CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY;
CURRENT_SIZE
------------
171966464
(3)所以, SGA_TARGET的大小为: 403M - 169M = 234M
(4)把数据库的系统级别设置成TYPICAL 或者 ALL, TYPICAL表示收集主要统计信息, ALL表示收集全部系统信息。
SQL> alter system set STATISTICS_LEVEL=TYPICAL;
System altered.
(5)显示SGA的最大值:
SQL> show parameter SGA_MAX_SIZE;
NAME TYPE VALUE
------------------------------------ -----------
sga_max_size big integer 404M
(6)将共享池,大池,流池,java池,数据高速缓冲区的值设置为0。
SQL> alter system set SHARED_POOL_SIZE=0;
System altered.
SQL> alter system set LARGE_POOL_SIZE=0;
System altered.
SQL> alter system set JAVA_POOL_SIZE=0;
System altered.
SQL> alter system set STREAMS_POOL_SIZE=0;
System altered.
SQL> alter system set DB_CACHE_SIZE=0;
System altered.
(7)最后设置SGA_TARGET的大小:
SQL> alter system set SGA_TARGET=200M;
System altered.
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
3 如何禁用SGA自动管理??
只需要将SGA_TARGET的值设置为0,就可以禁用自动SGA管理了。
4 如何改变SGA_MAX_SIZE的值?
需要关闭数据库,改参数文件(如spfile当中的内容,然后重新生成参数文件,再打开数据库就可以了)。
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
5 如何查看SGA最小单位的大小(Granule的大小)??
SQL> select * from v$sgainfo where name = 'Granule Size';
NAME BYTES RES
-------------------------------- ---------- ---
Granule Size 4194304 No
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
6 计算高速缓冲区中,hit ratio(命中概率)。
公式: hit ratio = 1 - physical reads cache/(consistent gets from cache + db block gets from cache)。
SQL> select name, value from v$sysstat where name in ('physical reads cache','db block gets from cache','consistent gets from cache');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets from cache 83212
consistent gets from cache 793653
physical reads cache 15014
so, hit ratio = 98%
如果数据库高速缓冲区命中率太低,这个时候,DBA就需要调整高速缓冲区的大小,不过,在此之前,最好做一下评估, 数据缓冲区太小,会造成命中率降低,但是缓冲区太大,也会造成内存的浪费:
SQL> select size_for_estimate, buffers_for_estimate, estd_physical_reads
2 from v$DB_CACHE_ADVICE
3 WHERE NAME = 'DEFAULT'
4 AND block_size=(select value from v$parameter where name = 'db_block_size')
5 AND advice_status = 'ON';
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READS
----------------- -------------------- -------------------
12 1488 47424
24 2976 29358
36 4464 25254
48 5952 22115
60 7440 18590
72 8928 16827
84 10416 16455
96 11904 15615
108 13392 15422
120 14880 14968
132 16368 14968
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READS
----------------- -------------------- -------------------
136 16864 14968
144 17856 14968
156 19344 14968
168 20832 14968
180 22320 14968
192 23808 14968
204 25296 14968
216 26784 14968
228 28272 14968
240 29760 14968
21 rows selected.
从结果可以看出, 预计的磁盘读取值随着buffer的增大而减少,但是到了某一个特定的值的时候,就趋于稳定了,所以在这个例子当中,把DB_CACHE_SIZE 设置为13M左右是最合适的了。
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
7 查看数据字典的命中率:
SQL> select sum(gets - getmisses - fixed)/sum(gets) as ratio from v$rowcache;
RATIO
----------
.919020305
可以看出shared pool 当中对于数据字典的命中率是 92%, 如果这个值太小,则需要调整shared pool 的大小了。
———————————————————————————————————————————————
***********************************************
———————————————————————————————————————————————
8 查看日志缓冲区的大小,以及决定是否需要修改大小(注意:日志缓冲区的大小,不能动态修改,需要修改参数文件才行)
首先,查看表项:
SQL> select name,value from v$sysstat where name = 'redo buffer allocation retries';
NAME VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries 0
看看值如果接近0,则不需要修改,如果值偏大,则说明有日志等待写入数据缓冲区,这时候需要调整大小。
如果需要改,步骤如下,首先由spfile 来创建pfile, 然后修改pfile当中的log_buffer的值,然后关闭数据库,重新生成spfile, 然后再打开数据库即可验证修改后的spfile内容了。