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 20:40:22 -0700
Message-ID: <1186112419.337740@bubbleator.drizzle.com>


dean wrote:

> On Aug 2, 7:47 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> 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-Hidequoted 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> Nope, no-one knows Oracle. That was run through tkprof. I will turn on
> the 10053 and 10132. The explain plan is just going to be a simple
> scan. Surely the issue is in transfering data over a slow network to
> the client, not the execution of the query on the server?

Surely we have no idea what the problem is and in Oracle we tend to work by finding out what is going on rather than making guesses. Don't touch anything, and don't change anything, until you have metrics that clearly prove the root cause.

-- 
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 - 22:40:22 CDT

Original text of this message

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