| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[6]: How to speed up parsing?
--Boundary-8366566-0-0
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
tsawmill_at_us.oracle.com
"The opinions expressed here are my own and not necessarily those of the
Oracle Corporation".
--Boundary-8366566-0-0
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
X-To: ORACLE-L_at_ccvm.sunysb.edu
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
dwhite_at_mccus.jnj.com
______________________________ 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
10.
Your milage may vary...
David White
dwhite_at_mccus.jnj.com
74577.3021_at_compuserve.com
______________________________ 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 > dwhite_at_mccus.jnj.com >
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 : tim_at_artemis.demon.co.uk
----------------------------------------------------------------------------
================== RFC 822 Headers ==================
Return-Path: owner-oracle-l_at_ccvm.sunysb.edu
Received: by sescva.esc.edu (UCX V3.3-7A);
Fri, 9 Feb 1996 15:56:12 -0500
Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (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." <ORACLE-L_at_ccvm.sunysb.edu>
Sender: "ORACLE database mailing list." <ORACLE-L_at_ccvm.sunysb.edu>
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>,
tim_at_artemis.demon.co.uk, ORACLE-L_at_ccvm.sunysb.edu
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_ccvm.sunysb.edu>
--Boundary-8366566-0-0--
Received on Tue Feb 13 1996 - 09:21:49 CST
 |
 |