Home » RDBMS Server » Performance Tuning » How we can find %age completion of query when is in running status (Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit)
How we can find %age completion of query when is in running status [message #553169] Wed, 02 May 2012 08:13 Go to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
i,

How can we check completion status for running sql query. i.e. how much % completed



SQL> begin
  2  delete from  gsmcrmdw.wc_loy_txn_f_aa
  3  where  integration_id in
  4  ( select  integration_id
  5    from    support_olap.recover_wc_loy_txn_f_953to955
  6  );
  7  commit;
  8  end;
  9  /



Re: How we can find %age completion of query when is in running status [message #553173 is a reply to message #553169] Wed, 02 May 2012 08:25 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
In your case you can try to look into v$session_longops.
Re: How we can find %age completion of query when is in running status [message #553174 is a reply to message #553169] Wed, 02 May 2012 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it runs long enough it'll show up in the v$session_longops view.
Otherwise there is no way as far as I'm aware.
Re: How we can find %age completion of query when is in running status [message #553176 is a reply to message #553174] Wed, 02 May 2012 08:26 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
yes I already checked and not able to pick that running query.

I shere any other way?
Re: How we can find %age completion of query when is in running status [message #553178 is a reply to message #553176] Wed, 02 May 2012 08:28 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
is there any other way for checking completion status for running small pl/sql block i.e. having simple delete query?
Re: How we can find %age completion of query when is in running status [message #553181 is a reply to message #553178] Wed, 02 May 2012 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
No
Re: How we can find %age completion of query when is in running status [message #553184 is a reply to message #553181] Wed, 02 May 2012 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>is there any other way for checking completion status for running small pl/sql block i.e. having simple delete query?
uncommited DML can not be seen by other sessions.
Re: How we can find %age completion of query when is in running status [message #553185 is a reply to message #553184] Wed, 02 May 2012 08:39 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Thanks BlackSwan,

This logic tells the perfect solution.

Thanks a lot !!!
Re: How we can find %age completion of query when is in running status [message #553186 is a reply to message #553174] Wed, 02 May 2012 08:40 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Recording in v$session_longops depends rather on kind of operation (for ex. fts, hash join, etc.). You cannot find there index range scans regardless how long they run.
Re: How we can find %age completion of query when is in running status [message #553187 is a reply to message #553186] Wed, 02 May 2012 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>This logic tells the perfect solution.
The "solution" is NOT to place COMMIT inside any LOOP!
Re: How we can find %age completion of query when is in running status [message #553188 is a reply to message #553169] Wed, 02 May 2012 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With experience you can estimate the progression and completion from the size of undo records used by the transaction.
Check v$transaction.used_ublk.

Regards
Michel
Re: How we can find %age completion of query when is in running status [message #553189 is a reply to message #553186] Wed, 02 May 2012 08:56 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
But we can perform intersession communication,so why we can't see running .sql query by other session in our existing session..

As per logic it can perform,if we can do intersession communication...
Re: How we can find %age completion of query when is in running status [message #553190 is a reply to message #553189] Wed, 02 May 2012 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>But we can perform intersession communication,
>so why we can't see running .sql query by other session in our existing session..
Oracle provides Read Consistent View of the data.
the DELETEd rows may be rolled back.
Any session can only see data that has been committed.

Re: How we can find %age completion of query when is in running status [message #553191 is a reply to message #553188] Wed, 02 May 2012 09:06 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Michel,

Actually we have 355 partitions i.e. exchange partition in our table and how we can check how much data deleted from complete table till now and how much % query completed and our table is having more than 80 million data.so,we want to check query execution status.


Re: How we can find %age completion of query when is in running status [message #553193 is a reply to message #553190] Wed, 02 May 2012 09:09 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Blackswan,

No i can not agree with your views because we can perform intersession communication.
Re: How we can find %age completion of query when is in running status [message #553196 is a reply to message #553193] Wed, 02 May 2012 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can disagree all you like. Oracle does not directly expose the progress of sql statements.
The fact that packages exist to allow comunication between sessions makes no difference to this.
Re: How we can find %age completion of query when is in running status [message #553199 is a reply to message #553196] Wed, 02 May 2012 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>No i can not agree with your views because we can perform intersession communication.
What exactly does having the ability to perform intersession communication
have to do with other session being able to see uncommitted data?

please take a few minutes to educate yourself about how Oracle actually works

http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABEAGIA
Re: How we can find %age completion of query when is in running status [message #553201 is a reply to message #553196] Wed, 02 May 2012 09:21 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hI Cookie,

I give one scenerio i.e. when we are using dbms_pipe package in pl/sql then we are transferring data from one session to other session and i think there is no commit from source side,so how data is moving into pipe for transferring data to Destination session.

Please Explain?

Re: How we can find %age completion of query when is in running status [message #553204 is a reply to message #553201] Wed, 02 May 2012 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Pipes don't need commits.
This in no way changes the fact that oracle does not expose the data you are after.
Re: How we can find %age completion of query when is in running status [message #553205 is a reply to message #553204] Wed, 02 May 2012 09:28 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Cookie,

I want to know what is the logic implemented in dbms_pipe for moving data from source session to destination session.Same logic i want to implement for getting information from running session.




Re: How we can find %age completion of query when is in running status [message #553206 is a reply to message #553188] Wed, 02 May 2012 09:29 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi Michel,

I checked but not getting clear picture for all partitions.

Please help !!!
Re: How we can find %age completion of query when is in running status [message #553207 is a reply to message #553205] Wed, 02 May 2012 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>Same logic i want to implement for getting information from running session.
The session doing the DELETE does not know how many rows are involved until after the DELETE completes.
Besides session doing the DELETE is busy & can not do anything else while DELETE is in process.
You can't do what you want to do!
Re: How we can find %age completion of query when is in running status [message #553208 is a reply to message #553207] Wed, 02 May 2012 09:34 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Blackswan,

That means we can't find how much data is deleted if we catch that session using dbms_pipe?
Re: How we can find %age completion of query when is in running status [message #553210 is a reply to message #553208] Wed, 02 May 2012 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>That means we can't find how much data is deleted if we catch that session using dbms_pipe?
CORRECT!
Re: How we can find %age completion of query when is in running status [message #553211 is a reply to message #553205] Wed, 02 May 2012 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Wed, 02 May 2012 15:28
hi Cookie,

I want to know what is the logic implemented in dbms_pipe for moving data from source session to destination session.Same logic i want to implement for getting information from running session.


Session 1 puts data in pipe. Session 2 gets data out of pipe.
Your problem is that session 1 can't put data in the pipe if it's busy doing something else - like a delete.

You could change your process to delete a row at time in a for loop and send data to a pipe between deletes.
The problem with that is that it'll make your already slow delete massively slower.

So you can do what you want but it'll cripple performance.
Re: How we can find %age completion of query when is in running status [message #553212 is a reply to message #553211] Wed, 02 May 2012 09:43 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Cookie,

I am not bothering about performance and only i want to achieve that solution.Then after that i will bother about performance as well.

If you can help please help me.
Re: How we can find %age completion of query when is in running status [message #553215 is a reply to message #553212] Wed, 02 May 2012 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
When you start with the wrong question, no matter how good an answer you get, it won't matter very much.
Your obsession with this metric of completion % is 100% totally misguided!
Re: How we can find %age completion of query when is in running status [message #553216 is a reply to message #553212] Wed, 02 May 2012 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Saying you'll bother about performance later is pointless.
The only way to fix the performance of the for loop approach is to remove the for loop.
So you can't fix it later.
Re: How we can find %age completion of query when is in running status [message #553217 is a reply to message #553215] Wed, 02 May 2012 09:48 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Hi Blackswan,

That means we can not find solution for this question???
Re: How we can find %age completion of query when is in running status [message #553219 is a reply to message #553217] Wed, 02 May 2012 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>That means we can not find solution for this question???
How will having the answer, change your behavior while the DELETE is in progress?
Re: How we can find %age completion of query when is in running status [message #553221 is a reply to message #553217] Wed, 02 May 2012 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can just repeat my answer:

Michel Cadot wrote on Wed, 02 May 2012 15:53
With experience you can estimate the progression and completion from the size of undo records used by the transaction.
Check v$transaction.used_ublk.


Regards
Michel

Re: How we can find %age completion of query when is in running status [message #553225 is a reply to message #553221] Wed, 02 May 2012 10:26 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Thanks Michel !!!
Re: How we can find %age completion of query when is in running status [message #553368 is a reply to message #553169] Thu, 03 May 2012 22:05 Go to previous messageGo to next message
Kevin Meade
Messages: 1940
Registered: December 1999
Location: Connecticut USA
Senior Member
The question is difficult to answer and at the same time easy.

1) it is correct, that given Oracle's default consistency model, it is not possible to see the results of an insert/update/delete before it is committed. Therefore you cannot create a process that can directly interrogate tables to see how far along things are.

2) of course, the internal processes of the Oracle database can see this kind of information; they have to. Oracle routinely reads dirty pages as it does work and communicates this type of information between its processes as necessary. No real news there. This means that it is possible for Oracle to provide an API through which you can interrogate the system to get this kind of info and you can be sure they have such an API and use it in their toolsets. But it is not generally available and I know of no third party tools that takes advantage of same. How this information would be turned into a "progress picture" for a query or update etc. is another matter. Maybe you can ask Oracle corp. for the API? Worst they can do is say no.

3) You have been told the two most common ways of "watching the progress" of dml:

a) using v$session_longops to check the progress of Oracle expected long operations. A good example of this is a simple table scan of a large (large being whatever oracle decides). With some basic math and v$session_longops message interrogation, you can see the number of blocks in total to be read, and the number of blocks alread read, and various time elements, from which you can compute a %complete and estimated runtime, and completion date etc. I in fact do this all the time. In Data Warehouses were table scans are common, and in tuning long running queries where table scans are common, this is very helpful. I also use it to compare the "usable data movement speed" of different databases. Here is my special script for doing same. Have fun. This is one of my big 10 tools that gives me an edge over my fellow DBA'ers, but I guess it is time to share it.

col time_remaining head 'Seconds|Remaining'
col scanned_blocks head 'Scanned Blocks'
col all_blocks head 'All Blocks'
col blocks_remaining head 'Blocks|Remaining'
col opname format a25
col target format a35
col username format a15
col MB_per_Second form 990.0 head 'MB/s'
col pct_scanned head '%Scanned' format 990.00
col predicted_runtime_seconds head 'Estmd.|Runtime|Seconds'
col total_blocks head 'Total|Blocks'
col sid format 99990
col block_size format 99990 head 'Block|Size'
col hash_passes_temp format a20

with
      scan_data as (
                     select
                              to_number(
                                     substr(a.message
                                    ,instr(a.message,': ',1,2)+2
                                    ,instr(a.message,' out of ',1,1)-instr(a.message,': ',1,2)-1
                                    )
                                       ) 
                            / to_number(
                                     substr(a.message
                                    ,instr(a.message,' out of ',1,1)+8
                                    ,instr(a.message,' Blocks done',1,1)-instr(a.message,' out of ',1,1)-7
                                    )
                                       ) *100 pct_scanned
                           ,  to_number(
                                     substr(a.message
                                    ,instr(a.message,' out of ',1,1)+8
                                    ,instr(a.message,' Blocks done',1,1)-instr(a.message,' out of ',1,1)-7
                                    )
                                       )
                            - to_number(
                                     substr(a.message
                                    ,instr(a.message,': ',1,2)+2
                                    ,instr(a.message,' out of ',1,1)-instr(a.message,': ',1,2)-1
                                    )
                                       ) blocks_remaining
                            , a.time_remaining
                            , a.opname
                            , to_number(b.value) block_size
                            , a.target
                            , a.sid
                            , a.username
                     from   (
                              select
                                      replace(v$session_longops.message,'RMAN:','RMAN') message
                                    , v$session_longops.time_remaining
                                    , v$session_longops.opname
                                    , v$session_longops.target
                                    , v$session_longops.sid
                                    , v$session_longops.username
                              from  v$session_longops
                                  , v$session
                              where v$session_longops.sid = v$session.sid
                            ) a
                           ,v$parameter b
                     where a.time_remaining > 0
                     and b.name = 'db_block_size'
                   )
select 
         round(blocks_remaining*block_size/1024/1024/time_remaining,1) MB_per_Second
       , scan_data.time_remaining
       , round(time_remaining/(1-pct_scanned/100)) predicted_runtime_seconds
       , scan_data.pct_scanned
       , scan_data.blocks_remaining
       , round(blocks_remaining/(1-pct_scanned/100)) total_blocks
       , scan_data.opname
       , scan_data.BLOCK_SIZE
       , scan_data.target
       , (
          select
                  DECODE(MAX(NUMBER_PASSES),0,'OPTIMAL',1,'ONE-PASS',NULL,NULL,'MULTI-PASS')||DECODE(max(TEMPSEG_SIZE),NULL,NULL,','||max(TEMPSEG_SIZE))
          from v$sql_workarea_active
          where v$sql_workarea_active.sid = scan_data.sid
          and scan_data.opname in ('Hash Join','Sort Output')
          and OPERATION_TYPE in ('HASH-JOIN','SORT','WINDOW (SORT)')
         ) hash_passes_temp
       , scan_data.sid
       , scan_data.username
from scan_data
order by time_remaining
/


Unfortunately as you will soon see, there is still some guessing and fill in the blank you will have to do where for those queries that are not all FULL TABLE SCAN/HASH JOIN. INDEX LOOKUP and NESTED LOOP aren't shown along with lots of other stuff. Still, this is a very useful script for queries. It is of much less use to INSERT/UPDATE/DELETE.

b) watching "UNDO". This is way tougher and I almost never use it to watch a query. It normally requires that you know how much work is being done by the insert/update/delete and then you simply measure in your head the amount of undo done vs. what you have seen before. You can do similar things with almost any statistic or metric.

The most useful way I have found to watch UNDO is to see if the numbers are going up or down. Numbers going up means your transaction is doing work. Numbers going down means your transaction is rolling back. You can compute how long it will take to rollback by clocking some amount of undo number changes and then doing the math. Here is a simple showundo script. I have not run it in a while so like everything, no warranty express or implied...

    select  s.sid, s.username, rn.name,     rs.curext
           ,rs.curblk,  t.used_ublk, t.used_urec
    from    v$transaction   t
           ,v$session       s
           ,v$rollname      rn
           ,v$rollstat      rs
    where  t.addr     = s.taddr
    and    t.xidusn   = rn.usn
    and    rn.usn     = rs.usn
order by s.sid
/


Good luck. Maybe Oracle will provide us an update to EM one of these days with an "active execution" page that shows some graph or chart or other visual to help us understand an executing query.

Kevin
Re: How we can find %age completion of query when is in running status [message #553472 is a reply to message #553368] Fri, 04 May 2012 18:59 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
Kevin,

Thanks for sharing these scripts. I will need to check these out.

- Bobby
Re: How we can find %age completion of query when is in running status [message #553477 is a reply to message #553472] Fri, 04 May 2012 21:25 Go to previous messageGo to next message
Kevin Meade
Messages: 1940
Registered: December 1999
Location: Connecticut USA
Senior Member
have at it my friend.
Re: How we can find %age completion of query when is in running status [message #553496 is a reply to message #553169] Sat, 05 May 2012 06:38 Go to previous message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
well ... you posted that you are not interested in performance so....we can device a solution.
(however I would not ever use it in a serious setup)

1 - create a logging table
2 - create a procedure which logs to that table. This table uses the "pragma autonomous_transaction"
create or replace procedure message2log (message IN varchar2)
is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into log values (sysdate,message);
  commit;
end;

3 - create a trigger on the table you are deleting from which uses the procedure from step 2
create or replace trigger log4table
  before delete
  on <table_name>
  for each row
begin
  message2log('deleting '||:old.num);
end;

4 - start the delete process on your table
5 - query your log-table to see how far you are

Again.....you can see how far the process is, but the process will be soooooo slow. Also it will take a lot of space. And...what happens when you do not commit the delete?.....your logging tells you the rows are deleted.

Have fun
Previous Topic: How we can check count for each partition
Next Topic: Parallel index with Non-partitioned Noparallel table
Goto Forum:
  


Current Time: Thu Oct 02 00:01:08 CDT 2014

Total time taken to generate the page: 0.10759 seconds