《深入浅出Oracle：DBA入门、进阶与诊断案例》学习笔记第4章-CSDN博客

虽然这本书已经买了一年多了，但是只看了几章而已。

现在把阅读过程中遇到的一些问题和需要注意的地方记录下来。

1. what if sga_target > sga_max_size

SGA_target 不能超过 SGa_max_size, 如果超过，则会出错。

idle> alter system set sga_target = 800M;
alter system set sga_target = 800M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

idle>

2. the difference of:

show parameter log_buffer; and
show sga displayed redo buffers

Redo Buffers 指日志缓冲区分配的内存大小，这个参数值通常笔log_buffers 参数设置略大。。

这是因为Log Buffer 并非按照块大小分配，在内存中通常需要设置保护页对Log Buffer 进行保护。当前的分配和使用具体的信息还可以通过V$sgastat 视图查询得到。

3. processes
PMON
SMON
MMON
DBWR
LGWR
CKPT
ARCn
MMAN
MMNL

4. In Oracle 10g, select *from v$sga; the value column is #??

5. shmmax。 In Solaris, in /etc/system
in Linux, in /proc/sys/kernel/shmmax in runtime， and in the file /etc/sysctl.conf

这不是共享内存的大小，而是单个内存共享段最大值。如果该值小于oracle
SGA,SGA仍可创建成功，但会被分配到到多个内存共享段。通常，通过调整shmmax，将SGA限制在一个共享内存段中。

In Windows system，由于系统采用多线程服务器，（all services are threads of one process），
所以不存在内存共享问题。

6. ipcs -sa

to check 创建的共享内存段
ipcs - provide information on ipc facilities

pmap - report memory map of a process

without modify /etc/sysctl.conf, it will record the following warning when Oracle starting up:

WARNING: EINVAL creating segment of size 0x........
fix shm parameters in /etc/sysctl.conf or equivalent

On Solaris， also see warning like:

WARNING: not enough physical memory for SHM_SHARE_MMU segment of size 0x......

这通常是因为SGA设置过大，超过了物理内存而导致的。

有时候也因为Oracle 异常关闭，后台进程未正常退出，共享内存未及时释放引起的。对于这种情况，可以通过ipcs命令找到共享内存段id（shared momery id），然后通过ipcrm命令强制释放该共享内存段。

8. in Oracle 9iR1，动态减小内存设置，可能触发一些bug，在繁忙的系统中，缩减各组件内存应当慎重。

在动态修改内存参数时，存在一些常见的限制：
1. 修改的内存大小必须是颗粒（Granule）大小的整数倍，否则会自动向上取整。
2. SGA总大小不能超过sga_max_size
3. SGA最低配置为3个颗粒(Granule)，一个颗粒用于固定的SGA（包括重中缓冲区），一个粒度用于缓冲区高速缓冲，一个粒度用于共享池。

通过OEM直观了解

9. db_cache_size and db_cache_advice (on/off/ready)

select id, name, block_size,size_for_estimate sfe,size_factor sf, estd_physical_read_factor eprf, estd_physical_reads epr from v$db_cache_advice;

10. shared_pool_size and statistics_level(typical/basic/all)

select statistics_name, session_status, system_status,activation_level, session_settable from v$statistics_level
idle> /

STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Timed Statistics ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Active Session History ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO

16 rows selected.

11. db_cache_size 受db_cache_advice 控制， timed statistics 受 timed_statistics控制，其他都受statistics_level 参数控制。

12.
select shared_pool_size_for_estimate spfe, shared_pool_size_factor spsf, estd_lc_size, estd_lc_memory_objects elmo, estd_lc_time_saved elts, estd_lc_time_saved_factor eltsf, estd_lc_memory_object_hits elmoh from v$shared_pool_advice
idle> /

SPFE SPSF ESTD_LC_SIZE ELMO ELTS ELTSF ELMOH
---------- ---------- ------------ ---------- ---------- ---------- ----------
60 .4286 19 2598 283 .9529 35321
76 .5429 34 3963 289 .9731 36102
92 .6571 49 5314 291 .9798 36439
108 .7714 64 7374 297 1 36781
124 .8857 67 7793 297 1 36781
140 1 67 7793 297 1 36781
156 1.1143 67 7793 297 1 36781
172 1.2286 67 7793 297 1 36781
188 1.3429 67 7793 297 1 36781
204 1.4571 67 7793 297 1 36781
220 1.5714 67 7793 297 1 36781
236 1.6857 67 7793 297 1 36781
252 1.8 67 7793 297 1 36781
268 1.9143 67 7793 297 1 36781
284 2.0286 67 7793 297 1 36781

15 rows selected.

13. 当进行动态修改参数时候，修改session会处于等待状态，等待事件为 background parameter adjustment;

select sid,seq#,event, seconds_in_wait, state from v$session_wait where sid =

调整时间及其漫长，从v$lock 视图中，还可以看到相关锁定信息

select * from v$lock;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
607BA03C 607BA050 165 XR 4 0 1 0 6150 0
607BA098 607BA0AC 165 CF 0 0 2 0 6150 0
607BA150 607BA164 165 RS 25 1 2 0 6139 0
607BA208 607BA21C 166 RT 1 0 6 0 6139 0
607BA264 607BA278 167 PW 1 0 3 0 6138 0
607BA378 607BA38C 167 MR 1 0 4 0 6139 0
607BA3D4 607BA3E8 167 MR 2 0 4 0 6139 0
607BA430 607BA444 167 MR 3 0 4 0 6139 0
607BA48C 607BA4A0 167 MR 4 0 4 0 6139 0
607BA4E8 607BA4FC 167 MR 5 0 4 0 6139 0
607BA544 607BA558 167 MR 6 0 4 0 6139 0
607BA5A0 607BA5B4 167 MR 201 0 4 0 6139 0
607BA6B4 607BA6C8 164 TS 3 1 3 0 6135 0

13 rows selected.

锁定类型为PE，即 Kernel Service parameter enqueue, 在修改参数时候需要获得该锁定。

14. large pool 为共享服务进程（MTS），并行计算即RMAN提供内存。

15。

10g 自动共享内存管理，即（Automatic Shared Memory Management ）ASMM.

在OLTP下，Buffer Cache 会获取大部分内存以达到良好的IO性能。当系统运行DSS批处理时，内存会自动转移给large pool，以便并行查询获得更多内存。

可以自动分配内存的包括：
Buffer CAche
Shared Pool
Java Pool
Large Pool

sga_target 为非零值，则oracle将启动自动内存管理。并且需要 statistics_level 为 typical 或者all

oracle 会根据系统运行情况自动调整这些内存大小，并记录在spfile中。进程重启时，不会丢失之前的调整结果。

以下几个初始化参数需要手工配置;

1. 非标准BLOCK_SIZE 的Cache
2. Keep/Recyle buffer cache
3. Redo log buffer
4。 Stream Pool

引入了新的后台进程MMAN（Memory manager）,用以调整内存组建。
动态调整依据来源于不间断的收集的内存建议。

16. 不想使用自动内存调整，只需要把sga_target 设置为 0. 当前的内存组建值会被计入spfile，作为手动调整的起始值。

17。当设置了sga_max_size, 启用了自动内存调整之后，相关内存值会处于未设置状态。

select name, value from v$parameter where name in ('java_pool_size', 'db_cache_size','large_pool_size','stream_pool_size','shared_pool_size')
idle> /

NAME VALUE
------------------------------ ------------------------------
shared_pool_size 104857600
large_pool_size 0
java_pool_size 0
db_cache_size 0

而真正决定大小的是由一组新引入的参数决定的：

select x.ksppinm name, y.ksppstvl value, x.ksppdesc descib from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm like '%pool_size%';

NAME VALUE
------------------------------ ------------------------------
DESCIB
------------------------------------------------------------------------------------------------------------------------
_NUMA_pool_size Not specified
aggregate size in bytes of NUMA pool

__shared_pool_size 146800640
Actual size in bytes of shared pool

shared_pool_size 104857600
size in bytes of shared pool

__large_pool_size 4194304
Actual size in bytes of large pool

large_pool_size 0
size in bytes of large pool

__java_pool_size 4194304
Actual size in bytes of java pool

java_pool_size 0
size in bytes of java pool

__streams_pool_size 0
Actual size in bytes of streams pool

streams_pool_size 0
size in bytes of the streams pool

_io_shared_pool_size 4194304
Size of I/O buffer pool from SGA

_backup_io_pool_size 1048576
memory to reserve from the large pool

global_context_pool_size
Global Application Context Pool Size in Bytes

olap_page_pool_size 0
size of the olap page pool in bytes

13 rows selected.

这些由两个下划线开头的参数决定了当前的SGA分配，也是动态管理调整的参数。这些参数的更改会被记录到spfile中。

18. Oracle 新增动态视图v$sga_dynamic_components, 可以看到动态组建的调整时间和调整类型。

select component, current_size, min_size, last_oper_type, last_oper_mode, to_char(last_oper_time, 'yyyy-mm-dd hh24:mi:ss') lot from v$sga_dynamic_components
idle> /

COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
---------------------------------------------------------------- ------------ ---------- ------------- --------- -------------------
shared pool 146800640 142606336 GROW IMMEDIATE 2010-02-24 20:33:57
large pool 4194304 4194304 STATIC
java pool 4194304 4194304 STATIC
streams pool 0 0 STATIC
DEFAULT buffer cache 373293056 373293056 SHRINK IMMEDIATE 2010-02-24 20:33:57
KEEP buffer cache 0 0 STATIC
RECYCLE buffer cache 0 0 STATIC
DEFAULT 2K buffer cache 0 0 STATIC
DEFAULT 4K buffer cache 0 0 STATIC
DEFAULT 8K buffer cache 0 0 STATIC
DEFAULT 16K buffer cache 0 0 STATIC
DEFAULT 32K buffer cache 0 0 STATIC
ASM Buffer Cache 0 0 STATIC

13 rows selected.

19. PGA 管理，program global area, 是服务进程（sever process）使用的一块包含数据和控制信息的内存区域。是非共享内存，在服务器进程启动时分配（在系统运行时，排序、连接等操作也可能进一步分配PAG内存），并为server process 排他访问。

PGA的内容依专用和共享服务器模式而不同，但通常来说，PGA中包含私有SQL区（存储绑定信息，运行时结构等）和session信息等内容。

所有服务进程分配的PGA总和通常被称为PGA合计（Aggregated PGA）.

在Oracle 8i中，参数有：

1. sort_area_size
2. hash_area_size
3. bitmap_merge_size
4. create_bitmap_area_size

从Oracle 9i 开始，Oracle 提供了一种PGA内存管理的新方法，自动化SQL执行内存管理，（Automated SQL Execution Memory Management）.使用这个新特性，Oracle 可以自动调整SQL内存区，而不用关闭数据库，这一改进大大简化了DBA 的工作，同时也提高oracle 数据库性能。

引入新的初始化参数：

1. pga_aggregate_target：所以session总计可以使用的最大PGA。可以被动态修改，10M-（4096G-1） bytes

2. workarea_size_policy （auto/manual）: 用于开关PGA内存自动管理功能。缺省值为auto

在Oracle 9i时，PAG_AGGREGATE_TARGET 参数仅对专用服务器模式下的专属连接有效。对共享服务器无效。

从Oracle 10g 开始，两种连接模式都有效。

PGA_aggregate_target 同时限制全局PGA分配和私有工作区内存分配。

1. 对于串行操作，单个SQL能够使用的PGA内存按照以下原则分配：

min（5% pga_aggregate_target, 100M）

2. 对于并行操作，
30% pga_aggregate_target /dop ( dop = degree of parallelism 并行度)

可调整内存（tunnable memory size ）是由SQL工作区使用的，其余部分是不可调整内存（untunnable memory size）

启用了自动PGA调整之后，Oracle仍需要遵循以下原则：

untunnable memory size + tunnable memory size <= PGA_aggregate_target
数据库系统只能控制部分内存分配，如果可调整部分太小，则Oracle 永远也不会强制启用这个等式。

另外，PGA_aggregate_target 参数在CBO优化器下，对于sql执行计划会产生影响。Oracle 在评估执行计划时，会根据PGA_aggregate_target 参数评估在Sort，hash-join，或者bitmap操作时，能够使用的最大或者最小内存，从而选择最优方案。

Oracle 建议：
1. 在OLTP 系统中：
pga_aggregate_target =( < total physical memory > * 80%) * 20%
2. 在DSS系统中
pga_aggregate_target = ( * 80% ) * 50%

也就是说，在一个单纯的数据库服务器中，通常需要保留20%的物理内存给操作系统使用，剩余的80%可以分配给Oracle 使用。
Oracle的内存分为pga和sga
pga可以占oracle消耗总内存的20% （OLTP）～ 50% （DSS）

在某些OS上，单个进程使用的真实内存可能远大于在oracle中看到的PGA大小。如AIX。

SQL在工作区是3种方式执行：

1. optimal （最优方式）：指所有的处理可以在内存中完成。
2. Onepass ：大部分操作在内存中完成，但是需要到硬盘操作，
3.Multipass：大量操作需要产生硬盘交互，性能极差。

通常对于PGA的优化目标，就是使得Optimal的执行量高，也就是尽量在内存中所有排序等操作，同时使Multipass操作尽量低，

也即是期望实现如下目标：

1. workarea execution -optimal > = 90%
2. workarea exectuion - mulitpass = 0%

1 select name, value, 100 *
2 (value / decode ((select sum(value) from v$sysstat where name like 'workarea executions%'), 0, NULL,
3 (select sum(value) from v$sysstat where name like 'workarea executions%'))
4 ) pct
5* from v$sysstat where name like 'workarea exections%'
idle> /

no rows selected

idle>

SELECT object_NAME FROM DBA_objects WHERE object_NAME LIKE 'V%STAT' and object_name not like 'V/_%' escape '/'
idle> /

OBJECT_NAME
----------------------------------------------------------------------------------------------------
V$PGASTAT
V$SESSTAT
V$MYSTAT
V$SYSSTAT
V$OSSTAT
V$FILESTAT
V$TEMPSTAT
V$FLASHBACK_DATABASE_STAT
V$ROLLSTAT
V$UNDOSTAT
V$PQ_SESSTAT
V$PQ_SYSSTAT
V$SGASTAT
V$WAITSTAT
V$PQ_TQSTAT
V$PX_SESSTAT
V$PX_PROCESS_SYSSTAT
V$SEGSTAT
V$ENQUEUE_STAT
V$ASM_DISKGROUP_STAT
V$ASM_DISK_STAT
V$LOADPSTAT
V$LOADISTAT

23 rows selected.

20. 引入新视图： V$PGASTAT.

select sql_text, operation_type, policy, last_memory_used / 1024/1024, last_execution,last_tempseg_size from v$sql l, v$sql_workarea a where l.hash_value = a.hash_value and sql_text = 'select distinct *from dba_objects where rownum < 5000000';

SQL_TEXT
----------------------------------------------------------------------------------------------------
OPERATION_TYPE
--------------------------------------------------------------------------------
POLICY LAST_MEMORY_USED/1024/1024
---------------------------------------- --------------------------
LAST_EXECUTION LAST_TEMPSEG_SIZE
---------------------------------------- -----------------
select distinct *from dba_objects where rownum < 5000000
HASH-JOIN
AUTO .634765625
OPTIMAL

select name, value / 1024/1024 MB from v$pgastat where name in ('aggregate PGA target parameter', 'global memory bound')
idle> /

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound 2

实际上，100MB的上限是受到另外一个隐含参数的控制，该参数为_pga_max_size. 该参数的缺省值为200M，单进程串行操作pga上限不能超过其 1/2

对于PGA的控制，还有一些列参数：

idle> @GetParDescrb
Enter value for par: smm
old 5: and x.ksppinm like '%&par%'
new 5: and x.ksppinm like '%smm%'

NAME VALUE
------------------------------ ------------------------------
DESCRIB
------------------------------------------------------------
_smm_auto_min_io_size 56
Minimum IO size (in KB) used by sort/hash-join in auto mode

_smm_auto_max_io_size 248
Maximum IO size (in KB) used by sort/hash-join in auto mode

_smm_auto_cost_enabled TRUE
if TRUE, use the AUTO size policy cost functions

_smm_control 0
provides controls on the memory manager

_smm_trace 0
Turn on/off tracing for SQL memory manager

_smm_min_size 128
minimum work area size in auto mode

_smm_max_size 6144
maximum work area size in auto mode (serial)

_smm_px_max_size 15360
maximum work area size in auto mode (global)

_smm_retain_size 0
work area retain size in SGA for shared server sessions (0 f
or AUTO)

_smm_bound 0
overwrites memory manager automatically computed bound

_smm_advice_log_size 0
overwrites default size of the PGA advice workarea history l
og

_smm_advice_enabled TRUE
if TRUE, enable v$pga_advice

_smm_freeable_retain 5120
value in KB of the instance freeable PGA memory to retain

_smm_isort_cap 102400
maximum work area for insertion sort(v1)

引入动态优化建议视图： V$pga_target_advice and v$pga_target_advice_histogram

v$pga_target_advice 通过对不同PGA设置进行评估，给出在不同设置下的PGA命中率和OverAlloc 等信息：

select pga_target_for_estimate/1024/1024 pgamb, pga_target_factor tf,estd_pga_cache_hit_percentage, estd_overalloc_count from v$pga_target_advice
idle> /

PGAMB TF ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ---------- ----------------------------- --------------------
15 .5 100 1
22.5 .75 100 1
30 1 100 1
36 1.2 100 1
42 1.4 100 1
48 1.6 100 0
54 1.8 100 0
60 2 100 0
90 3 100 0
120 4 100 0
180 6 100 0
240 8 100 0

12 rows selected.

select pga_target_factor fc, low_optimal_size / 1024 low, round(high_optimal_size / 1024 ) high, estd_optimal_executions estd_opt, estd_onepass_executions estdop, estd_multipasses_executions estd_mp, estd_total_executions estd_exec from v$pga_target_advice_histogram where pga_target_factor = 0.5 and estd_total_executions > 0
idle> /

FC LOW HIGH ESTD_OPT ESTDOP ESTD_MP ESTD_EXEC
---------- ---------- ---------- ---------- ---------- ---------- ----------
.5 512 1024 3 0 0 3
.5 2 4 486 0 0 486

对于RAM小于1G 的系统，dedicated 模式下，通常建议 oracle 的SGA 不超过 1/2 物理内存。

dbms_system 包的使用。