| 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: Storage guidelines in 9iR1 ??
Nope. I didn't get a chance to create a TAR on this. And a metalink search
turned up nothing. It was probably something related to my particular
environment.
Note to self. Think before you post..... (still slapping myself silly over this one. Ouch!)
Kevin
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Gaja
Krishna Vaidyanatha
Sent: Monday, March 24, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Storage guidelines in 9iR1 ??
Kevin,
Thanks for the clarification. So the real problem was not with the "number of extents", but with the number of extents for a segment in an LMT. This could be a bug associated with LMTs. Did you file a tar for this? If I were you, I would, just so that if there are any bugs in this, it will get resolved. Do you see the need for us to be "precise and detailed" while making statements on the list. The concern I have is that, some novice DBA will take your words as gospel and spread the myth that Oracle objects should always have less than 1024 extents. That is what I was trying to deter. Let me know when you get more on this. BTW, you can contact me on my private e-mail address gajav_at_yahoo.com. This one is for the list only.
Cheers,
Gaja
--- Kevin Toepke <ktoepke_at_tuningoracle.com> wrote:
> Gaja,
>
> You're correct. I should have quantified what I
> meant by significant. As
> well as given more detail on what I was doing. That
> said, here is what I
> remember of what I was doing....
>
> Specifically, At the request of management, I was
> testing the performance
> and extent allocation of locally managed tablespaces
> v.s. dictionary managed
> tablespaces. I was to give a summary of my results
> and a recommendation as
> to how new tablespaces were to be created.
>
> That said, I create 2 tablespaces. One dictionary
> managed and one locally
> managed (uniform extent size) on the same instance,
> same logical volume on
> the disk array and same extent sizes (1mb)
>
> The same table was created in both tablespaces,
> using the default storage
> clauses.
>
> I used SQL Loader to load the same data into both
> tables tablespaces
> multiple times. The source file was about 1mb.
>
> I was mostly testing non-direct path insert
> performance (via sqlldr) and
> select performance via several scripts (using
> sqlplus).
>
> What I found was that the performance of sqlldr
> stayed remarkably steady for
> the dictionary-managed tablespace well past 2000
> extents. The sqlldr
> run-times increased by about 5-10% for the
> locally-managed after about 1024
> extents had been reached.
>
> The performance of the select statements degraded in
> a linear fashion, based
> on the number of rows. The exception was that the
> LMT table saw a 5-10%
> degradation in performance after about 1024 extents
> were reached.
>
> No updates or deletes were performed on the tables.
> Also, there were no
> indexes or constraints on the tables. Nor did I
> generate statistics.
>
> And this was repeatable as I dropped and recreated
> the tablespaces several
> times.
>
> The methodology was as follows, recording the timing
> at each step
> 1) Load the file one time into each of the tables
> 2) note the number of extents
> 3) perform the selects
> -- count(*)
> -- select * from xxx where id = 1;
> -- a select with a group by.
>
> Kevin
>
> -----Original Message-----
> Krishna Vaidyanatha
> Sent: Friday, March 21, 2003 1:15 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Kevin,
>
> Long time no talk or see. Hope things are well with
> you. Going forward it will nice for us to "quantify"
> any performance differences that we observe,
> preferably with data supporting the claim. Don't get
> me wrong, I am not trying to beat you up on this,
> but
> trying to bring some clarity to the situation. Your
> original posting very strongly suggested that 1024
> was
> some kind of magical number beyond which SQL
> performance took a "significant" dive. We need to
> find
> out (at this stage hypothesise), what caused the
> performance decrease.
>
> If we were to look at this more objectively, it is
> very unlikely that full-table scans would perform
> worse with more extents, assuming that everything
> else
> remains constant. This is because, Oracle would have
> issued the same number of "read system calls", to
> process the data below the high-water
> mark,regardless
> of the number of extents. This ofcourse assumes that
> the value of db_file_multiblock_read_count did not
> change over time.
>
> Index scans are also rarely affected by the number
> of
> extents, because an index-scan will still require
> reading of the same root, branch and leaf nodes to
> determine the ROWIDs for the search (regardless of
> the
> number of extents in the table).
>
> One very plausible and probable cause for the
> performance decrease, could be the onset of
> "block-level fragmentation" that happens over time.
> This can be measured by calculating the "data
> density"
> (defined as rows/block below the high-water mark) of
> the blocks in the segment over time. Data density
> issues are usually caused by un-optimal settings of
> PCTUSED and PCTFREE. Again, if PCTUSED is causing
> the
> data density issue, then even that can be overcome
> by
> using Automatic Segment Space Management in 9i.
>
> So, if the high-water mark of the table has overtime
> inflated to 100000 blocks (due to frequent INSERT &
> DELETE operations), and the current data density is
> 10
> rows per block, and if we assume that each block in
> reality can actually hold 100 rows, then when the
> table is re-organized, the number of blocks below
> the
> high-water mark will drop to 10000 blocks.
>
> This most certainly will cause SELECTs that are
> performing full-table scans to perform faster, as
> Oracle will issue "fewer" read system calls to read
> the data. In all cases here, I am assuming here that
> the data is NOT in the database buffer cache. The
> same
> issue of "data density" can be suggested for
> indexes,
> as fewer and more dense leaf blocks, will result in
> less logical/physical I/O on the index blocks
> itself.
>
> So, the increase in the number of extents in a
> segment
> by itself does not cause the performance problem. It
> "may be" a symptom or a signal of something else
> occuring on segment, obviously caused by your
> application. I am not in anyway suggesting that
> everyone should start having 10000+ extents for all
> of
> their objects, but on the flip side I also don't
> want
> to see people inflicting pain on themselves, but
> re-organizing their objects whenever it grows beyond
> "a small number of extents". I will leave the
> definition of "small" to the reader.
>
>
> Best regards,
>
>
> Gaja
>
> --- Kevin Toepke <ktoepke_at_tuningoracle.com> wrote:
> > Performance of selects and non-direct path
> inserts.
> > I no longer have the
> > exact data as it was left at my former employer
> when
> > I left. Significantly
> > is relative -- it wasn't like performance went to
> > the pots, I'm talking
> > between about 5% and 10%. So a 1 minute query
> would
> > run a couple seconds
> > longer than expected.
> >
> > And why? I don't know. That was one of the things
> I
> > was going to look into.
> > If only I had metalink access I could do some
> > searching. Maybe I should try
> > to recreate it in my current environment (Win2k,
> > Oracle 9iR2 patchset 3)
> >
> > Kevin
> >
> > -----Original Message-----
> > Sent: Thursday, March 20, 2003 6:44 PM
> > To: ORACLE-L_at_fatcity.com
> > Cc: ktoepke_at_tuningoracle.com
> > Importance: High
> >
> >
>
=== message truncated ===
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha INET: oraperfman_at_yahoo.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 inReceived on Mon Mar 24 2003 - 14:28:00 CST
 |
 |