| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: more consistent gets, but more quickly?
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_NAMECLUSTERING_FACTOR
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 servicesReceived on Mon Jan 27 2003 - 14:02:23 CST
---------------------------------------------------------------------
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: Waleed.Khedr_at_FMR.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).
 |
 |