RE: SQL run time changed after DB upgrade but execution plan did not change
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Apr 2013 20:59:46 +0000
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 26 April 2013 21:24
To: oracle-l@xxxxxxxxxxxxx
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.--
//www.freelists.org/webpage/oracle-l
Other related posts:
