Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Oracle 12c

Posted on 2016-08-19
Medium Priority
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 400 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 400 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 400 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.


Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 400 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 400 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 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