Checking to see if a query is running

[THoey]

I am using V$SESSION to look to see if a long query is actually doing anything or is it hung. I see a column called LAST_CALL_ET that is a numeric field that keeps incrementing. Anyone know what this field implies? And is there a better way to monitor a long query process?

Thanks in advance... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[stevecal]

You might explore DBMS_APPLICATION_INFO which will give you the information you need at as granular a level you need.

 

[MikeJones]

While very simplistic we would just run a job every hour that took a snapshot of the v$sql view and compare it with what it saw last time. if any SQL had been running in both the current and last snapshot then it had been running for atleast an hour and we would kill it off. It was rarely wrong.

If your looking for stuff that doesn't appear to be doing anything then v$sessio shows the io for a session. If it stops incrementing for a long period of time then you may wish to count that as 'hung'. I guess it depends on your definition of when a job is 'hung'.

 

[sem]

Killing sessions every hour is a bit unsafe, for it may be a huge batch job, gathering data for the annual report, expected by company president . Or at least some iterative task, such as pipe writer. It's much safer to monitore v$session+v$sesstat+[v$statname fore readability] .

 

[THoey]

Well, this script seems to still be running. What the query is doing is deleting old records from a table that had over 8 million rows in it. I am deleting based on a being less than SYSDATE - 400, so I know it is doing a full table scan and expected it to take a while, but not this long. In retrospect, I should have calculated what SYSDATE - 400 was and hardcoded this one time run, but I did it this way to test out a script that I eventually will run every night to keep the database small.

I will try your suggestions and see what I can. That LAST_CALL_ET field from the V$SESSION view is still incrementing and the rollback segment is getting larger (got to increase that before it hits the limit).

Any other suggestions would be greatly appreciated. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[sem]

The only thing I suggest is not to kill this session, for rollback takes even more time. Or kill the proccess (not very clean exit). For future uses you may specify larger rollback segment by set transaction use rollback segment to avoid fragmentation.

 

[THoey]

The current rollback segment is one gig, but I am about to double it on the fly as it is getting to 80% full. I really wish there was a way to know what percentage complete or an estimated time to completion. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Just trying to "guestimate" here when this should complete. The tablespace that this table is in is sized for 6 gig, but is just over 50% used (3 gig). That table is one of several in there, but is the largest of them all. Figuring that approximately 25-33% of that amount of data is what I am deleting, is it safe to assume (I hate using that word) that about the time my rollback segment gets to 1 gig, the query should complete? Or is there a bunch of overhead added with the record in the rollback segment? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Well, I am now at 81% of a 2 gig rollback statement. The LAST_CALL_ET from V$SESSION has jumped from 98427 to 152777. I am hoping that that value is not the number of rows it has deleted so far. Especially since I know that there are over 3 million rows to delete.

I looked at the DBMS_APPLICATION_INFO, but it looks like something you need to register and use when you start your application or query. Since I am almost two days into this query, it is a bit late.

Any other suggestions? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Yikes, what a mess! The only consolation is that you can see "progress". You wouldn't be generating all that rollback data if Oracle weren't still processing the deletes.

For what it's worth, LAST_CALL_ET is not the number of rows deleted. It is the elapsed time (in seconds) since the start of the most recent sql statement. In general it won't tell you what sql statements are slow, because I think it keeps incrementing even after the sql statement has finished executing. Of course, in this case you know for sure that your sql is still running.

152777/(60*60*24) = 1.768 days so yes, you're well into your second day of execution.

 

[THoey]

Thanks for verifying that it is still running and the mess I am in. This table is the contact table for a regional call center and has data in it from January 1999. I am at least trying to get it parsed down to April 2000 (a little over a year and a month for archival reporting).

Has anyone ever deleted this many records at one time (not counting a truncation) or seen any statistics on rows per minute in a delete? Anything... I just need some sort of estimate. I am about to have to increase my rollback segment to 3 gig... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

*** Posting this in the two threads that I have started on this subject ***

Well, it has been decided by our "DBA" to terminate the script that I was running and wait for it to rollback. Through Oracle DBA Studio, I did a Disconnect Immediate. As far as I can tell, this will terminate the script and allow the rollback segments to roll back. Am I correct in that?

At that time he has suggested the following:

- Alter the table to turn off logging
- Drop all indexes
- Run the delete script for the whole chunk of data
- Re-build all indexes

Since I have so little permissions on this database, my first question is since I own the objects in question, will I have permissions to do what he has suggested?

He also mentioned he could turn off database level logging and that might speed things a little bit more.

Does this sound feasible to you all? I trust this bunch here, more than I do him. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.