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: Fri, 03 Aug 2007 00:53:27 -0000
Message-ID: <1186102407.403367.200980@e9g2000prf.googlegroups.com>


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? Received on Thu Aug 02 2007 - 19:53:27 CDT

Original text of this message

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