more consistent gets, but more quickly?

From: chao_ping <chao_ping_at_vip.163.com>
Date: Mon, 27 Jan 2003 06:59:14 -0800
Message-ID: <F001.0053B0C2.20030127065914@fatcity.com>

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)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.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).

Received on Mon Jan 27 2003 - 08:59:14 CST