Home » RDBMS Server » Performance Tuning » Full table scan with varying consistent gets
Full table scan with varying consistent gets [message #110324] Sat, 05 March 2005 15:14 Go to next message
skelly
Messages: 1
Registered: March 2005
Junior Member
Hi,

I have a table with 50,000 rows. When I perform a query which requires 5% of the table less consistent gets are used than in a query requiring 100% of the table.

The consitent gets rise steadily as the row selectivity increases from 5 to 100%, so it is not a freak occurence.

I thought consistent gets were used when reading the table, and a full table scan needs to read the whole table meaning there should be no difference.

Can anyone help with a reason this is happening? Im sure its simple but I need to know for a assignment due in very soon!

Here are my explain plans:

1 - 5%

select /*+ Full(biggertable50000) */* from biggertable50000
where ID <=2500;


2500 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=371 Card=3895 Bytes=
334970)

1 0 TABLE ACCESS (FULL) OF 'BIGGERTABLE50000' (Cost=371 Card=3
895 Bytes=334970)


Statistics
----------------------------------------------------------
144 recursive calls
7 db block gets
4026 consistent gets
3860 physical reads
0 redo size
60704 bytes sent via SQL*Net to client
11693 bytes received via SQL*Net from client
169 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2500 rows processed

Elapsed: 00:00:13.09


2 - 100%

select /*+ Full(biggertable50000) */* from biggertable50000
where ID <=50000;


50000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=371 Card=3895 Bytes=
334970)

1 0 TABLE ACCESS (FULL) OF 'BIGGERTABLE50000' (Cost=371 Card=3
895 Bytes=334970)

Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
6926 consistent gets
3819 physical reads
0 redo size
1249741 bytes sent via SQL*Net to client
230218 bytes received via SQL*Net from client
3336 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50000 rows processed

Elapsed: 00:03:57.09

Thanks.
Re: Full table scan with varying consistent gets [message #110327 is a reply to message #110324] Sat, 05 March 2005 22:08 Go to previous message
Frank Naude
Messages: 4578
Registered: April 1998
Senior Member
Hi,

Consistent gets is the number of accesses made to the buffer cache to retrieve data in a consistent mode. Most accesses are done with the "consistent get" mechanism, which uses the SCN (System Change Number) to ensure the data being read has not changed sine the query was started.

So, the more data you read from cache, the higher consistent gets would be.

Best regards.

Frank
Previous Topic: SQL AREA HIT Ratio
Next Topic: Index not used when changing the selected columns
Goto Forum:
  


Current Time: Tue Feb 27 08:09:56 CST 2024