Solved

Oracle 12c

Posted on 2016-08-19
10
99 Views
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

Thanks
0
Comment
Question by:gs79
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 23

Assisted Solution

by:David
David earned 100 total points
Comment Utility
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.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 100 total points
Comment Utility
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?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
Comment Utility
ITSS !
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
topbf.sql
swe.sql
0
 
LVL 4

Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 100 total points
Comment Utility
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

dba_stat_extensions

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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:Abhimanyu Suri
Comment Utility
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')
0
 

Author Comment

by:gs79
Comment Utility
Thanks all.

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
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.

Take a look at OPTIMIZER_FEATURES_ENABLE:
http://docs.oracle.com/database/121/REFRN/GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C.htm#REFRN10141

Use the HINT or ALTER method.

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

Author Comment

by:gs79
Comment Utility
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

Thanks
0
 

Author Comment

by:gs79
Comment Utility
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now