主要是些基础的理解
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 164M
sga_target big integer 0
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__shared_pool_size big integer 80M
shared_pool_size big integer 80M
SQL> alter system set shared_pool_size=84m;
alter system set shared_pool_size=84m
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORASQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 180M
sga_target big integer 0
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__shared_pool_size big integer 80M
shared_pool_size big integer 80M-04033: 没有足够的内存来增加池的容量
SQL> select component ,current_size/1024/1024 m from v$sga_dynamic_components;
COMPONENT M
---------------------------------------------------------------- ----------
shared pool 80
large pool 8
java pool 48
streams pool 0
DEFAULT buffer cache 24
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT M
---------------------------------------------------------------- ----------
DEFAULT 32K buffer cache 0
OSM Buffer Cache 0
SQL> alter system set shared_pool_size=90m;
系统已更改。
SQL> select component ,current_size/1024/1024 m from v$sga_dynamic_components;~~~~~~~~~~~~~~~~~动态看到变化
QL> select current_size/1024/1024 m,min_size,last_oper_type,last_oper_mode from
v$sga_dynamic_components;
M MIN_SIZE LAST_OPER_TYP LAST_OPER
--------- ---------- ------------- ---------
92 83886080 GROW MANUAL~~~~~~~~~~~~~~~~~~~~~~~~~最后的操作，增加，操作手动
8 8388608 STATIC
48 50331648 STATIC
0 0 STATIC
24 25165824 STATIC
0 0 STATIC
0 0 STATIC
0 0 STATIC
0 0 STATIC
0 0 STATIC
0 0 STATIC
M MIN_SIZE LAST_OPER_TYP LAST_OPER
--------- ---------- ------------- ---------
0 0 STATIC
0 0 STATIC
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 788088
Variable Size 162527624
Database Buffers 25165824
Redo Buffers 262144
SQL> select * from v$sgastat;
POOL NAME BYTES
------------ -------------------------- ----------
fixed_sga 788088
buffer_cache 25165824
log_buffer 262144
shared pool KQR L SO 61440
shared pool KQR M PO 789564
shared pool KQR M SO 85004
shared pool KQR S PO 80928
shared pool KQR S SO 512
shared pool KTI-UNDO 1235304
shared pool sessions 781324
shared pool sql area 3274304
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KGLS heap 946008
shared pool joxs heap 4220
shared pool row cache 3707272
shared pool parameters 8380
shared pool repository 53904
shared pool ASH buffers 2097152
shared pool free memory 52021936
shared pool PL/SQL DIANA 1191728
shared pool FileOpenBlock 746704
shared pool PL/SQL MPCODE 1541888
shared pool library cache 5005644
POOL NAME BYTES
------------ -------------------------- ----------
shared pool miscellaneous 12070420
shared pool pl/sql source 192
shared pool PLS non-lib hp 29556
shared pool STREAMS messag 16752
shared pool table definiti 1976
shared pool trigger defini 3672
shared pool trigger inform 1860
shared pool trigger source 640
shared pool type object de 389128
shared pool private strands 1198080
shared pool KSXR receive buffers 1033000
POOL NAME BYTES
------------ -------------------------- ----------
shared pool message pool freequeue 618504
shared pool KSXR pending messages que 841036
shared pool event statistics per sess 4384640
shared pool fixed allocation callback 264
shared pool flashback generation buff 1422760
shared pool kmgsb circular statistics 823296
large pool free memory 8388608
java pool free memory 50331648
已选择41行。
SQL> select sum(bytes)/1024/1024 from v$sgastat;
SUM(BYTES)/1024/1024
--------------------
173.001579
SQL> select sum(bytes)/1024/1024 from v$sgastat where pool='shared pool'~~~~~~~~~~~~~~~~~~~~~~~~~~~~`也显示
2 ;
SUM(BYTES)/1024/1024
--------------------
92
SQL> show parameter java_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__java_pool_size big integer 48M
java_pool_size big integer 48M
SQL> show parameter large_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
__large_pool_size big integer 8M
large_pool_size big integer 8M
SQL> desc v$sgainfo;
名称 是否为空? 类型
----------------------------------------- -------- -------------------------
NAME VARCHAR2(32)
BYTES NUMBER
RESIZEABLE VARCHAR2(3)
SQL> select * from v$sgainfo;~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 788088 No
Redo Buffers 262144 No
Buffer Cache Size 25165824 Yes
Shared Pool Size 96468992 Yes
Large Pool Size 8388608 Yes
Java Pool Size 50331648 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 188743680 No
Startup overhead in Shared Pool 25165824 No
Free SGA Memory Available 4194304
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 788088
Variable Size 162527624
Database Buffers 25165824
Redo Buffers 262144
SQL> select name,value/1024/1024 from v$sga;
NAME VALUE/1024/1024
-------------------- ---------------
Fixed Size .751579285
Variable Size 154.998421
Database Buffers 24
Redo Buffers .25
SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size .751579285
Redo Buffers .25
Buffer Cache Size 24
Shared Pool Size 92
Large Pool Size 8
Java Pool Size 48
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 180
Startup overhead in Shared Pool 24
Free SGA Memory Available 4
已选择11行。
SQL>
SQL> select current_size/1024/1024 from v$sga_dynamic_free_memory;
CURRENT_SIZE/1024/1024
----------------------
4~~~~~~~~~~~~~~~~~~~~~~~~~~还有4M可以调整sga_max_size 减去 其他内存之合
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 180M
sga_target big integer 0
SQL> select sum(bytes)/1024/1024 from v$sgastat;
SUM(BYTES)/1024/1024
--------------------
173.001579
SQL> select sum(current_size/1024/1024) m from v$sga_dynamic_components;
M
----------
172
SQL> select sum(value/1024/1024) from v$sga;
SUM(VALUE/1024/1024)
--------------------
180
SQL> desc v$db_cache_advice;~~~建议视图
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
ADVICE_STATUS VARCHAR2(3)
SIZE_FOR_ESTIMATE NUMBER
SIZE_FACTOR NUMBER
BUFFERS_FOR_ESTIMATE NUMBER
ESTD_PHYSICAL_READ_FACTOR NUMBER
ESTD_PHYSICAL_READS NUMBER
ESTD_PHYSICAL_READ_TIME NUMBER
ESTD_PCT_OF_DB_TIME_FOR_READS NUMBER
SQL> show parameter db_cache_ad
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_cache_advice string ON~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~打开这个才有
SQL>
SQL> select size_for_estimate, estd_physical_read_factor, id,estd_physical_reads
from v$db_cache_advice where name = 'DEFAULT';
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ID ESTD_PHYSICAL_READS
----------------- ------------------------- ---------- -------------------
4 2.6877 3 71854
8 1.9242 3 51441
12 1.6399 3 43841
16 1.509 3 40342
20 1.3123 3 35082
24 1 3 26734
28 .8944 3 23911
32 .8394 3 22439
36 .8159 3 21812
40 .7969 3 21305
44 .7888 3 21088
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ID ESTD_PHYSICAL_READS
----------------- ------------------------- ---------- -------------------
48 .7834 3 20943
已选择12行。
~~~~~~~~~~~~~~~~~主要是对比estd_physical_reads, size_for_estimate~~取一个合适的值
SQL> desc v$buffer_pool;
名称 是否为空? 类型
----------------------------------------- -------- ---------------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
RESIZE_STATE VARCHAR2(10)
CURRENT_SIZE NUMBER
BUFFERS NUMBER
TARGET_SIZE NUMBER
TARGET_BUFFERS NUMBER
PREV_SIZE NUMBER
PREV_BUFFERS NUMBER
LO_BNUM NUMBER
HI_BNUM NUMBER
LO_SETID NUMBER
HI_SETID NUMBER
SET_COUNT NUMBER
~~~~~~~~~~也有比较详细的信息
SQL> desc v$buffer_pool_statistics;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~很详细对buffer cache
名称 是否为空? 类型
----------------------------------------- -------- -------------------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
SET_MSIZE NUMBER
CNUM_REPL NUMBER
CNUM_WRITE NUMBER
CNUM_SET NUMBER
BUF_GOT NUMBER
SUM_WRITE NUMBER
SUM_SCAN NUMBER
FREE_BUFFER_WAIT NUMBER
WRITE_COMPLETE_WAIT NUMBER
BUFFER_BUSY_WAIT NUMBER
FREE_BUFFER_INSPECTED NUMBER
DIRTY_BUFFERS_INSPECTED NUMBER
DB_BLOCK_CHANGE NUMBER
DB_BLOCK_GETS NUMBER
CONSISTENT_GETS NUMBER
PHYSICAL_READS NUMBER
PHYSICAL_WRITES NUMBER
SQL> select id,name,dirty_buffers_inspected,free_buffer_inspected,physical_write
s from v$buffer_pool_statistics;
ID NAME DIRTY_BUFFERS_INSPECTED FREE_BUFFER_INSPECTED
---------- -------------------- ----------------------- ---------------------
PHYSICAL_WRITES
---------------
3 DEFAULT 2209 33020
9137
SQL> select id,name,dirty_buffers_inspected,free_buffer_inspected,physical_write
s from v$buffer_pool_statistics;
ID NAME DIRTY_BUFFERS_INSPECTED FREE_BUFFER_INSPECTED
---------- -------------------- ----------------------- ---------------------
PHYSICAL_WRITES
---------------
3 DEFAULT 2209 33020
9137
SQL> alter system checkpoint;
系统已更改。
SQL> select id,name,dirty_buffers_inspected,free_buffer_inspected,physical_write
s from v$buffer_pool_statistics;
ID NAME DIRTY_BUFFERS_INSPECTED FREE_BUFFER_INSPECTED
---------- -------------------- ----------------------- ---------------------
PHYSICAL_WRITES
---------------
3 DEFAULT 2209 33020
9181~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~发生了物理写 flush buffer cache 也会发生物理写
SQL> select id,name,cnum_write,physical_writes from v$buffer_pool_statistics;
ID NAME CNUM_WRITE PHYSICAL_WRITES
---------- -------------------- ---------- ---------------
3 DEFAULT 0 9181
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into table test values(i);
5 end loop;
6 end;
7 /
SQL> ed
已写入 file afiedt.buf
1 declare
2 begin
3 for i in 1..1000 loop
4 insert into test values(i);
5 end loop;
6* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> select id,name,cnum_write,physical_writes from v$buffer_pool_statistics;
1 declare
2 begin
3 for i in 1..10000 loop
4 insert into test values(i);
5 end loop;
6* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> select id,name,cnum_write,physical_writes from v$buffer_pool_statistics;
ID NAME CNUM_WRITE PHYSICAL_WRITES
---------- -------------------- ---------- ---------------
3 DEFAULT 0 9224~~~~~~~~都发生物理写了
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-607598/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-607598/
扫一扫
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
