dd[@more@]

当时xiongwh第一时间就说，怀疑是bug.5907779，

利用并改造该bug提供的SQL语句，发现sdmdb2确实有类似现象，因此问题定位成功，如下：

--开启event10046会导致oracle 10.2.0.3挂起

--kill受影响的会话，可以让数据库实例恢复正常

备注：看来，对RAC而言，仅挂起启用event10046的节点，

备注：如本例中，sdmdb2上启用alter system，则sdmdb1并不会出问题，只是在pscm程序切到sdmdb1后，才也被触发。

--备注：以后自己也经常看note:401435.1，这样就不会还奇怪老熊问题定位得这么快了，呵呵

--所以当时并没做gather_stat。只是错误的开启了event10046，也会触发挂起啊

SELECT u.INST_ID, u.sid, u.username, e.event

FROM gv$session u, gv$session_wait e
WHERE u.sid = e.sid
AND u.INST_ID = e.INST_ID;

--备注：15:30起已经无法连接入数据库，因此当时无法从sdmdb2执行该语句

--备注：下面为18:05的查询结果，当时pscm已连接到sdmdb1并导致后者也开始异常

--备注：所以从查询结果来看，sdmdb1也有了很多这种东西

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/426772/viewspace-1002474/ ，如需转载，请注明出处，否则将追究法律责任。

上一篇：没有了~

下一篇： Oracle高级SQL调优之：cluster_factor案例研究

请登录后发表评论登录

全部评论

XIAOTG

博文量
3
访问量

5552

最新文章

广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员

违法和不良信息举报电话:18101376593

INST_ID	SID	USERNAME	EVENT
2	825	PART	cursor: pin S wait on X
2	829	SDM_PART	cursor: pin S wait on X
2	831	SDM_PART	cursor: pin S wait on X
2	840	PART	cursor: pin S wait on X
2	841	SDM_PART	cursor: pin S wait on X
2	842	SDM_PART	cursor: pin S wait on X
2	844	SDM_PART	cursor: pin S wait on X
2	849	SDM_PART	cursor: pin S wait on X
2	850	PART	PX Deq: Execution Msg
2	851	XIAOTG	cursor: pin S wait on X
2	853	PART	cursor: pin S wait on X
2	856	PART	PX Deq: Parse Reply
2	857	PART	cursor: pin S wait on X
2	859	SDM_PART	cursor: pin S wait on X
2	860	PART	cursor: pin S wait on X
2	863	SDM_PART	cursor: pin S wait on X
2	865	PART	cursor: pin S wait on X
2	866	PART	cursor: pin S wait on X
2	867	SDM_PART	cursor: pin S wait on X
2	870	SDM_PART	cursor: pin S wait on X
2	871	PART	cursor: pin S wait on X
2	872	SDM_PART	cursor: pin S wait on X
2	873	SDM_PART	cursor: pin S wait on X
2	876	SDM_PART	cursor: pin S wait on X
2	877	XIAOTG	cursor: pin S wait on X
2	878	SDM_PART	cursor: pin S wait on X
2	879	SDM_PART	cursor: pin S wait on X
2	881	SDM_PART	cursor: pin S wait on X
2	884	SDM_PART	cursor: pin S wait on X
2	886	SDM_PART	cursor: pin S wait on X
2	887	PART	cursor: pin S wait on X
2	889	SYS	cursor: pin S wait on X
2	891	SDM_PART	cursor: pin S wait on X
2	892	SDM_PART	cursor: pin S wait on X
2	894	PART	cursor: pin S wait on X
2	895	SDM_PART	cursor: pin S wait on X
2	896	SDM_PART	cursor: pin S wait on X
2	900	SDM_PART	cursor: pin S wait on X
2	901	SDM_IVR	SQL*Net message from client
2	902	SDM_PART	cursor: pin S wait on X
2	905	SDM_PART	cursor: pin S wait on X
2	906	SDM_PART	cursor: pin S wait on X
2	908	SDM_PART	cursor: pin S wait on X
2	909	SYS	cursor: pin S wait on X
2	910	PART	cursor: pin S wait on X
2	913	PART	cursor: pin S wait on X
2	914	SDMI_BAS	SQL*Net message from client
2	915	SDM_IVR	SQL*Net message from client
2	916	SDM_PART	cursor: pin S wait on X
2	920	SDM_PART	cursor: pin S wait on X
2	921	PART	cursor: pin S wait on X
2	922	SDM_PART	cursor: pin S wait on X
2	924	SYS	cursor: pin S wait on X
2	926	SDM_PART	cursor: pin S wait on X
2	929	SDM_PART	cursor: pin S wait on X
2	932	SDM_PART	cursor: pin S wait on X
2	934	PART	cursor: pin S wait on X
2	937	SDM_PART	cursor: pin S wait on X
2	939	SDM_IVR	SQL*Net message from client
2	940	SDM_PART	cursor: pin S wait on X
2	943	SDM_PART	cursor: pin S wait on X
2	945	SDM_PART	cursor: pin S wait on X
2	948	SDM_PART	cursor: pin S wait on X
2	950	SDM_PART	cursor: pin S wait on X
2	951	PART	cursor: pin S wait on X
2	953	SDM_PART	cursor: pin S wait on X
2	955	SDM_PART	cursor: pin S wait on X
2	957	SDM_IVR	SQL*Net message from client
2	961	XIAOTG	PX Deq Credit: send blkd
2	964	DBSNMP	cursor: pin S wait on X
2	969	SDM_PART	cursor: pin S wait on X
2	971	SDM_IVR	SQL*Net message from client
2	974	SDM_PART	cursor: pin S wait on X
2	975	SDM_PART	cursor: pin S wait on X
2	976	SDM_PART	cursor: pin S wait on X
2	978	PART	cursor: pin S wait on X
2	980	SDM_PART	cursor: pin S wait on X
2	985	SDM_IVR	SQL*Net message from client
2	989	SDM_PART	cursor: pin S wait on X
2	992	SDM_PART	cursor: pin S wait on X
2	993	SDM_PART	cursor: pin S wait on X
2	994	SDM_PART	cursor: pin S wait on X
2	997	SDM_PART	cursor: pin S wait on X
2	998	SDM_PART	cursor: pin S wait on X
2	1002	SDM_PART	cursor: pin S wait on X
2	1004	SDM_IVR	SQL*Net message from client
2	1005	SDM_PART	cursor: pin S wait on X
2	1006	PART	cursor: pin S wait on X
2	1007	SDM_PART	cursor: pin S wait on X
2	1008	PART	cursor: pin S wait on X
2	1013	PART	cursor: pin S wait on X
2	1015	SDM_PART	cursor: pin S wait on X
2	1017	SDM_PART	cursor: pin S wait on X
2	1018	SDM_PART	cursor: pin S wait on X
2	1019	SDM_PART	cursor: pin S wait on X
2	1020	SDM_PART	cursor: pin S wait on X
2	1021	SDM_PART	cursor: pin S wait on X
2	1023	DBSNMP	cursor: pin S wait on X
2	1024	XIAOTG	cursor: pin S wait on X
2	1025	SDM_PART	cursor: pin S wait on X
2	1028	SDM_PART	cursor: pin S wait on X
2	1029	SDM_PART	cursor: pin S wait on X
2	1030	SDM_PART	cursor: pin S wait on X
1	51	SDM_CC	SQL*Net message from client
1	59	SDM_PART	row cache lock
1	60	SYSMAN	SQL*Net message from client
2	1032	PART	PX Deq: Execution Msg
2	1033	SDM_PART	cursor: pin S wait on X
2	1034	PART	cursor: pin S wait on X
2	1035	SDM_PART	cursor: pin S wait on X
1	62	SDM_SPM	cursor: pin S wait on X
1	67	SDM_PD	SQL*Net message from client
1	68	SDM_CC	SQL*Net message from client
1	69	SDM_SPM	cursor: pin S wait on X
2	1037	SDM_PART	cursor: pin S wait on X
2	1038	SYS	cursor: pin S wait on X
2	1039	SDM_PART	cursor: pin S wait on X
2	1040	SDM_PART	cursor: pin S wait on X
1	71	SDM_CC	SQL*Net message from client
1	74	SDM_PD	SQL*Net message from client
1	78	SDM_SPM	cursor: pin S wait on X
2	1042	SDM_IVR	SQL*Net message from client
2	1043		Streams AQ: qmn slave idle wait
2	1045	SDM_IVR	SQL*Net message from client
1	80	SDM_SPM	cursor: pin S wait on X
1	84	SDM_SPM	cursor: pin S wait on X
1	89	SDM_PART	cursor: pin S wait on X
2	1046	SDM_PART	cursor: pin S wait on X
2	1049	SDM_PART	cursor: pin S wait on X
2	1051	SDM_PART	cursor: pin S wait on X
2	1054	SDM_PART	cursor: pin S wait on X
1	91	SDM_IVR	SQL*Net message from client
1	92	SDM_SPM	cursor: pin S wait on X
1	93	SDM_PART	SQL*Net message from client
1	94	SDM_SPM	cursor: pin S wait on X
2	1056	SDM_PART	cursor: pin S wait on X
2	1059	SDM_PART	cursor: pin S wait on X
2	1060	SDM_IVR	SQL*Net message from client
2	1061	PART	SQL*Net message from client
1	96	SDM_SPM	cursor: pin S wait on X
1	103	SDM_PD	SQL*Net message from client
1	104	SDM_CC	SQL*Net message from client
2	1065	SDM_PART	cursor: pin S wait on X
2	1068		Streams AQ: waiting for time management or cleanup tasks
2	1071	SYS	SQL*Net message from client
2	1072		Streams AQ: qmn coordinator idle wait
1	110	SDM_SPM	cursor: pin S wait on X
1	112	SDM_PD	SQL*Net message from client
1	113	SDM_PD	SQL*Net message from client
1	114	SDM_IVR	SQL*Net message from client
2	1074	SYS	SQL*Net message from client
2	1077		rdbms ipc message
2	1078		rdbms ipc message
2	1080	SYS	SQL*Net message from client
1	121	SDM_SPM	cursor: pin S wait on X
1	126	SDM_SPM	cursor: pin S wait on X
1	127	SDM_SPM	cursor: pin S wait on X
2	1081	PART	cursor: pin S wait on X
2	1082		rdbms ipc message
2	1087		rdbms ipc message
2	1088		rdbms ipc message
1	130	SDM_SPM	cursor: pin S wait on X
1	131	SDM_SPM	cursor: pin S wait on X
1	137	SDM_SPM	cursor: pin S wait on X
1	139	SDM_CC	SQL*Net message from client
2	1089		rdbms ipc message
2	1090		rdbms ipc message
2	1091		smon timer
2	1092		rdbms ipc message
1	140	SDM_SPM	SQL*Net message from client
1	146	SDM_SPM	cursor: pin S wait on X
1	151	SDM_SPM	cursor: pin S wait on X
2	1093		rdbms ipc message
2	1094		rdbms ipc message
2	1095		rdbms ipc message
2	1096		rdbms ipc message
1	153	SYSMAN	SQL*Net message from client
1	156	SDM_PART	cursor: pin S wait on X
1	157	SDM_PART	cursor: pin S wait on X
1	161	LITEMP	SQL*Net message from client
2	1097		gcs remote message
2	1098		gcs remote message
2	1099		gcs remote message
2	1100		gcs remote message
1	162	SDM_SPM	cursor: pin S wait on X
1	163	SDM_CC	SQL*Net message from client
1	166	SDM_PART	cursor: pin S wait on X
2	1101		ges remote message
2	1102		rdbms ipc message
2	1103		rdbms ipc message
2	1104		DIAG idle wait
2	1105		pmon timer
1	167	SDM_SPM	cursor: pin S wait on X
1	169	SDM_SPM	cursor: pin S wait on X
1	170	SDM_SPM	cursor: pin S wait on X
1	171	SDM_SPM	cursor: pin S wait on X
1	172	SDM_PART	SQL*Net message from client
1	175	SDM_PART	cursor: pin S wait on X
1	176	SDM_SPM	cursor: pin S wait on X
1	178	SYSMAN	SQL*Net message from client
1	179	SDM_SPM	cursor: pin S wait on X
1	180	SDM_SPM	cursor: pin S wait on X
1	181	SDM_SPM	cursor: pin S wait on X
1	182	SDM_SPM	cursor: pin S wait on X
1	183	SYSMAN	SQL*Net message from client
1	184	SDM_SPM	cursor: pin S wait on X
1	188	SDM_SPM	cursor: pin S wait on X
1	189	SDM_SPM	cursor: pin S wait on X
1	192	SDM_SPM	cursor: pin S wait on X
1	193	SDM_IVR	SQL*Net message from client
1	194	SDM_SPM	cursor: pin S wait on X
1	195	SDM_SPM	cursor: pin S wait on X
1	197	SDM_IVR	SQL*Net message from client
1	198	SDM_PART	cursor: pin S wait on X
1	201	SDM_SPM	row cache lock
1	203	SDM_SPM	cursor: pin S wait on X
1	204	SDM_SPM	row cache lock
1	206	SDM_IVR	SQL*Net message from client
1	207	SDM_SPM	cursor: pin S wait on X
1	209	SYSMAN	SQL*Net message from client
1	213	SDM_SPM	SQL*Net message from client
1	216	SDM_SPM	cursor: pin S wait on X
1	217	SYSMAN	SQL*Net message from client
1	225	SDM_PART	row cache lock
1	226	SDM_SPM	cursor: pin S wait on X
1	227	SDM_SPM	cursor: pin S wait on X
1	228	SYSMAN	SQL*Net message from client
1	229	SDM_CC	SQL*Net message from client
1	230	SYSMAN	SQL*Net message from client
1	231	SDM_SPM	cursor: pin S wait on X
1	232	SDM_SPM	cursor: pin S wait on X
1	234	SDM_IVR	SQL*Net message from client
1	235	SDM_SPM	SQL*Net message from client
1	238	SDM_IVR	SQL*Net message from client
1	240	SDM_IVR	SQL*Net message from client
1	243	SDM_CC	SQL*Net message from client
1	244	SDM_SPM	cursor: pin S wait on X
1	245	SDM_CC	SQL*Net message from client
1	246	SDM_SPM	cursor: pin S wait on X
1	249	SYSMAN	SQL*Net message from client
1	250	SYSMAN	SQL*Net message from client
1	251	SDM_PART	row cache lock
1	252	SDM_CC	SQL*Net message from client
1	253	SDM_PD	SQL*Net message from client
1	254	SYSMAN	SQL*Net message from client
1	255	SDM_IVR	SQL*Net message from client
1	256	SDM_SPM	cursor: pin S wait on X
1	257	SDM_PART	row cache lock
1	259	SDM_IVR	SQL*Net message from client
1	262	SDM_CC	SQL*Net message from client
1	265	SDM_SPM	cursor: pin S wait on X
1	267	SDM_CC	SQL*Net message from client
1	268	SDM_SPM	cursor: pin S wait on X
1	271	SDM_CC	SQL*Net message from client
1	273	XIAOTG	PX Deq: Execute Reply
1	274	SDM_PART	row cache lock
1	275	SDM_SPM	cursor: pin S wait on X
1	276	XIAOTG	SQL*Net message from client
1	277	SDM_SPM	cursor: pin S wait on X
1	278	SYSMAN	SQL*Net message from client
1	280	SDM_SPM	cursor: pin S wait on X
1	281	DBSNMP	SQL*Net message from client
1	282	SDM_IVR	SQL*Net message from client
1	283	SDM_PD	SQL*Net message from client
1	284	SDM_PART	row cache lock
1	287	PART	row cache lock
1	290	SDM_IVR	SQL*Net message from client
1	292	SDM_SPM	cursor: pin S wait on X
1	294	SDM_SPM	cursor: pin S wait on X
1	296	SDM_CC	SQL*Net message from client
1	297	SDM_SPM	SQL*Net message from client
1	298	SDM_PART	SQL*Net message from client
1	300	SDM_SPM	cursor: pin S wait on X
1	302	SDM_SPM	cursor: pin S wait on X
1	303	SDM_SPM	cursor: pin S wait on X
1	304	SDM_CC	SQL*Net message from client
1	305	SDM_PD	SQL*Net message from client
1	308	SDM_SPM	cursor: pin S wait on X
1	311	SDM_SPM	cursor: pin S wait on X
1	312	SDM_SPM	cursor: pin S wait on X
1	313	SDM_SPM	cursor: pin S wait on X
1	314	SDM_PD	SQL*Net message from client
1	316	SDM_PD	SQL*Net message from client
1	317	SDM_SPM	cursor: pin S wait on X
1	319	SDM_SPM	cursor: pin S wait on X
1	323	SDM_SPM	cursor: pin S wait on X
1	324	SDM_SPM	cursor: pin S wait on X
1	325	SDM_SPM	cursor: pin S wait on X
1	326	SDM_PD	SQL*Net message from client
1	330	SDM_SPM	cursor: pin S wait on X
1	331	SDM_SPM	cursor: pin S wait on X
1	333	SDM_SPM	SQL*Net message from client
1	335	SDM_CC	SQL*Net message from client
1	336	SDM_SPM	cursor: pin S wait on X
1	337	SDM_CC	SQL*Net message from client
1	339	SDM_SPM	cursor: pin S wait on X

ITPub博客

dd

最新文章