Re: 10053 trace for sql fired from pl/sql (stored code)
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Sat, 24 Dec 2005 16:06:37 -0500 (EST)
Wolfgang,
> > This is what I suspected, but wanted to get a
> > confirmation from an authoritative source. If
Wolfgang
> > says you can't get a cbo trace, I know I can't get
a
> > 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
of
> > you being here - do you know when EXACTLY
> > v$sql_plan_statistics gets populated? Sometimes I
get
> > a result set back sometime ago, and the plan is
> > visible in v$sql_plan, but the stats are still
all
> > zeros in v$sql_plan_statistics. Any way to "flush"
it
> > (like flushing index monitoring info visible in
> > v$object_usage, which otherwise might not appear
for
> > 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 -
v$sql_plan_statistics.last_output_rows=8,000
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 http://autos.yahoo.ca
--
//www.freelists.org/webpage/oracle-l
Other related posts:
