Need to find out how long will a query take to run or when I am running the query where can I check to see that X% of my query is complete.

I know there is some place in Oracle Enterprise manager or something like that which shows a progress bar once the query is started, but I dont exactly recollect it can somebody help me with this..

Also when do we run analyze plan estimate statistics and how does this help if I have to run a complex query. does estimating statistics on a table increase the query performance if so how ?

You can use this query to find out the information you want to know:

CODE

REM Displays the long running operations

SET LINESIZE 200

COLUMN operation FORMAT a15
COLUMN username FORMAT a15
COLUMN object FORMAT a25

SELECT a.sid,
a.serial#,
b.username ,
opname OPERATION,
target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)",
TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a,
v$session b
WHERE a.sid = b.sid AND
b.username not IN ('SYS', 'SYSTEM') AND
totalwork > 0
ORDER BY elapsed_seconds;

Needed some clrafication regrding this

"when do we run analyze plan estimate statistics and how does this help if I have to run a complex query. Does estimating statistics on a table increase the query performance if so how ?"

Your last post contains three questions:
1>when I am running the query where can I check to see that X% of my query is complete
2>predict the time to answer of a query
3>estimating statistics on a table increase the query performance if so how ?

My point of view is
1>Answers to the first question have given above..

2>The second and the third questions implie to understand what is a CBO (cost based optimizer) and a RBO (rule based optimizer). Are you familiar with that? The ROB might not be used any more by oracle (it is an another story). You cannot predict the time of answer of a sql request. There are too many factors.

3>Regarding the third question, the Analyze is useful if the sql request is analyzed by the COB engine. And it should be.
Yes, Analyzing the table allows oracle to know the volume and the repartition of the tables. Therefore it performs the appropriate tasks to compute the data together. But sometimes some complex sql requests are not really well written. In that case you need to analyze the plan table.