[Profile Register/Log In] (What's This?)

From: "Sorrin, Eric" <Eric.Sorrin@childrenscolorado.org>
Subject: [techies] wild cost estimates
Date: Sat Jan 9 02:04:26 2016

Hi folks, been a long time.

 

We recently upgraded one of our databases from 11.2.0.3 to 12.1.0.2.  The 12.1.0.2 database has the latest combo PSU  a couple of one-off patches, and a multitude of undocumented initialization parameter settings recommended by Oracle and our vendor to fix serious 12C issues.

 

What we are now seeing are queries that had reasonable explain plans and execution times in 11 reporting wild cost estimates in 12.

 

For example, one query had a 8K cost and ran in 11 seconds actual time in 11.2.0.3.  In the 12 environment the estimated cost is 14 Trillion,  the estimated time is 11 days, but the query runs in 1 minute and 25 seconds.

 

Setting optimizer_features_enable to 11.2.0.3 as a workaround alleviates the issue.

 

Has anyone seen similar behavior?

 

Thanks,

 

Eric Sorrin

Database Administrator

Children’s Hospital Colorado


CONFIDENTIALITY NOTICE:  This e-mail, including any attachments, is for the sole use of the intended recipient and may contain confidential and privileged information.  If you are not an intended recipient, or the person responsible for delivering this message to an intended recipient, you are hereby notified that reading, copying, using or distributing this message is prohibited. If you are not an intended recipient, please contact the sender by reply email and destroy all copies of the original message from your computer system.