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

The Previous entry exceeded size limit. Hence reposting.

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

ALL tables to have FULL atleast once. We had similar issue when we moved from 9201 to 9206. The generated plan for the query would be different if FULL is not done atleast once.

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.

set echo on;
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