查询某个session造成lmode为3的TM锁的表:
点击(此处)折叠或打开
- select owner,object_name,subobject_name
- from dba_objects
- where object_id in (
- select object_id
- from v$locked_object
- where locked_mode=3 and session_id=798);
上面这条sql需要执行很长时间,而子查询select object_id from v$locked_object where locked_mode=3 and session_id=798执行很快,父查询select owner,object_name,subobject_name from dba_objects where object_id in ('aaa','bbbb','cccc')也执行很快。
后来做了调优,具体如下:
点击(此处)折叠或打开
- with a as (
- select object_id
- from v$locked_object
- where locked_mode=3 and session_id =798)
- select b.owner,b.object_name,b.subobject_name
- from dba_objects b,a
- where b.object_id=a.object_id;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31441616/viewspace-2153265/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31441616/viewspace-2153265/