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: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Aug 2007 16:47:35 -0700
Message-ID: <1186098453.412617@bubbleator.drizzle.com>


dean wrote:
> 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
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 890
> 0.00 0.00
> SQL*Net message from client 890
> 4.43 5.17
> SQL*Net more data to client 1
> 0.00 0.00
>
>
>
> I am still trying to get the same for the client site, on its long
> distance connection....

Do you work with anyone that has Oracle experience?

Tracing SQLNET is not what we are talking about.

Explain Plan:
http://www.psoug.org/reference/explain_plan.html

Trace:
http://www.psoug.org/reference/trace_tkprof.html Look at 10053 and 10132.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Aug 02 2007 - 18:47:35 CDT

Original text of this message

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