Re: long running select min(timestamp) query
Date: Mon, 28 Jan 2008 15:26:22 -0800 (PST)
Message-ID: <310acdee-c261-4505-890d-04f2976372a5@q39g2000hsf.googlegroups.com>
On Jan 28, 11:39 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jan 28, 10:40 am, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
>
>
>
> > Hi,
>
> > I have a basic event table in my 10g database, primary integer key,
> > and a nonnull timestamp "eventdate" field. When executing the
> > following command:
>
> > select min(eventdate) from events;
>
> > It can occasionally take a VERY long time. There is a standard btree
> > index on "eventdate", and roughly 20 other columns, a few of which
> > also have basic btree indexes.
>
> > In my current scenario, the table has less than a million records, and
> > the query seems to be taking upwards of 10 minutes!!! Here's what the
> > console says the plan is:
>
> > Operation Object Object Type Order Rows Size (KB) Cost Time (sec) CPU
> > Cost I/O Cost
> > SELECT STATEMENT 3 4
> > SORT AGGREGATE 2 1 0.011
> > INDEX FULL SCAN (MIN/MAX) EVENTS_EVENTDATE INDEX 1 736195 7,908.345
> > 4 1 28686 4
>
> > I've run the ADDM, it updated the stats, but that doesn't seem to help
> > (i run it again it has no recommendations). Am i doing something
> > silly with this table or is there some better way for me to phrase
> > this query?
>
> > Thanks,
> > Bob
>
> Try a simple test:
> First, create a table with a single column and an index on that
> column:
> CREATE TABLE T1 (EVENTDATE TIMESTAMP NOT NULL);
> CREATE INDEX T1_IND1 ON T1(EVENTDATE);
>
> Now, insert 900,000 rows into the table, commit and gather table and
> index stats:
> INSERT INTO
> T1
> SELECT
> TRUNC(SYSDATE-3000)+ROWNUM/100
> FROM
> DUAL
> CONNECT BY
> LEVEL<=900000;
>
> COMMIT;
>
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
>
> Finally, run a simple query:
> SELECT
> MIN(EVENTDATE)
> FROM
> T1;
>
> From a 10046 trace, only 3 blocks read, all by consistent read:
> EXEC #12:c=0,e=352,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=7550820123
> FETCH #12:c=0,e=86,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=7550820562
>
> The DBMS XPLAN:
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows | A-Time | Buffers |
> ------------------------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 | 1 |
> 1 |00:00:00.01 | 3 |
> | 2 | INDEX FULL SCAN (MIN/MAX)| T1_IND1 | 1 | 904K|
> 1 |00:00:00.01 | 3 |
> ------------------------------------------------------------------------------------------------
>
> Does the above experiment execute quickly, and read only 3 blocks on
> your system?
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Thank you for your replies, i'll do this one first.
I tried your experiment, and achieved similar responses - i'm a complete novice and don't know exactly how you got the 10046 trace output, but i did do the explain stuff:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| T1_IND1 | 901K| 9685K| 3(0)| 00:00:01 |
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. I attempted to enable the plustrace role, and do as suggested, here's what i got:
SQL> select min(eventdate) from events;
MIN(EVENTDATE)
21-JAN-08 04.51.45.525000 PM Execution Plan
Plan hash value: 116994577
| Id | Operation | Name | Rows |Bytes | Cost (
%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 11 | 4
(0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 |
11 |
| |
| 2 | INDEX FULL SCAN (MIN/MAX)| EVENTS_EVENTDATE | 736K|
7908K| 4
(0)| 00:00:01 |
Statistics
901 recursive calls
0 db block gets
118525 consistent gets
118248 physical reads
0 redo size
424 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
1 rows processed
Does this explain anything? Here's the same output using the "new" table, which for whatever reason is much better: SQL> select min(eventdate) from t1;
MIN(EVENTDATE)
11-NOV-99 12.14.24.000000 AM Execution Plan
Plan hash value: 3630964933
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time
|
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:0
0:01 |
| 1 | SORT AGGREGATE | | 1 | 11
| |
|
| 2 | INDEX FULL SCAN (MIN/MAX)| T1_IND1 | 901K| 9685K| 3
(0)| 00:0
0:01 |
Statistics
676 recursive calls
0 db block gets
132 consistent gets
6 physical reads
0 redo size
422 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
1 rows processed
The data in my table is likely mucked up in some capacity, i just don't know what to do to fix it.
I will definitely get that book. I'm new to all this, so any obligatory text's you can recommend i'll be all over.
Thanks! Received on Mon Jan 28 2008 - 17:26:22 CST