Re: 10053 Trace and OBJ$ (DBA_SEGMENTS)

Hi,

i've had suggestions (sent to me personally) that Oracle doesn't present the
plan/stats if it is soft parsed. well, i had made sure that Oracle hard
parses my query each and every time. even then, 10053 doesn't contain the
information i need.

on the same note, there is a bit of subtlety to the soft parse behaviour.

if the query is already parsed (and in the shared pool) and a new session
executes the same query, the 10053 information is written fully. i can see
all the stats i need. note that i am using EMP, DEPT tables here. So, that
is/was a soft parse. you can dig V$SQL, V$SQLAREA for all the stats.

if the query is already parsed (and in the shared pool) and i re-execute the
same query within the same session, then the 10053 is not written. This
is/was also a soft parse. This is probably the case that some of the
(im)posters were referring to ;)

to make things more amusing for us, i just saw Oracle hard parse the same
query in the same session!! no idea why. i have a huuuggge shared pool (350
MB).

Coming to the my original question, what's with the OBJ$ or the SEG$  tables
and 10053 in 10.2 !!

thanks
anand


On 05/02/07, Ghassan Salem <salem.ghassan@xxxxxxxxx> wrote:


Anand,
When I do an explain plan on your query (logged in as sys), I see all the
stats (on 10.2.0.2)

On 2/5/07, Anand Rao <panandrao@xxxxxxxxx> wrote:
>
> Hi,
>
>  just to add that even if i query without the recyclebin, the problem
> exists.
>
>  SELECT NVL2(partition_name,
>  segment_name || ':' || partition_name,
>  segment_name)
>  FROM user_segments
>  WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
>  segment_name NOT IN (:p1,:p2,:p3,:p4,:p5)
>
>
> Also, bind variables or not, its the same result.
>
> thanks
> anand
>
> On 05/02/07, Anand Rao <panandrao@xxxxxxxxx> wrote:
> >
> > Folks,
> >
> > Has anyone come across this issue where,
> >
> > you run a simple query on dba_segments (or user_segments) and produce
> > a 10053 trace. The trace file does not contain the " BASE STATISTICAL
> > INFORMATION" section. The table and index stats are completely
> > missing. So, i don't see the  #Rows:, #Blks:, AvgRowLen:, AvgLen:,
> > NDV:, Nulls:, Density: and so on...
> >
> > Dictionary stats have been gathered, i haven't gathered System stats.
> >
> > The query is,
> >
> > SELECT NVL2(partition_name,
> >                             segment_name || ':' || partition_name,
> >                             segment_name)
> >                   FROM user_segments
> >                  WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
> > AND
> >                        segment_name NOT IN
> >                        (SELECT object_name
> >                           FROM recyclebin bin) AND
> >                        segment_name NOT IN (:p1,:p2,:p3,:p4,:p5)
> >                             ORDER BY bytes DESC
> >
> >
> > Now, if i run another query based on some other non-dictionary based
> > tables (EMP, DEPT), then everything is fine.
> >
> > It seems to be an issue with some access rights to OBJ$ is what i can
> > figure out but what permission needs to be granted is the question. The user
> > is a OS authenticated user, OPS$DEV1 and has DBA privilege. Even if i
> > execute the same query as user ' oracle', the owner of the database,
> > it doesn't help.
> >
> > Oracle version is 10.2.0.1.0 on Sun Solaris 9.
> >
> > is there something fundamental i am missing here? RTFM...??? i did a
> > bit of Meta-linking, searching the list and RTFMs but could not find
> > anything in particular.
> >
> > thanks,
> >
> > anand
> >
>
>

Other related posts: