Monitoring the status of a pl/sql procedure (merged 7) [message #444790] |
Wed, 24 February 2010 03:55  |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
Hi All,
I've got a pl/sql procedure that i would like to monitor its progress. Its been running for quite a few hours now and i dont really know if it is stuck in a loop or if it is just taking too long read/execute queries. Is there a way to find out what is the status of the procedure? or what it is currently doing?
I looked at views like v$sessions or v$session_longops but they dont actually tell me what it is currently doing. All of them just point to the call to the pl/sql procedure.
Also, is it possible to look at the physical files on the filesystem to know if there is actually any processing going on and files are being written to? Is this possible or a files written to only after a commit?
Thanks
|
|
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444826 is a reply to message #444790] |
Wed, 24 February 2010 06:21   |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
Sorry guys, i had trouble saving this post and i think i probably saved it several times.
The procedure is reading rows from one table, processing it and makes updates to other tables.
Here is the output to v$sesstat. How do i decode this to an operation? Do i need to join it to some other view?
SID STATISTIC# VALUE
---------------------- ---------------------- ----------------------
248 377 0
[edit MC: remove content of v$sesstat]
[Updated on: Wed, 24 February 2010 06:46] by Moderator Report message to a moderator
|
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444850 is a reply to message #444827] |
Wed, 24 February 2010 08:48   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
What's your database version?
This here I use on 10.2.X databases when something is "hanging" or "running" to see what it is doing and waiting for at the moment.
select DISTINCT s.sid,s. SERIAL#, event, p1text, p1, p2text,
p2, p3text, p3, wait_time, seconds_in_wait, state,
osuser, program,client_info, s.MODULE, sql_text
from v$session s
join v$sql q ON q.hash_value = s.sql_hash_value
where event not in ('SQL*Net message from client',
'SQL*Net message to client',
'jobq slave wait')
AND event not like 'PX Deq Credit%';
|
|
|
|
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444886 is a reply to message #444790] |
Wed, 24 February 2010 12:07   |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
The procedure is processing a table that has has 2,383,828 two million rows. I just looked at the table definition and already found a lot of problems with that table. For example the table has not indexes.
i run this query
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
And the output is shown below
SQL_FULLTEXT |SQL_ID |CHILD_NUMBER|DISK_READS|EXECUTIONS|FIRST_LOAD_TIME |LAST_LOAD_TIME
--------------------------------------------------------------------------------|-------------|------------|----------|----------|-------------------|-------------------
CALL update_invalid_records() |fab70rhmqu24f| 0|1059316946| 3|2010-02-22/10:53:22|2010-02-22/10:53:22
UPDATE TEMP_TABLE SET INVALID = 'Y' WHERE CODE = :B2 AN |44czxccc9m183| 1| 535976319| 22151|2010-02-22/10:53:23|2010-02-24/00:28:25
UPDATE TEMP_TABLE SET INVALID = 'Y' WHERE CODE = :B2 AN |44czxccc9m183| 0| 358547504| 17747|2010-02-22/10:53:23|2010-02-23/11:20:36
call dbms_stats.gather_database_stats_job_proc ( ) |b6usrg82hwsa3| 0| 89697| 1|2010-01-20/22:00:16|2010-02-23/22:00:22
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n|6ssrk2dqj7jbx| 0| 2| 607572|2010-01-20/11:16:38|2010-01-20/11:16:38
select size_for_estimate, size_factor * 100 f, |aykvshm7zsabd| 0| 0| 101127|2010-01-20/11:16:23|2010-02-24/14:01:07
select DBMS_METADATA.get_dependent_ddl('CONSTRAINT',:NAME,:OWNER) sql from user_|6myxax5g4pwzd| 0| 32| 148|2010-02-23/13:05:12|2010-02-23/13:05:12
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd|g2wr3u7s1gtf3| 0| 0| 607553|2010-01-20/11:16:34|2010-01-20/11:16:34
insert into source$(obj#,line,source) values (:1,:2,:3) |9mgpsp39pxjqg| 2| 3| 24|2010-01-20/11:16:30|2010-02-24/10:37:17
The first line is the call to the pl/sql procedure and the 2nd and 3rd is a query in that stored procedure. What is happening there is
that it reads some data from another table (Table B) which has around 110,459. For each row in table B it updates the TEMP_TABLE as shown in the result query above
The code itsself looks something like this
WHILE TableB%found
LOOP
UPDATE TEMP_TABLE
SET INVALID = 'Y'
WHERE CAR_CODE = TableB.CODE
FETCH TableB INTO rec_TableB;
END LOOP;
Obviously because there are no indexes in the table it will take too long to complete.
Is there anyway i can estimate how long it will take to process everything using the values shown on the output above and taking the number of rows in the tables.
Thanks
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444889 is a reply to message #444790] |
Wed, 24 February 2010 12:15   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Indexes could be a red herring but without a more complete look at what the procedure is doing it's hard to tell.
Remember that if your queries hit a high percentage of rows in a table then oracle will ignore indexes and do a full table scan as it's more efficient.
Also bare in mind that indexes have to be modified when you update a table so not having them there might actually help.
I suspect the real problem here however is that you are doing row by row processing instead of a single update statement which would be:
a) more efficient
b) measurable via v$session_longops
|
|
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444979 is a reply to message #444891] |
Thu, 25 February 2010 00:45   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 24 February 2010 19:18Too bad you launched your procedure before asking us as we could say you that you could do it in a single statement instead of this slow by slow process ((c) T. Kyte, i.e. row by row).
Easy said in retrospect.
Guess what answer he would have gotten if he had asked "I have this procedure and I want to know what will happen when I run it"
I think the important thing here is that the original poster is so afraid to just cancel the execution, that it makes me think that he is running it on production. If so, he probably did not run it first on a similar test environment..
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444998 is a reply to message #444790] |
Thu, 25 February 2010 03:22   |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
No it is not running on a production environment but it is running on a formal test environment. I have to let it run to completion as i have to include how long it took to run in the test report. If i make any changes to the delivered code then that would invalidate the test. Thats the only reason i cant stop it.
|
|
|
|
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #445014 is a reply to message #444998] |
Thu, 25 February 2010 04:02  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
ziggy25 wrote on Thu, 25 February 2010 10:22No it is not running on a production environment but it is running on a formal test environment. I have to let it run to completion as i have to include how long it took to run in the test report. If i make any changes to the delivered code then that would invalidate the test. Thats the only reason i cant stop it.
I agree but if you provide a code that executes 10 times faster, I bet you will be forgiven to have aborted the test.
Regards
Michel
|
|
|