Oracle 初始化参数&性能视图

来源：互联网发布：c 查询网络北京时间编辑：程序博客网时间：2024/05/21 06:13

标签：oracle初始化参数性能视图

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://leonarding.blog.51cto.com/6045525/1143571

《Oracle初始化参数&性能视图》

1.数据库版本

LEO1@LEO1>select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux:Version 11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

2.设置memory_target参数，并通过v$memory_target_advice分析数据库的最佳内存大小

Memory_target：1.是oracle11g中的一个内存调整参数，11g对自动化管理内存方面又继续加强了，原来10g中可以对SGA进行自动管理与分配，11g即可以自动管理SGA，又可以自动管理PGA，对这两部分进行综合管理，自动调整所有内存区的大小。11g中默认为0

现在把这几个参数语法列举一下，这是静态参数需要重启数据库生效

alter systemset memory_max_target= 1000m scope=spfile;
alter system set memory_target= 1000m scope=spfile;
alter system set sga_max_size=600m scope=spfile;
alter system set pga_aggregate_target=400m scope=spfile;

2.memory_max_target是设定 Oracle 能占物理内存多大空间，一个是 Oracle SGA区最大能占多大内存空间+PGA区多大空间，memory_max_target是memory_target上限值，如果只设置了memory_max_target没有设置memory_target，则Oracle认为memory_target=0不使用内存自动管理。

3.如果只设置memory_target，没有设置memory_max_target，则Oracle自动将memory_max_target设置为memory_target。

4.如果同时设置这两个值，则memory_target的上限值为memory_max_target。

这是我的数据库上的参数值

LEO1@LEO1> showparameter memory_max_target

NAME TYPE VALUE

----------------------------------------------- ------------------------------

memory_max_target big integer 652M

LEO1@LEO1> showparameter memory_target

NAME TYPE VALUE

----------------------------------------------- ------------------------------

memory_target big integer 652M

5. 10g的sga_max_size 是动态分配 Shared Pool Size,database buffer cache,largepool,java pool，redo log buffer大小的，根据 Oracle 运行状态来重新分配 SGA各内存区大小。 PGA 在 10g中需要单独设定（即手工管理）。

实验

下面我们通过以下的几个命令来让大家清楚memory_target的设置与PGA和SGA的关系

（1）memory_target设置为非0值

Memory_Target=SGA_TARGET+PGA_AGGREGATE_TARGET ，大小等于memory_max_size一致。

sga_target和pga_aggregate_target都设置了大小，则这两个参数将做为最小起始值

sga_target 设置大小， pga_aggregate_target 没有设置大小

那么 pga_aggregate_target 初始化值 =memory_target-sga_target

sga_target 没有设置大小， pga_aggregate_target 设置大小

那么 sga_target 初始化值 =memory_target-pga_aggregate_target

sga_target 和pga_aggregate_target都没有设置大小 Oracle 11g将根据数据库运行状态自动分配大小。但在数据库启动时会有一个固定比例来分配：

sga_target =memory_target *60% pga_aggregate_target=memory_target *40%

（2）memory_target没有设置或等于0（11g中默认为0）

11g中默认为0则初始状态下取消了 memory_target的作用，完全和10g在内存管理上一致，完全向下兼容。

（也有三种情况来对 SGA和 PGA 的大小进行分配）

SGA_TARGET设置值，则自动调节 SGA 中的 shared pool,buffer cache,redo logbuffer,java pool,larger pool内存区，PGA则依赖 pga_aggregate_target 的大小单独设置。 sga和 pga不能自动增长和自动缩小。

SGA_target和PGA_AGGREGATE_TARGET 都没有设置，SGA中的各内存区大小都要明确设定，不能自动调整各内存区大小。PGA 不能自动增长和收缩。

memory_max_target设置而memory_target =0 这种情况和10g一样不使用内存自动管理

LEO1@LEO1> showparameter target

NAME TYPE VALUE

----------------------------------------------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 652M

memory_target big integer 652M

parallel_servers_target integer 8

pga_aggregate_target big integer 0

sga_target big integer 0

现在我们看到sga_target和pga_aggregate_target的值都是0，由oracle自动调整大小，memory_target和memory_max_target的大小是652M

LEO1@LEO1>select * from v$memory_target_advice; 分析数据库最佳内存大小

MEMORY_SIZE MEMORY_SIZE_FACTORESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

----------------------------- ------------ ------------------- ----------

489 .75 5522 1.0002 0

652 1 5521 1 0

815 1.25 5518 .9994 0

978 1.5 5517 .9993 0

1141 1.75 5517 .9992 0

1304 2 5517 .9992 0

MEMORY_SIZE：oracle建议的内存大小

MEMORY_SIZE_FACTOR：内存基线因子，0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2

ESTD_DB_TIME：Forcurrent memory size (MEMORY_SIZE_FACTOR = 1), the amount of database time requiredto complete the current workload. For a proposed memory size, the estimatedamount of database time that would be required if the MEMORY_TARGET parameterwere changed to the proposed size.（官方文档解释）

当内存基线因子为1时，完成当前数据库工作量所需要的所有数据库时间（即所有用户消耗的数据库时间），这是一个建议值，它会根据memory_target参数的改变而改变

ESTD_DB_TIME_FACTOR：Fora proposed memory size, ratio of estimated database time to current databasetime（官方文档解释）

消耗数据库时间的比例因子

VERSION：Versionnumber of this recommendation (this snapshot of the V$MEMORY_TARGET_ADVICE view

v$memory_target_advice这个视图快照的版本号

1.当memory_target=489M时，ESTD_DB_TIME=5522

2.当memory_target=652M时，ESTD_DB_TIME=5521 当前值

3.当memory_target=815M时，ESTD_DB_TIME=5518

4.当memory_target=978M时，ESTD_DB_TIME=5517

5.当memory_target=1141M时，ESTD_DB_TIME=5517

6.当memory_target=1304M时，ESTD_DB_TIME=5517

从如上的系统资源消耗情况来看，memory_target=489M是之前652M的四分之三，但ESTD_DB_TIME才增加了1性价比非常高，我们可以把memory_target=修改成489M，节约我们的内存资源

LEO1@LEO1>alter system set memory_max_target=489m scope=spfile;

System altered.

LEO1@LEO1>alter system set memory_target=489m scope=spfile;

System altered.

LEO1@LEO1> showparameter target

NAME TYPE VALUE

----------------------------------------------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 652M

memory_target big integer 652M

parallel_servers_target integer 8

pga_aggregate_target big integer 0

sga_target big integer 0

现在只是修改了spfile参数文件的内容，需要重启数据库才生效

LEO1@LEO1>shutdown immediate

ORA-01031:insufficient privileges 权限不足

LEO1@LEO1> conn/ as sysdba 切换sys用户

Connected.

SYS@LEO1>shutdown immediate 关闭实例

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SYS@LEO1>startup

ORACLE instancestarted.

Total SystemGlobal Area 513585152 bytes

Fixed Size 2214856 bytes

Variable Size 314573880 bytes

DatabaseBuffers 188743680 bytes

Redo Buffers 8052736 bytes

Database mounted.

Database opened.

SYS@LEO1> showparameter target

NAME TYPE VALUE

----------------------------------------------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 492M oracle做了一点点修正

memory_target big integer 492M

parallel_servers_target integer 8

pga_aggregate_target big integer 0

sga_target big integer 0

小结：我们可以根据v$memory_target_advice视图来合理的调整memory_target的内存值，提高资源利用率。

3.通过调整参数optimizer_index_cost_adj的大小，演示SQL产生不同执行计划

Optimizer_index_cost_adj：这个参数是用于CBO在计算索引成本时的权重修正值

Optimizer_index_cost_adj的值越高，使用索引的几率越低，CBO倾向于全表扫描

Optimizer_index_cost_adj的值越低，使用索引的几率越高，CBO倾向于走索引

Optimizer_index_cost_adj默认值为100

SYS@LEO1> showparameter optimizer_index_cost_adj;

NAME TYPE VALUE

----------------------------------------------- ------------------------------

optimizer_index_cost_adj integer 100

实验

LEO1@LEO1> droptable leo1 purge; 清理环境

Table dropped.

LEO1@LEO1>create table leo1 as select * from dba_objects where rownum<200;创建leo1表199条记录

Table created.

LEO1@LEO1>create index idx_leo1 on leo1(object_id); 创建idx_leo1 B-tree索引

Index created.

我们来看一下当optimizer_index_cost_adj=100时执行计划

LEO1@LEO1> setautotrace trace explain

LEO1@LEO1>select * from leo1;

Execution Plan

----------------------------------------------------------

Plan hash value:2716644435

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 199 | 41193 | 3 (0)| 00:00:01 |

| 1 |TABLE ACCESS FULL | LEO1 | 199 | 41193 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

走的是全表扫描，此时我们只发生了19次一致性读，只扫描数据块没有扫描索引块

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

19 consistent gets 产生19个一致性读

0 physical reads

0 redo size

20823 bytes sent via SQL*Net to client

667 bytes received via SQL*Net from client

15 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

199 rows processed

LEO1@LEO1>alter session set optimizer_index_cost_adj=10; 从100修改成10

Session altered.

当这个参数越小时，CBO更倾向于走索引

LEO1@LEO1>select * from leo1 where object_id<=800;

Execution Plan

----------------------------------------------------------

Plan hash value:1434365503

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 199 | 41193 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO1 | 199| 41193 | 1 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |IDX_LEO1 | 199 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走的是索引，此时我们发生了32次一致性读，先扫描索引块然后根据rowid扫描数据块

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

32 consistent gets 产生32个一致性读

0 physical reads

0 redo size

20823 bytes sent via SQL*Net to client

667 bytes received via SQL*Net from client

15 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts(disk)

199 rows processed

小结：相同结果集我们在走索引的时候比全表扫描产生的一致性读要多，说明发生的逻辑IO次数更多了，消耗的系统IO资源更多了，这是不合理的，我们应该在生产中进行避免。

4.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT不同的值，演示对SQL效率的影响

db_file_multiblock_read_count：这个初始化参数叫做“一次读多少个数据块or一次多块读可以读几个数据块”。这个参数值并不是无限大的，大多数平台下的oracle都是128。一般oracle block size =8k

128*8=1M，也就是说1M是大多数操作系统一次最大IO的限制，如果还有其他限制要从这1M里面扣除，初始化参数db_file_multiblock_read_count的最大值之所以定为128，也是为了保守策略。

场景：

（1）全表扫描FTS（FULL TABLE SCAN）：这时oracle支持多块读

（2）索引快速全扫描IFFS（INDEX FAST FULL SCAN）：索引并行读取的时候也支持多块读

（3）OLAP：可以设置的大一些，但不是越大越好

（4）还会受到操作系统IO本身的限制

实验

LEO1@LEO1> droptable leo2 purge; 清理环境

Table dropped.

LEO1@LEO1> droptable leo3 purge;

Table dropped.

LEO1@LEO1>create table leo2 as select * from dba_objects;创建leo2表

Table created.

LEO1@LEO1>create table leo3 as select * from dba_objects;创建leo3表

Table created.

LEO1@LEO1>create index idx_leo3 on leo3(object_id); 创建idx_leo3索引

Index created.

LEO1@LEO1>select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_namein ('LEO2','LEO3','IDX_LEO3');

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024

-------------------------------------------------------------------------------------------

IDX_LEO3 INDEX 2

LEO3 TABLE 9

LEO2 TABLE 9

LEO2表大小是9M（段头+数据），LEO3表大小9+2=11M（表+索引）

LEO1@LEO1>alter session set db_file_multiblock_read_count=16; 设置一次多块读可以读16个数据块

Session altered.

LEO1@LEO1> showparameter db_file_multiblock_read_count 16块*8k=128k

NAME TYPE VALUE

----------------------------------------------- ------------------------------------------

db_file_multiblock_read_count integer 16

LEO1@LEO1>select count(*) from leo2;

Execution Plan

----------------------------------------------------------

Plan hash value:3963694794

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 233 (1)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL | LEO2 | 73470 | 233 (1)| 00:00:03 |

-------------------------------------------------------------------

Statistics

----------------------------------------------------------

210 recursive calls

0 db block gets

1119 consistent gets

1025 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

一般执行2遍，统计信息会稳定下来

LEO1@LEO1>select count(*) from leo2;

Execution Plan

----------------------------------------------------------

Plan hash value:3963694794

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 233 (1)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL | LEO2 | 73470 | 233 (1)| 00:00:03 |

-------------------------------------------------------------------

全表扫描会使用多块读

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1030 consistent gets 1030块*8K=8240k约等于9M

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

我们在用1030/16=64.375次，oracle需要读取64.375次IO，才能把所有记录读取完。

LEO1@LEO1> selectcount(object_id) from leo3;

Execution Plan

----------------------------------------------------------

Plan hash value:3677630522

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 40 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FAST FULL SCAN| IDX_LEO3 | 71702 | 910K| 40 (0)| 00:00:01 |

----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

236 consistent gets

160 physical reads

0 redo size

536 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

执行2遍

LEO1@LEO1> selectcount(object_id) from leo3;

Execution Plan

----------------------------------------------------------

Plan hash value:3677630522

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 40 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FAST FULL SCAN| IDX_LEO3 | 71702 | 910K| 40 (0)| 00:00:01 |

----------------------------------------------------------------------------------

索引快速全扫描会使用多块读

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

168 consistent gets 168块*8k=1344k约等于2M

0 physical reads

0 redo size

536 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

我们在用168/16=10.5次，oracle需要读取10.5次IO，才能计算出最后结果

LEO1@LEO1> setautotrace off

LEO1@LEO1>alter session set db_file_multiblock_read_count=128;设置一次多块读可以读128个数据块

Session altered.

LEO1@LEO1> showparameter db_file_multiblock_read_count 128块*8k=1M

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_file_multiblock_read_count integer 128

LEO1@LEO1> setautotrace traceonly

LEO1@LEO1>select count(*) from leo2;

Execution Plan

----------------------------------------------------------

Plan hash value:3963694794

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 185 (1)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| LEO2 | 73470 | 185 (1)| 00:00:03 |

-------------------------------------------------------------------

参数调整后cost从原来233减少到185，IO代价减少了，说明参数生效了

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1030 consistent gets 1030/128=8.04次IO

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

我们在用1030/128=8.04次，oracle从读取64.375次减少到8.04次，IO资源消耗大大降低，SQL效率提高不少。

LEO1@LEO1> selectcount(object_id) from leo3;

Execution Plan

----------------------------------------------------------

Plan hash value:3677630522

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13| 32 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FAST FULL SCAN |IDX_LEO3 | 71702 | 910K| 32 (0)| 00:00:01 |

----------------------------------------------------------------------------------

索引快速全扫描cost从原来40减少到32，IO代价也减少了，说明参数生效了

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

168 consistent gets 168/128=1.3125次

0 physical reads

0 redo size

536 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rowsprocessed

小结：oracle走索引从10.5次减少到1.3125次，IO次数大大降低，SQL读取的效率自然就提高了。
5.示例说明数据库中“会话”和“进程”之间的关系。

会话：指的是连接到数据库的服务进程数，一般分为用户会话后台进程会话应用程序会话

注意：会话是面向数据库的，我们从数据库层面看就叫会话

进程：指的是操作系统层面发起的连接数据库的进程数，不管连接成不成功就会增加一个进程

注意：进程是面向操作系统的，我们从操作系统层面看就叫进程

会话与进程的关系，官方文档推荐的设置公式

Oracle 10g 官方文档：SESSIONS=Derived: (1.1 * PROCESSES) ＋ 5

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams191.htm#i1133629

Oracle 11g 官方文档：SESSIONS=Derived: (1.5 * PROCESSES) ＋ 22

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams231.htm#REFRN10197

LEO1@LEO1> showparameter process

NAME TYPE VALUE

----------------------------------------------- ------------------------------

processes integer 150

LEO1@LEO1> showparameter session

NAME TYPE VALUE

----------------------------------------------- ------------------------------

sessions integer 248

(1.5*150)＋22=247约等于248与官方文档的公式吻合

注意在10g和11g中推荐的sessions数是不一样的，大家知道为什么嘛？留一个小题，第一个回答正确者奖励100金哦，100金哦

Process可以等于session，也可以小于session，还可以大于session

实验

LEO1@LEO1> colusername for a10

LEO1@LEO1> colosuser for a10

LEO1@LEO1> colprocess for a10

LEO1@LEO1>select sid,username,paddr,status,server,osuser,process,sql_id,machine fromv$session;

SID USERNAME PADDR STATUS SERVER OSUSER PROCESS SQL_ID MACHINE

-------------------- ---------------- -------- --------- ---------- ----------------------- ----------------------------------------------------------------

1 000000007E079BB0 ACTIVE DEDICATED oracle 2903 leonarding1.oracle.com

2 000000007E07BC30 ACTIVE DEDICATED oracle 2913 leonarding1.oracle.com

3 000000007E07DCB0 ACTIVE DEDICATED oracle 2921 leonarding1.oracle.com

4 000000007E07FD30 ACTIVE DEDICATED oracle 2929 leonarding1.oracle.com

5 000000007E081DB0 ACTIVE DEDICATED oracle 2937 leonarding1.oracle.com

6 000000007E083E30 ACTIVE DEDICATED oracle 2945 leonarding1.oracle.com

7 000000007E085EB0 ACTIVE DEDICATED oracle 2953 leonarding1.oracle.com

8 000000007E087F30 ACTIVE DEDICATED oracle 2961 leonarding1.oracle.com

10 000000007E08C030 ACTIVE DEDICATED oracle 3013 leonarding1.oracle.com

15 000000007E090130 ACTIVE DEDICATED oracle 3520 leonarding1.oracle.com

17 000000007E0921B0 ACTIVE DEDICATED oracle 3528 leonarding1.oracle.com

19 000000007E09A3B0 ACTIVE DEDICATED oracle 5226 leonarding1.oracle.com

23 SYSMAN 000000007E08E0B0 INACTIVE DEDICATEDoracle 1234 leonarding1.oracle.com

27 000000007E094230 ACTIVE DEDICATED oracle 9266 leonarding1.oracle.com

28 LEO1 000000007E0962B0 ACTIVE DEDICATED oracle 9596 71dh8u20z3x5w leonarding1.oracle.com

30 SYSMAN 000000007E098330 INACTIVE DEDICATEDoracle 1234 leonarding1.oracle.com

126 000000007E07ABF0 ACTIVE DEDICATED oracle 2907 leonarding1.oracle.com

127 000000007E07CC70 ACTIVE DEDICATED oracle 2917 leonarding1.oracle.com

128 000000007E07ECF0 ACTIVE DEDICATED oracle 2925 leonarding1.oracle.com

129 000000007E080D70 ACTIVE DEDICATED oracle 2933 leonarding1.oracle.com

130 000000007E082DF0 ACTIVE DEDICATED oracle 2941 leonarding1.oracle.com

131 000000007E084E70 ACTIVE DEDICATED oracle 2949 leonarding1.oracle.com

132 000000007E086EF0 ACTIVE DEDICATED oracle 2957 leonarding1.oracle.com

135 DBSNMP 000000007E08AFF0 INACTIVE DEDICATEDoracle 6064 leonarding1.oracle.com

139 000000007E091170 ACTIVE DEDICATED oracle 3524 leonarding1.oracle.com

141 000000007E095270 ACTIVE DEDICATED oracle 9653 leonarding1.oracle.com

147 DBSNMP 000000007E09D470 INACTIVE DEDICATED oracle 6064 leonarding1.oracle.com

148 DBSNMP 000000007E09F4F0 ACTIVE DEDICATED oracle 6064 leonarding1.oracle.com

149 000000007E08F0F0 ACTIVE DEDICATED oracle 6148 leonarding1.oracle.com

150 SYSMAN 000000007E0931F0 INACTIVE DEDICATEDoracle 1234 leonarding1.oracle.com

154 000000007E08D070 ACTIVE DEDICATED oracle 9649 leonarding1.oracle.com

155 SYSMAN 000000007E0972F0 ACTIVE DEDICATED oracle 1234 2b064ybzkwf1y leonarding1.oracle.com

我们刚开机有32个会话，下面解释常用的几个字段

Sid：会话ID

Username：数据库用户名，我们这里只有一个leo1用户的会话

Paddr：进程地址

v$session和v$process之间的关系：一个会话对应一个服务器进程，通过地址关联s.paddr=p.addr,p这样就可以通过一个进程找到一个会话；或者通过一个会话，找到它的服务器进程

status：会话状态 active 活动状态 inactive 未活动状态

server：采用的连接方式 DEDICATED 专用连接方式 SHARED 共享连接方式

osuser：操作系统用户名我们使用的都是oracle

process：会话对应的进程号

sql_id：会话执行的SQL语句

machine：从哪台机器上发出的会话连接 leonarding1.oracle.com 标识的是机器名

我们看process>session 打开另一个命令窗口执行下面的操作

[oracle@leonarding1~]$ sqlplus /nolog

SQL*Plus: Release11.2.0.1.0 Production on Sat Mar 2 10:25:31 2013

Copyright (c)1982, 2009, Oracle. All rights reserved.

@> 看到这个标识符和之前有区别了吧（LEO1@LEO1>前面的leo1指的是当前用户名后面的leo1指的数据库实例名）现在什么也没有说明客户端并没有发起连接数据库的请求，当然就没有会话啦

sqlplus/nolog 是一种匿名登录，就是先进入sqlplus环境，但没有连接数据库，进入sqlplus就会产生进程

这时我们在看一下v$session内容没有显示新会话吧，还是只有原来的leo1会话

LEO1@LEO1>select sid,username,paddr,status,server,osuser,process,sql_id,machine fromv$session;

SID USERNAME PADDR STATUS SERVER OSUSER PROCESS SQL_ID MACHINE

-------------------- ---------------- -------- --------- ---------- ----------------------- ----------------------------------------------------------------

1 000000007E079BB0 ACTIVE DEDICATED oracle 2903 leonarding1.oracle.com

2 000000007E07BC30 ACTIVE DEDICATED oracle 2913 leonarding1.oracle.com

3 000000007E07DCB0 ACTIVE DEDICATED oracle 2921 leonarding1.oracle.com

4 000000007E07FD30 ACTIVE DEDICATED oracle 2929 leonarding1.oracle.com

5 000000007E081DB0 ACTIVE DEDICATED oracle 2937 leonarding1.oracle.com

6 000000007E083E30 ACTIVE DEDICATED oracle 2945 leonarding1.oracle.com

7 000000007E085EB0 ACTIVE DEDICATED oracle 2953 leonarding1.oracle.com

8 000000007E087F30 ACTIVE DEDICATED oracle 2961 leonarding1.oracle.com

10 000000007E08C030 ACTIVE DEDICATED oracle 3013 leonarding1.oracle.com

15 000000007E090130 ACTIVE DEDICATED oracle 3520 leonarding1.oracle.com

17 000000007E0921B0 ACTIVE DEDICATED oracle 3528 leonarding1.oracle.com

19 000000007E09A3B0 ACTIVE DEDICATED oracle 5226 leonarding1.oracle.com

23 SYSMAN 000000007E08E0B0 ACTIVE DEDICATED oracle 1234 8t43xdhf4d9x2 leonarding1.oracle.com

28 LEO1 000000007E0962B0 ACTIVE DEDICATED oracle 9596 71dh8u20z3x5w leonarding1.oracle.com

30 SYSMAN 000000007E098330 INACTIVE DEDICATEDoracle 1234 leonarding1.oracle.com

126 000000007E07ABF0 ACTIVE DEDICATED oracle 2907 leonarding1.oracle.com

127 000000007E07CC70 ACTIVE DEDICATED oracle 2917 leonarding1.oracle.com

128 000000007E07ECF0 ACTIVE DEDICATED oracle 2925 leonarding1.oracle.com

129 000000007E080D70 ACTIVE DEDICATED oracle 2933 leonarding1.oracle.com

130 000000007E082DF0 ACTIVE DEDICATED oracle 2941 leonarding1.oracle.com

131 000000007E084E70 ACTIVE DEDICATED oracle 2949 leonarding1.oracle.com

132 000000007E086EF0 ACTIVE DEDICATED oracle 2957 leonarding1.oracle.com

135 DBSNMP 000000007E08AFF0 INACTIVE DEDICATEDoracle 6064 a8j39qb13tqkr leonarding1.oracle.com

139 000000007E091170 ACTIVE DEDICATED oracle 3524 leonarding1.oracle.com

147 DBSNMP 000000007E09D470 INACTIVE DEDICATEDoracle 6064 leonarding1.oracle.com

148 DBSNMP 000000007E09F4F0 ACTIVE DEDICATED oracle 6064 leonarding1.oracle.com

149 000000007E08F0F0 ACTIVE DEDICATED oracle 6148 leonarding1.oracle.com

150 SYSMAN 000000007E0931F0 INACTIVE DEDICATEDoracle 1234 leonarding1.oracle.com

154 000000007E08D070 ACTIVE DEDICATED oracle 15877 leonarding1.oracle.com

155 SYSMAN 000000007E0972F0 ACTIVE DEDICATED oracle 1234 2b064ybzkwf1y leonarding1.oracle.com

下面我们在从操作系统层面来看

[oracle@leonarding1~]$ ps -ef | grep sqlplus 有2个sqlplus进程

oracle 9596 9565 0 09:59 pts/1 00:00:00 sqlplus sqlplus leo1/leo1

oracle 16979 5229 0 10:49 pts/3 00:00:00 sqlplus sqlplus /nolog

现在我们应该明白了吧，sqlplus /nolog操作没有产生session，但是产生了process

@> exit 退出匿名登录

[oracle@leonarding1~]$ ps -ef | grep sqlplus 现在只剩下一个sqlplus进程啦

oracle 9596 9565 0 09:59 pts/1 00:00:00 sqlplus

完美呈现process>session

我们在来看process=session的情况，这种情况是最常见的，一个clientprocess发起连接请求后，数据库的server process进程响应，形成一个连接通道，这个通道就是一个会话，如果clientprocess是一个用户发起的，这个会话就叫“用户会话”。如果clientprocess是一个后台进程发起的，这个会话就叫“后台进程会话”。如果clientprocess是一个应用程序发起的，这个会话就叫“应用程序会话”。

LEO1@LEO1>select sid,username,paddr,status,server,osuser,process,sql_id,machine fromv$session;

28 LEO1 000000007E0962B0 ACTIVE DEDICATED oracle 9596 71dh8u20z3x5w leonarding1.oracle.com

一个client process发起连接请求后，就会在操作系统层面生成一个进程9596，即sqlplus发起。这在v$session中可以显示出来了

[oracle@leonarding1~]$ ps -ef | grep sqlplus

oracle 9596 9565 0 09:59 pts/1 00:00:00 sqlplus

数据库的一个server process进程响应（它们是一一对应的）

LEO1@LEO1>select s.sid,s.username,s.process,p.spid from v$session s,v$process p wheres.paddr=p.addr and p.addr='000000007E0962B0';

SID USERNAME PROCESS SPID

-------------------- ---------- ---------------------------------

28 LEO1 9596 16065

v$session v$process联合视图显示9596进程由16065进程进行响应，我们从操作系统层面来看看是不是这样

[oracle@leonarding1~]$ ps -ef | grep 16065

oracle 16065 9596 0 10:37 ? 00:00:00 oracleLEO1(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

没有问题，并且还告诉你这个会话是本地连接(LOCAL=YES)并非远程服务器连接过来的

完美呈现process=session

我们在来看process<session的情况，这种情况非常少见，一个process对应两个session

LEO1@LEO1>select s.sid,s.username,s.process,p.spid,s.paddr session_paddr,p.addrprocess_addr from v$session s,v$process p where s.paddr=p.addr ands.paddr='000000007E0962B0';

SID USERNAME PROCESS SPID SESSION_PADDR PROCESS_ADDR

-------------------- ---------- ------------------------ ------------------------------------------------ ----------------

22 LEO1 9596 16065 000000007E0962B0 000000007E0962B0

28 LEO1 9596 16065 000000007E0962B0 000000007E0962B0

我们通过SESSION_PADDR，PROCESS_ADDR字段的process地址关联，看到了一个process对连接两个session，原来我们说它们应该是1：1对应的，现在形成了1：2，这是为什么呢？

Statistics

----------------------------------------------------------

15 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

981 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

2 rows processed

仔细的筒子们发现下面打印出了SQL语句的统计信息，这个统计信息是谁来打印出来的呢？呼呼哈嘿

上面视图中是不是多出了22会话，这个会话就是打印统计信息的始作俑者。那么筒子们会问22会话和28会话是什么关系呢？

答：oracle在执行28会话的时候另外产生22会话，22会话会获取28会话的统计信息打印出来，也就是说22会话是为28会话服务的，由此产生了一对process滋生出两个session，形成process<session的情况。

6.演示通过动态视图查看某个会话的等待事件。

v$session：会话当前的各种状态和属性，例如如上我们写的一些会话属性

v$session_wait：会话当前等待事件的详细信息

v$session_event：会话的所有等待事件的详细信息

v$sesstat：会话的资源统计信息

当前会话id是28

LEO1@LEO1>select distinct sid from v$mystat;

SID

----------------

28

LEO1@LEO1>select sid,event,state from v$session_wait where sid=28; 目前没有等待事件

SID EVENT STATE

---------------------------------------------------------------------------------------------

28 SQL*Net message to client WAITED SHORT TIME

LEO1@LEO1> droptable leo4 purge; 清空环境

Table dropped.

LEO1@LEO1>create table leo4 as select * from dba_objects; 创建leo4表

Table created.

LEO1@LEO1>select count(*) from leo4; 一共72010条

COUNT(*)

-----------------

72010

LEO1@LEO1> droptable leo5 purge;

Table dropped.

LEO1@LEO1>create table leo5 as select * from dba_objects; 创建leo5表

Table created.

LEO1@LEO1> altersystem flush buffer_cache; 清空data_buffer_cache好显示等待事件

System altered.

LEO1@LEO1>select leo4.* from leo4,leo5 where leo4.object_name=leo5.object_name;

Execution Plan

----------------------------------------------------------

Plan hash value:1968989376

-----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 119K| 13M| | 1073 (1)| 00:00:13 |

|* 1 | HASH JOIN | | 119K| 13M| 2608K| 1073 (1)| 00:00:13 |

| 2 | TABLE ACCESS FULL| LEO5 | 72009 | 1758K| | 287 (1)| 00:00:04 |

| 3 | TABLE ACCESS FULL| LEO4 | 72010 | 6821K| | 287 (1)| 00:00:04 |

-----------------------------------------------------------------------------------

全表扫描会产生db file sequential read等待事件

LEO1@LEO1>begin

for leo in1..10000 loop

execute immediate'select leo4.* from leo4,leo5 where leo4.object_name=leo5.object_name';

execute immediate'alter system flush buffer_cache';

end loop;

end;

/

2 3 4 5 6 7

PL/SQL proceduresuccessfully completed.

v$session_wait显示会话28当前存在的等待事件

LEO1@LEO1>select sid,event,wait_class from v$session_wait where event like '%db filesequential read%';

SID EVENT WAIT_CLASS

-------------------------------------------------------------------

28 dbfile sequential read UserI/O

SID：28 会话号

EVENT：dbfile sequential read 等待事件名

WAIT_CLASS：UserI/O 用户操作产生的IO等待事件

v$session_event显示会话28所有的等待事件

LEO1@LEO1>select sid,event,total_waits,total_timeouts,time_waited from v$session_eventwhere sid=28;

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED

-------------------------------------------------------------------------- ------------------------- -----------

28 Disk file operations I/O 12 0 2

28 direct path sync 1 0 8

28 Data file init write 12 0 37

28 control file sequential read 24 0 0

28 control file parallel write 3 0 1

28 latch: cache buffers chains 2 0 0

28 enq: RO - fast object reuse 3 0 0

28 log file switch (private strand flushincomplete) 1 0 0

28 log file sync 15 0 7

28 db file sequential read 181415 0 1522

等待了181415次，耗时1522秒

28 db file scattered read 138 0 20

28 db file single write 1 0 0

28 direct path write 83 0 86

28 SQL*Net message to client 738 0 2

28 SQL*Net message from client 737 0 881451

28 SQL*Net break/reset to client 19 0 151

28 events in waitclass Other 60133 114 30468

v$sesstat显示会话28不同资源的统计信息

LEO1@LEO1>select * from v$sesstat where sid=28;

28 158 250

28 159 0

28 160 0

28 161 1492

28 162 0

28 163 61540

28 164 445088

28 165 0

28 166 0

28 167 0

28 168 128664

28 169 44444100

28 170 0

28 171 0

28 172 4316

小结：到此我们的Oracle 初始化参数&性能视图章节实验完毕，大家对这两方面都有了深刻的理解了吧！请牢记我们常用的性能视图帮助我们提高运维管理的质量和自动化。

好系统：合理架构＋优良SQL＋完善对象建模＋精准初始化参数（10%）

memory_target v$memory_target_advice optimizer_index_cost_adj db_file_multiblock_read_count v$process v$session

Leonarding
2013.3.2
天津&spring
分享技术~成就梦想
Blog：www.leonarding.com

0 0