Oracle 12c

Posted on 2016-08-19
Last Modified: 2016-09-21
Its a broad question. The queries and plsql jobs are running slower on a 12c  database upgraded from 11g. All things being same, hardware, parameters etc, is 12c slower than 11g out of the box? Are there any extra parameters that I need to turn on/off in 12 c Please let me know where I should be looking to make the jobs as fast as in 11g

Question by:gs79
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 23

Assisted Solution

David earned 100 total points
ID: 41763209
All things being the same, no. Therefore, it's likely that something is not the same as you presume. The answer could be as simple as a different block size for the data files, or perhaps the table statistics haven't been optimized since the live data was laid down.

One way I would use to deconstruct the complexity, is to take representative statements , whether they are queries or updates. Capture and compare  the execution time as well as the resources used. Then see if that gives you a quick insight  where there may be differences.
LVL 74

Accepted Solution

sdstuber earned 100 total points
ID: 41763210
is your database a pluggable inside a container?  If so, are you sharing resources that you weren't previously?

You can always try changing the compatibility and optimizer settings back to 11g if you don't like the 12c optimizer.  

Look at the advisor views, anything there that should be changed?
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
ID: 41763519
It's The Stupid Sql's !

And the resource they consume

The plsql jobs time is the sum of the time for the queries inside
So you needn't bother looking at those directly

unless you have nested to the 143th level ... or even deeper

v$sql contains the current slow (and fast) statements being run
I only ever use 2 querries to find the slow ones:
The SessionWait query > to see the active ones
The TopBf query > to see what the highest resource consumers for buffer gets are
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.


Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 100 total points
ID: 41763643
1. Do you have historical data available from 11g instance ?
    If so, compare the SQL execution statistics for SQLs in general, especially for the top SQLs generated as output of "GEERT GRUWEZ's SQL"

2. Compare explain plans

exec dbms_xplan.display_awr('SQL_ID') will give you all historical plans for a SQL provided plan was changed and historical data is still available.

3. Review AWR

4. Check statistics on tables / indexes
    12c has a new feature of extended statistics collection by default, if SQL has directives and is eligible of optimization ( Extended stat has been there since 11gR1 but was more of a manual process ) . Oracle  gathers the collective information for group of columns


Also, it will be visible as a virtual column in dba_tab_col_statistics

5. Check top wait events from ASH data and then drill it down further for example :

select to_char(sample_time,'mmddrr hh24:mi'),event,sum(time_waited)/1000,count(1)
from (g)v$active_session_history where sample_time << between the duration of test or peak load >> group by to_char(sample_time,'mmddrr hh24:mi'),event

Once you have top events , you can modify the same SQL by including SQL_ID , program etc. as per your environment.
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41763690
for 5, just run the swe query in sqlplus while the load is running

you'll visually see which sql's are running
if the same query keeps showing, then that's the one you need to tune

you don't really those expensive items like ASH, AWR  from the tuning pack

Expert Comment

by:Abhimanyu Suri
ID: 41763695
Hi Geert,

Since your SQL is a join of v$session it won't show data for recent past.
Thats why I requested that.

It is a good way to drill down to the absolute second, which leads to correction to_char(sample_time,'mmddrr hh24:mi:ss')

Author Comment

ID: 41769298
Thanks all.

I am trying out the various solutions posted here. Will get back to you soon
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 41769472
First thing I would probably do is tell the Optimizer to go back to your 11g versions.

I've read about some "bad" behaviors with 12c in other systems.


Use the HINT or ALTER method.

If your 11g was
select  /*+ optimizer_features_enable('') */  blah blah blah...

Author Comment

ID: 41807244
Sorry for delay in getting back. I got side tracked from this

@sdstuber I think the database is a pluggable database as per v$database. CDB value is set to NO. But there are no other databases connected so I am assuming all the resources are used by this database?

@slightvw I modifed the session to use the optimizer feature to the previous version. Didnt see any significant improvements there

Also we are collecting the statistics everyday after the batch is complete in both the environments (12c and 11g). So the stats on the table are upto date. Is there anything I should look into. The program I am using to bench mark uses a parallel ppelined function to load 24 million rows into a current year partition of table which has 130 columns. It bulk collect into source and associative arrays are used for lookups. The partition index is made unusable before the start of the process and rebuild after loading. The process takes 16-18 min in 11g where has 28-30 min in 12c..

Please let me know if you think any other place I need to look into


Author Comment

ID: 41810033
I will open a new thread as a continuation of this with last my comment as op. The issue is still there and do not what could be causing this

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question