Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tuning question...
gdas_at_my-deja.com wrote in message <94dt74$r8i$1_at_nnrp1.deja.com>...
>I've got a query that I'm trying to tune on 8.1.6 and when I run a
>trace on it, the execution plan seems to be ok (no full table scans,
>all the proper indexes are being used), but the stats for the query
>show an extremely high number of consistent gets (all other stats are
>very low)... and of course the reason i'm trying to tune this in the
>first place is because it's slow.
>
A consistent get is the retrieval of a row from the Oracle data block buffer, if necessary applying rollback to unwind any updates which have been applied to the block after your query started.
Consistent gets rarely initiate physical I/O, as you're reading from the data block buffer in memory rather than from disc. But they do take *some* time, so if you've got hundreds of thousands, they'll impact the query performance.
Jonathan and Spencer have both provided good advice. Here's my bit:
You say you have no table scans, and all the proper indexes are being used, but are you sure the optimal indexes are being used? Let me give an example:
Suppose you have
select d.dname, e.ename
from dept d, emp e
and suppose there are indexes on both d.deptno and e.deptno. Suppose also that there are 100 dept rows and 100,000 emp rows.
The optimiser will probably execute this using nested loops. There are two possibilities.
The first is:
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' INDEX (RANGE SCAN) OF 'DEPT$DEPTNO' (NON-UNIQUE)
If we interpret this as pseudo-code, it means:
read rows from EMP /* Full-table scan: 100,000 rows {
for each current EMP row { look up the DEPT row for the current EMP.DEPTNO value using the index DEPT$DEPTNO }
The outside loop will be executed 100,000 times so there will also be 100,000 lookups of the DEPT table, giving a total of 200,000 consistent gets.
The other possibility is:
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS TABLE ACCESS (FULL) OF 'DEPT' TABLE ACCESS (BY INDEX ROWID) OF 'EMP' INDEX (RANGE SCAN) OF 'EMP$DEPTNO' (NON-UNIQUE)
If we interpret this as pseudo-code, it means:
read rows from DEPT /* Full-table scan: 100 rows { for each current DEPT row { look up the EMP rows for the current DEPT.DEPTNO value using the index EMP$DEPTNO }
The outside loop will be executed 100 times. There will also be 100,000 gets from the EMP table in total, giving a total of 100,100 consistent gets.
So although the execution plans look similar, the number of consistent gets is considerably different: 200,000 in the first example, and just over half that, 100,100 in the second.
HTH, Dave.
-- If you reply to this newsgroup posting by email, remove the "nospam" from my email address first.Received on Mon Jan 22 2001 - 15:09:03 CST