There is no fixed cost for a logical I/O. The cost of a LIO will change depending on many factors.
Anjo. On Monday 27 January 2003 06:59, chao_ping wrote: > 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) -- ---------------------------------------------------------------- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk 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).
