| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this a good case for an IOT?
On Aug 2, 11:40 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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-Hidequotedtext -
> >>>>>> - 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-Hidequoted 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?
>
>
>> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Trace:http://www.psoug.org/reference/trace_tkprof.html
> >> Look at 10053 and 10132.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
>
>> > scan. Surely the issue is in transfering data over a slow network to
> > 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
>
Well, I did do the test of making the IOT and it made no difference. The DBA on the client site (this is the 2000 mile link) ran the query and it was relatively instantaneous, as to be expected since its a select from one table. The issue is in bringing all that data down over a distance network, and doing it more efficiently.
So please, help me out here, what tools are available for examining this? What techniques are used to bring in data from several 10,000's of records into memory on a thick client. I have played with fetchsizes, blockreadsizes, cache sizes and other ADO parameters.
I also wanted to clarify something on your fast_way procedure. That is moving data around from parent to child (both on the server) - I don't see what that has to do with getting the data into a client app. Received on Thu Aug 02 2007 - 23:15:44 CDT
 |
 |