oracle實驗記錄 關於記憶體的幾個view

主要是些基礎的理解

 

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/，如需轉載，請註明出處，否則將追究法律責任。