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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this a good case for an IOT?

Re: Is this a good case for an IOT?

From: joel garry <joel-garry_at_home.com>
Date: Thu, 02 Aug 2007 14:33:38 -0700
Message-ID: <1186090418.834237.58060@x35g2000prf.googlegroups.com>


On Aug 1, 3:32 pm, dean <deanbrow..._at_yahoo.com> wrote:
> 9.2i, 10g, Windows platforms.
>
> Hello all,
>
> We have a table USER_PREFERENCE, which gets updated frequently by
> various users, and appears to be badly fragmented (takes relatively
> large per-record timespan to load into memory, compared to other
> tables). It has 5 fields:
>
> Name Null? Type
> ----------------------------- -------- --------------------
> USER_I NOT NULL VARCHAR2(30)
> FORM_I NOT NULL VARCHAR2(100)
> CATEGORY_I NOT NULL VARCHAR2(100)
> KEY_I NOT NULL VARCHAR2(100)
> VALUE_X VARCHAR2(2000)
>
> The primary key is on the first 4 fields. There are no NULL values for
> the last field, VALUE_X.
>
> Is this a good candidate for an index organized table? The hope being
> that this would then maintain itself over time and solve the loading
> issue.
>
> Thanks for any tips!
>
> DeanB

In addition to what the others said, it is possible that some of the difference you see may be related to whether the fields have been updated or are in the process, and what-all is happening in the buffer cache. For example, you say there are a lot of updates going on, you should understand how Oracle handles concurrency and consistency, (this is well-explained in the Concepts manual), so perhaps Oracle is having to do a lot of work going back to undo to create consistent views of the table. You can get some idea of what is happening by looking at the v$bh view (see the docs, especially the performance manual, and google for further clarification of what you are really looking at). You might also dig into what latch waits are happening and what sort of activity your undo is undergoing.

What are the indexes on the table? Things updated often are usually not good candidates for IOT. If your app is reading in the entire table to just get a few rows, that may be your problem right there...

There are other things that could be made to go wrong with your data distribution, such as overly dense and overly sparse packing. The former could cause problems with too many people trying to update the same block (which could cause a lot of latch contentions) while the latter could cause problems by making oracle simply have to read too many blocks. Either of those would likely show a wide range of performance under different conditions. There are ways to make the high water mark go screwy, so Oracle might read too many blocks just because. That could cause a relatively stable performance issue.

In the end, most problems wind up being the application, anyways. You might have more than one problem if any of this is new to you. Simply timing how long to select some records only works in simple systems that don't have multiple users.

You might check and see what PCTFREE and PCTUSED are set to, then do a create table xyz as select * from yourtable;, and see if the performance is any different with xyz. If it is, you don't have fragmentation as it is specifically defined, but you do have some sort of issue that may be worth pursuing, as your app may be purposefully creating a data distribution problem.

Um, you haven't run a Windows disk defragmenter on your Oracle files, have you?
jg

--
@home.com is bogus.
http://custom.despair.com/viewall.html
Received on Thu Aug 02 2007 - 16:33:38 CDT

Original text of this message

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