Before worrying about anything subtle:
Are the SQL_IDs really the same
Are there database parameters really the same
Are the table definitions really the same
Are the statistcs really the same
Are the character sets on the two databases the same
Are the optimizer parameters on the two databases the same
Are you getting the plans from memory after running the query, or from calling
explain plan
Are the values used for any bind variables used to generate the plans the same
Are the plans the same with different costs -- make sure you check the
predicate section
If you're running the query and the plans are the same is the performance the
same regardless of the change in cost
After you've got answers to those questions show us the query and the execution
plan(s) and we may be able to explain what you're seeing.
Here's a random suggestion based on insufficient information - perhaps you've
got some scalar subqueries in your select list. The cost in 11g won't allow for
the possible number of executions of those subqueries, 12c will - so the final
cost could be much higher because it's (possibly) "number of rows x 50% of
summed cost of all the scalar subqueries)". (Coincidentally, this isn't an
entirely random suggestion because I happen to have written a note about a new
transformation in 12c and mentioned this calculation in passing:
https://jonathanlewis.wordpress.com/2015/12/09/12-scalar-subquery/ )
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Chitale, Hemant K [Hemant-K.Chitale@xxxxxx]
Sent: 10 December 2015 06:31
To: Yogesh.Tiwari@xxxxxxxxxxxxxx
Cc: ORACLE-L
Subject: RE: Two 12.1.0.2 database show different cost calculations mechanism
for same query
“Explain Plan Lies”.
“Explain Plan Will Lie More Frequently In 12c”.
12c has more Adaptives, more Evolutions.
Look at actual runtime statistics for the SQL, for multiple executions of the
SQL to see if it is adapting / evolving !
NUM_ROWS may be the same. If you did Export-Import, CLUSTERING_FACTOR may be
different.
System Statistics may be different.
Cost in Gs and Ts would normally mean something drastic. In the old days it
would be a missing join. I presume you are sure that the submitted SQLs are
exactly the same in SIT and PROD. Did you compare the 10053 Traces in SIT and
PROD and identify the differences ?
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tiwari, Yogesh
Sent: Thursday, December 10, 2015 1:33 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Cc: Tiwari, Yogesh
Subject: Two 12.1.0.2 database show different cost calculations mechanism for
same query
Hi,
We recently upgraded to 12.1.0.2 from 11.2.0.3 on linux x86-64. Apart from all
kinds of perf issues that we are fixing/struggling with, I found another
strange behaviour.
Same query that runs in SIT env, has cost in few Gs, while same query in UAT
has cost of few thousands. I went through 10053 trace, and found query in SIT
is using additional CPU costing, along with IO costing, while same query in UAT
is only using IO costing (akin to 11g) mechanism.
All tables involved are same, in both DBs, and have almost (99%) same num_rows.
Are we hitting some bug? Is it expected behaviour?
In same UAT for other queries(ones we investigated as part of perf issues), we
see they too have huge cost compared to 11g cost. Huge here means, 11g cost was
in few thousands, and 12c cost is in Gs and Ts :(
---Same patches are applied in SIT and UAT--- This is a DWH system
$ORACLE_HOME/OPatch/opatch lspatches
18747342;
18961555;
21463894;
19141838;
20877664;
18966843;
21611406;OCW Interim patch for 21611406
19509982;
20373598;
21068507;Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)
20831110;Database Patch Set Update : 12.1.0.2.4 (20831110)
---
Thanks,
Yogi | Technical Specialist – Databases | Fidelity International
FIL India Business Services Private Limited
5th Floor, Tower – D, Unitech Cyber Park,
Sector 39, Gurgaon 122 002, Haryana, India.
T: +91 124 412 3656 | I: 8 779 3656 | E:
yogesh.tiwari@xxxxxxx<mailto:yogesh.tiwari@xxxxxxx>
Disclaimer: The information transmitted is intended for the person or entity to
which it is addressed and may contain confidential, privileged or copyrighted
material or attorney work product. If you receive this in error, please contact
the sender and delete the material from any computer. Any comments or
statements made are not necessarily those of FIL India Business Services
Private Limited or any other Fidelity entity. All e-mails may be monitored or
recorded.
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
