等待事件“inactivesession”剖析过程。
今天是2014-03-21，应用侧发过信息来说undo表空间不足，需要扩展undo表空间，可是目前undo表空间在我接手该数据库之前就已经被调整为30GB，且数据库处理的数据事务并不是很多很大，因此仅仅增加undo表空间那将不是最佳选择。应用反馈信息如下：
首先暂且不说“inactive session”该等待事件吧。如下摘录oracle 联机手册 concept：
Undo Segments
Oracle Database maintains records of the actions of transactions, collectively known as undo data. Oracle Database uses undo to do the following:
Roll back an active transaction
Recover a terminated transaction
Provide read consistency
Perform some logical flashback operations
Oracle Database stores undo data inside the database rather than in external logs. Undo data is stored in blocks that are updated just like data blocks, with changes to these blocks generating redo. In this way, Oracle Database can efficiently access undo data without needing to read external logs.
Undo data is stored in an undo tablespace. Oracle Database provides a fully automated mechanism, known as automatic undo management mode, for managing undo segments and space in an undo tablespace.
Undo Segments and Transactions
When a transaction starts, the database binds (assigns) the transaction to an undo segment, and therefore to a transaction table, in the current undo tablespace. In rare circumstances, if the database instance does not have a designated undo tablespace, then the transaction binds to the system undo segment.
Multiple active transactions can write concurrently to the same undo segment or to different segments. For example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes to undo segment U2.
Conceptually, the extents in an undo segment form a ring. Transactions write to one undo extent, and then to the next extent in the ring, and so on in cyclical fashion. Figure 12-20 shows two transactions, T1 and T2, which begin writing in the third extent (E3) of an undo segment and continue writing to the fourth extent (E4).
Figure 12-20 Ring of Allocated Extents in an Undo Segment
Description of "Figure 12-20 Ring of Allocated Extents in an Undo Segment "
At any given time, a transaction writes sequentially to only one extent in an undo segment, known as the current extent for the transaction. Multiple active transactions can write simultaneously to the same current extent or to different current extents. Figure 12-20 shows transactions T1 and T2 writing simultaneously to extent E3. Within an undo extent, a data block contains data for only one transaction.
As the current undo extent fills, the first transaction needing space checks the availability of the next allocated extent in the ring. If the next extent does not contain data from an active transaction, then this extent becomes the current extent. Now all transactions that need space can write to the new current extent. In Figure 12-21, when E4 is full, T1 and T2 continue writing to E1, overwriting the nonactive undo data in E1.
Figure 12-21 Cyclical Use of Allocated Extents in an Undo Segment
Description of "Figure 12-21 Cyclical Use of Allocated Extents in an Undo Segment"
If the next extent does contain data from an active transaction, then the database must allocate a new extent. Figure 12-22 shows a scenario in which T1 and T2 are writing to E4. When E4 fills up, the transactions cannot continue writing to E1 because E1 contains active undo entries. Therefore, the database allocates a new extent (E5) for this undo segment. The transactions continue writing to E5.
Figure 12-22 Allocation of a New Extent for an Undo Segment
Description of "Figure 12-22 Allocation of a New Extent for an Undo Segment"
See Also:
Oracle Database 2 Day DBA and Oracle Database Administrator's Guide to learn how to manage undo segmentsTransaction Rollback
When a ROLLBACK statement is issued, the database uses undo records to roll back changes made to the database by the uncommitted transaction. During recovery, the database rolls back any uncommitted changes applied from the online redo log to the data files. Undo records provide read consistency by maintaining the before image of the data for users accessing data at the same time that another user is changing it.
至此undo基本原理介绍完毕。
另外对于undo retention 参数了解详见：http://blog.csdn.net/rhys_oracle/article/details/10162043
既然这样，那么就收集一下awr查看一下吧。
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| CPU time | 1,657 | 56.2 | |||
| db file sequential read | 562,614 | 998 | 2 | 33.8 | User I/O |
| db file scattered read | 47,756 | 89 | 2 | 3.0 | User I/O |
| inactive session | 68 | 66 | 977 | 2.3 | Other |
| SQL*Net more data from client | 7,652 | 42 | 5 | 1.4 | Network |
发现数据库确实存在一定的问题“INACTIVE SESSION"等待事件严重。
首先介绍v$session status状态信息：
| STATUS | VARCHAR2(8) | Status of the session:
ACTIVE - Session currently executing SQL INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits KILLED - Session marked to be killed CACHED - Session temporarily cached for use by Oracle*XA SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again. |
那么目前数据库的会话状态是什么样的呢？
（信息做了相应处理）
SQL>
SQL> select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
2 'orakill '||sid||' '||spid HOST_COMMAND,
3 'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
4 from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6
5 ;
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
1105 20355 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1105 20355 alter system kill session '1105,1'
1099 20367 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1099 20367 alter system kill session '1099,1'
1092 20381 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1092 20381 alter system kill session '1092,1'
1100 20365 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1100 20365 alter system kill session '1100,1'
1093 20379 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1093 20379 alter system kill session '1093,1'
1101 20363 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1101 20363 alter system kill session '1101,1'
1094 20377 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1094 20377 alter system kill session '1094,1'
1087 20392 1 orarep 2014-03-07 322.761666 ACTIVE orakill 1087 20392 alter system kill session '1087,1'
1097 20371 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1097 20371 alter system kill session '1097,1'
1098 20369 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1098 20369 alter system kill session '1098,1'
1104 20357 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1104 20357 alter system kill session '1104,1'
1102 20361 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1102 20361 alter system kill session '1102,1'
1095 20375 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1095 20375 alter system kill session '1095,1'
1088 20390 1 orarep 2014-03-07 322.761666 ACTIVE orakill 1088 20390 alter system kill session '1088,1'
1103 20359 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1103 20359 alter system kill session '1103,1'
969 19690 44870 Rhys_rhys_rhys_ rhysrh1 2014-03-12 207.935 KILLED orakill 969 19690 alter system kill session '969,44870'
1096 20373 1 orarep 2014-03-07 322.764166 ACTIVE orakill 1096 20373 alter system kill session '1096,1'
1075 28813 19026 RHYSRH rhysrh1 2014-03-08 0.17444444 INACTIVE orakill 1075 28813 alter system kill session '1075,19026'
1010 11193 64281 SYSTEM Administrator 2014-03-21 0.265 INACTIVE orakill 1010 11193 alter system kill session '1010,64281'
1037 23528 2658 RHYSRH rhysrh1 2014-03-07 16.8152777 INACTIVE orakill 1037 23528 alter system kill session '1037,2658'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
997 11598 17342 PMSUSER2 SYSTEM 2014-03-21 8.655 INACTIVE orakill 997 11598 alter system kill session '997,17342'
1050 26792 2952 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.00055555 INACTIVE orakill 1050 26792 alter system kill session '1050,2952'
1038 21445 30 Rhys_rhys_rhys_ rhysrh1 2014-03-07 0.31333333 INACTIVE orakill 1038 21445 alter system kill session '1038,30'
1055 26829 1098 RHYSRH rhysrh1 2014-03-08 0.6675 INACTIVE orakill 1055 26829 alter system kill session '1055,1098'
1031 28823 3591 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.18305555 INACTIVE orakill 1031 28823 alter system kill session '1031,3591'
1023 24516 4112 RHYSRH rhysrh1 2014-03-09 0.03361111 INACTIVE orakill 1023 24516 alter system kill session '1023,4112'
950 6470 51886 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.17444444 INACTIVE orakill 950 6470 alter system kill session '950,51886'
1006 12029 65113 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00472222 INACTIVE orakill 1006 12029 alter system kill session '1006,65113'
1004 12189 39669 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00333333 INACTIVE orakill 1004 12189 alter system kill session '1004,39669'
991 9634 9575 Rhys_rhys_rhys_ unicom 2014-03-21 0.13194444 INACTIVE orakill 991 9634 alter system kill session '991,9575'
952 261 63669 Rhys_rhys_rhys_ rhysrh1 2014-03-20 0.03861111 INACTIVE orakill 952 261 alter system kill session '952,63669'
1029 11738 54978 Rhys_rhys_rhys_ unicom 2014-03-21 0.13138888 INACTIVE orakill 1029 11738 alter system kill session '1029,54978'
935 12047 23961 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 935 12047 alter system kill session '935,23961'
1091 12055 40063 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00888888 INACTIVE orakill 1091 12055 alter system kill session '1091,40063'
979 10404 7299 Rhys_rhys_rhys_ hr 2014-03-21 0.3425 INACTIVE orakill 979 10404 alter system kill session '979,7299'
953 11596 26108 PMSUSER2 SYSTEM 2014-03-21 8.6575 INACTIVE orakill 953 11596 alter system kill session '953,26108'
1036 26790 2843 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.07027777 INACTIVE orakill 1036 26790 alter system kill session '1036,2843'
1057 28815 16184 RHYSRH rhysrh1 2014-03-08 0.17444444 INACTIVE orakill 1057 28815 alter system kill session '1057,16184'
1045 28817 1587 RHYSRH rhysrh1 2014-03-08 0.17444444 INACTIVE orakill 1045 28817 alter system kill session '1045,1587'
1061 28821 3027 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.17444444 INACTIVE orakill 1061 28821 alter system kill session '1061,3027'
1005 24514 447 RHYSRH rhysrh1 2014-03-09 0.03361111 INACTIVE orakill 1005 24514 alter system kill session '1005,447'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
943 10000 29091 Rhys_rhys_rhys_ unicom 2014-03-21 0.35944444 INACTIVE orakill 943 10000 alter system kill session '943,29091'
973 12224 14955 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00416666 INACTIVE orakill 973 12224 alter system kill session '973,14955'
999 12185 9626 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00333333 INACTIVE orakill 999 12185 alter system kill session '999,9626'
984 7305 51145 Rhys_rhys_rhys_ rhysrh1 2014-03-18 18.0855555 INACTIVE orakill 984 7305 alter system kill session '984,51145'
1002 9657 20151 Rhys_rhys_rhys_ dum 2014-03-21 0.57638888 INACTIVE orakill 1002 9657 alter system kill session '1002,20151'
998 12257 15458 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 998 12257 alter system kill session '998,15458'
970 12153 40388 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00555555 INACTIVE orakill 970 12153 alter system kill session '970,40388'
963 10116 56760 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.13277777 INACTIVE orakill 963 10116 alter system kill session '963,56760'
1072 12891 58537 PMS_PORT root 2014-03-13 201.3425 INACTIVE orakill 1072 12891 alter system kill session '1072,58537'
1076 23539 852 Rhys_rhys_rhys_ rhysrh1 2014-03-07 18.0855555 INACTIVE orakill 1076 23539 alter system kill session '1076,852'
1019 12117 6100 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.01166666 INACTIVE orakill 1019 12117 alter system kill session '1019,6100'
1009 12204 54434 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00027777 INACTIVE orakill 1009 12204 alter system kill session '1009,54434'
1030 21449 3 Rhys_rhys_rhys_ rhysrh1 2014-03-07 0.3275 INACTIVE orakill 1030 21449 alter system kill session '1030,3'
1022 26841 2 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.6675 INACTIVE orakill 1022 26841 alter system kill session '1022,2'
1044 24512 29852 RHYSRH rhysrh1 2014-03-09 0.03361111 INACTIVE orakill 1044 24512 alter system kill session '1044,29852'
954 9392 42267 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.40777777 INACTIVE orakill 954 9392 alter system kill session '954,42267'
982 12214 4240 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00277777 INACTIVE orakill 982 12214 alter system kill session '982,4240'
971 12187 64974 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00277777 INACTIVE orakill 971 12187 alter system kill session '971,64974'
1035 9630 56921 Rhys_rhys_rhys_ dum 2014-03-21 0.58 INACTIVE orakill 1035 9630 alter system kill session '1035,56921'
988 12240 54472 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 988 12240 alter system kill session '988,54472'
995 12244 52722 SYSTEM Administrator 2014-03-21 0 ACTIVE orakill 995 12244 alter system kill session '995,52722'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
1059 16815 6933 Rhys_rhys_rhys_ rhysrh1 2014-03-20 0.31361111 INACTIVE orakill 1059 16815 alter system kill session '1059,6933'
961 12889 24502 PMS_PORT root 2014-03-13 201.3425 INACTIVE orakill 961 12889 alter system kill session '961,24502'
1069 23537 164 Rhys_rhys_rhys_ rhysrh1 2014-03-07 16.965 INACTIVE orakill 1069 23537 alter system kill session '1069,164'
1058 9671 35482 Rhys_rhys_rhys_ unicom 2014-03-21 0.53916666 INACTIVE orakill 1058 9671 alter system kill session '1058,35482'
944 12210 51746 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00194444 INACTIVE orakill 944 12210 alter system kill session '944,51746'
1032 21447 36 Rhys_rhys_rhys_ rhysrh1 2014-03-07 0.31333333 INACTIVE orakill 1032 21447 alter system kill session '1032,36'
1025 26839 83 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.67 INACTIVE orakill 1025 26839 alter system kill session '1025,83'
1015 28825 421 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.64138888 INACTIVE orakill 1015 28825 alter system kill session '1015,421'
1056 24527 19979 Rhys_rhys_rhys_ rhysrh1 2014-03-09 0.03361111 INACTIVE orakill 1056 24527 alter system kill session '1056,19979'
965 12208 64465 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00194444 INACTIVE orakill 965 12208 alter system kill session '965,64465'
945 12123 65499 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 945 12123 alter system kill session '945,65499'
1003 7303 62039 Rhys_rhys_rhys_ rhysrh1 2014-03-18 16.8152777 INACTIVE orakill 1003 7303 alter system kill session '1003,62039'
1043 12234 50993 SYSTEM Administrator 2014-03-21 0.00194444 INACTIVE orakill 1043 12234 alter system kill session '1043,50993'
1041 12242 8685 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 1041 12242 alter system kill session '1041,8685'
1068 12198 21046 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 1068 12198 alter system kill session '1068,21046'
1039 23526 1449 RHYSRH rhysrh1 2014-03-07 16.8152777 INACTIVE orakill 1039 23526 alter system kill session '1039,1449'
993 11594 64752 PMSUSER2 SYSTEM 2014-03-21 8.6575 INACTIVE orakill 993 11594 alter system kill session '993,64752'
1047 23535 872 Rhys_rhys_rhys_ rhysrh1 2014-03-07 18.0855555 INACTIVE orakill 1047 23535 alter system kill session '1047,872'
1067 26786 2157 RHYSRH rhysrh1 2014-03-08 0.00138888 INACTIVE orakill 1067 26786 alter system kill session '1067,2157'
1063 9741 7984 Rhys_rhys_rhys_ unicom 2014-03-21 0.58722222 INACTIVE orakill 1063 9741 alter system kill session '1063,7984'
1040 21441 54 RHYSRH rhysrh1 2014-03-07 0.31333333 INACTIVE orakill 1040 21441 alter system kill session '1040,54'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
1028 26837 419 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.6675 INACTIVE orakill 1028 26837 alter system kill session '1028,419'
947 10753 50133 Rhys_rhys_rhys_ Ricky 2014-03-21 0.36138888 INACTIVE orakill 947 10753 alter system kill session '947,50133'
1066 24525 15979 Rhys_rhys_rhys_ rhysrh1 2014-03-09 0.03861111 INACTIVE orakill 1066 24525 alter system kill session '1066,15979'
989 12206 15558 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00194444 INACTIVE orakill 989 12206 alter system kill session '989,15558'
955 12059 16751 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.04916666 INACTIVE orakill 955 12059 alter system kill session '955,16751'
1077 7301 45954 Rhys_rhys_rhys_ rhysrh1 2014-03-18 18.0855555 INACTIVE orakill 1077 7301 alter system kill session '1077,45954'
1071 10440 49091 Rhys_rhys_rhys_ hr 2014-03-21 0.34222222 INACTIVE orakill 1071 10440 alter system kill session '1071,49091'
1013 12068 57083 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00361111 INACTIVE orakill 1013 12068 alter system kill session '1013,57083'
1053 11280 44438 SYS orarep 2014-03-21 0.23222222 INACTIVE orakill 1053 11280 alter system kill session '1053,44438'
1014 12044 61091 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.04916666 INACTIVE orakill 1014 12044 alter system kill session '1014,61091'
978 9517 61560 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.17444444 INACTIVE orakill 978 9517 alter system kill session '978,61560'
994 12887 12158 PMS_PORT root 2014-03-13 9.17277777 INACTIVE orakill 994 12887 alter system kill session '994,12158'
1017 12202 16999 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00194444 INACTIVE orakill 1017 12202 alter system kill session '1017,16999'
1074 26784 2962 RHYSRH rhysrh1 2014-03-08 0.07 INACTIVE orakill 1074 26784 alter system kill session '1074,2962'
1018 12119 62638 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 1018 12119 alter system kill session '1018,62638'
1033 21439 6 RHYSRH rhysrh1 2014-03-07 0.31333333 INACTIVE orakill 1033 21439 alter system kill session '1033,6'
1024 26833 237 RHYSRH rhysrh1 2014-03-08 0.6675 INACTIVE orakill 1024 26833 alter system kill session '1024,237'
949 9604 13361 Rhys_rhys_rhys_ dum 2014-03-21 0.00194444 INACTIVE orakill 949 9604 alter system kill session '949,13361'
1001 24523 724 Rhys_rhys_rhys_ rhysrh1 2014-03-09 0.20611111 INACTIVE orakill 1001 24523 alter system kill session '1001,724'
938 25179 14678 Rhys_rhys_rhys_ rhysrh1 2014-03-20 0.00138888 INACTIVE orakill 938 25179 alter system kill session '938,14678'
964 9250 51164 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.11666666 INACTIVE orakill 964 9250 alter system kill session '964,51164'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
1027 12226 16099 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00416666 INACTIVE orakill 1027 12226 alter system kill session '1027,16099'
1049 12230 6561 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00416666 INACTIVE orakill 1049 12230 alter system kill session '1049,6561'
934 12066 54685 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00138888 INACTIVE orakill 934 12066 alter system kill session '934,54685'
981 11184 16840 SYSTEM Administrator 2014-03-21 0.14666666 INACTIVE orakill 981 11184 alter system kill session '981,16840'
1065 9345 8795 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.46638888 INACTIVE orakill 1065 9345 alter system kill session '1065,8795'
966 12157 11891 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 966 12157 alter system kill session '966,11891'
1062 12200 15295 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00638888 INACTIVE orakill 1062 12200 alter system kill session '1062,15295'
1054 23530 1352 RHYSRH rhysrh1 2014-03-07 16.8152777 INACTIVE orakill 1054 23530 alter system kill session '1054,1352'
1048 26782 3516 RHYSRH rhysrh1 2014-03-08 0.00138888 INACTIVE orakill 1048 26782 alter system kill session '1048,3516'
1060 26794 3140 Rhys_rhys_rhys_ rhysrh1 2014-03-08 0.00055555 INACTIVE orakill 1060 26794 alter system kill session '1060,3140'
1034 21437 1 RHYSRH rhysrh1 2014-03-07 0.31333333 INACTIVE orakill 1034 21437 alter system kill session '1034,1'
1051 26831 1886 RHYSRH rhysrh1 2014-03-08 0.6675 INACTIVE orakill 1051 26831 alter system kill session '1051,1886'
992 12183 17083 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00388888 INACTIVE orakill 992 12183 alter system kill session '992,17083'
1011 12212 34126 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00472222 INACTIVE orakill 1011 12212 alter system kill session '1011,34126'
1073 12121 6074 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00472222 INACTIVE orakill 1073 12121 alter system kill session '1073,6074'
986 12057 44228 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00805555 INACTIVE orakill 986 12057 alter system kill session '986,44228'
962 12228 1273 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0.00416666 INACTIVE orakill 962 12228 alter system kill session '962,1273'
967 26928 57809 Rhys_rhys_rhys_ rhysrh1 2014-03-20 15.8111111 INACTIVE orakill 967 26928 alter system kill session '967,57809'
968 12238 26843 Rhys_rhys_rhys_ rhysrh1 2014-03-21 0 INACTIVE orakill 968 12238 alter system kill session '968,26843'
1080 20407 3 orarep 2014-03-07 322.758333 ACTIVE orakill 1080 20407 alter system kill session '1080,3'
1082 20396 1 orarep 2014-03-07 322.761111 ACTIVE orakill 1082 20396 alter system kill session '1082,1'
SID SPID SERIAL# LOCKWAIT USERNAME OSUSER LOGON_TIME LAST_HOUR STATUS HOST_COMMAND SQL_COMMAND
---------- ------------ ---------- ---------------- ------------------------------ ------------------------------ ----------- ---------- -------- ------------------------------------------------------------- --------------------------------------------------------------------------------
1090 20409 9 orarep 2014-03-07 322.758333 ACTIVE orakill 1090 20409 alter system kill session '1090,9'
126 rows selected
SQL>
可以知道目前，数据库会话存在两种状态一种是有sql执行当前的会话信息（active），另一种是不活的会话（inactive），还有一种是killed这种会话状态产生的原因是，某个会话运行语句然后kill掉该进程，那么在该会话运行的dml操作语句事务将进行回滚，当然这个过程将占用很多的资源消耗（undo使用率过高原因所在）。
另外inactive的会话出现的原因呢？
1、连接数据库的应用程序在退出的时候连接没有得到释放
2、网络突然中断，应用进程异常退出
那么该数据库对应的前端为websphere中间件，对于中间件来说》连接池里的initpool是长连接。查看属性配置如下：
根据中间件中应用程序各种数据源中的连接池中的连接数共计80左右。
查看数据库一天会话统计信息如下，吻合中间件配置信息。
那么如何处理inactive session呢？
方法一：
找到相应的spid进行kill，在删掉session。
eg：
select A.SID,
B.SPID,
A.SERIAL#,
a.lockwait,
A.USERNAME,
A.OSUSER,
a.logon_time,
a.last_call_et / 3600 LAST_HOUR,
A.STATUS,
'orakill ' || sid || ' ' || spid HOST_COMMAND,
'alter system kill session ''' || A.sid || ',' || A.SERIAL# || '''' SQL_COMMAND
from v$session A, V$PROCESS B
where A.PADDR = B.ADDR
AND SID > 6
其中另一个办法是在sqlnet.ora中设置sqlnet.expire_time参数（单位分钟），使其会话在该时间不活动后退出。
SQLNET.EXPIRE_TIME
Purpose
Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
Limitations on using this terminated connection detection feature are:
It is not allowed on bequeathed connections.
Though very small, a probe packet generates additional traffic that may downgrade network performance.
Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default
0
Minimum Value
0
Recommended Value
10
Example
SQLNET.EXPIRE_TIME=10
最后一种办法是设置用户的profile文件中的idle_time参数。
目前数据库状态：
|
Event |
Waits |
Time(s) |
Avg Wait(ms) |
% Total Call Time |
Wait Class |
|---|---|---|---|---|---|
|
CPU time |
|
517 |
|
62.9 |
|
|
db file sequential read |
112,520 |
221 |
2 |
26.9 |
User I/O |
|
db file scattered read |
28,598 |
50 |
2 |
6.1 |
User I/O |
|
read by other session |
24,358 |
31 |
1 |
3.7 |
User I/O |
|
SQL*Net more data from client |
2,888 |
14 |
5 |
1.7 |
Network |
That's all!
- 文章
- 推荐
- 热门新闻