 |
Newsgroups: comp.databases.oracle.server
Date: Tue, 29 Jan 2008 09:34:02 -0800 (PST)
Local: Tues, Jan 29 2008 12:34 pm
Subject: Re: long running select min(timestamp) query
Comments embedded.
On Jan 28, 5:26 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote: > On Jan 28, 11:39 am, Charles Hooper <hooperc2...@yahoo.com> wrote: 118525 consistent gets and 118248 physical reads to return one row > > On Jan 28, 10:40 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote: > > > Hi, > > > I have a basic event table in my 10g database, primary integer key, > > > select min(eventdate) from events; > > > It can occasionally take a VERY long time. There is a standard btree > > > In my current scenario, the table has less than a million records, and > > > Operation Object Object Type Order Rows Size (KB) Cost Time (sec) CPU > > > I've run the ADDM, it updated the stats, but that doesn't seem to help > > > Thanks, > > Try a simple test: > > Now, insert 900,000 rows into the table, commit and gather table and > > COMMIT; > > EXEC > > Finally, run a simple query: > > From a 10046 trace, only 3 blocks read, all by consistent read: > > The DBMS XPLAN: > > Does the above experiment execute quickly, and read only 3 blocks on > > Charles Hooper > Thank you for your replies, i'll do this one first. > I tried your experiment, and achieved similar responses - i'm a > --------------------------------------------------------------------------- ----------- > I think this is comparable, but please advise if not. > 2nd question - my oracle web console tells me i'm running 10.2.0.1.0. > SQL> select min(eventdate) from events; > MIN(EVENTDATE) > Execution Plan > --------------------------------------------------------------------------- ----- > ----------------- > | Id | Operation | Name | Rows | > %CPU)| Time | > --------------------------------------------------------------------------- ----- > ----------------- > | 0 | SELECT STATEMENT | | 1 | > | 1 | SORT AGGREGATE | | 1 | > | 2 | INDEX FULL SCAN (MIN/MAX)| EVENTS_EVENTDATE | 736K| > --------------------------------------------------------------------------- ----- > ----------------- > Statistics from an index full scan is a bit much, I think, and explains why this is taking so long to complete. You've hit the buffer cache for 118525 blocks, and you've also loaded the buffer cache with 118248 blocks from disk; this is a lot of I/O in my opinion. Reducing this is the goal in my estimation, as it should then speed up the query (wading through less data gets you through the 'swamp' faster). You may be experiencing the effects of row migration, where one data analyze table events list chained rows; would populate the CHAINED_ROWS table and could provide some select owner_name, table_name, count(*) You may have nothing returned, or you may have a large number of analyze table events list chained rows; Hopefully if you had any migrated rows before they would now be gone. > Does this explain anything? Here's the same output using the "new" Here you have a completely different story: 132 blocks read from the > table, which for whatever reason is much better: > SQL> select min(eventdate) from t1; > MIN(EVENTDATE) > Execution Plan > --------------------------------------------------------------------------- ----- > ------ > | Id | Operation | Name | Rows | Bytes | Cost > | > --------------------------------------------------------------------------- ----- > ------ > | 0 | SELECT STATEMENT | | 1 | 11 | 3 > 0:01 | > | 1 | SORT AGGREGATE | | 1 | 11 > | 2 | INDEX FULL SCAN (MIN/MAX)| T1_IND1 | 901K| 9685K| 3 > 0:01 | > --------------------------------------------------------------------------- ----- > ------ > Statistics buffer cache and 6 reads from disk to help populate the buffer cache. Of course thisis on your single-column table, but the response should be similar (depending upon your data) for your events table. You generate the same type of execution plan (INDEX FULL SCAN) yet the index for the EVENTS table is undergoing a tremendous amount of I/ O to return your single-row result. I'll ask if the statistics for the EVENTS table are computed or > The data in my table is likely mucked up in some capacity, > i just You will when/if you find out how it's 'mucked up'. You have some > don't know what to do to fix it. suggestions here, and others may be in order depending upon the state of your data. > I will definitely get that book. I'm new to all this, so any David Fitzjarrell > obligatory text's you can recommend i'll be all over. > Thanks!- Hide quoted text - > - Show quoted text - You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||