Steve, January 24, 2003 - 11:02 am UTC
Hi Tom,
Thank you so much for the answer!
1) when I run
select * from v$session_longops where time_remaining > 0,
I found sometime there is no record in a short period of time. Does that mean in WAIT mode?
2) If I already have an excution plan for this query, How do I associate the step in execution plan with the opname in the v$session_longops?
3) By the way, May I have your script printtbl8.
Thank you so much!
Steve
Steve, January 27, 2003 - 3:47 pm UTC
Hi Tom,
When I run a quert in parallel mode and I run
select * from v$session_longops where time_remaining > 0
I just get one entry for 12 parallel session, Is that Oracle desige goal or I could set something to get all 12 parallel session display in 12 entry in the view of v$session_longops
Thanks!
Steve
January 27, 2003 - 4:29 pm UTC
In parallel, only the QC (query coordinator) makes such calls. The servers send statistics messages to the QC with their amount of completed cost, who aggreagates these normalized costs and makes the call to dbms_application_info.
What if there is nothing in v$session_longops ?
Philippe, October 26, 2006 - 9:21 am UTC
Hi,
I also need to monitor a long running insert into tab select...
Unfortunately, the select is optimized by Oracle as a set of NESTED LOOPs... and nothing is stored in v$session_longops because, as you explained in another post, there are "no long thing".
In that case... how could I monitor the progress of my INSERT... SELECT ?
- using a DBMS_PIPE to send a message every, for example, 1000 rows ?
- using the DBMS_APPLICATION_INFO from within the select (how to guess the total_work ?) ?
Do you have any suggestion ?
Thanks in advance !
October 26, 2006 - 12:07 pm UTC
if the plan is wrong (you have a really long running nested loops join) why don't we try to fix that - so it does the right plan?
I don't know how you would monitor the progress here - you cannot send a message every 1000 rows from a sql statement, you cannot control how many times, or in what order, or ANYTHING about how the plsql calls would be called.
What if there is nothing in v$session_longops ?
Philippe, October 26, 2006 - 2:09 pm UTC
Tom,
My plan doesn't seem to be wrong... there are a lot of data to select... and it takes a long time.
I was thinking it was possible to do something like :
select col,...,
case
when mod(rownum,1000) = 0 then some_function
else null
end
from ...
and the some_function function would set some info about the progress somewhere...
October 26, 2006 - 3:28 pm UTC
if there is a lot of data to selelct, it gets to be doubtful that nested loops is correct. That is my point.
You cannot be sure when/where/how that function will be called - sql is non-procedural you see, the access path will dictate how/when and where the function is called.
eg:
nested loops
index range scan
table access by index rowid
index range scan
table access by index rowid
versus:
hash join
full scan
full scan
they would call the function at ENTIRELY different places/times - even though both are logically equivalent plans.
suggest you really do look at the plan - I have a theory that if you have a long running query AND IT DOES NOT appear in v$session_longops - it is probably becauze of an inefficient plan!
Great !
Philippe, October 27, 2006 - 4:07 am UTC
Tom, you are right ! ( as usual ;-) )
I added hints to get a FTS on the "main" tables of my query (we have to read at least half of the table anyway) and it goes much faster.
Thanks Tom, your theory was correct !
But I still have two questions :
1. Why did Oracle choose a plan with nested loops instead of hash joins ? Any theory about that ?
2. My query is used through a view (that will be reused) and a DBLink. I would prefer not to put the HINTS in the view itself but rather in the select on the view. Is there a way to add hints to the SQL so that they are applied on the view ?
Thanks,
Philippe
October 27, 2006 - 7:48 am UTC
1) it got the cardinality wrong for whatever reason - wrong estimated cardinality, wrong plan. this can happen for many reasons - invalid, stale, missing statistics, or the optimizer had to "guess" (complex predicate - dynamic sampling can be very helpful)
2) see the performance guide, it describes how to hint in great detail.
pl/sql code timing
andr, October 27, 2006 - 6:34 am UTC
Hi Tom!
I can trace some session and get cpu/waiting of sql statements executions.
But how I can calculate the cpu of only pure pl/sql code from the trace file ? (for example, cpu of execution this code:
begin
select_statement;
for i in 1..10000000 loop <-- cpu
some_calculate(no sql);
end loop; <-- cpu - ?
select_statement;
...
end;
October 27, 2006 - 7:53 am UTC
you could subtract out the cpu of the sql that is done.