RE: SQL run time changed after DB upgrade but execution plan did not change

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 26 Apr 2013 20:59:46 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90CFCC3_at_exmbx06.thus.corp>

It looks like the execution plan of the scalar subquery has changed to use a tablescan.

Your execution plan looks like the plan for just the scalar subquery run through explain plan, you need to pull the execution plans from memory using dbms_xplan.display_cursor()

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 26 April 2013 21:24
To: oracle-l_at_freelists.org
Subject: SQL run time changed after DB upgrade but execution plan did not change

Below are statistics from 11.1.0.7 when all 1.5 million rows from "T1" table were processed: call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1     67.22      67.20       1280      42104    6500059     1588081
Fetch        0      0.00       0.00          0          0          0           0


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2     67.22      67.20       1280      42104    6500059     1588081


In 11.2.0.3, the statistics look below when processing 100 rows from "T1":
call     count       cpu    elapsed       disk      query    current        rows


------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1     89.16     106.70    1417957    1634828        311         100
Fetch        0      0.00       0.00          0          0          0           0


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2     89.16     106.70    1417957    1634828        311         100

I am not sure if this is due to a bug or something else is causing this much IOs.-- http://www.freelists.org/webpage/oracle-l Received on Fri Apr 26 2013 - 22:59:46 CEST