???Oracle??????????????SGA/PGA???,????10g????????????ASMM????,????????ASMM?????????Oracle??????????,?ASMM??????DBA????????????;????????ASMM???????????????DBA???:????????????DB,?????????????DBA?????????????????????????????????,ASMM??????????,???????????,??????????,??????????????????;?10g release 1?10.2??????ASMM?????????????,???????ASMM????????ASMM?????startup???????????ASMM??AMM??,????????DBA????SGA/PGA?????????”??”??”???”???,???????????DBA????chemist(???????1??2??????????????)?
?????????????????ASMM?????,?????????????……
Oracle?SGA???????9i???????????,????:
Buffer Cache ????????????,???????????????
Default Pool ??????,???DB_CACHE_SIZE??
Keep Pool ??????,???DB_KEEP_CACHE_SIZE??
Non standard pool ???????,???DB_nK_cache_size??
Recycle pool ???,???db_recycle_cache_size??
Shared Pool ???,???shared_pool_size??
Library cache ??????
Row cache ???,??????
Java Pool java?,???Java_pool_size??
Large Pool ??,???Large_pool_size??
Fixed SGA ???SGA??,???Oracle???????,?????????granule?
?9i?????ASMM,???????????SGA,??????MSMM??9i???buffer cache??????????,?????????????????????????,???9i?????????????,??????????????????????????
????SGA?????:
?????shared pool?default buffer pool????????,???????????
?9i???????????(advisor),??????????
???????????????
?????????,??????
?????,?????ORA-04031??????????
ASMM?????:
??????????
????????????????
???????sga_target??
???????????,???????????
??MSMM???????:
????
????
??????
????
??????????,???????????
??????????????????,??????????ORA-04031???
ASMM???????????:1.??????sga_target???????2.???????,???:????(memory component),????(memory broker)???????(memory mechanism)3.????(memory advisor)
ASMM????????????(Automatically set),??????:shared_pool_size?db_cache_size?java_pool_size?large_pool _size?streams_pool_size;?????????????????,???:db_keep_cache_size?db_recycle_cache_size?db_nk_cache_size?log_buffer????SGA?????,????????????????,??log_buffer?fixed sga???????????????
??ASMM?????????sga_target??,???????ASMM??????????????????db_cache_size?java_pool_size???,?????????????????????,????????????????????(???)????????,Oracle?????????(granule,?SGA<1GB?granule???4M,?SGA>1GB?granule???16M)???????,??????????????buffer cache,??????????????????(granule)??????????????????????sga_target??,???????????????????(dism,???????)???ASMM?????????????statistics_level?????typical?ALL,?????BASIC??MMON????(Memory Monitor is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds)?????????????????????ASMM?????,???????????sga_target?????statistics_level?BASIC:
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2000M
sga_target big integer 2000M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2000M
SQL> alter system set statistics_level=BASIC;
alter system set statistics_level=BASIC
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
?????server parameter file?spfile??,ASMM????shutdown??????????????(Oracle???????,????????)???spfile?,?????strings?????spfile????????????????????,?:
G10R2.__db_cache_size=973078528
G10R2.__java_pool_size=16777216
G10R2.__large_pool_size=16777216
G10R2.__shared_pool_size=1006632960
G10R2.__streams_pool_size=67108864
???spfile?????????????????,???????????”???”?????,??????????”??”??
?ASMM??????????????
?????(tunable):????????????????????????????buffer cache?????????,cache????????????????,?????????? IO????????????????????????????Library cache????? subheap????,?????????????????????????????????(open cursors)?????????client??????????????buffer cache???????,???????????pin??buffer???(???????)
?????(Un-tunable):???????????????????,?????????????????,?????????????????????????large pool??????
??????(Fixed Size):???????????,???????????????????????????????????????
????????????????(memory resize request)?????????,?????:
??????(Immediate Request):???????????ASMM????????????????????????(chunk)?,??????OUT-OF-MEMORY(ORA-04031)???,????????????????????(granule)????????????????????granule,????????????,?????????????????????????????,????granule???????????????
??????(Deferred Request):???????????????????????????,??????????????granule???????????????MMON??????????delta.
??????(Manual Request):????????????alter system?????????????????????????????????????????????????granule,??????grow?????ORA-4033??,?????shrink?????ORA-4034???
?ASMM????,????(Memory Broker)????????????????????????????(Deferred)??????????????????????(auto-tunable component)???????????????,???????????????MMON??????????????????????????????????,????????????????;MMON????Memory Broker?????????????????????????MMON????????????????????????????????????????(resize request system queue)?MMAN????(Memory Manager is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases)???????????????????
?10gR1?Shared Pool?shrink??????????,?????????????Buffer Cache???????????granule,????Buffer Cache?granule????granule header?Metadata(???buffer header??RAC??Lock Elements)????,?????????????????????shared pool????????duration(?????)?chunk??????granule?,????????????granule??10gR2????Buffer Cache Granule????????granule header?buffer?Metadata(buffer header?LE)????,??shared pool???duration?chunk????????granule,??????buffer cache?shared pool??????????????10gr2?streams pool?????????(???????streams pool duration????)
??????????(Donor,???trace????)???,?????????granule???buffer cache,????granule????????????:
????granule???????granule header
?????chunk????granule?????????buffer header
???,???chunk??????????????????????metadata?
???2-4??,???granule????
??????????????????,??buffer cache??granule???shared pool?,???????:
MMAN??????????buffer cache???granule
MMAN????granule??quiesce???(Moving 1 granule from inuse to quiesce list of DEFAULT buffer cache for an immediate req)
DBWR???????quiesced???granule????buffer(dirty buffer)
MMAN??shared pool????????(consume callback),granule?free?chunk???shared pool??(consume)?,????????????????????granule????shared granule??????,???????????granule???????????,??????pin??buffer??Metadata(???buffer header?LE)?????buffer cache???
???granule???????shared pool,???granule?????shared???
?????ASMM???????????,??????????:
_enabled_shared_pool_duration:?????????10g????shared pool duration??,?????sga_target?0?????false;???10.2.0.5??cursor_space_for_time???true??????false,???10.2.0.5??cursor_space_for_time?????
_memory_broker_shrink_heaps:???????0??Oracle?????shared pool?java pool,??????0,??shrink request???????????????????
_memory_management_tracing: ???????MMON?MMAN??????????(advisor)?????(Memory Broker)?????trace???;??ORA-04031????????36,???8?????????????trace,???23????Memory Broker decision???,???32???cache resize???;??????????:
Level
Contents
0×01
Enables statistics tracing
0×02
Enables policy tracing
0×04
Enables transfer of granules tracing
0×08
Enables startup tracing
0×10
Enables tuning tracing
0×20
Enables cache tracing
?????????_memory_management_tracing?????DUMP_TRANSFER_OPS????????????????,?????????????????trace?????????mman_trace?transfer_ops_dump?
SQL> alter system set "_memory_management_tracing"=63;
System altered
Operation make shared pool grow and buffer cache shrink!!!..............
???????granule?????,????default buffer pool?resize??:
AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
/* ???0xdc9c2628??????addr */
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
/* ???????????Immediate???? */
AUTO SGA: Receiver of memory is shared pool, size=16, state=3, flg=0
/* ?????????shared pool,???,????16?granule,??grow?? */
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=106, state=4, flg=0
/* ???????Default buffer cache,????,????106?granule,??shrink?? */
AUTO SGA: Memory requested=3896, remaining=3896
/* ??immeidate request???????3896 bytes */
AUTO SGA: Memory received=0, minreq=3896, gransz=16777216
/* ????free?granule,??received?0,gransz?granule??? */
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
/* ??????????,??????inactive?? */
AUTO SGA: Init bef rsz for request 0xdc9c2628
/* ????????before-process???? */
AUTO SGA: Set rq dc9c2628 status to PENDING
/* ?request??pending?? */
AUTO SGA: 0xca000000 rem=3896, rcvd=16777216, 105, 16777216, 17
/* ???????0xca000000?16M??granule */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, a
AUTO SGA: Resize done for pool DEFAULT, 8192
/* ???default pool?resize */
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fff917964a0
AUTO SGA: Receiver of memory is shared pool, size=17, state=0, flg=0
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=105, state=0, flg=0
AUTO SGA: Memory requested=3896, remaining=0
AUTO SGA: Memory received=16777216, minreq=3896, gransz=16777216
AUTO SGA: Request 0x7fff917964a0 status is COMPLETE
/* shared pool????16M?granule */
AUTO SGA: activated granule 0xca000000 of shared pool
?????partial granule????????????trace:
AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
AUTO SGA: Receiver of memory is shared pool, size=82, state=3, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=36, state=4, flg=1
/* ????????shared pool,?????default buffer cache */
AUTO SGA: Memory requested=4120, remaining=4120
AUTO SGA: Memory received=0, minreq=4120, gransz=16777216
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
AUTO SGA: Init bef rsz for request 0xdc9c2628
AUTO SGA: Set rq dc9c2628 status to PENDING
AUTO SGA: Moving granule 0x93000000 of DEFAULT buffer cache to activate list
AUTO SGA: Moving 1 granule 0x8c000000 from inuse to quiesce list of DEFAULT buffer cache for an immediate req
/* ???buffer cache??????0x8c000000?granule??????inuse list,
???????quiesce list? */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: activated granule 0x93000000 of DEFAULT buffer cache
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
/ * ??dbwr??0x8c000000 granule????dirty buffer */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
.........................................
AUTO SGA: Rcv shared pool consuming 8192 from 0x8c000000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 90112 from 0x8c002000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 24576 from 0x8c01a000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 65536 from 0x8c022000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 131072 from 0x8c034000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 286720 from 0x8c056000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 98304 from 0x8c09e000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 106496 from 0x8c0b8000 in granule 0x8c000000; owner is DEFAULT buffer cache
.....................
/* ??shared pool????0x8c000000 granule??chunk,
??granule?owner????default buffer cache */
AUTO SGA: Imm xfer 0x8c000000 from quiesce list of DEFAULT buffer cache to partial inuse list of shared pool
/* ???0x8c000000 granule?default buffer cache????????shared pool????inuse list */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, 20a
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fffe9bcd0e0
AUTO SGA: Receiver of memory is shared pool, size=83, state=0, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=35, state=0, flg=1
AUTO SGA: Memory requested=4120, remaining=0
AUTO SGA: Memory received=14934016, minreq=4120, gransz=16777216
AUTO SGA: Request 0x7fffe9bcd0e0 status is COMPLETE
/* ????partial transfer?? */
?????partial transfer??????DUMP_TRANSFER_OPS????0x8c000000 partial granule???????,?:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump DUMP_TRANSFER_OPS 1;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_21482.trc
=======================trace content==============================
GRANULE SIZE is 16777216
COMPONENT NAME : shared pool
Number of granules in partially inuse list (listid 4) is 23
Granule addr is 0x8c000000 Granule owner is DEFAULT buffer cache
/* ?0x8c000000 granule?shared pool?partially inuse list,
?????owner??default buffer cache */
Granule 0x8c000000 dump from owner perspective
gptr = 0x8c000000, num buf hdrs = 1989, num buffers = 156, ghdr = 0x8cffe000
/ * ?????granule?granule header????0x8cffe000,
????156?buffer block,1989?buffer header */
/* ??granule??????,??????buffer cache??shared pool chunk */
BH:0x8cf76018 BA:(nil) st:11 flg:20000
BH:0x8cf76128 BA:(nil) st:11 flg:20000
BH:0x8cf76238 BA:(nil) st:11 flg:20000
BH:0x8cf76348 BA:(nil) st:11 flg:20000
BH:0x8cf76458 BA:(nil) st:11 flg:20000
BH:0x8cf76568 BA:(nil) st:11 flg:20000
BH:0x8cf76678 BA:(nil) st:11 flg:20000
BH:0x8cf76788 BA:(nil) st:11 flg:20000
BH:0x8cf76898 BA:(nil) st:11 flg:20000
BH:0x8cf769a8 BA:(nil) st:11 flg:20000
BH:0x8cf76ab8 BA:(nil) st:11 flg:20000
BH:0x8cf76bc8 BA:(nil) st:11 flg:20000
BH:0x8cf76cd8 BA:0x8c018000 st:1 flg:622202
...............
Address 0x8cf30000 to 0x8cf74000 not in cache
Address 0x8cf74000 to 0x8d000000 in cache
Granule 0x8c000000 dump from receivers perspective
Dumping layout
Address 0x8c000000 to 0x8c018000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c018000 to 0x8c01a000 not in this pool
Address 0x8c01a000 to 0x8c020000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c020000 to 0x8c022000 not in this pool
Address 0x8c022000 to 0x8c032000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c032000 to 0x8c034000 not in this pool
Address 0x8c034000 to 0x8c054000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c054000 to 0x8c056000 not in this pool
Address 0x8c056000 to 0x8c09c000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c09c000 to 0x8c09e000 not in this pool
Address 0x8c09e000 to 0x8c0b6000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c0b6000 to 0x8c0b8000 not in this pool
Address 0x8c0b8000 to 0x8c0d2000 in sga heap(1,3) (idx=1, dur=4)
???????granule?????shared granule??????,?????????buffer block,????1?shared subpool??????durtaion?4?chunk,duration=4?execution duration;??duration?chunk???????????,??extent???quiesce list??????????????free?execution duration?????????????,??????duration???extent(??????extent????granule)???????
?????????????ASMM?????????,????:
V$SGAINFODisplays summary information about the system global area (SGA).
V$SGADisplays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTATDisplays detailed information about the SGA.
V$SGA_DYNAMIC_COMPONENTSDisplays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_DYNAMIC_FREE_MEMORYDisplays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$SGA_RESIZE_OPSDisplays information about the last 400 completed SGA resize operations.
V$SGA_CURRENT_RESIZE_OPSDisplays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.
V$SGA_TARGET_ADVICEDisplays information that helps you tune SGA_TARGET.
?????????shared pool duration???,?????????
