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?
dean wrote:
> On Aug 2, 7:47 pm, DA Morgan <damor..._at_psoug.org> wrote: >> dean 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 -
>> 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.orgReceived on Thu Aug 02 2007 - 22:40:22 CDT