few additions: the V$SESS_TIME_MODEL corresponding stats for parsing are "parse elapsed time" and "hard parse elapsed time".
Btw, an easy way for sampling such performance stats is by using my Snapper script ;-)
V$SESS_TIME_MODEL query returned 0 for all the stats even after minutes
SID STAT_ID STAT_NAME VALUE
---------- ---------- -------------------------------------------------- ----------
223 3649082374 DB time 0
223 2748282437 DB CPU 0
223 4157170894 background elapsed time 0
223 2451517896 background cpu time 0
223 4127043053 sequence load elapsed time 0
223 1431595225 parse time elapsed 0
223 372226525 hard parse elapsed time 0
223 2821698184 sql execute elapsed time 0
223 1990024365 connection management call elapsed time 0
223 1824284809 failed parse elapsed time 0
223 4125607023 failed parse (out of shared memory) elapsed time 0
223 3138706091 hard parse (sharing criteria) elapsed time 0
223 268357648 hard parse (bind mismatch) elapsed time 0
223 2643905994 PL/SQL execution elapsed time 0
223 290749718 inbound PL/SQL rpc elapsed time 0
223 1311180441 PL/SQL compilation elapsed time 0
223 751169994 Java execution elapsed time 0
223 1159091985 repeated bind elapsed time 0
223 2411117902 RMAN cpu time (backup/restore) 0
Is your statistics_level = basic by any chance?
You need to have it at least TYPICAL for V$SESS_TIME_MODEL to be populated.
Anyway, a stack trace would tell you definitevily where in kernel codepath your process is spending its time.
:-) I don't know that that is what is really happening, Tanel's advice is good for that. The implications I was making were that it should be tested in the latest release, and we can't really know what unpublished bugs are doing or what their patches are really fixing (without some in depth research, anyways). But we can see that various things with analytics are fixed at various times.
Also see Note:285913.1 suggests Alter session set events '10119 trace name context forever, level 12'; of course, that's about an ORA-600, but hey, we're in discovery mode here, trying to figure out if that analytics problem may be related to this one.
Yes, this process is hard parsing.
(reading the stack from bottom up):
kksParseCursor shows you are parsing.
kksLoadChild you are hard parsing (loading a new child cursor to library cache)
apa* functions are hard parse driver functions
kko* functions are ones who are responsible for query compilation, transformations and validations etc and it looks like that's where the loop is happening (if these functions are on top of the stack in most of samples taken).
As I said previously, parse times in minutes mean you've hit a bug, so Oracle support should be able to help you (send them the same stack trace and optimzer environment trace/10053).
If you want to experiment around you could set some optimizer's cost based transformation parameters fals and see if you still hit this bug.
Like for example set "_optimizer_cost_based_transformation"=false and set "_optimizer_push_pred_cost_based"=false .
However I'm pretty sure it's a known bug by now so the best thing to do is send this information to Oracle Support!
Also you can set statistics_level=typical at your session level for the experiment. And then query V$SESS_TIME_MODEL.
However this should not be needed as the stack trace already has proven (for sure) that the process was in parsing code when the stack sample was taken.
Please let us know how it turns out :)
I wrote a blog entry about this too: