Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: extremely long parse time

RE: extremely long parse time

From: Farnsworth, Dave <DFarnsworth_at_Ashleyfurniture.com>
Date: Tue, 01 Oct 2002 10:15:43 -0800
Message-ID: <F001.004DD9A0.20021001101543@fatcity.com>


It reports all the parameter settings and the base statistics that Oracle takes into account when choosing a plan.

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

Sent: Tuesday, October 01, 2002 12: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.

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

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: Farnsworth, Dave
  INET: DFarnsworth_at_Ashleyfurniture.com

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:15:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US