疑问:
1、what's mean of "enqueue hash chains" latch?
http://www.freelists.org/post/oracle-l/whats-mean-of-enqueue-hash-chains-latch
dml_locks = 2000
enqueue_resources = 2200
提示:阅读Oracle8i Internal Services for Waits, Latches, Locks,and Memory的第四章
2、Oracle MTS模式下 进程地址与会话信息
http://www.eygle.com/archives/2010/11/oracle_mts_paddr.html
前几天在客户现场,检查一个数据库时发现在OS上根本没有明确的数据库连接,但是在数据库内部却可以看到会话信息。
想了一会,才明白过来,原来这是一个MTS系统,共享服务器模式已经很久没有遇到了,几乎忘却了。
在检查会话信息时,你会发现,用户进程的进程地址PADDR是相同的,都是812960DC :
SQL> select paddr,sid,username,osuser,process,terminal,machine,logon_time from v$session;
PADDR SID USERNAME OSUSER PROCESS TERMINAL MACHINE LOGON_TIME
-------- --- ---------- ---------- ------------ --------------- ----------------- -------------------
8129301C 1 oracle 6568 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:07
812933DC 2 oracle 6570 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:08
8129379C 3 oracle 6573 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:08
81293B5C 4 oracle 6576 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
81293F1C 5 oracle 6578 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
812942DC 7 oracle 6580 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
8129469C 9 oracle 6582 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
81294A5C 10 oracle 6584 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
81294E1C 11 oracle 6586 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
812951DC 12 oracle 6588 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
8129559C 13 oracle 6590 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
8129595C 14 oracle 6592 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:11
812960DC 15 EYGLE eyg 3572:220 EYGLEE EYGLEENMO\EYGLEE 2010-10-28 09:04:14
812960DC 16 EYGLE eyg 1804:3708 EYGLEE EYGLEENMO\EYGLEE 2010-10-28 09:04:19
8129685C 17 oracle 6611 UNKNOWN EYGLEORACLE1 2010-09-27 11:39:13
81295D1C 20 EYGLE root unknown EYGLEWEB1 2010-10-28 07:45:30
812960DC 24 EYGLE root unknown EYGLEWEB1 2010-10-28 09:11:13
812960DC 30 EYGLE eyg 3296:2968 EYGLEE EYGLEENMO\EYGLEE 2010-10-28 09:04:22
812960DC 31 EYGLE root unknown EYGLEWEB1 2010-10-28 08:21:09
812960DC 32 EYGLE root unknown EYGLEWEB1 2010-10-28 09:13:45
812960DC 33 EYGLE oracle 721122 oracle2 2010-10-13 13:34:13
8129649C 35 SYS oracle 23221 pts/1 EYGLEORACLE1 2010-10-28 09:24:21
而 812960DC 进程正是Dispatcher进程 oracle@EYGLEORACLE1 (D000)
SQL> select addr,spid,program from v$process;
ADDR SPID PROGRAM
-------- ------------ ------------------------------------------------
81292C5C PSEUDO
8129301C 6568 oracle@EYGLEORACLE1 (PMON)
812933DC 6570 oracle@EYGLEORACLE1 (DIAG)
8129379C 6573 oracle@EYGLEORACLE1 (LMON)
81293B5C 6576 oracle@EYGLEORACLE1 (LMD0)
81293F1C 6578 oracle@EYGLEORACLE1 (LMS0)
812942DC 6580 oracle@EYGLEORACLE1 (LMS1)
8129469C 6582 oracle@EYGLEORACLE1 (DBW0)
81294A5C 6584 oracle@EYGLEORACLE1 (LGWR)
81294E1C 6586 oracle@EYGLEORACLE1 (CKPT)
812951DC 6588 oracle@EYGLEORACLE1 (SMON)
ADDR SPID PROGRAM
-------- ------------ ------------------------------------------------
8129559C 6590 oracle@EYGLEORACLE1 (RECO)
8129595C 6592 oracle@EYGLEORACLE1 (CJQ0)
81295D1C 6594 oracle@EYGLEORACLE1 (S000)
812960DC 6596 oracle@EYGLEORACLE1 (D000)
8129649C 23222 oracle@EYGLEORACLE1 (TNS V1-V3)
8129685C 6611 oracle@EYGLEORACLE1 (LCK0)
81296FDC 9955 oracle@EYGLEORACLE1 (S003)
而通过监听器也可以看到,数据库所有的连接都来自Dispatcher,没有Dedicated模式的连接建立:
[oracle@EYGLEORACLE1 log]$ lsnrctl service
LSNRCTL for Linux: Version 9.2.0.7.0 - Production on 28-OCT-2010 09:38:51
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jgzx1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "jgzx" has 2 instance(s).
Instance "jgzx1", status READY, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=jgzx1)(PORT=1521))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:117664 refused:0 current:21 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=jgzx1)(PORT=32812))
Instance "jgzx2", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=jgzx2)(PORT=1521))
"D000" established:114273 refused:0 current:18 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=jgzx2)(PORT=32784))
Service "jgzx1" has 1 instance(s).
Instance "jgzx1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
由此,MTS模式的特点显而易见,其减少进程可以缩减系统的资源消耗,但是显然会导致排队,并不适用于对响应时间要求较高的OLTP系统。
提示:oracle各进程都有一个acronym(首字母缩略词),比如Dnnn——DISPATCHER,LGWR——Redo log writer,DBW0...j——Database writer,等等,具体请参见oracle11g体系图。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/95233/viewspace-678274/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/95233/viewspace-678274/