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

From: Rajaram Subramanian <rajaram.subramanian_at_yahoo.com>
Date: Thu, 16 Feb 2012 05:47:27 +0000 (GMT)
Message-ID: <1329371247.74760.YahooMailNeo_at_web29902.mail.ird.yahoo.com>

Can you run the same query using gather_plan_statistics hint and post the execution plan along with the actual/estimated rows and also the predicate section from both the databases using dbms_xplan.display_cursor.  Thanks

---

 From: fmhabash <fmhabash_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, 15 February 2012, 20:09 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 - 23:47:27 CST