% Completed in long running Queries

[uparna]

Hi,

I have encountered this situation a million times as a DBA... there are migration queries that seem to take forever to complete because of the huge volumes of data involved. After 4 migrations under my belt , i am working on the 5th Migration now...and have this question --> in Oracle 8i/9i is there a method by which we can find out the percentage completion of a SQL query execution ?

For eg:- if i do a

update <tablename> x set <col2> =
( select col2 from <another table>y where y.col1=x.col1 );


and there are 2 mill rows in x and 2 mill rows in Y , its gonna take forever (fts)... after letting this run overnight , i would like to know in the morning ( if it is still running) , how much of it is still left and when i can expect it to complete.

I know that exact pinpoints are difficult for this ,but do you have any method of estimation/anything-else that can help me find the % completed?

Do let me know.

Regards,
S. Jayaram Uparna .

 

[lewisp]

I'm pretty sure there is no way Oracle will tell you how far through a query it is. Nothing gets reported back from the engine until the whole process is complete.

I hope to be corrected.

 

[sem]

Try to invesigate V$SESSION_LONGOPS view.



Regards, Dima

http://seminihin.narod.ru

 

[uparna]

sem , very very very intersting view.....didnt know about it. thanks !

However, i am in 8i now, am using 8i sql*plus client....and all over the place i see this error
ERROR at line 1:
ORA-01861: literal does not match format string

all i can do is do a select * from --- without a where clause or any sort of order by ... the error prevents any where clause and any order by. For an ordinary select * from , it shows the results and then gives the error message.

any idea why?

Regards,
S. Jayaram Uparna .

 

[uparna]

more over , this view shows "16412 OUT OF 16412 BLOCKS DONE" for this transaction --- but the query is still running...for a looOOng time it's been the same. Hmm....

Regards,
S. Jayaram Uparna .

 

[amrita418]

I know this is not a direct answer to your question but instead of running the whole update , you should break it down.

If nothing else, you should filter by rownum

update  TableA
set     col1 = (select col2 
                from   TableB
                where  a.id = b.id)
where   rownum < 100000

Best Regards,
AA

 

[sem]

If you decide to use amrita418 code you should split your work by key, not by rownum, as the code provided would update arbitrary in theory but in practice most probably the same set of 100000 rows.

update  TableA a
set     col1 = (select col2
                from   TableB b
                where  a.id = b.id)
where a.id> last_processed_id and  rownum < 100000

Regards, Dima

http://seminihin.narod.ru

 

[uparna]

yup. Thanks....i already do that for a couple of them. Infact , i write a procedure taking 1000 or 5000 rows at a time and committing them in a loop.

But sometimes i am not in a mood to write procedures, so i write one SQL, and then sit and bonk my head on the desk waiting for this to complete.

For those who are curious to know what happenned to my x and y tables -- i waited 24 hours for it to complete (let it run on a spare desktop i have). It didnt complete , so killed it , rolled it back and wrote a procedure. The procedure completed in 13 minutes <dont worry, i slapped my forehead quite a few times after this. From now on , screw to my mood(forgive my french)....i am going the procedure way only !>


Hey ,thanks all of you for assisting me. esp thanks for sem for introducing me to an interesting system view.

Regards,
S. Jayaram Uparna .