Identical table definitions, identical queries, identical xpalns: significant elapsed times in 2 different databases

I'm testing same exact sql in 2 different databases (identical object 
metadata (cloned), but different data volume). This query produces same 
exact xplan (identical plan hash) in both databases.

1) What could cause same query to do more LIO/PIO (same xplan) on the 
smaller table and smaller sga as compared to bigger table? My suspicion 
is ...
- fragmented object (online shrink).
- Different bind values passed even though all is using same xplan.

2) Why is the query on same table in DB2 consistently returns same count 
of LIO/PIO? Why data is not cached in subsequent runs so that PIO almost 
disappears. This is what I typically see.

DB 1
----------------------------------------
- sga: 4g
- table rows: 45M
- sql ET: 7secs
- sql stats:
     182  recursive calls
       0  db block gets
      72  consistent gets
       4  physical reads
       0  redo size
     525  bytes sent via SQL*Net to client
     524  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       2  sorts (memory)
       0  sorts (disk)
       1  rows processed


DB 2
----------------------------------------
- sga: 15g
- table rows: 12M
- sql ET: 9min
- sql stats:
           84  recursive calls
       0  db block gets
     1203774  consistent gets
      435808  physical reads
     412  redo size
     528  bytes sent via SQL*Net to client
     524  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

xplan
----------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost 
(%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |     1 |    27 |    
40    (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |              |     1 |    27 |     
    |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID      | XXXXXXXXXXXX   |     1 |    
27 |    40    (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS      |              |       
|       |        |          |
|   4 |     BITMAP AND              |              |       |       |     
    |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|              |       |       
|        |          |
|*  6 |       INDEX RANGE SCAN          | XXXXXXXXXXXX |   654 |       
|     5    (0)| 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|              |       |       
|        |          |
|*  8 |       INDEX RANGE SCAN          | YYYYYYYYYYYYY    |   654 
|       |    35    (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Other related posts: