Home » SQL & PL/SQL » SQL & PL/SQL » Monitoring the status of a pl/sql procedure (merged 7)
Monitoring the status of a pl/sql procedure (merged 7) [message #444790] Wed, 24 February 2010 03:55 Go to next message
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 [message #444792 is a reply to message #444790] Wed, 24 February 2010 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does the procedure do?
Re: Monitoring the status of a pl/sql procedure [message #444796 is a reply to message #444790] Wed, 24 February 2010 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can:
- follow statistics in v$sesstat, if procedure is reading/modifying blocks or commit/rollback
- use dbms_application_info.set_client_info in your procedure to set client_info with what you want
- use dbms_application_info.set_session_longops in your procedure to set with units you process and can see it in v$session_longops
...

Regards
Michel
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 Go to previous messageGo to next message
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 #444827 is a reply to message #444826] Wed, 24 February 2010 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can join it with v$statname to get the statistics you want.
If you don't know what are these statistics, better use another way to follow your procedure activity.

Regards
Michel
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 Go to previous messageGo to next message
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 #444857 is a reply to message #444850] Wed, 24 February 2010 09:46 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi thanks for that. I am using 10.2

The problem i have is i can use sql_text to look at the query but all i see is this

 CALL update_invalid_records()  


That is the call to the pl/sql procedure. I cant find a way to see any child queries from the above call.
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444859 is a reply to message #444857] Wed, 24 February 2010 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do as I said: use dbms_application_info package.

Regards
Michel
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444869 is a reply to message #444859] Wed, 24 February 2010 10:20 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

To use dbms_application_info i will have to stop the process which i cant really do at the moment.

Thanks.
Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444873 is a reply to message #444869] Wed, 24 February 2010 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As we don't know what it does we can't tell you what you can have a look at.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #444891 is a reply to message #444886] Wed, 24 February 2010 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can follow "redo writes" statistics.

Too 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).

Regards
Michel

[Updated on: Wed, 24 February 2010 12:19]

Report message to a moderator

Re: Monitoring the status of a pl/sql procedure (merged 7) [message #444892 is a reply to message #444886] Wed, 24 February 2010 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.


COMMIT is a double edged sword.
Without intervening COMMIT in existing procedure, no other session can see what data is being changed.

Having intervening COMMIT would provide visibility, but it would make performance worse & possibly cause other problems; like ORA-01555 errors.
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 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 24 February 2010 19:18
Too 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 Go to previous messageGo to next message
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 #445003 is a reply to message #444790] Thu, 25 February 2010 03:33 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you post the code we can probably give you some tips on how to speed it up.
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 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ziggy25 wrote on Thu, 25 February 2010 10:22
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.

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

Previous Topic: retrieve one value for phone number
Next Topic: Trigger problem
Goto Forum:
  


Current Time: Mon Feb 17 17:28:18 CST 2025