Oracle 初始化参数&性能视图

原创

Oracle小混子 2013-08-21 16:43:43 ©著作权

文章标签 Oracle Oracle初始化参数 Oracle性能视图 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者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 .××× 0

1304 2 5517 .××× 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 &nbs,p; 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

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

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

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