The stats output for your sql shows no physical reads.
This means either the whole table is cached or simply since you ran the test
many times you got all the blocks you're interested in cached.
Since all the needed blocks are cached, I do not think fetching the rows
using the rowid would be any different if the rows are in one cached block
or many cached blocks.
Waleed
-----Original Message-----
Sent: Monday, January 27, 2003 9:59 AM
To: Multiple recipients of list ORACLE-L
hi, friends:
I hit some strange performance problem on my 9.2.0.2 on redhat
linux.
I want to show developer/manager why delete data for archiving
history data is not a good idear, and I did a test:
There is some big table in our app, and currently we use cron to
delete rows everyday(delete rows before 15 days). I exported it from the
production and imported it to test env(with same hardware), the imported
table named UCM_USERCOMMENT_MAINTAIN_old, later I created a new table
UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old order
by ucm_create_dtm;
And I want to tell developers the factor of CLUSTERING_FACTOR:
SQL> select table_name,index_name,CLUSTERING_FACTOR from user_indexes where
table_name like 'UCM%';
TABLE_NAME INDEX_NAME
CLUSTERING_FACTOR
------------------------------ ------------------------------
-----------------
UCM_USERCOMMENT_MAINTAIN IDX_UCM4
22165
UCM_USERCOMMENT_MAINTAIN_OLD IDX_UCM5
49681
And I am sure the following SQL:
select count(*) from UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN
)
WHERE ucm_create_dtm<(sysdate-(2/24)) AND ucm_notify_email=1 AND
ucm_notify_sms=0 ;
To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to
query from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising:
SQL> select count(*) from UCM_USERCOMMENT_MAINTAIN_old
2 WHERE ucm_create_dtm<(sysdate-(2/24)) AND ucm_notify_email=1 AND
ucm_notify_sms=0 ;
COUNT(*)
----------
350399
Elapsed: 00:00:01.63
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF
'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997)
3 2 INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24
Card=7724)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
43629 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from UCM_USERCOMMENT_MAINTAIN
2 WHERE ucm_create_dtm<(sysdate-(2/24)) AND ucm_notify_email=1 AND
ucm_notify_sms=0 ;
COUNT(*)
----------
350399
Elapsed: 00:00:01.70
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN'
(Cost=224 Card=10916 Bytes=120076)
3 2 INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24
Card=7860)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22083 consistent gets
1 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you see, the first sql generated 43629 consistent gets and the
second sql 22083 consistent gets, this is ok, how ever, the first take
1.63 second and the second take 1.70 second.This seems strange, right? Since
in most case, higher consistent gets means longer time. There is no one else
running on this server, And I also tested with event 10046 with no wait
event.I tested for several times, with the same result.
Can someone help me understand it?
Thanks very much.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: chao_ping
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
