Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[6]: How to speed up parsing?

Re: Re[6]: How to speed up parsing?

Date: Tue, 13 Feb 1996 08:57:21 -0500
Message-Id: <>


As I remember, the original problem was a view that existed on two different instances on two different machines. Accessing it was fast on one instance and considerably slower on the other. The usual questions were asked about data volumes, system load, view definitions, indexes on the underlying tables, explain plans, etc. The last item I remember was the speculation that the explain plans showed differences possibly because the indexes being used were "equal" in the optimizer's opinion and as a result the first index created was used, and the indexes were not created in the same order on both instances. Extents really should play no part in this discussion. Do I have it right so far? Has the original poster come up with any new info?

Tim Sawmiller
"The opinions expressed here are my own and not necessarily those of the Oracle Corporation".


Content-Type: message/rfc822

Date: 12 Feb 96 16:39:16
From:"David White " <DWHITE_at_MCCFWC01.SSW.JNJ.COM> To: Multiple,recipients,of,list,ORACLE-L,ORACLE-L_at_CCVM.SUNYSB.EDU Subject: Re[6]: How to speed up parsing? Reply-to: ORACLE-L_at_CCVM.SUNYSB.EDU
X-Orcl-Application: Sender: "ORACLE database mailing list."  <ORACLE-L_at_CCVM.SUNYSB.EDU>

X-Orcl-Application: Comments:      MEMO 02/12/96 16:42:00

     Perhaps I'm mistaken, I have not kept the previous e-mails on the
     original question, but as I remember it, the original question used
     the term parse when actually the person was interested in more than
     just parsing (execute/fetch). I seem to remember that the original
     question asked why queries on one instance took longer than on another
     thought-to-be-identical instance.

     The question I've answered below was a follow-up question by someone
     else. It was my intention to answer the question (see text below):

     "This discussion fascinates me just how can having many extents ruin
     performance of your database."

     I understood that question as an opportunity to refer to the problems
     associated with having many extents.

     If I've misunderstood the original question, or the follow-up
     question, sorry for the un-needed response.

     David White

______________________________ Reply Separator _________________________________
Subject: Re: Re[4]: How to speed up parsing? Author: ORACLEL (INTERNET.ORACLEL1) at ~SWIFTMAIL Date: 2/12/96 11:42 AM

How is this a factor for the parse phase? Parsing is where the optimizer decides upon the steps that it will take and in what order they will occur.
Wouldn't the number of extents only come into play during the Execute/Fetch stages of a query?

Jeff Weiss

<included messages below>
Subj: Re[4]: How to speed up parsing?

     Perhaps the shortest summary is the one in the Oracle7 Server
     Administrator's Guide, page 8-16

     "*  You can use the estimated size of an individual table to better
     manage the disk space that the table will use. When a table is
     created, you can set appropriate storage parameters and improve I/O
     performance of applications that use the table.
        For example, assume that you estimate the maximum size of a table
     before creating it. If you then set the storage parameters when
     you create the table, fewer extents will be allocated for the table's
     data segment, and all of the table's data will be stored in a
     relatively contiguous section of disk space; this decreases the time
     necessary for disk I/O operations involving this table."

     In my application, I don't worry until the number of exents exceeds
     Your milage may vary...

David White

______________________________ Reply Separator
_________________________________ Subject: Re: Re[2]: How to speed up parsing?
Author: TIM (INTERNET.TIM4) at ~SWIFTMAIL Date: 2/8/96 5:03 PM

In your message dated Thursday 1, February 1996 you wrote :

> Just double-checked, and all tables are in 1 or 2 extents at most.
> Jeff
> --------------------<included message>--------------------
> Subj:   Re[2]: How to speed up parsing?
>      One more question for you:
>         On a table by table, index by index basis, do the number of extents
>      used match? (Hopefully at a number close to 1).
>         It's easy to get different initial/next extents if your exports
>      were made a different dates. This assumes you used compress=y in the
>      export. Having many extents, as you probably know, can ruin
>      performance.
>      David White

  This discussion fascinates me just how can having many extents ruin performance of your database.

  Please explain.

        Come, let us retract the foreskin of ignorance, and apply the
        wirebrush of enlightenment

        Tim London                      phone : +44 1753 662393
        Oracle DBA,Trainer and          mobile: +44 836 287064
        part time OU tutor              Email :

================== RFC 822 Headers ==================
Return-Path: Received: by (UCX V3.3-7A);

        Fri, 9 Feb 1996 15:56:12 -0500
Received: from CCVM.SUNYSB.EDU by (IBM VM SMTP V2R3)

   with BSMTP id 6071; Fri, 09 Feb 96 14:42:22 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV_at_SBCCVM) by CCVM.SUNYSB.EDU  (LMail V1.2a/1.8a) with BSMTP id 6295; Fri, 9 Feb 1996 14:42:19 -0500

Date:         Fri, 9 Feb 1996 14:33:14 GMT
Reply-To:     "ORACLE database mailing list." <>
Sender:       "ORACLE database mailing list." <>
Comments:     MEMO 02/09/96 14:38:00
From:         David White <DWHITE_at_MCCFWC01.SSW.JNJ.COM>
Subject:      Re[4]: How to speed up parsing?
X-To:         "Joan E. Brittingham" <JBRITTIN_at_MCCFWC01.SSW.JNJ.COM>,
              "William J. McMenamin" <WMCMENAM_at_MCCFWC01.SSW.JNJ.COM>,
To:           Multiple recipients of list ORACLE-L <>

Received on Tue Feb 13 1996 - 09:21:49 CST

Original text of this message