【性能优化】 之性能视图及性能参数
来源：互联网 发布：怎样在淘宝买东西啊 编辑：程序博客网 时间：2024/05/16 07:19
1.设置memory_target参数，并通过 v$memory_target_advice分析数据库的最佳内存大小。<br>
2.通过调整参数optimizer_index_cost_adj的大小，演示SQL产生不同执行计划。<br>
3.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT 不同的值，演示对SQL效率的影响（sql_trace or 10046 的输出结果）<br>
4.示例说明数据库中“会话”和“进程”之间的关系。<br>
5.演示通过动态视图查看某个会话的等待事件。<br>
=============================================================================================
1.设置memory_target参数，并通过 v$memory_target_advice分析数据库的最佳内存大小。<br>
先查了一下相关参数值，发现memory_target 没有设置，默认值为0，
这时 sga_target 是有设置的，那么这时的设置
v$memory_target_advice 表中没有数据，说明这时没有使用内存自动调整？
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ -------------------- ----------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ---------------- ----------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ------------------ ----------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4912M
sga_target big integer 4912M
SQL> select * from v$memory_target_advice;
no rows selected
SQL>
调整内存参数，
我把内存参数设置成系统内存的1/2,
sga 设置为memory_target 的65%
alter system set memory_max_target=10000M scope=spfile;
alter system set memory_target=8000M scope=spfile;
alter system set sga_max_size=6000M scope=spfile;
alter system set sga_target=5200M scope=spfile;
SQL> alter system set memory_max_target=10000M scope=spfile;
System altered.
SQL> alter system set memory_target=8000M scope=spfile;
System altered.
SQL> alter system set sga_max_size=6000M scope=spfile;
System altered.
SQL> alter system set sga_target=5200M scope=spfile;
System altered.
重启服务器使参数生效
SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 6263357440 bytes
Fixed Size 2266816 bytes
Variable Size 1912604992 bytes
Database Buffers 4328521728 bytes
Redo Buffers 19963904 bytes
数据库装载完毕。
数据库已经打开。
SQL>
SQL> set linesize 400;
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ -------------------------------- ----------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6000M
sga_target big integer 5200M
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ -------------------------------- ----------
hi_shared_memory_address integer 0
memory_max_target big integer 10000M
memory_target big integer 8000M
shared_memory_address integer 0
SQL>
查询内存优化表，可以看出，这时ORACLE已给出了调整方案了。
同时也可以看到，这里的最大内存 16000 即为我操作系统中的内存总数。
从下面两个表中数据可以看到，在这个数据库中，内存调整从2G--16G，对性能来说，
都没有变化。内存的调整对性能没有什么质的变化。
SQL> set pagesize 800;
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
2000 .25 34 1 0
4000 .5 34 1 0
5000 .625 34 1 0
6000 .75 34 1 0
7000 .875 34 1 0
8000 1 34 1 0
9000 1.125 34 1 0
10000 1.25 34 1 0
11000 1.375 34 1 0
12000 1.5 34 1 0
13000 1.625 34 1 0
14000 1.75 34 1 0
15000 1.875 34 1 0
16000 2 34 1 0
14 rows selected.
SQL> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
1300 .25 39 1 35898
1950 .375 39 1 35898
2600 .5 39 1 35898
3250 .625 39 1 35898
3900 .75 39 1 35898
4550 .875 39 1 35898
5200 1 39 1 35898
5850 1.125 39 1 35898
6500 1.25 39 1 35898
7150 1.375 39 1 35898
7800 1.5 39 1 35898
8450 1.625 39 1 35898
9100 1.75 39 1 35898
9750 1.875 39 1 35898
10400 2 39 1 35898
15 rows selected.
----------------------------------------------------------------------------------------
2.通过调整参数 optimizer_index_cost_adj 的大小，演示SQL产生不同执行计划。<br>
参数说明：
OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。
这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
比如设置以下值：
Optimizer_index_cost_adj=20 ，表示索引的成本和全表扫描的成本比为1:5。
2.1 建立演示数据表：
SQL> CREATE TABLE T12 AS SELECT * FROM DBA_OBJECTS where object_id<=1000;
SQL> CREATE INDEX IDX_T12_OWNER ON T12(OWNER);
Index created
SQL>
BEGIN
dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=> NULL,
METHOD_OPT=>'for all columns size 254');
END;
SQL> SET LINESIZE 500;
SQL> SET PAGESIZE 800;
C:\Users\Administrator>sqlplus tang/sa@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 26 15:38:29 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在默认参数情况下，可以看到，查询所以数据及使用条件查询object_id<1200，走的都是全表检索。
这是正确的。
SQL> SET AUTOTRACE TRACEONLY
SQL> select * from t13;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL> select * from t13 where object_id<1200;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<1200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
2.2 设置参数值为10 ，这时ORACLE 会认为走索引的成本 更低。
SQL> alter session set optimizer_index_cost_adj=10;
Session altered.
SQL> select * from t13;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL> set linesize 400;
SQL> select * from t13 where object_id<1200;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 541349760
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T13 | 998 | 85828 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T13_ID | 998 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL>
从最后的查询可以看到，这时ORACLE走索引了。其实OBJECT_ID<1200就是全部数据。但人为的告诉ORACLE走索引更低，
这里有161个唯一值读。而全表检索也只不够是82个唯一值的读。
--------------------------------------------------------------------------------------------------------------
3.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT 不同的值，演示对SQL效率的影响（sql_trace or 10046 的输出结果）<br>
SQL> drop table t13;
Table dropped.
SQL> create table t13 as select * from dba_objects;
Table created.
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT
NAME TYPE VALUE
------------------------------ ------- --------
db_file_multiblock_read_count integer 128
SQL> SET AUTOTRACE TRACEONLY;
SQL> select count(*) from t13;
Execution Plan
----------------------------------------------------------
Plan hash value: 2598196162
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T13 | 82867 | 196 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1095 consistent gets
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
#1092/128=8.53125,约要读8.5次可以把数据读完。
SQL> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16;
System altered.
SQL> select count(*) from t13;
Execution Plan
----------------------------------------------------------
Plan hash value: 2598196162
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 247 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T13 | 82867 | 247 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1095 consistent gets
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
SQL>
#1092/16=68.25,约要读68次可以把数据读完。
上面为两次在不同的 DB_FILE_MULTIBLOCK_READ_COUNT 参数值环境下，同一执行计划的成本。
可以看出，在一次只读16块时，成本上升。
再打开10046事件跟踪，查看在不同参数环境下，查询到底发生了什么变化。
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT
NAME TYPE VALUE
------------------------------ ------- --------
db_file_multiblock_read_count integer 128
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t13;
COUNT(*)
----------
998
SQL> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16;
System altered.
SQL> select count(*) from t13;
COUNT(*)
----------
998
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Administrator>
trace file content:
-------------------------------------------------------
=====================
PARSING IN CURSOR #438005240 len=24 dep=0 uid=84 oct=3 lid=84 tim=10125283859956 hv=988653825 ad='2f0ddbf70' sqlid='6aqutrwxfva81'
select count(*) from t13
END OF STMT
PARSE #438005240:c=0,e=9297,p=0,cr=103,cu=4,mis=1,r=0,dep=0,og=1,plh=2598196162,tim=10125283859955
EXEC #438005240:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2598196162,tim=10125283860031
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283860085
FETCH #438005240:c=15600,e=12634,p=0,cr=1095,cu=0,mis=0,r=1,dep=0,og=1,plh=2598196162,tim=10125283872748
STAT #438005240 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1095 pr=0 pw=0 time=12629 us)'
STAT #438005240 id=2 cnt=76475 pid=1 pos=1 obj=99240 op='TABLE ACCESS FULL T13 (cr=1095 pr=0 pw=0 time=80082 us cost=196 size=0 card=82867)'
WAIT #438005240: nam='SQL*Net message from client' ela= 605 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283873478
FETCH #438005240:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2598196162,tim=10125283873523
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283873551
*** 2013-12-26 16:06:06.746
WAIT #438005240: nam='SQL*Net message from client' ela= 6166840 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125290040409
CLOSE #438005240:c=0,e=12,dep=0,type=0,tim=10125290040702
我们来重点查看 FETCH部分
c=15600 消耗的CPU时间
e=12634 这步操作的总用时
p=0 物理读的次数
cr=1095 一致性读的次数（也叫数据块数），这个一致性读跟数据块在内存中还是硬盘中是没有关系的，它代表就需要读这么多次而已。如果要找的数据没有在内存中就会触发一次物理读
cu=0 current方式读的次数（数据块数）
mis=0 硬解析的次数
r=1 rows处理的行数
dep=1 递归的SQL深度
og=1 optimizer goal优化其模式
tim=10125283872748 时间戳
plh=2598196162 plan hash value 执行计划的哈希值
=====================
PARSING IN CURSOR #436681312 len=49 dep=0 uid=84 oct=49 lid=84 tim=10125290040764 hv=2944834790 ad='0' sqlid='6yq881yrsd776'
alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16
END OF STMT
PARSE #436681312:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125290040763
WAIT #436681312: nam='reliable message' ela= 80 channel context=12688921096 channel handle=12537097552 broadcast message=12689296352 obj#=-1 tim=10125290041221
WAIT #436681312: nam='Disk file operations I/O' ela= 220 FileOperation=2 fileno=0 filetype=13 obj#=-1 tim=10125290041485
WAIT #436681312: nam='Parameter File I/O' ela= 157 blkno=1 #blks=1 read/write=1 obj#=-1 tim=10125290041667
WAIT #436681312: nam='Parameter File I/O' ela= 85 blkno=2 #blks=3 read/write=1 obj#=-1 tim=10125290041850
WAIT #436681312: nam='Parameter File I/O' ela= 89 blkno=5 #blks=3 read/write=2 obj#=-1 tim=10125290044211
WAIT #436681312: nam='Parameter File I/O' ela= 85 blkno=1 #blks=1 read/write=2 obj#=-1 tim=10125290044332
WAIT #436681312: nam='Parameter File I/O' ela= 108 blkno=5 #blks=3 read/write=1 obj#=-1 tim=10125290044468
WAIT #436681312: nam='Parameter File I/O' ela= 57 blkno=2 #blks=3 read/write=2 obj#=-1 tim=10125290044557
WAIT #436681312: nam='Parameter File I/O' ela= 53 blkno=1 #blks=1 read/write=2 obj#=-1 tim=10125290044639
WAIT #436681312: nam='Parameter File I/O' ela= 53 blkno=5 #blks=3 read/write=2 obj#=-1 tim=10125290044719
WAIT #436681312: nam='Disk file operations I/O' ela= 441 FileOperation=5 fileno=0 filetype=13 obj#=-1 tim=10125290045185
=====================
.............
=====================
PARSING IN CURSOR #438005240 len=24 dep=0 uid=84 oct=3 lid=84 tim=10125294712617 hv=988653825 ad='2f0ddbf70' sqlid='6aqutrwxfva81'
select count(*) from t13
END OF STMT
PARSE #438005240:c=0,e=4128,p=0,cr=73,cu=0,mis=1,r=0,dep=0,og=1,plh=2598196162,tim=10125294712616
EXEC #438005240:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2598196162,tim=10125294712692
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294712743
FETCH #438005240:c=15600,e=12991,p=0,cr=1095,cu=0,mis=0,r=1,dep=0,og=1,plh=2598196162,tim=10125294725760
STAT #438005240 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1095 pr=0 pw=0 time=12985 us)'
STAT #438005240 id=2 cnt=76475 pid=1 pos=1 obj=99240 op='TABLE ACCESS FULL T13 (cr=1095 pr=0 pw=0 time=83408 us cost=247 size=0 card=82867)'
WAIT #438005240: nam='SQL*Net message from client' ela= 426 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294726301
FETCH #438005240:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2598196162,tim=10125294726343
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294726371
*** 2013-12-26 16:06:16.309
WAIT #438005240: nam='SQL*Net message from client' ela= 4876078 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125299602467
CLOSE #438005240:c=0,e=9,dep=0,type=0,tim=10125299602722
我们来看参数值为16时的 FETCH部分
c=15600 消耗的CPU时间
e=12991 （上一次12634 可以看出增加了） 这步操作的总用时
p=0 物理读的次数
cr=1095 一致性读的次数（也叫数据块数），这个一致性读跟数据块在内存中还是硬盘中是没有关系的，它代表就需要读这么多次而已。如果要找的数据没有在内存中就会触发一次物理读
cu=0 current方式读的次数（数据块数）
mis=0 硬解析的次数
r=1 rows处理的行数
dep=1 递归的SQL深度
og=1 optimizer goal优化其模式
tim=10125294725760 （上一次 10125283872748） 时间戳
plh=2598196162 plan hash value 执行计划的哈希值
=====================
PARSING IN CURSOR #438005240 len=55 dep=0 uid=84 oct=42 lid=84 tim=10125299602877 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #438005240:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125299602876
EXEC #438005240:c=0,e=503,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125299603428
------------------------------------------------------------------
4.示例说明数据库中“会话”和“进程”之间的关系。<br>
先梳理一下名称
连接：从客户端到ORACLE实例的一条链路，
会话：指与数据库的一个连接就是一个会话，会话是实例中存在的一个逻辑实体。
这就是你的会话状态（session state），ORACLE实例已分配了对应的内存空间。
进程：指操作系统层面，与数据库开启了一个连接。
4.1.一个进程对应一个会话：
登录ORACLE
[oracle@ract1 ~]$ sqlplus tang/sa@orcl
查询当前会话：
SQL> select SS.USERNAME,SPID from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID
------------------------------------------------------------------------
SYS 10787
TANG 11621
在操作系统中查看会话的进程 按进程号看到11621 是存在的
[root@ract1 ~]# ps -ef|grep 11621
oracle 11621 1 0 11:41 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12049 11659 0 11:50 pts/3 00:00:00 grep 11621
4.2.有进程，没会话
[oracle@ract1 ~]$ sqlplus tang/sa@orcl
SQL> disconnect;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL>
在另一个SYS 登录的窗口查询：
SQL> /
USERNAME SPID
-------------------------
SYS 10787
SQL>
看到这时在ORACLE下，没有会话信息了。
但在同一台服务器中，再查询是否还有打开ORACLE的进程呢，可以看到，是有的
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12184 1 0 11:54 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12271 11659 0 11:56 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]#
还可以再建立连接。查看刚看到的进程，是否就是打开的SQLPUS窗口的进程
SQL> connect
Enter user-name: tang
Enter password:
Connected.
SQL>
从下面的两次对比可以看出。
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12184 1 0 11:54 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12271 11659 0 11:56 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12308 12182 5 11:57 ? 00:00:00 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12312 11659 0 11:57 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]#
SQL> /
USERNAME SPID
--------------------
SYS 10787
TANG 12308
SQL>
4.3.无进程，无会话：
4.3.1在一个窗口登录
[oracle@ract1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 14:35:43 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL>
4.3.2在另一个窗口查询
SQL> select SS.USERNAME,SPID from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID
----------- -------
SYS 10787
4.3.3在另一个SHELL 窗口查看进程：
[oracle@ract1 ~]$ ps -ef|grep oracletdb
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19187 18653 0 14:36 pts/3 00:00:00 grep oracletdb
[oracle@ract1 ~]$
可以看到，使用ORACLE 的进程只有一个 10787 ，就是使用SYS登录 查询会话的窗口，
而第一个窗口登录的，却没有会话记录，也没有进程信息。
4.4 单连接，单进程，多会话
4.4.1 登录ORACLE,打开跟踪
[oracle@ract1 ~]$ sqlplus tang/sa@tdb1
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 14:43:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL> set autotrace on
SQL> set linesize 200;
4.4.2 另外一窗口查询
SQL> select SS.USERNAME,SPID,SS.SERIAL# from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID SERIAL#
--------------------------------
SYS 10787 5
TANG 19522 20
TANG 19522 100
SQL>
4.4.3 查询进程
[oracle@ract1 ~]$ ps -ef|grep oracletdb
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19522 1 0 14:43 ? 00:00:00 oracletdb1 (LOCAL=NO)
oracle 19689 18653 0 14:47 pts/3 00:00:00 grep oracletdb
看到进程数还是两个，但在进程 19522 ，中。会话却有了2个
当启用set autotrace功能后，通常会创建一个新的会话用于监控当前的操作并返回统计信息，并记录到跟踪日志中。
session：指定了一个实例中允许的会话数，即能同时登录到数据库的并发用户数。
process: 指定了一个实例在操作系统级别能同时运行的进程数，包括后台进程与服务器进程。
由上面的分析可知，一个后台进程可能同时对应对个会话，因此通常sessions的值是大于processes的值
通常的设置公式
sessions = 1.1 * processes + 5
------------------------------------------------------------------
5.演示通过动态视图查看某个会话的等待事件。<br>
几个相关的性能视图：
v$session 会话当前的各种状态和属性；
v$session_wait 会话当前的等待事件详细信息；
v$session_event 会话的所有等待事件的详细信息;
v$session_wait_history 会话的等待事件的历史信息
v$sesstat 会话资源的统计信息
#查询当前SESSION_ID
SQL> select distinct sid from v$mystat;
SID
----------
42
#建立一个测试环境数据
SQL> drop table t13 purge;
Table dropped.
SQL> create table t13 as select * from dba_objects;
Table created.
SQL> create table t13_name as select object_name from dba_objects;
Table created.
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set autot trace expl;
SQL> set linesize 400;
SQL> set pagesize 800;
SQL>
#为了能查看到等待事件，我用了 两个表的两字段关联。可以看出是进行了全表检索
SQL> select t.* from t13 t inner join t13_name n on t.object_name=n.object_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 3251948810
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 414K| 107M| | 1467 (1)| 00:00:18 |
|* 1 | HASH JOIN | | 414K| 107M| 5840K| 1467 (1)| 00:00:18 |
| 2 | TABLE ACCESS FULL| T13_NAME | 76610 | 4937K| | 74 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T13 | 82867 | 16M| | 248 (1)| 00:00:03 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_NAME"="N"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
为了更好的查看等待事件，我特别进行全表查询，并且每次都清空缓存
SQL> set autot off;
SQL> for i in 1..10000 loop
SP2-0734: unknown command beginning "for i in 1..." - rest of line ignored.
SQL> begin
2 for i in 1..10000 loop
3 execute immediate 'select t.* from t13 t inner join t13_name n on t.object_name=n.object_name';
4 execute immediate 'alter system flush buffer_cache';
5 end loop;
6 end;
7 /
在另一个窗口查看等待事件情况：
select sid,event,total_waits,total_timeouts,time_waited
from v$session_event where sid =42;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
---------------------------------------------------
1 42 Disk file operations I/O 4 0 0 #操作系统IO 等待
2 42 latch: cache buffers chains 2 0 0 #LATCH 等待
3 42 buffer busy waits 7 0 0 #buffer 等待
4 42 read by other session 2 0 0
5 42 enq: RO - fast object reuse 1 0 0
6 42 log file sync 5 0 0
7 42 db file sequential read 24665 0 350 #数据文件顺序读等待
8 42 db file scattered read 130 0 15
9 42 direct path write 2 0 0
10 42 SQL*Net message to client 39 0 0
11 42 SQL*Net message from client 39 0 228811
12 42 SQL*Net break/reset to client 2 0 0
13 42 events in waitclass Other 8240 0 51570
可以从此表中看到，当上面的循环查询没完成前，‘db file sequential read’ 数据读等待 及等待时间，还是一直增加的。
完成后，也可以在等待厍事件表中可以同样查询到
select * from v$session_wait_history where sid=42;
SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO
-----------------------------------------------------------------------------------------------------------------------
1 42 1 348 SQL*Net message to client driver id 1413697536 #bytes 1 0 0 3 216
2 42 2 146 db file sequential read file# 3 block# 2248 blocks 1 0 93 39
3 42 3 146 db file sequential read file# 3 block# 240 blocks 1 0 113 92
4 42 4 146 db file sequential read file# 1 block# 244652 blocks 1 0 114 236
5 42 5 440 rdbms ipc reply from_process 14 timeout 21474836 0 5 52869 693
6 42 6 146 db file sequential read file# 3 block# 2248 blocks 1 0 132 38
7 42 7 146 db file sequential read file# 3 block# 240 blocks 1 0 109 92
8 42 8 146 db file sequential read file# 1 block# 244652 blocks 1 0 140 241
9 42 9 440 rdbms ipc reply from_process 14 timeout 21474836 0 5 54544 587
10 42 10 146 db file sequential read file# 3 block# 2248 blocks 1 0 109 34
2.通过调整参数optimizer_index_cost_adj的大小，演示SQL产生不同执行计划。<br>
3.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT 不同的值，演示对SQL效率的影响（sql_trace or 10046 的输出结果）<br>
4.示例说明数据库中“会话”和“进程”之间的关系。<br>
5.演示通过动态视图查看某个会话的等待事件。<br>
=============================================================================================
1.设置memory_target参数，并通过 v$memory_target_advice分析数据库的最佳内存大小。<br>
先查了一下相关参数值，发现memory_target 没有设置，默认值为0，
这时 sga_target 是有设置的，那么这时的设置
v$memory_target_advice 表中没有数据，说明这时没有使用内存自动调整？
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ -------------------- ----------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ---------------- ----------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ------------------ ----------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4912M
sga_target big integer 4912M
SQL> select * from v$memory_target_advice;
no rows selected
SQL>
调整内存参数，
我把内存参数设置成系统内存的1/2,
sga 设置为memory_target 的65%
alter system set memory_max_target=10000M scope=spfile;
alter system set memory_target=8000M scope=spfile;
alter system set sga_max_size=6000M scope=spfile;
alter system set sga_target=5200M scope=spfile;
SQL> alter system set memory_max_target=10000M scope=spfile;
System altered.
SQL> alter system set memory_target=8000M scope=spfile;
System altered.
SQL> alter system set sga_max_size=6000M scope=spfile;
System altered.
SQL> alter system set sga_target=5200M scope=spfile;
System altered.
重启服务器使参数生效
SQL> startup force;
ORACLE 例程已经启动。
Total System Global Area 6263357440 bytes
Fixed Size 2266816 bytes
Variable Size 1912604992 bytes
Database Buffers 4328521728 bytes
Redo Buffers 19963904 bytes
数据库装载完毕。
数据库已经打开。
SQL>
SQL> set linesize 400;
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ -------------------------------- ----------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6000M
sga_target big integer 5200M
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ -------------------------------- ----------
hi_shared_memory_address integer 0
memory_max_target big integer 10000M
memory_target big integer 8000M
shared_memory_address integer 0
SQL>
查询内存优化表，可以看出，这时ORACLE已给出了调整方案了。
同时也可以看到，这里的最大内存 16000 即为我操作系统中的内存总数。
从下面两个表中数据可以看到，在这个数据库中，内存调整从2G--16G，对性能来说，
都没有变化。内存的调整对性能没有什么质的变化。
SQL> set pagesize 800;
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
2000 .25 34 1 0
4000 .5 34 1 0
5000 .625 34 1 0
6000 .75 34 1 0
7000 .875 34 1 0
8000 1 34 1 0
9000 1.125 34 1 0
10000 1.25 34 1 0
11000 1.375 34 1 0
12000 1.5 34 1 0
13000 1.625 34 1 0
14000 1.75 34 1 0
15000 1.875 34 1 0
16000 2 34 1 0
14 rows selected.
SQL> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
1300 .25 39 1 35898
1950 .375 39 1 35898
2600 .5 39 1 35898
3250 .625 39 1 35898
3900 .75 39 1 35898
4550 .875 39 1 35898
5200 1 39 1 35898
5850 1.125 39 1 35898
6500 1.25 39 1 35898
7150 1.375 39 1 35898
7800 1.5 39 1 35898
8450 1.625 39 1 35898
9100 1.75 39 1 35898
9750 1.875 39 1 35898
10400 2 39 1 35898
15 rows selected.
----------------------------------------------------------------------------------------
2.通过调整参数 optimizer_index_cost_adj 的大小，演示SQL产生不同执行计划。<br>
参数说明：
OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。
这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
比如设置以下值：
Optimizer_index_cost_adj=20 ，表示索引的成本和全表扫描的成本比为1:5。
2.1 建立演示数据表：
SQL> CREATE TABLE T12 AS SELECT * FROM DBA_OBJECTS where object_id<=1000;
SQL> CREATE INDEX IDX_T12_OWNER ON T12(OWNER);
Index created
SQL>
BEGIN
dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=> NULL,
METHOD_OPT=>'for all columns size 254');
END;
SQL> SET LINESIZE 500;
SQL> SET PAGESIZE 800;
C:\Users\Administrator>sqlplus tang/sa@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 26 15:38:29 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
在默认参数情况下，可以看到，查询所以数据及使用条件查询object_id<1200，走的都是全表检索。
这是正确的。
SQL> SET AUTOTRACE TRACEONLY
SQL> select * from t13;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL> select * from t13 where object_id<1200;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<1200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
2.2 设置参数值为10 ，这时ORACLE 会认为走索引的成本 更低。
SQL> alter session set optimizer_index_cost_adj=10;
Session altered.
SQL> select * from t13;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 17950186
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T13 | 998 | 85828 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL> set linesize 400;
SQL> select * from t13 where object_id<1200;
998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 541349760
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998 | 85828 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T13 | 998 | 85828 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T13_ID | 998 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
105204 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed
SQL>
从最后的查询可以看到，这时ORACLE走索引了。其实OBJECT_ID<1200就是全部数据。但人为的告诉ORACLE走索引更低，
这里有161个唯一值读。而全表检索也只不够是82个唯一值的读。
--------------------------------------------------------------------------------------------------------------
3.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT 不同的值，演示对SQL效率的影响（sql_trace or 10046 的输出结果）<br>
SQL> drop table t13;
Table dropped.
SQL> create table t13 as select * from dba_objects;
Table created.
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT
NAME TYPE VALUE
------------------------------ ------- --------
db_file_multiblock_read_count integer 128
SQL> SET AUTOTRACE TRACEONLY;
SQL> select count(*) from t13;
Execution Plan
----------------------------------------------------------
Plan hash value: 2598196162
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T13 | 82867 | 196 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1095 consistent gets
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
#1092/128=8.53125,约要读8.5次可以把数据读完。
SQL> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16;
System altered.
SQL> select count(*) from t13;
Execution Plan
----------------------------------------------------------
Plan hash value: 2598196162
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 247 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T13 | 82867 | 247 (1)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1095 consistent gets
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
SQL>
#1092/16=68.25,约要读68次可以把数据读完。
上面为两次在不同的 DB_FILE_MULTIBLOCK_READ_COUNT 参数值环境下，同一执行计划的成本。
可以看出，在一次只读16块时，成本上升。
再打开10046事件跟踪，查看在不同参数环境下，查询到底发生了什么变化。
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT
NAME TYPE VALUE
------------------------------ ------- --------
db_file_multiblock_read_count integer 128
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t13;
COUNT(*)
----------
998
SQL> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16;
System altered.
SQL> select count(*) from t13;
COUNT(*)
----------
998
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Administrator>
trace file content:
-------------------------------------------------------
=====================
PARSING IN CURSOR #438005240 len=24 dep=0 uid=84 oct=3 lid=84 tim=10125283859956 hv=988653825 ad='2f0ddbf70' sqlid='6aqutrwxfva81'
select count(*) from t13
END OF STMT
PARSE #438005240:c=0,e=9297,p=0,cr=103,cu=4,mis=1,r=0,dep=0,og=1,plh=2598196162,tim=10125283859955
EXEC #438005240:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2598196162,tim=10125283860031
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283860085
FETCH #438005240:c=15600,e=12634,p=0,cr=1095,cu=0,mis=0,r=1,dep=0,og=1,plh=2598196162,tim=10125283872748
STAT #438005240 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1095 pr=0 pw=0 time=12629 us)'
STAT #438005240 id=2 cnt=76475 pid=1 pos=1 obj=99240 op='TABLE ACCESS FULL T13 (cr=1095 pr=0 pw=0 time=80082 us cost=196 size=0 card=82867)'
WAIT #438005240: nam='SQL*Net message from client' ela= 605 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283873478
FETCH #438005240:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2598196162,tim=10125283873523
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125283873551
*** 2013-12-26 16:06:06.746
WAIT #438005240: nam='SQL*Net message from client' ela= 6166840 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125290040409
CLOSE #438005240:c=0,e=12,dep=0,type=0,tim=10125290040702
我们来重点查看 FETCH部分
c=15600 消耗的CPU时间
e=12634 这步操作的总用时
p=0 物理读的次数
cr=1095 一致性读的次数（也叫数据块数），这个一致性读跟数据块在内存中还是硬盘中是没有关系的，它代表就需要读这么多次而已。如果要找的数据没有在内存中就会触发一次物理读
cu=0 current方式读的次数（数据块数）
mis=0 硬解析的次数
r=1 rows处理的行数
dep=1 递归的SQL深度
og=1 optimizer goal优化其模式
tim=10125283872748 时间戳
plh=2598196162 plan hash value 执行计划的哈希值
=====================
PARSING IN CURSOR #436681312 len=49 dep=0 uid=84 oct=49 lid=84 tim=10125290040764 hv=2944834790 ad='0' sqlid='6yq881yrsd776'
alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16
END OF STMT
PARSE #436681312:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125290040763
WAIT #436681312: nam='reliable message' ela= 80 channel context=12688921096 channel handle=12537097552 broadcast message=12689296352 obj#=-1 tim=10125290041221
WAIT #436681312: nam='Disk file operations I/O' ela= 220 FileOperation=2 fileno=0 filetype=13 obj#=-1 tim=10125290041485
WAIT #436681312: nam='Parameter File I/O' ela= 157 blkno=1 #blks=1 read/write=1 obj#=-1 tim=10125290041667
WAIT #436681312: nam='Parameter File I/O' ela= 85 blkno=2 #blks=3 read/write=1 obj#=-1 tim=10125290041850
WAIT #436681312: nam='Parameter File I/O' ela= 89 blkno=5 #blks=3 read/write=2 obj#=-1 tim=10125290044211
WAIT #436681312: nam='Parameter File I/O' ela= 85 blkno=1 #blks=1 read/write=2 obj#=-1 tim=10125290044332
WAIT #436681312: nam='Parameter File I/O' ela= 108 blkno=5 #blks=3 read/write=1 obj#=-1 tim=10125290044468
WAIT #436681312: nam='Parameter File I/O' ela= 57 blkno=2 #blks=3 read/write=2 obj#=-1 tim=10125290044557
WAIT #436681312: nam='Parameter File I/O' ela= 53 blkno=1 #blks=1 read/write=2 obj#=-1 tim=10125290044639
WAIT #436681312: nam='Parameter File I/O' ela= 53 blkno=5 #blks=3 read/write=2 obj#=-1 tim=10125290044719
WAIT #436681312: nam='Disk file operations I/O' ela= 441 FileOperation=5 fileno=0 filetype=13 obj#=-1 tim=10125290045185
=====================
.............
=====================
PARSING IN CURSOR #438005240 len=24 dep=0 uid=84 oct=3 lid=84 tim=10125294712617 hv=988653825 ad='2f0ddbf70' sqlid='6aqutrwxfva81'
select count(*) from t13
END OF STMT
PARSE #438005240:c=0,e=4128,p=0,cr=73,cu=0,mis=1,r=0,dep=0,og=1,plh=2598196162,tim=10125294712616
EXEC #438005240:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2598196162,tim=10125294712692
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294712743
FETCH #438005240:c=15600,e=12991,p=0,cr=1095,cu=0,mis=0,r=1,dep=0,og=1,plh=2598196162,tim=10125294725760
STAT #438005240 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1095 pr=0 pw=0 time=12985 us)'
STAT #438005240 id=2 cnt=76475 pid=1 pos=1 obj=99240 op='TABLE ACCESS FULL T13 (cr=1095 pr=0 pw=0 time=83408 us cost=247 size=0 card=82867)'
WAIT #438005240: nam='SQL*Net message from client' ela= 426 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294726301
FETCH #438005240:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2598196162,tim=10125294726343
WAIT #438005240: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125294726371
*** 2013-12-26 16:06:16.309
WAIT #438005240: nam='SQL*Net message from client' ela= 4876078 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10125299602467
CLOSE #438005240:c=0,e=9,dep=0,type=0,tim=10125299602722
我们来看参数值为16时的 FETCH部分
c=15600 消耗的CPU时间
e=12991 （上一次12634 可以看出增加了） 这步操作的总用时
p=0 物理读的次数
cr=1095 一致性读的次数（也叫数据块数），这个一致性读跟数据块在内存中还是硬盘中是没有关系的，它代表就需要读这么多次而已。如果要找的数据没有在内存中就会触发一次物理读
cu=0 current方式读的次数（数据块数）
mis=0 硬解析的次数
r=1 rows处理的行数
dep=1 递归的SQL深度
og=1 optimizer goal优化其模式
tim=10125294725760 （上一次 10125283872748） 时间戳
plh=2598196162 plan hash value 执行计划的哈希值
=====================
PARSING IN CURSOR #438005240 len=55 dep=0 uid=84 oct=42 lid=84 tim=10125299602877 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #438005240:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125299602876
EXEC #438005240:c=0,e=503,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=10125299603428
------------------------------------------------------------------
4.示例说明数据库中“会话”和“进程”之间的关系。<br>
先梳理一下名称
连接：从客户端到ORACLE实例的一条链路，
会话：指与数据库的一个连接就是一个会话，会话是实例中存在的一个逻辑实体。
这就是你的会话状态（session state），ORACLE实例已分配了对应的内存空间。
进程：指操作系统层面，与数据库开启了一个连接。
4.1.一个进程对应一个会话：
登录ORACLE
[oracle@ract1 ~]$ sqlplus tang/sa@orcl
查询当前会话：
SQL> select SS.USERNAME,SPID from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID
------------------------------------------------------------------------
SYS 10787
TANG 11621
在操作系统中查看会话的进程 按进程号看到11621 是存在的
[root@ract1 ~]# ps -ef|grep 11621
oracle 11621 1 0 11:41 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12049 11659 0 11:50 pts/3 00:00:00 grep 11621
4.2.有进程，没会话
[oracle@ract1 ~]$ sqlplus tang/sa@orcl
SQL> disconnect;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL>
在另一个SYS 登录的窗口查询：
SQL> /
USERNAME SPID
-------------------------
SYS 10787
SQL>
看到这时在ORACLE下，没有会话信息了。
但在同一台服务器中，再查询是否还有打开ORACLE的进程呢，可以看到，是有的
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12184 1 0 11:54 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12271 11659 0 11:56 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]#
还可以再建立连接。查看刚看到的进程，是否就是打开的SQLPUS窗口的进程
SQL> connect
Enter user-name: tang
Enter password:
Connected.
SQL>
从下面的两次对比可以看出。
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12184 1 0 11:54 ? 00:00:00 oracletdb1 (LOCAL=NO)
root 12271 11659 0 11:56 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]# ps -ef|grep oracletdb1
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 12308 12182 5 11:57 ? 00:00:00 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 12312 11659 0 11:57 pts/3 00:00:00 grep oracletdb1
[root@ract1 ~]#
SQL> /
USERNAME SPID
--------------------
SYS 10787
TANG 12308
SQL>
4.3.无进程，无会话：
4.3.1在一个窗口登录
[oracle@ract1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 14:35:43 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL>
4.3.2在另一个窗口查询
SQL> select SS.USERNAME,SPID from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID
----------- -------
SYS 10787
4.3.3在另一个SHELL 窗口查看进程：
[oracle@ract1 ~]$ ps -ef|grep oracletdb
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19187 18653 0 14:36 pts/3 00:00:00 grep oracletdb
[oracle@ract1 ~]$
可以看到，使用ORACLE 的进程只有一个 10787 ，就是使用SYS登录 查询会话的窗口，
而第一个窗口登录的，却没有会话记录，也没有进程信息。
4.4 单连接，单进程，多会话
4.4.1 登录ORACLE,打开跟踪
[oracle@ract1 ~]$ sqlplus tang/sa@tdb1
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 14:43:05 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL> set autotrace on
SQL> set linesize 200;
4.4.2 另外一窗口查询
SQL> select SS.USERNAME,SPID,SS.SERIAL# from v$process pr,v$session ss where pr.addr=ss.paddr and ss.USERNAME IN ('SYS','TANG');
USERNAME SPID SERIAL#
--------------------------------
SYS 10787 5
TANG 19522 20
TANG 19522 100
SQL>
4.4.3 查询进程
[oracle@ract1 ~]$ ps -ef|grep oracletdb
oracle 10787 10615 0 11:27 ? 00:00:04 oracletdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 19522 1 0 14:43 ? 00:00:00 oracletdb1 (LOCAL=NO)
oracle 19689 18653 0 14:47 pts/3 00:00:00 grep oracletdb
看到进程数还是两个，但在进程 19522 ，中。会话却有了2个
当启用set autotrace功能后，通常会创建一个新的会话用于监控当前的操作并返回统计信息，并记录到跟踪日志中。
session：指定了一个实例中允许的会话数，即能同时登录到数据库的并发用户数。
process: 指定了一个实例在操作系统级别能同时运行的进程数，包括后台进程与服务器进程。
由上面的分析可知，一个后台进程可能同时对应对个会话，因此通常sessions的值是大于processes的值
通常的设置公式
sessions = 1.1 * processes + 5
------------------------------------------------------------------
5.演示通过动态视图查看某个会话的等待事件。<br>
几个相关的性能视图：
v$session 会话当前的各种状态和属性；
v$session_wait 会话当前的等待事件详细信息；
v$session_event 会话的所有等待事件的详细信息;
v$session_wait_history 会话的等待事件的历史信息
v$sesstat 会话资源的统计信息
#查询当前SESSION_ID
SQL> select distinct sid from v$mystat;
SID
----------
42
#建立一个测试环境数据
SQL> drop table t13 purge;
Table dropped.
SQL> create table t13 as select * from dba_objects;
Table created.
SQL> create table t13_name as select object_name from dba_objects;
Table created.
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set autot trace expl;
SQL> set linesize 400;
SQL> set pagesize 800;
SQL>
#为了能查看到等待事件，我用了 两个表的两字段关联。可以看出是进行了全表检索
SQL> select t.* from t13 t inner join t13_name n on t.object_name=n.object_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 3251948810
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 414K| 107M| | 1467 (1)| 00:00:18 |
|* 1 | HASH JOIN | | 414K| 107M| 5840K| 1467 (1)| 00:00:18 |
| 2 | TABLE ACCESS FULL| T13_NAME | 76610 | 4937K| | 74 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T13 | 82867 | 16M| | 248 (1)| 00:00:03 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_NAME"="N"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
为了更好的查看等待事件，我特别进行全表查询，并且每次都清空缓存
SQL> set autot off;
SQL> for i in 1..10000 loop
SP2-0734: unknown command beginning "for i in 1..." - rest of line ignored.
SQL> begin
2 for i in 1..10000 loop
3 execute immediate 'select t.* from t13 t inner join t13_name n on t.object_name=n.object_name';
4 execute immediate 'alter system flush buffer_cache';
5 end loop;
6 end;
7 /
在另一个窗口查看等待事件情况：
select sid,event,total_waits,total_timeouts,time_waited
from v$session_event where sid =42;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
---------------------------------------------------
1 42 Disk file operations I/O 4 0 0 #操作系统IO 等待
2 42 latch: cache buffers chains 2 0 0 #LATCH 等待
3 42 buffer busy waits 7 0 0 #buffer 等待
4 42 read by other session 2 0 0
5 42 enq: RO - fast object reuse 1 0 0
6 42 log file sync 5 0 0
7 42 db file sequential read 24665 0 350 #数据文件顺序读等待
8 42 db file scattered read 130 0 15
9 42 direct path write 2 0 0
10 42 SQL*Net message to client 39 0 0
11 42 SQL*Net message from client 39 0 228811
12 42 SQL*Net break/reset to client 2 0 0
13 42 events in waitclass Other 8240 0 51570
可以从此表中看到，当上面的循环查询没完成前，‘db file sequential read’ 数据读等待 及等待时间，还是一直增加的。
完成后，也可以在等待厍事件表中可以同样查询到
select * from v$session_wait_history where sid=42;
SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO
-----------------------------------------------------------------------------------------------------------------------
1 42 1 348 SQL*Net message to client driver id 1413697536 #bytes 1 0 0 3 216
2 42 2 146 db file sequential read file# 3 block# 2248 blocks 1 0 93 39
3 42 3 146 db file sequential read file# 3 block# 240 blocks 1 0 113 92
4 42 4 146 db file sequential read file# 1 block# 244652 blocks 1 0 114 236
5 42 5 440 rdbms ipc reply from_process 14 timeout 21474836 0 5 52869 693
6 42 6 146 db file sequential read file# 3 block# 2248 blocks 1 0 132 38
7 42 7 146 db file sequential read file# 3 block# 240 blocks 1 0 109 92
8 42 8 146 db file sequential read file# 1 block# 244652 blocks 1 0 140 241
9 42 9 440 rdbms ipc reply from_process 14 timeout 21474836 0 5 54544 587
10 42 10 146 db file sequential read file# 3 block# 2248 blocks 1 0 109 34
0 0
- 【性能优化】 之性能视图及性能参数
- 性能视图和性能参数
- 性能视图和性能参数
- 性能优化(一) - 性能参数指标
- <<Oracle数据库性能优化艺术（第五期）>> 第13周 性能视图和性能参数
- 安卓性能优化之视图优化
- Android app 性能优化之视图优化
- iOS视图成像理论及性能优化
- [Android]ListView性能优化之视图缓存
- ListView性能优化之视图缓存
- [Android]ListView性能优化之视图缓存
- [Android]ListView性能优化之视图缓存
- ListView性能优化之视图缓存
- [Android]ListView性能优化之视图缓存
- Android ListView性能优化之视图缓存
- ListView性能优化之视图缓存
- [Android]ListView性能优化之视图缓存
- [Android]ListView性能优化之视图缓存
- 【性能优化】 之10046 事件
- uva - 146 - ID Codes
- 【性能优化】 之 10053 事件
- 美国这也社交网站LinkesIn入华，原糯米网CEO任全球高级副总裁
- 有效处理Java异常三原则
- 【性能优化】 之性能视图及性能参数
- Qwidget 添加 滚动条 QScrollArea
- Linux运行级别
- Linux的文件权限与目录配置 -- 鸟哥的Linux私房菜 笔记 第6章
- 【性能优化】 之AWR 报告分析
- Linux 技巧：让进程在后台可靠运行的几种方法
- CharArrayWriter 源码分析
- 当当低估了，唯品会高估了
- mysql大小写敏感（默认为1，不敏感）
