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

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 15 Feb 2012 14:44:00 -0800 (PST)
Message-ID: <1329345840.56199.YahooMailNeo_at_web160903.mail.bf1.yahoo.com>

You have provided no version information or configuration settings for these two databases so it's difficult to say why you're seeing such behaviour.  You do mention one database has a smaller SGA which would provide a smaller buffer cache and thus a lower likelihood that the data will remain cached between query runs.
 

Please provide Oracle version, sga sizes, spfile parameter settings for both databases as well as the number of rows for this table in both databases. David Fitzjarrell

---

From: fmhabash <fmhabash_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, February 15, 2012 1:09 PM Subject: 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

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

Received on Wed Feb 15 2012 - 16:44:00 CST