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

From: fmhabash <fmhabash_at_gmail.com>
Date: Wed, 15 Feb 2012 15:09:51 -0500
Message-ID: <4F3C110F.9050408_at_gmail.com>

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

Received on Wed Feb 15 2012 - 14:09:51 CST