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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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
> > >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l