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.
