Thanks for the question, Himanshu.
Asked: February 13, 2006 - 11:53 am UTC
Last updated: February 13, 2006 - 4:24 pm UTC
Version: 9.1.7
Viewed 1000+ times
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