Re: Two 12.1.0.2 database show different cost calculations mechanism for same query

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 10 Dec 2015 01:08:43 -0500
Message-ID: <566916EB.7040600_at_gmail.com>



Hi Yogesh,
Are you using Oracle EE or SE? In Oracle EE you have a little something called DBMS_SPM and baselines. People usually capture baselines and import them into the new version of the database, then let some of those baselines evolve. The whole thing should be approached as a project, with proper testing and planning. If you have Oracle SE, you can still use outlines, which are well proven tool for upgrades. Just upgrading the production database will probably not cut it. Also, make sure that all your systems have the same system stats, because without the system stats, the plans will be different. Database upgrade is a project on which companies spend around 6 months on average. And, as everybody knows, there are 6 phases of any big project:
  1. Enthusiasm
  2. Disillusionment
  3. Panic and hysteria
  4. Hunt for the guilty
  5. Punishing the innocent
  6. Rewarding the uninvolved.

Agile methodology only makes those phases worse. Upgrade to 12c projects frequently follow the above steps quite nicely. I usually try to help with some witticisms form Wally, which are usually under-appreciated. I don't really understand why. Here is a good template for upgrade to 12c project:

http://dilbert.com/strip/2010-07-01

On 12/10/2015 12:33 AM, Tiwari, Yogesh wrote:
>
> 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 L
>
> ---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_at_fil.com
> <mailto:yogesh.tiwari_at_fil.com>
>
> 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.
>

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 10 2015 - 07:08:43 CET

Original text of this message