OraFAQ Forum: Server Administration » query completion time/query completion %

Home » RDBMS Server » Server Administration » query completion time/query completion %

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query completion time/query completion % [message #322031]

Thu, 22 May 2008 04:12

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.

Report message to a moderator

Re: query completion time/query completion % [message #322040 is a reply to message #322031]

Thu, 22 May 2008 04:41

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: query completion time/query completion % [message #322049 is a reply to message #322031]

Thu, 22 May 2008 05:00

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,

Report message to a moderator

Re: query completion time/query completion % [message #322055 is a reply to message #322049]

Thu, 22 May 2008 05:17

Michel Cadot
Messages: 68675
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

Report message to a moderator

Previous Topic:	checkpoint causes a commit
Next Topic:	Upgrade from 10.1.0.2 to 10.2.0.4

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 06 13:59:17 CDT 2024