Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to view running stored procedures in sql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Recently, the project has been adding fields to the table, and many invalid objects need to be compiled, and it is often found that the compiled session HANG is there because the process is running until the end of the process.

If only there was a way to tell me which processes were running in the database, then we could choose to add fields after the tables that depend on these processes to avoid this problem.

You can locate the running stored procedure with the following query:

Col name for a40

Select name,locks,pins

From v$db_object_cache

Where locks > 0 and pins > 0 and type='PROCEDURE'

NAME LOCKS PINS

P_GLOBAL_ACCOUNT_SM 1 1

PRO_SERVICE_MONITOR_VAS 4 1

BUILD_ORD_ORDER_SEARCH_PRO 23 1

The output of the locks in the query result represents the library cache lock of n sessions holding the object in the library cache area.

The output of pins represents that there are n sessions holding the library cache pin of the object in the library cache area.

The locks and pins in the v$db_object_cache view represent how many sessions on the object hold the library cache lock/pin on the object.

But it doesn't tell you which session holds it, nor can it tell you the pattern you hold. If only to be able to compile the process smoothly, it is enough to know the above information.

Furthermore, if you want to know which conversations are performing this process, it will take a bit of effort.

In fact, the condition locks of the above query statement is greater than 0 is not required:

1) during the running of the stored procedure, library cache lock will add a lock of null and library cache pin will add a lock of s.

Use this lock to protect the memory HEAP stored by the code while the stored procedure is running. If you compile while the stored procedure is running, you will encounter library cache pin waiting.

Because the compiled session needs to get the x-mode library cache pin, this x-mode is not compatible with the s-mode held by the session executing the process and waits.

2) but historically, if a session executes a procedure more than 3 times, then the session may also retain the library cache lock of the null mode of the library cache object

Even if the session does not currently execute the procedure, the library cache lock of the null will be retained. No locks are added to library cache pin, which is what happens when session_cached_cursors is turned on.

Of course, the function of this parameter is not only limited to the PL/SQL process, but also to the cursor, and the function of the library cache lock that retains the null is that the pointer to the library cache object is retained in the pga, and the next parse

It can be accurately located, and you don't have to search in hash bucket if you hold library cache latch for a long time (relatively).

According to the above discussion, we can know that if the process is executing, pin must hold it, and if the process does not execute pin, it must not be held (compilation holding time is extreme, so we can ignore it), then pins > 0 can represent this.

The process has n sessions running it, and the value of n is equal to the value of pins.

Select name,locks,pins

From v$db_object_cache

Where type='PROCEDURE' and rownum 0 and pins > 0 and type='PROCEDURE'

NAME LOCKS PINS

TMP_PREPARE_SYNC_DATA 4 1

A pins of 1 means that one process is running the process. A locks of 4 means that four sessions hold the library cache lock of the null pattern in this process, and it can be inferred that 3 of the locks are

These session histories have performed this process and are no longer running.

Select sid,sql_text from v$open_cursor where sql_text like'% tmp_prepare_sy%' and user_name='RETL_RPT'

SID SQL_TEXT

2142 call RETL_RPT.tmp_prepare_sync_data ()

1880 call RETL_RPT.tmp_prepare_sync_data ()

2107 call RETL_RPT.tmp_prepare_sync_data ()

1851 call RETL_RPT.tmp_prepare_sync_data ()

Unfortunately, when we look at v$open_cursor, we can get the sid that executes this process, but it is not difficult to find that the result of our query shows four records, that is, this view will put the current cursor in the

The open status shows that only one session is performing the process we are paying attention to. At this point, we can use v$session to determine which sql these sessions are currently performing.

If the executed sql is included in the process we focus on, then we can locate the session that executes the process we care about.

@ active

SID SPID EVENT P1 P2 P3 SQL_ID SECON

--

1428 1130998 SQL*Net message from dbli 675562835 1 0 bzrggnv5fqp7x 304

1517 2314552 SQL*Net message to client 1650815232 1 0 3t37hp1cnkuux 0

1801 2126202 db file scattered read 27 93442 16 a5s8306j8a699 1

1849 405924 db file scattered read 142 476281 7 2zvv5wpg7qajb 70

1644 1761680 db file sequential read 318 446010 1 4xk36k7z79fpj 10

1737 1663014 db file sequential read 62 180837 1 536qa75pznr0z 8

1804 1302550 db file sequential read 278 341240 1 8vtas2njh5t3c 369

1835 1085950 db file sequential read 23 58000 1 faywn3b7f7p19 0

1851 1606066 db file sequential read 109 630082 1 anfr2phncqn6t 603

You can see that only 1851 are not idle waiting, its operating system process number is 1606066, currently running anfr2phncqn6t (sql_id) this statement, and finally locate this sql is in our stored procedure. The other three sessions are idle.

It seems that it is not easy to locate which sessions a process is being performed in oracle. In fact, after the whole process is familiar, it is not troublesome to locate it.

The accompanying active script is as follows:

Select / * + use_nl (a recalcitrant bpje c) * / distinct a. Sidrech a. Serialrecy a. Usernamememe. Terminal.a. Machine.programmeme b. Spidjold c. Sqljurisdiction c. Sqlgift text as sql_text1 c.

From v$session a dint vandalism process breel vandal SQL c

Where a.paddr = b.addr (+)

And a.sql_hash_value = c.hash_value

And a.sql_address = c.address

And a.status = 'ACTIVE'

And a.type = 'USER'

/

Select / * + ordered use_nl (aforme b) * / a.sid as sid,b.spid as spid,substr (c. Eventminary 1m 25) as event,c.p1,c.p2,c.p3,trim (to_char (a.sql_id)) as sql_id,to_char (LAST_CALL_ET) as seconds

From v$session a minute vandalism process brew vandals sessionkeeper wait c

Where a.type = 'USER' and a.status =' ACTIVE'

And a.paddr = b.addr

And a.sid = c.sid

And a.wait_class' Idle'

Order by event

/

This is the end of this article on "how to view running stored procedures in sql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.