I was watching a long running query, and I realized that either I've forgotten, or didn't know, how to correlate an "execution plan" with "actual execution".
For example, in longops, I saw info like this:
Sort Output: : 19741 out of 19741 Blocks done
Sort Output: : 42500 out of 42500 Blocks done
Sort/Merge: : 53222 out of 53222 Blocks done
Sort/Merge: : 53204 out of 53204 Blocks done
Sort/Merge: : 53274 out of 53274 Blocks done
etc, etc...
After it completed, I could generate an EXPLAIN_PLAN like this:
INSERT STATEMENT
VIEW
WINDOW BUFFER
TABLE ACCESS BY INDEX ROWID| O_PRDCR_COMM_WIP
INDEX FULL SCAN | XOPRCMW0
(It's a data warehouse, so the plan wouldn't change like in a dynamic OLTP environment).
I know I could generate tracefiles and see step-by-step, but is there something similar in the database?
How does one correlate all this "on the fly"? Thank you.
the true infrastructure for that is in 11gr1 and above:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm#CACGEEIF prior to that, you cannot really "tie them together". Say you have a query like:
select * from t1, t2 where t1.key = t2.key;
and the plan is
hash join
full scan t1
full scan t2
so, it'll full scan t1 and hash it into memory (hopefully, might be into temp on disk too). The LONGOPS view will show the result of the full scan during that first pass. Now, when we get done with that - there will be a full scan of T2. LONGOPS will start showing that for that session.
For that one, easy, you can tell.
now, what if it is
select * from t t1, t t2 ....
now it will be
hash join
full scan t
full scan t
you won't be able to differentiate between them very easily (at all really)
or what if you used first_rows, the plan would be:
nested loops
full scan t
index range scan t_idx
table access by index rowid tNow - there are NO long running operations - each is done for a very very brief moment in time, longops will contain nothing for that query.
Until 11g, you cannot do it step by step in an explain plan format like that.