Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10053 trace for sql fired from pl/sql (stored code)

Re: 10053 trace for sql fired from pl/sql (stored code)

From: Boris Dali <>
Date: 2005-12-24 22:06:37


> > This is what I suspected, but wanted to get a
> > confirmation from an authoritative source. If
> > says you can't get a cbo trace, I know I can't get
> > cbo trace.
> Not true. Just because I couldn't figure it out
> doesn't mean there is no way.

I beg to differ. I believe there are very few people out there who know and experiment with the cbo as much as you do and more importantly are willing to share. So if it didn't work for me after couple of days of testing on different platforms (that's why I asked the question) and you couldn't figure it out - I am pretty confident it just doesn't work. So let's just agree to disagree on this :-)

BTW I just reached Jonathan's last chapter where this is stated black on white. I guess instead of bothering the list I could just proceed with the reading (on the bright side now there are probably more people aware of this little trouble - my mailbox was full the day I asked the question with the [mostly] offline mails stating that it sure thing works)

> > 3) Another stupid question, just taking advantage
> > you being here - do you know when EXACTLY
> > v$sql_plan_statistics gets populated? Sometimes I
> > a result set back sometime ago, and the plan is
> > visible in v$sql_plan, but the stats are still
> > zeros in v$sql_plan_statistics. Any way to "flush"
> > (like flushing index monitoring info visible in
> > v$object_usage, which otherwise might not appear
> > some 15min)?
> I seem to only get values into v$sql_plan_statistics
> when I set statistics_level to ALL. At least
reliably. Very
> occasionally I do see
> values there without changing statistics_level from
> the default TYPICAL.
> But I have no idea what triggers those rare
> occasions. They have so far
> happened too infrequently for me to detect a pattern
> or form a hypothesis.

Yes, statistics_level=ALL goes without saying. But even than, I find v$sql_plan_statistics to be unreliable. It's one thing to gather data for a controled test (I usually disconnect from a session right after a test to get the plan stats), but it is much worse on a real system. I see cases where for the same sql/plan with v$sql.executions=10 for one step in the plan I get v$sql_plan_statistics.last_output_rows =0 and with v$sql.executions=2 for the same step in the plan -

But what's worse it looks even not self consistent within "one case", e.g. for exec=10 in the same step, while last_output_rows=0, cr_buffer_gets=3. That looks as if last_output_rows has not been updated yet (because all 10 cursors are still open?), but cr_buffer_gets already got some (maybe partial) value in.

I don't know whether it is because the underlying x$kqfvi and x$kqfvt fixed tables are updated without latch protection or something else, but in many cases I need to "fiddle" with v$sql_plan_statistics (e.g. query it number of times in quick succession, disconnect from a session, run another sql in a session, etc.) to get what I expect and if I don't know what to expect than the plan stats are always a suspect. Do you experience something similar? May be I should pay more attention to v$sql.open_versions (or users_opening/users_executing) not only executions.

Thanks again for your help,
Boris Dali.                                   

Find your next car at
Received on Sat Dec 24 2005 - 22:06:37 CST

Original text of this message