Oracle-L: RE: extremely long parse time

From: Inka Bezdziecka <IBezdziecka_at_cupe.ca>
Date: Tue, 01 Oct 2002 10:30:21 -0800
Message-ID: <F001.004DDA06.20021001103021@fatcity.com>

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

Sent: Tuesday, October 01, 2002 1:56 PM
To: Multiple recipients of list ORACLE-L

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

Matt Adams - GE Appliances - matt.adams_at_appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws.

Douglas Adams

-----Original Message-----

Sent: Tuesday, October 01, 2002 12:42 PM To: ORACLE-L_at_fatcity.com
Cc: Adams, Matthew (GEA, MABG, 088130)
Importance: High

Matt,

Have you done a 10053 trace on this query?

Jared

"Adams, Matthew (GEA, MABG, 088130)" <MATT.ADAMS_at_APPL.GE.COM> Sent by: root_at_fatcity.com
09/30/2002 11:43 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
        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 - matt.adams_at_appl.ge.com Their fundamental design flaws are completely hidden by their superficial design flaws.

Douglas Adams
-----Original Message-----
Sent: Monday, September 30, 2002 1:27 PM To: 'ORACLE-L_at_fatcity.com' Cc: Adams, Matthew (GEA, MABG, 088130)

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 
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI

USA
-----Original Message-----

mailto:MATT.ADAMS_at_APPL.GE.COM]
Sent: Monday, September 30, 2002 12:38 PM To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Inka Bezdziecka
  INET: IBezdziecka_at_cupe.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Oct 01 2002 - 13:30:21 CDT