Shulou(Shulou.com)05/31 Report--

Oracle sql tuning example analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Query a table with a session that causes an TM lock with a lmode of 3:

Click (here) to collapse or open

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)

The above sql takes a long time to execute, while the subquery select object_id from v$locked_object where locked_mode=3 and session_id=798 executes quickly, as does the parent query select owner,object_name,subobject_name from dba_objects where object_id in ('aaa','bbbb','cccc').

Later, it was tuned as follows:

Click (here) to collapse or open

With an 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

As for the reason, it is not clear.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.