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?
> 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;
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
> 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
Sorry to say, but '10g' doesn't say much about which release of Oracle you're using, as that covers releases 10.1.0.2 through 10.2.0.3, and the optimizer changed between those two base versions.
My suggestion is you configure the PLUSTRACE role, grant it to the user in question then set autotrace on and run your query. The query plan itself won't tell you nearly as much as the resulting statistics reported by autotrace. As an example:
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 293 bytes sent via SQL*Net to client 245 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processed
SQL>
The db block gets, consistent gets and physical reads can reveal much about what Oracle is doing with your query. Another example shows this:
Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 7944 consistent gets 239 physical reads 0 redo size 867354 bytes sent via SQL*Net to client 53760 bytes received via SQL*Net from client 7647 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 114688 rows processed
SQL>
Such information can be of great help in diagnosing and tuning a query.
Another excellent resource is 'Cost-Based Oracle Fundamentals', by Jonathan Lewis ( a semi-regular poster to this newsgroup ). I strongly suggest you purchase a copy, read it and work through the examples provided. A wealth of information is packed inside those pages.
With what you have supplied it's difficult to know where this query is going awry. Please post the autotrace output for a sluggish run of this query so we can see what else Oracle may be doing that isn't apparent from the bare query plan.
> 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, > > 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;
> 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
> 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:
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:
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
On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
Well, I'm guessing that your 20 column index is just not the best one for your min. What happens if you make a btree index on just that eventdate column? It's been my experience that any table with more than a few columns in an index isn't properly normalized.
There might be bug/patch issues, too. timestamp is relatively new, and you are using an unpatched base release.
How are you loading this table? What is the exact definition of the index?
> On Jan 28, 11:39 am, Charles Hooper <hooperc2...@yahoo.com> wrote: > > 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;
> > 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
> > Does the above experiment execute quickly, and read only 3 blocks on > > your system?
> 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:
> I think this is comparable, but please advise if not.
What you posted above are the estimates, and do not include the actual execution statistics. But for this comparison, that will work just as well. See the following for an example of how to use DBMS_XPLAN to retrieve the actual execution statistics: http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
Your explain plan (of my query example) above shows a prediction of 901K rows, while on my system Oracle was predicting 904K rows. My explain plan shows that the actual number of rows were 1, and that 3 buffers were used (the 10046 plan showed that these were consistent reads - logical in-memory reads). Your explain plan shows a cost of 3, implying that 3 blocks will be read.
Now, let's look at the explain plan that you posted for your original query and compare it to the explain plan that you posted of my query example: 736K rows predicted, with a cost of 4 - your query is expected to perform one more logical or physical read than my query example. This implies that your query should execute roughly as fast as my query example (in roughly 0.01 seconds or less).
To enable a 10046 trace for your session with wait events: ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
To disable the trace for your session: ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
You can find the trace file that is generated in the udump directory on the server. As you take a look at the 10046 trace files, you will see lines containing a series of values, such as: c=0,e=352,p=0,cr=0,cu=0
The above reads as: 0 CPU seconds, 352/1000000 second elapsed time, 0 physical reads, 0 consistent reads (logical, in-memory), 0 current mode reads (similar to consistent reads, but typically more efficient).
You will likely also find various wait events which may help identify the source of any delays.
Please post a DBMS_XPLAN for your query using the technique described on the above link.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
> Well, I'm guessing that your 20 column index is just not the best one > for your min. What happens if you make a btree index on just that > eventdate column? It's been my experience that any table with more > than a few columns in an index isn't properly normalized.
> There might be bug/patch issues, too. timestamp is relatively new, > and you are using an unpatched base release.
> How are you loading this table? What is the exact definition of the > index?
All the indexes on the table only span ONE column, not all! Here's the ddl (i've modified some names, none of the content has been changed):
CREATE TABLE "MYSCHEMA"."EVENTS" ( "EVENTID" NUMBER, "EVENTDATE" TIMESTAMP(6) NOT NULL , "HOSTID" NUMBER NOT NULL , "DIRECTION" NUMBER NOT NULL , "INTERFACE" NVARCHAR2(17), "FRAME" NUMBER NOT NULL , "SOURCEMAC" NVARCHAR2(17), "DESTINATIONMAC" NVARCHAR2(17), "PROTOCOL" NUMBER NOT NULL , "FLAGS" NVARCHAR2(64), "SOURCEIP" NVARCHAR2(15), "SOURCEPORT" NUMBER NOT NULL , "DESTINATIONIP" NVARCHAR2(15), "DESTINATIONPORT" NUMBER NOT NULL , "PACKETSIZE" NUMBER NOT NULL , "TYPE" NUMBER NOT NULL , "ACTION" NUMBER NOT NULL , "ITEMID" NUMBER, "REPEATCOUNT" NUMBER NOT NULL , "ENDEVENTDATE" TIMESTAMP(6), "RANK" NUMBER, "HOSTASSETVALUE" NUMBER(5), "FILTERSEVERITYVALUE" NUMBER(5), PRIMARY KEY ("EVENTID") VALIDATE ) TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
CREATE INDEX "MYSCHEMA"."EVENTS_EVENTDATE" ON "MYSCHEMA"."EVENTS" ("EVENTDATE") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING LOCAL
Then there's a bunch of other indexes, 8 total, with the one above and the one PK, 6 more look similar to the one below:
CREATE INDEX "MYSCHEMA"."EVENTS_ACTION" ON "MYSCHEMA"."EVENTS" ("ACTION") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING LOCAL
I enabled the trace as described, but think perhaps i had it enabled globally already :) Anyways, i found the statement in the 12mb trace file, it starts out like this:
Sorry about the clipping - let me know if i can send the 13mb file directly.
Why is it stuck waiting? Normally there are a bunch of sessions accessing this table, but to run these tests i shut down the application and am ONLY touching oracle with sqlplus. What could it be waiting on? Is it doing maintenance perhaps?
> I enabled the trace as described, but think perhaps i had it enabled > globally already :) Anyways, i found the statement in the 12mb trace > file, it starts out like this:
> Why is it stuck waiting? Normally there are a bunch of sessions > accessing this table, but to run these tests i shut down the > application and am ONLY touching oracle with sqlplus. What could it > be waiting on? Is it doing maintenance perhaps?
> Thanks again for any help you can provide, > Bob
It is hard to tell from the small snippet that you posted. My guess is that Oracle is attempting to apply undo to build a consistent image of the index blocks based on the last commit. It appears that the server has a slow single block read time, which could mean that you need to spread out the I/O over a greater number of spindles (but that might not be the case). Wait events: Trace Time Wait Time Wait Event 0.000206 0.000s SQL*Net message to client 0.012654 0.012s db file sequential read 0.024509 0.012s db file sequential read 0.026754 0.002s db file sequential read 0.033117 0.006s db file sequential read 0.042893 0.010s db file sequential read ... 944.90019 0.009s db file sequential read 944.900376 0.000s db file sequential read 944.910382 0.010s db file sequential read
Execution statistics - seems like a lot of time required for a parse, CPU was involved in the fetch for 7.9 of the 944.9 seconds - the rest of the time is likely from the file system performing single block reads: |PARSEs 1|CPU S 0.031250|CLOCK S 0.068246|ROWs 0|PHY RD BLKs 2| CON RD BLKs (Mem) 199|CUR RD BLKs (Mem) 0| |EXECs 1|CPU S 0.000000|CLOCK S 0.000050|ROWs 0|PHY RD BLKs 0| CON RD BLKs (Mem) 0|CUR RD BLKs (Mem) 0| |FETCHs 2|CPU S 7.906250|CLOCK S 944.910346|ROWs 1|PHY RD BLKs 118910| CON RD BLKs (Mem) 118953|CUR RD BLKs (Mem) 0|
Actual execution plan: 118,953 consistent reads (in memory), 118910 physical reads (from disk): SORT AGGREGATE (cr=118953 pr=118910 pw=0 time=305509471 us INDEX FULL SCAN (MIN/ MAX) EVENTS_EVENTDATE (cr=118953 pr=118910 pw=0 time=305509450 us)
Do you have a session that has made a lot of changes to the table, but not committed?
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> On Jan 29, 9:55 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > Thanks again for all your help!
> > I enabled the trace as described, but think perhaps i had it enabled > > globally already :) Anyways, i found the statement in the 12mb trace > > file, it starts out like this:
> > Why is it stuck waiting? Normally there are a bunch of sessions > > accessing this table, but to run these tests i shut down the > > application and am ONLY touching oracle with sqlplus. What could it > > be waiting on? Is it doing maintenance perhaps?
> > Thanks again for any help you can provide, > > Bob
> It is hard to tell from the small snippet that you posted. My guess > is that Oracle is attempting to apply undo to build a consistent image > of the index blocks based on the last commit. It appears that the > server has a slow single block read time, which could mean that you > need to spread out the I/O over a greater number of spindles (but that > might not be the case). > Wait events: > Trace Time Wait Time Wait Event > 0.000206 0.000s SQL*Net message to client > 0.012654 0.012s db file sequential read > 0.024509 0.012s db file sequential read > 0.026754 0.002s db file sequential read > 0.033117 0.006s db file sequential read > 0.042893 0.010s db file sequential read > ... > 944.90019 0.009s db file sequential read > 944.900376 0.000s db file sequential read > 944.910382 0.010s db file sequential read
> Execution statistics - seems like a lot of time required for a parse, > CPU was involved in the fetch for 7.9 of the 944.9 seconds - the rest > of the time is likely from the file system performing single block > reads: > |PARSEs 1|CPU S 0.031250|CLOCK S 0.068246|ROWs 0|PHY RD BLKs 2| > CON RD BLKs (Mem) 199|CUR RD BLKs (Mem) 0| > |EXECs 1|CPU S 0.000000|CLOCK S 0.000050|ROWs 0|PHY RD BLKs 0| > CON RD BLKs (Mem) 0|CUR RD BLKs (Mem) 0| > |FETCHs 2|CPU S 7.906250|CLOCK S 944.910346|ROWs 1|PHY RD BLKs 118910| > CON RD BLKs (Mem) 118953|CUR RD BLKs (Mem) 0|
> Actual execution plan: 118,953 consistent reads (in memory), 118910 > physical reads (from disk): > SORT AGGREGATE (cr=118953 pr=118910 pw=0 time=305509471 us > INDEX FULL SCAN (MIN/ MAX) EVENTS_EVENTDATE (cr=118953 pr=118910 > pw=0 time=305509450 us)
> Do you have a session that has made a lot of changes to the table, but > not committed?
> Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.
There is certainly no CURRENT session in the database that has a large number of uncommitted operations, but it is certainly possible there was one in the past, and perhaps it never finished properly. A typical operation on the database involves doing a "DELETE FROM EVENTS WHERE EVENTDATE < ?" - its possible one of these happenned in the past and didn't finish properly.
Is there some way i can get evidence of something like this? I'm searching the sessions as we speak and see none of my application sessions lingering. When i look at top activity when the query is not running, it doesn't look like anything else is happenning - how can i tell if oracle IS busy with a crapped out session, or maintenance (no other jobs running either).
> On Jan 28, 11:39 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > 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, > > > 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;
> > 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
> > 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:
> 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:
> 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
118525 consistent gets and 118248 physical reads to return one row 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 row has two or more 'pointers' because it's been relocated one or more times due to block space issues. Creating the CHAINED_ROWS table ($ORACLE_HOME/rdbms/admin/ utlchain.sql) and running
analyze table events list chained rows;
would populate the CHAINED_ROWS table and could provide some additional information. Querying CHAINED_ROWS:
select owner_name, table_name, count(*) from chained_rows group by owner_name, table_name;
You may have nothing returned, or you may have a large number of chained/migrated rows with which to deal. Looking at your table definition and presuming an 8k block size I can't expect you'd have any actual chained rows (rows that, no matter what, will not fit into a single data block), but I can expect you may have any number of migrated rows, which can be fixed by increasing the PCTFREE for the table (currently set at 10). An alter table events move pctfree 25; could fix the migrated row issue and may improve your query response time. Notice I said 'may'; nothing, including the advice offered here, is guaranteed. You can see if the migrated rows are fixed by truncating the CHAINED_ROWS table and executing the same analyze table statement as before:
analyze table events list chained rows;
Hopefully if you had any migrated rows before they would now be gone. Executing your query against the now cleansed' table might show different statistics through autotrace. And those statistics might be an improvement over the original run. And again, this is somewhat guesswork.
> 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
> 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
Here you have a completely different story: 132 blocks read from the 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
...
On Jan 29, 12:25 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> There is certainly no CURRENT session in the database that has a large > number of uncommitted operations, but it is certainly possible there > was one in the past, and perhaps it never finished properly. A > typical operation on the database involves doing a "DELETE FROM EVENTS > WHERE EVENTDATE < ?" - its possible one of these happenned in the past > and didn't finish properly.
> Is there some way i can get evidence of something like this? I'm > searching the sessions as we speak and see none of my application > sessions lingering. When i look at top activity when the query is not > running, it doesn't look like anything else is happenning - how can i > tell if oracle IS busy with a crapped out session, or maintenance (no > other jobs running either).
> Thanks AGAIN!!!
Test runs with sample data may be helpful. Back to the original sample that I posted: CREATE TABLE T1 (EVENTDATE TIMESTAMP NOT NULL); CREATE INDEX T1_IND1 ON T1(EVENTDATE);
INSERT INTO T1 SELECT TRUNC(SYSDATE-3000)+ROWNUM/100 FROM DUAL CONNECT BY LEVEL<=900000;
From the 10046 trace file: PARSE #1:c=0,e=6954,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5794606730 EXEC #1:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5794608342 WAIT #1: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5794608829 FETCH #1:c=0,e=91,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=5794609296 WAIT #1: nam='SQL*Net message from client' ela= 10946 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5794620734 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3 pr=0 pw=0 time=93 us)' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=32286 op='INDEX FULL SCAN (MIN/MAX) T1_IND1 (cr=3 pr=0 pw=0 time=55 us)'
In the above, Oracle used an INDEX FULL SCAN (MIN/MAX) to find the minimum using an optimization, requiring only 3 consistent reads (cr=3 and Buffers = 3), rather than having to scan all of the blocks in the index. Oracle required 91/1000000 of a second to fetch the result.
Now, let's see what happens when other sessions are involved. In a second session: DELETE FROM T1 WHERE EVENTDATE<SYSDATE-1000;
200,053 ROWS DELETED
(no commit performed)
Back to the first session to see how the query is affected: SELECT MIN(EVENTDATE) FROM T1;
Note now that Oracle performed 365 consistent reads, and that the elapsed time has increased from 565 to 44496. Actual time in the DBMS Xplan reports an increase from 0.01 seconds to 0.04 seconds.
You might take a look at the output of the following to help determine if Oracle is having to rollback uncommitted data to provide a consistent view of the data due to an in-process transaction: SELECT * FROM V$TRANSACTION;
SELECT * FROM V$LOCK;
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> On Jan 28, 7:17 pm, joel garry <joel-ga...@home.com> wrote:
> > On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
> > Well, I'm guessing that your 20 column index is just not the best one > > for your min. What happens if you make a btree index on just that > > eventdate column? It's been my experience that any table with more > > than a few columns in an index isn't properly normalized.
> > There might be bug/patch issues, too. timestamp is relatively new, > > and you are using an unpatched base release.
> > How are you loading this table? What is the exact definition of the > > index?
> All the indexes on the table only span ONE column, not all! Here's > the ddl (i've modified some names, none of the content has been > changed):
Sorry 'bout that!
Charles has bought up a good point about concurrency, and it makes me wonder if you've done mass deletes on this table. Have you tried recreating the index? (See Richard Foote's blog about indices, the general advice is you don't need to rebuild indices, but there are certain odd situations which you can and should check for if you are seeing noticeable issues. I'm wondering if you've made a realllllly sparse index, and why Oracle wouldn't be smart enough to find the min right away.).
I'm also idly wondering if delayed block cleanout can be involved (google for details). Postulating a mass delete, followed by a simple inquiry that has to traverse all the deleted blocks and write them out. If you are getting consistent results doing Charles' traces, it wouldn't be this. But I'm focused on the description of "It can occasionally take a VERY long time" and am wondering what is different at those occasions. Have you checked wait states at those times? Maybe there's more than one thing wrong here - for example, slow disks plus disks overwhelmed with with I/O while archiving logs plus concurrency issues. Also, I have a vague thought of some process like MMON going nuts, are you seeing unexplained extremely high processor usage at times?
I've done as you suggested, and the CHAINED_ROWS table is empty - so i did not attempt to change the PCTFREE parameter.
I haven't configured how statistics are generated, but i believe that our java application occasionally asks (via oracle's JDBC driver) to estimate statistics in order to get index information. I can't find an obvious pointer in the UI that indicates if the statistics it's presenting are estimated or computed. I will see if i can "compute" the statistics now and try again.
> On Jan 29, 6:39 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > On Jan 28, 7:17 pm, joel garry <joel-ga...@home.com> wrote:
> > > On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
> > > Well, I'm guessing that your 20 column index is just not the best one > > > for your min. What happens if you make a btree index on just that > > > eventdate column? It's been my experience that any table with more > > > than a few columns in an index isn't properly normalized.
> > > There might be bug/patch issues, too. timestamp is relatively new, > > > and you are using an unpatched base release.
> > > How are you loading this table? What is the exact definition of the > > > index?
> > All the indexes on the table only span ONE column, not all! Here's > > the ddl (i've modified some names, none of the content has been > > changed):
> Sorry 'bout that!
> Charles has bought up a good point about concurrency, and it makes me > wonder if you've done mass deletes on this table. Have you tried > recreating the index? (See Richard Foote's blog about indices, the > general advice is you don't need to rebuild indices, but there are > certain odd situations which you can and should check for if you are > seeing noticeable issues. I'm wondering if you've made a realllllly > sparse index, and why Oracle wouldn't be smart enough to find the min > right away.).
> I'm also idly wondering if delayed block cleanout can be involved > (google for details). Postulating a mass delete, followed by a simple > inquiry that has to traverse all the deleted blocks and write them > out. If you are getting consistent results doing Charles' traces, it > wouldn't be this. But I'm focused on the description of "It can > occasionally take a VERY long time" and am wondering what is different > at those occasions. Have you checked wait states at those times? > Maybe there's more than one thing wrong here - for example, slow disks > plus disks overwhelmed with with I/O while archiving logs plus > concurrency issues. Also, I have a vague thought of some process like > MMON going nuts, are you seeing unexplained extremely high processor > usage at times?
> > CREATE TABLE "MYSCHEMA"."EVENTS" > > ( "EVENTID" NUMBER, > > "EVENTDATE" TIMESTAMP(6) NOT NULL , > > "HOSTID" NUMBER NOT NULL , > > "DIRECTION" NUMBER NOT NULL , > > "INTERFACE" NVARCHAR2(17), > > "FRAME" NUMBER NOT NULL , > > "SOURCEMAC" NVARCHAR2(17), > > "DESTINATIONMAC" NVARCHAR2(17), > > "PROTOCOL" NUMBER NOT NULL , > > "FLAGS" NVARCHAR2(64), > > "SOURCEIP" NVARCHAR2(15), > > "SOURCEPORT" NUMBER NOT NULL , > > "DESTINATIONIP" NVARCHAR2(15), > > "DESTINATIONPORT" NUMBER NOT NULL , > > "PACKETSIZE" NUMBER NOT NULL , > > "TYPE" NUMBER NOT NULL , > > "ACTION" NUMBER NOT NULL , > > "ITEMID" NUMBER, > > "REPEATCOUNT" NUMBER NOT NULL , > > "ENDEVENTDATE" TIMESTAMP(6), > > "RANK" NUMBER, > > "HOSTASSETVALUE" NUMBER(5), > > "FILTERSEVERITYVALUE" NUMBER(5), > > PRIMARY KEY ("EVENTID") VALIDATE ) > > TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE > > ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
> > CREATE INDEX "MYSCHEMA"."EVENTS_EVENTDATE" ON > > "MYSCHEMA"."EVENTS" ("EVENTDATE") TABLESPACE "USERS" PCTFREE 10 > > INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) > > LOGGING LOCAL
> > Then there's a bunch of other indexes, 8 total, with the one above and > > the one PK, 6 more look similar to the one below:
> > CREATE INDEX "MYSCHEMA"."EVENTS_ACTION" ON > > "MYSCHEMA"."EVENTS" ("ACTION") TABLESPACE "USERS" PCTFREE 10 INITRANS > > 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING > > LOCAL
> jg > -- > @home.com is bogus. > "Weather forecasts are horoscopes with numbers."
I definitely have done some large scale deletes. When i say occasionally, there have been times when the query has been fast, but it hasn't been fast in a long time... likely since the last large scale delete operation actually. The CPU never spikes on the oracle box, its definitely always blocking on IO. Mind you, the disk is slow, but since it is occasionally speedy, i can't blame it all on disk (can i?).
I just tried to get more stats via the following call, and then checked again for chained rows (not sure if it would've affected, but tried anyways):
> On Jan 29, 2:53 pm, joel garry <joel-ga...@home.com> wrote:
> > On Jan 29, 6:39 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > > On Jan 28, 7:17 pm, joel garry <joel-ga...@home.com> wrote:
> > > > On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
> > > > Well, I'm guessing that your 20 column index is just not the best one > > > > for your min. What happens if you make a btree index on just that > > > > eventdate column? It's been my experience that any table with more > > > > than a few columns in an index isn't properly normalized.
> > > > There might be bug/patch issues, too. timestamp is relatively new, > > > > and you are using an unpatched base release.
> > > > How are you loading this table? What is the exact definition of the > > > > index?
> > > All the indexes on the table only span ONE column, not all! Here's > > > the ddl (i've modified some names, none of the content has been > > > changed):
> > Sorry 'bout that!
> > Charles has bought up a good point about concurrency, and it makes me > > wonder if you've done mass deletes on this table. Have you tried > > recreating the index? (See Richard Foote's blog about indices, the > > general advice is you don't need to rebuild indices, but there are > > certain odd situations which you can and should check for if you are > > seeing noticeable issues. I'm wondering if you've made a realllllly > > sparse index, and why Oracle wouldn't be smart enough to find the min > > right away.).
> > I'm also idly wondering if delayed block cleanout can be involved > > (google for details). Postulating a mass delete, followed by a simple > > inquiry that has to traverse all the deleted blocks and write them > > out. If you are getting consistent results doing Charles' traces, it > > wouldn't be this. But I'm focused on the description of "It can > > occasionally take a VERY long time" and am wondering what is different > > at those occasions. Have you checked wait states at those times? > > Maybe there's more than one thing wrong here - for example, slow disks > > plus disks overwhelmed with with I/O while archiving logs plus > > concurrency issues. Also, I have a vague thought of some process like > > MMON going nuts, are you seeing unexplained extremely high processor > > usage at times?
> > > CREATE TABLE "MYSCHEMA"."EVENTS" > > > ( "EVENTID" NUMBER, > > > "EVENTDATE" TIMESTAMP(6) NOT NULL , > > > "HOSTID" NUMBER NOT NULL , > > > "DIRECTION" NUMBER NOT NULL , > > > "INTERFACE" NVARCHAR2(17), > > > "FRAME" NUMBER NOT NULL , > > > "SOURCEMAC" NVARCHAR2(17), > > > "DESTINATIONMAC" NVARCHAR2(17), > > > "PROTOCOL" NUMBER NOT NULL , > > > "FLAGS" NVARCHAR2(64), > > > "SOURCEIP" NVARCHAR2(15), > > > "SOURCEPORT" NUMBER NOT NULL , > > > "DESTINATIONIP" NVARCHAR2(15), > > > "DESTINATIONPORT" NUMBER NOT NULL , > > > "PACKETSIZE" NUMBER NOT NULL , > > > "TYPE" NUMBER NOT NULL , > > > "ACTION" NUMBER NOT NULL , > > > "ITEMID" NUMBER, > > > "REPEATCOUNT" NUMBER NOT NULL , > > > "ENDEVENTDATE" TIMESTAMP(6), > > > "RANK" NUMBER, > > > "HOSTASSETVALUE" NUMBER(5), > > > "FILTERSEVERITYVALUE" NUMBER(5), > > > PRIMARY KEY ("EVENTID") VALIDATE ) > > > TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE > > > ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
> > jg > > -- > > @home.com is bogus. > > "Weather forecasts are horoscopes with numbers."
> I definitely have done some large scale deletes. When i say > occasionally, there have been times when the query has been fast, but > it hasn't been fast in a long time... likely since the last large > scale delete operation actually. The CPU never spikes on the oracle > box, its definitely always blocking on IO. Mind you, the disk is > slow, but since it is occasionally speedy, i can't blame it all on > disk (can i?).
Sure, when all else fails blame the hardware! :-) (There are people who don't tune and just throw hardware at it - I've done that myself, it can work - but it is generally stupid without figuring out what is really going on). I have seen failing hardware be slow and sometimes fast.
I think we've ruled out the delayed block cleanout issue if it hasn't been fast in a long time, that would be an occasional thing, anything that touches all blocks, like getting stats without sampling, would clean it out. And we've ruled out chaining.
Also, please post your top waits, and continue on the path Charles has started, it may very well also lead to the solution(s). I/O binding can be caused by many things, from actually needing lots of data to tuning to bad code. It's most often bad code. Mass deletes can be done many ways, some of which may be considered bad at times. Search http://asktom.oracle.com for mass delete. Sometimes a better way is to create a new table with what you want to keep, then rename.
> I just tried to get more stats via the following call, and then > checked again for chained rows (not sure if it would've affected, but > tried anyways):
> On Jan 29, 1:36 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote: > > On Jan 29, 2:53 pm, joel garry <joel-ga...@home.com> wrote:
> > > On Jan 29, 6:39 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > > > On Jan 28, 7:17 pm, joel garry <joel-ga...@home.com> wrote:
> > > > > On Jan 28, 7:40 am, "bobdu...@gmail.com" <bobdu...@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.
> > > > > Well, I'm guessing that your 20 column index is just not the best one > > > > > for your min. What happens if you make a btree index on just that > > > > > eventdate column? It's been my experience that any table with more > > > > > than a few columns in an index isn't properly normalized.
> > > > > There might be bug/patch issues, too. timestamp is relatively new, > > > > > and you are using an unpatched base release.
> > > > > How are you loading this table? What is the exact definition of the > > > > > index?
> > > > All the indexes on the table only span ONE column, not all! Here's > > > > the ddl (i've modified some names, none of the content has been > > > > changed):
> > > Sorry 'bout that!
> > > Charles has bought up a good point about concurrency, and it makes me > > > wonder if you've done mass deletes on this table. Have you tried > > > recreating the index? (See Richard Foote's blog about indices, the > > > general advice is you don't need to rebuild indices, but there are > > > certain odd situations which you can and should check for if you are > > > seeing noticeable issues. I'm wondering if you've made a realllllly > > > sparse index, and why Oracle wouldn't be smart enough to find the min > > > right away.).
> > > I'm also idly wondering if delayed block cleanout can be involved > > > (google for details). Postulating a mass delete, followed by a simple > > > inquiry that has to traverse all the deleted blocks and write them > > > out. If you are getting consistent results doing Charles' traces, it > > > wouldn't be this. But I'm focused on the description of "It can > > > occasionally take a VERY long time" and am wondering what is different > > > at those occasions. Have you checked wait states at those times? > > > Maybe there's more than one thing wrong here - for example, slow disks > > > plus disks overwhelmed with with I/O while archiving logs plus > > > concurrency issues. Also, I have a vague thought of some process like > > > MMON going nuts, are you seeing unexplained extremely high processor > > > usage at times?
> > > jg > > > -- > > > @home.com is bogus. > > > "Weather forecasts are horoscopes with numbers."
> > I definitely have done some large scale deletes. When i say > > occasionally, there have been times when the query has been fast, but > > it hasn't been fast in a long time... likely since the last large > > scale delete operation actually. The CPU never spikes on the oracle > > box, its definitely always blocking on IO. Mind you, the disk is > > slow, but since it is occasionally speedy, i can't blame it all on > > disk (can i?).
> Sure, when all else fails blame the hardware! :-) (There are people > who don't tune and just throw hardware at it - I've done that myself, > it can work - but it is generally stupid without figuring out what is > really going on). I have seen failing hardware be slow and sometimes > fast.
> I think we've ruled out the delayed block cleanout issue if it hasn't > been fast in a long time, that would be an occasional thing, anything > that touches all blocks, like getting stats without sampling, would > clean it out. And we've ruled out chaining.
> Also, please post your top waits, and continue on the path Charles has > started, it may very well also lead to the solution(s). I/O binding > can be caused by many things, from actually needing lots of data to > tuning to bad code. It's most often bad code. Mass deletes can be > done many ways, some of which may be considered bad at times. Searchhttp://asktom.oracle.comfor mass delete. Sometimes a better way is > to create a new table with what you want to keep, then rename.
> > I just tried to get more stats via the following call, and then > > checked again for chained rows (not sure if it would've affected, but > > tried anyways):
In addition to looking at the wait events, you may also want to further investigate session level statistics to see if undo blocks are being applied to produce a consistent view of the data. For example, before running your query, execute the following to obtain a beginning snapshot of the statistics: SELECT SN.NAME, MS.VALUE FROM V$STATNAME SN, V$MYSTAT MS WHERE SN.NAME IN ( 'consistent changes', 'data blocks consistent reads - undo records applied', 'consistent gets', 'cleanouts and rollbacks - consistent read gets') AND SN.STATISTIC#=MS.STATISTIC#;
Then, execute your query: select min(eventdate) from events;
Then execute the statistics query again: SELECT SN.NAME, MS.VALUE FROM V$STATNAME SN, V$MYSTAT MS WHERE SN.NAME IN ( 'consistent changes', 'data blocks consistent reads - undo records applied', 'consistent gets', 'cleanouts and rollbacks - consistent read gets') AND SN.STATISTIC#=MS.STATISTIC#;
Finally, manually subtract the beginning values from the ending values.
For example: NAME VALUE --------------------------------------------------- ---------- consistent gets 409934 consistent changes 1520 data blocks consistent reads - undo records applied 1468 cleanouts and rollbacks - consistent read gets 1
---
NAME VALUE -------------------------------------------------- ---------- consistent gets 409940 consistent changes 1522 data blocks consistent reads - undo records applied 1470 cleanouts and rollbacks - consistent read gets 2
The 10046 trace for the query that I executed showed 8 consistent reads.
CONSISTENT GETS: Requests for a Block Consistent Read - Statistic counts the number of logical reads in consistent mode.
CONSISTENT CHANGES: Undo Records Applied to Provide Consistent Read - Statistic indicates the number of times a database block has rollback entries applied to perform a consistent read of the block. Work loads that produce a great deal of consistent changes can consume a great deal of resources. The value of this statistic should be small in relation to the 'consistent gets' statistic.
DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED: Undo Applied to Block for a Consistent Read - Number of undo records applied to data blocks that have been rolled back for consistent read purposes.
CLEANOUTS AND ROLLBACKS - CONSISTENT READ GETS: Undo/Block Cleanout Required for Cons. Read - Number of consistent gets that require both block rollbacks and block cleanouts.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
On Jan 29, 10:36 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> I definitely have done some large scale deletes. When i say > occasionally, there have been times when the query has been fast, but > it hasn't been fast in a long time... likely since the last large > scale delete operation actually.
I doubt it makes a difference for the min/max types of queries but do you update statistics on a regular basis, or at least after such mass deletes? There *may* be an impact if concurrently running queries are less efficient because of stale statistics and your IO becomes slower just because of increased concurrent IO load.
> I just tried to get more stats via the following call, and then > checked again for chained rows (not sure if it would've affected, but > tried anyways):
I'm afraid i don't know how to calculate what the top waits are - i've seen references to it, but nothing conclusive. That udump file i had contains MANY waits, is there a tool that will go through it and summarize?
I think i finally figured out how to get the xplan, although i'm not sure what it tells me: SQL> select 2 /*+ 3 gather_plan_statistics 4 ordered use_nl(events) index(events) 5 */ 6 min(eventdate) 7 from 8 events 9 ;
MIN(eventdate) --------------------------------------------------------------------------- 22-JAN-08 08.44.55.912000 AM
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- ----- SQL_ID 797v1v1tbvp6s, child number 0 ------------------------------------- select /*+ gather_plan_statistics ordered use_nl(events) inde x(events) */
This indicates that there are tons of estimated rows - i'm not sure what else this really tells me... i'm still digging on the other responses, thanks for all your prolonged support!
On Jan 30, 5:02 am, Robert Klemme <shortcut...@googlemail.com> wrote:
> On Jan 29, 10:36 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> > I definitely have done some large scale deletes. When i say > > occasionally, there have been times when the query has been fast, but > > it hasn't been fast in a long time... likely since the last large > > scale delete operation actually.
> I doubt it makes a difference for the min/max types of queries but do > you update statistics on a regular basis, or at least after such mass > deletes? There *may* be an impact if concurrently running queries are > less efficient because of stale statistics and your IO becomes slower > just because of increased concurrent IO load.
I don't do anything regularily, which is bad yes, but ianaodba, so i don't even know what to do, i try to let this intelligent system handle itself :) In any event, i have updated the statistics via the command described in an earlier post, there are still no active clients to the db, and the query is still uberslow.
> > I just tried to get more stats via the following call, and then > > checked again for chained rows (not sure if it would've affected, but > > tried anyways):
> BEFORE: > NAME > VALUE > ---------------------------------------------------------------- > ---------- > consistent gets > 1656576 > consistent > changes 49 > data blocks consistent reads - undo records > applied 43 > cleanouts and rollbacks - consistent read > gets 0
> AFTER: > NAME > VALUE > ---------------------------------------------------------------- > ---------- > consistent gets > 1775792 > consistent > changes 49 > data blocks consistent reads - undo records > applied 43 > cleanouts and rollbacks - consistent read > gets 0
> DIFF: > NAME > VALUE > ---------------------------------------------------------------- > ---------- > consistent gets > 119216 > consistent > changes 0 > data blocks consistent reads - undo records > applied 0 > cleanouts and rollbacks - consistent read > gets 0
> Looks like a lot of consistent gets...
A lot of consistent gets, and likely also a lot of physical reads, and 0 undo blocks. That certainly is not what I expected. The wait events that you posted looked similar to the following: WAIT #14: nam='db file sequential read' ela= 12382 file#=4 block#=1918069 blocks=1 obj#=92627 tim=3655578822
The above indicates that Oracle is performing a single block read in file number 4 starting at block 1918069. What object is at that location? SELECT DE.OWNER, DE.SEGMENT_NAME, DE.SEGMENT_TYPE, DE.TABLESPACE_NAME, DE.BLOCK_ID, DE.BLOCKSFROM DBA_EXTENTS DE WHERE DE.FILE_ID= 4 AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
The DBMS Xplan is showing that Oracle is performing roughly 118,000 reads, while the plan that you originally posted indicated that Oracle predicted that it would perform roughly 4 reads. The DBMS XPlan is also indicating that the query required nearly 15 minutes to execute, rather than 0.01 seconds. I wonder if the extra hints that you are providing: ordered use_nl(events) index(events) Are affecting whether or not Oracle is able to use the MIN/MAX optimization, so instead of Oracle visiting 3 or 4 blocks, it is actually scanning 118,000 blocks in the index? Maybe you can try flushing the shared pool and re-executing the query without the extra hints.
To determine if system statistics have been gathered: SELECT * FROM SYS.AUX_STATS$;
If system statistics have not been gathered, there might be entries in a couple of the statistics. If system statistics have been gathered, you will see entries for SREADTIM (single block read time) and MREADTIM (multi-block read time). Collect statistics when the system is relatively busy.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> A lot of consistent gets, and likely also a lot of physical reads, and > 0 undo blocks. That certainly is not what I expected. The wait > events that you posted looked similar to the following: > WAIT #14: nam='db file sequential read' ela= 12382 file#=4 > block#=1918069 blocks=1 obj#=92627 tim=3655578822
> The above indicates that Oracle is performing a single block read in > file number 4 starting at block 1918069. What object is at that > location? > SELECT > DE.OWNER, > DE.SEGMENT_NAME, > DE.SEGMENT_TYPE, > DE.TABLESPACE_NAME, > DE.BLOCK_ID, > DE.BLOCKSFROM > DBA_EXTENTS DE > WHERE > DE.FILE_ID= 4 > AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
> The DBMS Xplan is showing that Oracle is performing roughly 118,000 > reads, while the plan that you originally posted indicated that Oracle > predicted that it would perform roughly 4 reads. The DBMS XPlan is > also indicating that the query required nearly 15 minutes to execute, > rather than 0.01 seconds. I wonder if the extra hints that you are > providing: > ordered use_nl(events) index(events) > Are affecting whether or not Oracle is able to use the MIN/MAX > optimization, so instead of Oracle visiting 3 or 4 blocks, it is > actually scanning 118,000 blocks in the index? Maybe you can try > flushing the shared pool and re-executing the query without the extra > hints.
> To determine if system statistics have been gathered: > SELECT > * > FROM > SYS.AUX_STATS$;
> If system statistics have not been gathered, there might be entries in > a couple of the statistics. If system statistics have been gathered, > you will see entries for SREADTIM (single block read time) and > MREADTIM (multi-block read time). Collect statistics when the system > is relatively busy.
> Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.
OK!!! Note - i've given up on masking the table/index/schema name, table events=packetlogs, column logdate=eventdate - i was trying to solve the problem generally, but now am getting lazy:
SQL> SELECT 2 DE.OWNER, 3 DE.SEGMENT_NAME, 4 DE.SEGMENT_TYPE, 5 DE.TABLESPACE_NAME, 6 DE.BLOCK_ID, 7 DE.BLOCKS 8 FROM 9 DBA_EXTENTS DE 10 WHERE 11 DE.FILE_ID= 4 12 AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
So that is the spot in the index in question, as expected i guess.
I did as you said, ran: ALTER SYSTEM FLUSH SHARED_POOL;
Reran with only the gather statistics hint, and same result - its taking a darn long time.
I selected system stats, its pretty sparse, those SREADTIM and MREADTIM are blank, and the one date is from 2005 (before this instance of oracle was installed!). So i think my next step will be to gather system statistics, but i'm not clear on how that will help this one lowly query. What hint can i give to tell it to NOT use the index?
Looking in the console, this is what it says about the index:
Last Analyzed 2008-01-30 10:02:07 Blevel 3 Distinct Keys 870 Clustering Factor 455854 Leaf Blocks 2947 Average Leaf Blocks Per Key 3 Average Data Blocks Per Key 523 Number of Rows 523030 Sample Size 523030
> On Jan 30, 1:16 pm, Charles Hooper <hooperc2...@yahoo.com> wrote: > > A lot of consistent gets, and likely also a lot of physical reads, and > > 0 undo blocks. That certainly is not what I expected. The wait > > events that you posted looked similar to the following: > > WAIT #14: nam='db file sequential read' ela= 12382 file#=4 > > block#=1918069 blocks=1 obj#=92627 tim=3655578822
> > The above indicates that Oracle is performing a single block read in > > file number 4 starting at block 1918069. What object is at that > > location? > > SELECT > > DE.OWNER, > > DE.SEGMENT_NAME, > > DE.SEGMENT_TYPE, > > DE.TABLESPACE_NAME, > > DE.BLOCK_ID, > > DE.BLOCKSFROM > > DBA_EXTENTS DE > > WHERE > > DE.FILE_ID= 4 > > AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
> > The DBMS Xplan is showing that Oracle is performing roughly 118,000 > > reads, while the plan that you originally posted indicated that Oracle > > predicted that it would perform roughly 4 reads. The DBMS XPlan is > > also indicating that the query required nearly 15 minutes to execute, > > rather than 0.01 seconds. I wonder if the extra hints that you are > > providing: > > ordered use_nl(events) index(events) > > Are affecting whether or not Oracle is able to use the MIN/MAX > > optimization, so instead of Oracle visiting 3 or 4 blocks, it is > > actually scanning 118,000 blocks in the index? Maybe you can try > > flushing the shared pool and re-executing the query without the extra > > hints.
> > To determine if system statistics have been gathered: > > SELECT > > * > > FROM > > SYS.AUX_STATS$;
> > If system statistics have not been gathered, there might be entries in > > a couple of the statistics. If system statistics have been gathered, > > you will see entries for SREADTIM (single block read time) and > > MREADTIM (multi-block read time). Collect statistics when the system > > is relatively busy.
> > Charles Hooper > > IT Manager/Oracle DBA > > K&M Machine-Fabricating, Inc.
> OK!!! Note - i've given up on masking the table/index/schema name, > table events=packetlogs, column logdate=eventdate - i was trying to > solve the problem generally, but now am getting lazy:
> SQL> SELECT > 2 DE.OWNER, > 3 DE.SEGMENT_NAME, > 4 DE.SEGMENT_TYPE, > 5 DE.TABLESPACE_NAME, > 6 DE.BLOCK_ID, > 7 DE.BLOCKS > 8 FROM > 9 DBA_EXTENTS DE > 10 WHERE > 11 DE.FILE_ID= 4 > 12 AND 1918069 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
> So that is the spot in the index in question, as expected i guess.
> I did as you said, ran: > ALTER SYSTEM FLUSH SHARED_POOL;
> Reran with only the gather statistics hint, and same result - its > taking a darn long time.
> I selected system stats, its pretty sparse, those SREADTIM and > MREADTIM are blank, and the one date is from 2005 (before this > instance of oracle was installed!). So i think my next step will be > to gather system statistics, but i'm not clear on how that will help > this one lowly query. What hint can i give to tell it to NOT use the > index?
> Looking in the console, this is what it says about the index:
> Last Analyzed 2008-01-30 10:02:07 > Blevel 3 > Distinct Keys 870 > Clustering Factor 455854 > Leaf Blocks 2947 > Average Leaf Blocks Per Key 3 > Average Data Blocks Per Key 523 > Number of Rows 523030 > Sample Size 523030
No problem with masking the table and index names if needed.
A minor correction to a previous statement to avoid confusion with table or index statistics: Collect <system> statistics when the system is relatively busy.
System statistics will help Oracle determine the correct (optimal) execution plan based on the actual performance characteristics of the server. As best I can tell, Oracle is coming up with the best plan, but is apparently not using the MIN/MAX optimization.
To prevent the use of the one index currently being used: /*+ NO_INDEX(packetlogs PACKETLOGS_LOGDATE) */
Do not use any index: /*+ FULL(packetlogs) */
You can forward the 12MB trace file to my Yahoo email address, and I will try to pass it through a program I wrote for analysis.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
On Jan 30, 6:26 pm, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> On Jan 30, 5:02 am, Robert Klemme <shortcut...@googlemail.com> wrote: > > I doubt it makes a difference for the min/max types of queries but do > > you update statistics on a regular basis, or at least after such mass > > deletes? There *may* be an impact if concurrently running queries are > > less efficient because of stale statistics and your IO becomes slower > > just because of increased concurrent IO load.
> I don't do anything regularily, which is bad yes, but ianaodba,
Then please do yourself a favor ask whoever is responsible for the database to set up statistics gathering.
> so i > don't even know what to do, i try to let this intelligent system > handle itself :)
Oracle is getting better with manageability IMHO but you cannot let a DB left unattended.
> How do i know if i have system statistics set?
From an earlier posting in this group:
select pname, pval1, sname from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'
