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: dean <deanbrown3d_at_yahoo.com>
Date: Thu, 02 Aug 2007 15:24:11 -0700
Message-ID: <1186093451.289504.203600@j4g2000prf.googlegroups.com>


On Aug 2, 11:39 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> dean wrote:
> > On Aug 1, 9:57 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> dean 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
> >> Possibly but how is it accessed and on what basis do you make the
> >> statement "appears to be badly fragmented," what does that mean, and
> >> what has that got to do with anything? Extents are never laid down
> >> on disk contiguously ... not even in an IOT.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>
> > Well, I am comparing it (table A) to another table (B) which loads up
> > into memory much faster - compare the following properties:
>
> > A: total records 175K
> > B: total records 125K
>
> > A: number of fields: 5
> > B: number of fields: 20 (various types)
>
> > A: time to load 10K records to memory: 35s
> > B: time to load 10K records to memory: 10s
>
> > I can't think of any other reason why B should load so much faster
> > than A; from the structure it should load slower.
>
> Have you looked at the Explain Plan for the two queries? Have you looked
> at a 10053 trace to see how the CBO is handling this? These are but two
> of the many tools that Daniel is alluding to in this thread.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -

This is the trace for the query running on our local development machines:

select user_preference.*, user_preference.ROWID from
 user_preference where user_i = 'DEAN'

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        2      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch      885      0.14       0.13          0       4999
0       14142

------- ------ -------- ---------- ---------- ---------- ----------
total      888      0.14       0.13          0       4999
0       14142

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total
Waited

I am still trying to get the same for the client site, on its long distance connection.... Received on Thu Aug 02 2007 - 17:24:11 CDT

Original text of this message

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