RE: Tuning Help - select condition from cdef$ where rowid=:1

On Thu, 10 Nov 2005 12:39:41 -0600, "Thomas Biju" <BThomas_at_br-inc.com> said:
> Thank you. The trace file I have is 1.3GB. I downloaded the trace
> analyzer from Oracle. And its analysis is going on since 10PM last night
> (exec trca$i.trace_analyzer). Does this help to find the parent?
>
> Did full stats on all schema tables (COMPUTE) except 4 large ones where
> we did 10% sample.
>
> With 8i this particular job ran for 22 minutes, in 9i it takes about 4
> hours.
>
>
> -----Original Message-----
> From: Egor Starostin [mailto:egorst_at_gmail.com]
> Sent: Thursday, November 10, 2005 12:30 PM
> To: Thomas Biju
> Cc: oracle-l_at_freelists.org
> Subject: Re: Tuning Help - select condition from cdef$ where rowid=:1
>
> > These are the stats from the trace file for this statement.
> >
> > select condition
> > from
> > cdef$ where rowid=:1
> > [...]
> > What are the next steps for me to diagnose the issue?
> Here's the next step (not a solution though):
> Find the parent(s) for this recursive statement. tkprof can't display
> recursive relationship between statements, for this task you need to
> use a profiler which can display it. Like OraSRP, for example.
>
>
> --
> Egor
> http://www.oracledba.ru

set echo on;
As a Temporary / Quick Fix this is what we did (later at the end of the day took maintenance window and did a FULL stats gathering on big tables) for the schemas the production application was connecting. You can do this for your batch user.

CREATE OR REPLACE TRIGGER On_Logon_Trigger_dba2 AFTER LOGON ON SCHEMA
BEGIN
execute immediate 'alter session set optimizer_mode=rule'; EXCEPTION
WHEN OTHERS THEN NULL;
END;
/

HTH
GovindanK

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