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).
