RE: extremely long parse time

[Inka Bezdziecka]

Title: RE: extremely long parse time

It is used diagnosing optimiser problems. The trace shows the optimiser access path. One of those "should NOT be used unless explicitly requested by support" and not documented features.

 

inka 

-----Original Message-----
From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 1:56 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: extremely long parse time

I know what a 10046 trace does. What's
a 10053 trace?

----
Matt Adams - GE Appliances - [EMAIL PROTECTED]
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 12:42 PM
To: [EMAIL PROTECTED]
Cc: Adams, Matthew (GEA, MABG, 088130)
Subject: RE: extremely long parse time
Importance: High

Matt,

Have you done a 10053 trace on this query?

Jared

"Adams, Matthew (GEA, MABG, 088130)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 09/30/2002 11:43 AM
 Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:
        Subject:        RE: extremely long parse time

OK, I think we're on to something here.
The DBA_TAB_COL_STATISTICS shows no rows for tables
owned by sys (although strangely, owner is not a column
of this table).  However, the DBA_ANALYZE_OBJECTS view IS
listing objects owned by SYS, which implies that they have
been analyzed in the past.
Since I don't see any of the statisics filled in on the
DBA_TABLES entries for tables owned by SYS, what would
you recommend doing at this point?
"analyze table sys.XXXXX delete statistics"?

----
Matt Adams - GE Appliances - [EMAIL PROTECTED]
Their fundamental design flaws are completely
hidden by their superficial design flaws.
          - Douglas Adams
-----Original Message-----
From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 1:27 PM
To: '[EMAIL PROTECTED]'
Cc: Adams, Matthew (GEA, MABG, 088130)
Subject: RE: extremely long parse time

Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or DBA_ANALYZE_OBJECTS?
Is the COST column in your PLAN_TABLE null???
Rich Jesse                           System/Database Administrator
[EMAIL PROTECTED]              Quad/Tech International, Sussex, WI
USA
-----Original Message-----
From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 12:38 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: extremely long parse time

We are using First_rows for the optimizer mode,
but the last_analyzed column in DBA_TABLES and
DBA_INDEXES is NULL for all objects owned by SYS.
The really wierd part is:  Changing the query
to use rule based optimization (via the /*+ RULE */ hint
caused it to execute sub-second.
Why would optimization mode affect parsing?  Is query
optimization considered part of the parsing routine?
Matt