Home » RDBMS Server » Server Administration » query completion time/query completion %
query completion time/query completion % [message #322031] Thu, 22 May 2008 04:12 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,

I would like to know is that can i determine the execution time before query will run/while query is running ?

My problem is on the production sometimes it took me 30-40 minutes to execute the query.
is there any utility/tool/supplied package for oracle which will tell me how much % the query is completed, how much till will it take to complete?


Thanks in advance.
Re: query completion time/query completion % [message #322040 is a reply to message #322031] Thu, 22 May 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would like to know is that can i determine the execution time before query will run/while query is running ?


No, you can't.

Quote:
is there any utility/tool/supplied package for oracle which will tell me how much % the query is completed, how much till will it take to complete?

If it is a long call (different from a long query) then you can query v$session_longops.

Regards
Michel
Re: query completion time/query completion % [message #322049 is a reply to message #322031] Thu, 22 May 2008 05:00 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Michel,
The other thing i would to ask is that.

In the following query both tables are of 20g
SELECT COUNT(1) 
FROM  format fm 
WHERE NOT EXISTS (SELECT id 
                  FROM leader
                  WHERE  leader.id = fm.leader_id );


The above query is running from last 50 minutes yet not completed.

So will it impact the database since it is using much database buffers. Will other update statement will get slow? Or is there any other consequences?

Thanks,
Re: query completion time/query completion % [message #322055 is a reply to message #322049] Thu, 22 May 2008 05:17 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will other update statement will get slow?

This is not an update statement.
Of course concurrent process will suffer (even if they don't touch the same tables).

Try:
SELECT COUNT(*) 
FROM ( SELECT leader_id FROM format 
       MINUS
       SELECT id FROM leader
     )
/

I think it wil be faster.

Regards
Michel
Previous Topic: checkpoint causes a commit
Next Topic: Upgrade from 10.1.0.2 to 10.2.0.4
Goto Forum:
  


Current Time: Fri Dec 09 15:21:59 CST 2016

Total time taken to generate the page: 0.16382 seconds