Oracle-L: RE: extremely long parse time

From: <Rick_Cale_at_teamhealth.com>
Date: Tue, 01 Oct 2002 10:43:30 -0800
Message-ID: <F001.004DDAB2.20021001104330@fatcity.com>

Yes, the site is http://www.hotsos.com/dnloads/1/10053/Breitling2002.pdf. Requires free membership

                                                                                                     
                    John Kanagaraj                                                                   
                    <john.kanagara       To:     Multiple recipients of list ORACLE-L                
                    j_at_hds.com>            <ORACLE-L_at_fatcity.com>                                     
                    Sent by:             cc:                                                         
                    root_at_fatcity.c       Subject:     RE: extremely long parse time                  
                    om                                                                               
                                                                                                     
                                                                                                     
                    10/01/2002                                                                       
                    02:30 PM                                                                         
                    Please respond                                                                   
                    to ORACLE-L

Thats a CBO trace. Have fun trying to read it :) Actually, there was an excellent presentation from a Wolfgang Breitling on this very topic at IOUG.
And I believe Cary's site carries it (hotsos.com).

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com <http://www.klove.com/>

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

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

Sent: Tuesday, October 01, 2002 10:56 AM 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-----

<mailto:Jared.Still_at_radisys.com> ]
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-----
<mailto:Rich.Jesse_at_qtiworld.com> ] 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: John Kanagaraj
INET: john.kanagaraj_at_hds.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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rick_Cale_at_teamhealth.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