Re: more consistent gets, but more quickly?

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:chao_ping_at_163.com
> 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: anjo_at_oraperf.com

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: ListGuru_at_fatcity.com (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).