See why stored procedures in Oracle are stuck for a long time

Source: Internet
Author: User
Tags session id sessions

1: Check V$db_object_cache

SELECT * from V$db_object_cache WHERE name= ' cux_oe_order_rpt_pkg ' and locks!= ' 0 ';

Note: Cux_oe_order_rpt_pkg is the name of the stored procedure.

Discover locks=2

2: Find the SID value by object

Select/*+ rule*/SID from v$access WHERE object= ' cux_oe_order_rpt_pkg ';

Note: Cux_oe_order_rpt_pkg is the name of the stored procedure.

3: Check sid,serial#

SELECT sid,serial#,paddr from v$session WHERE sid= ' SID ' just found;

4, depending on the session ID (SID), the wait event for this session:

[SQL]View PlainCopy
    1. SELECT * from v$session where sid=***;

The event field is waiting for events. After querying we find that this session waits for the event to be sql*net message from Dblink; two days before the logon_time of the viewing session. This time is much more than our estimated time.

5. View the SQL statement that this session is executing based on the session ID

[SQL]View PlainCopy
    1. Select Sql_text from v$sqlarea where address= (select sql_address from v$session where sid=***);

The query found that the SQL statement that was being executed was inserted into table B by querying data for a table on the remote database via Dblink.

6. Connect to the remote database and query the object that is currently locked

[SQL]View PlainCopy
    1. SELECT * from v$locked_object lo,
    2. All_objects ao where lo.  Object_id= ao.object_id;

View after discovering that the remote database does not involve A, B is locked

7. View the session for remote data:

[SQL]View PlainCopy
    1. SELECT * from v$session where terminal like '% machine name% ' and program=' Oracle.exe '

Using Dblink to connect to the remote database, the session on the remote database program should be Oracle.exe

The query found that two remote libraries sometimes had no related sessions at all, and sometimes related sessions, but their wait events were sql*net message from the Client remote library waiting for local Oracle to send him a request.

Dblink remote libraries such as local libraries, remote libraries await client messages. It seems that this stored procedure is not going to be done.

It is unclear what caused the problem.

The way to deal with this is to kill the conversation.


http://blog.csdn.net/fupei/article/details/7325190

Refer to the above article for specific steps

See why stored procedures in Oracle are stuck for a long time

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.