| Full table scan with varying consistent gets [message #110324] |
Sat, 05 March 2005 15:14  |
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  |
Frank Naude
Messages: 4579
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
