You Asked

Following query does 24801 consistent gets though it only returns 1 row.
Can you please explain the relationship between number of rows returned and consistent gets?

Table prod has 1.7 million rows,
Table vendor has 24000 rows.

SQL> SELECT COUNT (DISTINCT a.fnc_and_ins_key)
2 FROM REP_FNC_AND_INS_DTL_FACT a,
3 REP_DLR_DIM dlrd
4 WHERE a.dlvry_date >= TO_DATE ('12/01/2005 0000', 'MM/DD/YYYY hh24mi')
5 AND a.dlvry_date <= TO_DATE ('12/31/2005 2359', 'MM/DD/YYYY hh24mi')
6 AND a.dlr_key = dlrd.dlr_key
7 AND dlrd.bllbl_ind = 'Y';

COUNT(DISTINCTA.FNC_AND_INS_KEY)
--------------------------------
221847

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=149 Card=1 Bytes=26)
1 0 SORT (GROUP BY)
2 1 HASH JOIN (Cost=149 Card=3053 Bytes=79378)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'REP_FNC_AND_INS_DTL_
FACT' (Cost=117 Card=3053 Bytes=61060)

4 3 INDEX (RANGE SCAN) OF 'REP_DLVRY_DTE_FK_FS_DTL_IDX'
(NON-UNIQUE) (Cost=7 Card=3053)

5 2 TABLE ACCESS (FULL) OF 'REP_DLR_DIM' (Cost=31 Card=115
97 Bytes=69582)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24801 consistent gets
0 physical reads
0 redo size
222 bytes sent via SQL*Net to client
270 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Thanks

and Tom said...

... Can you please explain the relationship between number of rows returned and
consistent gets? ...

there generally isn't one, especially in this case!!!

Think about it - you asked for "select count(distinct something)"

if a COUNT(*) comes back and says "there were 1,000,000,000 things we just counted", I would expect a TON of logical IO as we need to sort of retrieve, process and then aggregate 1,000,000,000 things. (remember count(distinct) can process the same 1,000,000,000 records and return the number "0" or "1" - count(*) would be needed to see how many rows we processed)

So, in that case, you have to look at the answer from the count distinct and ask yourself "ok, that answer was X, in order to process X rows from this table, how many IO's would I expect to do?"

In this case, with this plan, I would expect the IO's to be:

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'REP_FNC_AND_INS_DTL_FACT'
(Cost=117 Card=3053 Bytes=61060)
4 3 INDEX (RANGE SCAN) OF 'REP_DLVRY_DTE_FK_FS_DTL_IDX'
(Cost=7 Card=3053)
5 2 TABLE ACCESS (FULL) OF 'REP_DLR_DIM'
(Cost=31 Card=11597 Bytes=69582)

the number of blocks in REP_DLR_DIM
plus
the number of blocks it takes to range scan REP_DLVRY_DTE_FK_FS_DTL_IDX, a couple of hundred if the 3,053 card=estimate is accurate
plus
3053 blocks for the table access by index rowid

Various factors influence the amount of work performed here. How "good" is that index, do we scan thousands of entries we don't need to? Is the high water mark on the table we full scanned way high, relative to the amount of data in the table (meaning the table was once much much larger than it currently is, we are scanning lots of empty blocks). What was the array fetch size, it can impact it (not too much with the hash join probably, since on the index range scan/table access part would be affected, for example:

big_table@ORA10GR2> set arraysize 10

big_table@ORA10GR2> select *
2 from big_table a, (select * from big_table where id <= 2000) b
3 where a.data_object_id = b.data_object_id
4 /

22113 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2097991270

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3009 | 575K| 3289 (2)| 00:00:40 |
|* 1 | HASH JOIN | | 3009 | 575K| 3289 (2)| 00:00:40 |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 145 | 14210 | 34 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK | 1891 | | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BIG_TABLE | 76427 | 7314K| 3253 (2)| 00:00:40 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."DATA_OBJECT_ID"="BIG_TABLE"."DATA_OBJECT_ID")
2 - filter("BIG_TABLE"."DATA_OBJECT_ID" IS NOT NULL)
3 - access("ID"<=2000)
4 - filter("A"."DATA_OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16427 consistent gets
0 physical reads
432 redo size
1631612 bytes sent via SQL*Net to client
24702 bytes received via SQL*Net from client
2213 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22113 rows processed

big_table@ORA10GR2> set arraysize 1000
big_table@ORA10GR2> /

22113 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2097991270

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3009 | 575K| 3289 (2)| 00:00:40 |
|* 1 | HASH JOIN | | 3009 | 575K| 3289 (2)| 00:00:40 |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 145 | 14210 | 34 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK | 1891 | | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BIG_TABLE | 76427 | 7314K| 3253 (2)| 00:00:40 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."DATA_OBJECT_ID"="BIG_TABLE"."DATA_OBJECT_ID")
2 - filter("BIG_TABLE"."DATA_OBJECT_ID" IS NOT NULL)
3 - access("ID"<=2000)
4 - filter("A"."DATA_OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14513 consistent gets
0 physical reads
432 redo size
1353609 bytes sent via SQL*Net to client
623 bytes received via SQL*Net from client
24 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22113 rows processed

big_table@ORA10GR2> set autotrace off

It all comes down to you asking "so, given the amount of data we had to process - is the IO count 'reasonable'"

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Relevant expression has a sort

Thomas Gilmore, February 13, 2006 - 4:12 pm UTC

If you add a sort to your table i.e. order by a, the consistent gets are consistent with a single read of each block! This is coming from the sort area and does not involve the client in the retrieval. This is really more relevant as very few selects are done without a sort!

February 13, 2006 - 4:24 pm UTC

No, as always "it depends"

ops$tkyte@ORA9IR2> select * from big_table order by id;

10000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=161 Card=10000 Bytes=940000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=161 Card=10000 Bytes=940000)
   2    1     INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=21 Card=10000)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1483  consistent gets
          0  physical reads
          0  redo size
     771937  bytes sent via SQL*Net to client
       7829  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte@ORA9IR2> set arraysize 1000
ops$tkyte@ORA9IR2> /

10000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=161 Card=10000 Bytes=940000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=161 Card=10000 Bytes=940000)
   2    1     INDEX (FULL SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=21 Card=10000)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        181  consistent gets
          0  physical reads
          0  redo size
     692440  bytes sent via SQL*Net to client
        602  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed


It depends on whether or not the sort is actually done in temp <b>, it depends entirely on the access path, the plan</b>.  And to say they are retrieved with a "single read of each block" is not really accurate either - it really depends on how the data is physically organized, what access path is used and many other factors.


I alluded to that (the reads from temp) above when I said:

... (not too much with the hash join probably, since on the index range scan/table access part would be affected,....


The table that is read and hashed would be "read once".  The other part of the plan (the index + table access) would be affected by the array size.


Reason I'm pointing out this (well, because it isn't true for one) but mostly to avoid people adding order bys to reduce logical IO.

Please don't add bogus order bys in attempt to reduce LIO's.  That isn't the solution.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Relevant expression has a sort

More to Explore

Performance

Count query and consistent gets

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Relevant expression has a sort

More to Explore

Performance