SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database a while ago:

New SPFILE parameters in Oracle Database

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

  • You must set the SGA_MIN_SIZE value to a value that meets these requirements:
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
    • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

    When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4


Parameter Recommendations for Oracle Database 12c – Part II

Best Practice Hint
See also:

Parameter Recommendations for Oracle Database 12c – Part I

Time for a new round on Parameter Recommendations for Oracle Database The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we’d like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well.


Again, please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend Real Application Testing, especially the SQL Performance Analyzer but also Database Replay to verify the effect of any of those parameters.

Known Parameters – Interesting Behavior

  • parallel_min_servers
    • Setting it to a value below the default will let the database ignore it.
    • In Oracle Database 11g the default was 0
    • Compare vs on the same box:
      • 11g:
        SQL> show parameter parallel_min_servers
        NAME                  TYPE     VALUE
        ——————— ——– ——
        parallel_min_servers  integer  0
      • 12c:
        SQL> show parameter parallel_min_servers
        NAME                  TYPE     VALUE
        ——————— ——– ——
        parallel_min_servers  integer  8
  • recyclebin
    • See the Oracle Documentation – controls whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin. If this parameter is set to ON, then dropped tables go into the recycle bin and can be recovered.
    • ON
    • If the recyclebin is ON (the default) in your environment then empty it at least once per week. Create a default job in all your environments emptying the recycle bin every Sunday morning at 3am for instance:
      SQL> purge DBA_RECYCLEBIN;
    • The recycle bin is on in every database by default since Oracle 10g. The danger is that it may not be emptied but especially on developer databases many objects may be created and dropped again. As a result the dropped objects and its dependents still stay in the database until the space needs to be reclaimed. That means, they exist in the data dictionary as well, for instance in TAB$. Their name is different now starting with “BIN$…” instead of “EMP” – but they will blow up your dictionary. And emptying it not often enough may introduce a performance dip to your system as the cleanup of many objects can be quite resource intense
    • Check your current recycle bins:
      ————- —————————- ———– ——————-
      TEST_RBIN     BIN$2e51YTaSK8TL/mPy+FuA==$0 TABLE       2010-05-27:15:23:45
      TEST_RBIN     BIN$5dF60S3GSEOSSYREaqCg==$0 TABLE       2010-05-27:15:23:43
      TEST_RBIN     BIN$JHCDN9YwQRXjXGOJcCIg==$0 TABLE       2010-05-27:15:23:42


  • deferred_segment_creation
    • See the Oracle Documentation – set to the default (TRUE), then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table
    • TRUE
    • Set it to FALSE unless you plan to create a larger number of tables/indexes knowing that you won’t populate many of them.
    • If my understanding is correct this parameter got introduced with Oracle Database 11.2 in order to save space when applications such as EBS, Siebel or SAP create tons of tables and indexes which never may get used as you don’t work with the matching module of the software
    • The risk can be that certain query check DBA_SEGMENTS and/or DBA_EXTENTS – and if there’s no segment allocated you won’t find an indication about the existence of the object in there – but it actually exists. Furthermore we have seen issues with Data Pump workers getting contention, and some other things.
    • The documentation has become now pretty conservative as well since Oracle and I’ll second that:
      Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.


Parameter Recommendations for Oracle Database 12c – Part I

Best Practice Hint

See also:

Parameter Recommendations for Oracle Database 12c – Part II

A few weeks ago we’ve published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I’ll post an update as soon as something is published.


Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters.

How to read this blog post?

Never ever blindly set any underscore or hidden parameters because “somebody said” or “somebody wrote on a blog” (including this blog!) or “because our country has the best tuning experts worldwide” … Only trust Oracle Support if it’s written into a MOS Note or an official Oracle White Paper or if you work with a particular support or consulting engineer for quite a long time who understands your environment.

Important Parameter Settings

  • memory_target
    • Unexpected failing database upgrades with settings of  memory_target < 1GB where equal settings ofsga_target and pga_aggregate_target didn’t cause issues
    • It prevents the important use of HugePages
    • Avoid memory_target by any chance
    • Better use sga_target and pga_aggregate_target instead
  • pga_aggregate_limit
    • Be aware that it’s not a true limit.
      Monitor your PGA growth – you can’t rely exactly on pga_aggregate_limit

Essential MOS Notes for Oracle Database


What happened to the blog post about “12c parameters”?

Best Practice Hint

Two weeks ago I published a blog post about Parameter Recommendations for Oracle Database And I took it down a day later. Why that?

I’ve got a lot of input from external sources for the “Parameter” blog post. And I’d like to thank everybody who contributed to it, especially Oracle ACE Ludovico Caldara.

Generally there was a bit of a misunderstanding internally about whether we should advertise” underscore parameters to cure some misbehavior of the database. In 99% of all cases I’d agree that underscores are not a good solution – especially when it comes to database upgrades as our slide deck still contains a real world example about what happens when you keep old underscore parameters in your spfile. It can not only slow down the entire upgrade but also makes it very hard for Oracle Support to reproduce issues in case of something going the wrong direction.

But in some situations an underscore seems to be the only remedy in cases where a patch is not available for a particular release – the release you are using at the moment. And even if a patch is available or if the fix is available in a future PSU or BP that does not mean necessarily that one can apply it for several reasons.

We still have a lot of very productive discussions going on internally between many groups. That is very good as it means that we have plenty of smart people around, especially in Oracle’s Database Development 🙂

Furthermore we agreed that the Optimizer PM team will take over the part of my (taken down) blog post targeting wrong query results and other optimizer topics. We are in constant exchange and I’ll link it as soon as something gets published.


New (some undocumented) Parameters in Oracle

Every release offers some surprises – even to myself 😉

Right now Roy and I are in the final steps to refresh our big slide deck to the new layout, but more important, to have Oracle information included as well (were necessary). So I did my usual “compare parameters” query between releases – getting unusual surprises this time.

This is the list of new parameters introduced with the patch set Oracle Database Where applicable I have added the link to the doc.

But as you may recognize not all of them are explained in the doc 😉

  • DBFIPS_140
    • Default: FALSE
    • DBFIPS_140 enables Transparent Data Encryption (TDE) and DBMS_CRYPTO PL/SQL package program units to run in a mode compliant to the Federal Information Processing Standard (subsequently known as “FIPS mode”)
    • Default: c##
    • Specifies a prefix that the names of common users, roles, and profiles in a multitenant container database (CDB) must start with. If COMMON_USER_PREFIX is set to an empty string, Oracle will not enforce any restrictions on the names of common or local users, roles, and profiles.
  • DB_PERFORMANCE_PROFILE <<updated Dec 16>>
    • Undocumented
    • See bug17861171, bug18406144 and bug19817284 – IORM feature on Exadata only
    • Default: FALSE
    • Controls services provided by the RDBMS for Oracle GoldenGate (both capture and apply services). Set this to true to enable RDBMS services used by Oracle GoldenGate
    • Introduced with Oracle and Oracle
    • Undocumented
    • Per feedback by the Multitenant team:
      • Default: TRUE
      • Setting this parameter to FALSE would return results for the seed
        database when querying against the CDB views
    • Default: an empty string
    • Enables you to specify a default In-Memory Column Store (IM column store) clause for new tables and materialized views. If the INMEMORY_CLAUSE_DEFAULT parameter is unset or set to an empty string (the default), only tables and materialized views explicitly specified asINMEMORY will be populated into the IM column store. Setting the value of the INMEMORY_CLAUSE_DEFAULT parameter to NO INMEMORY has the same effect as setting it to the default value.
    • Default: DEFAULT
    • Allows you to specify whether tables and materialized view that are specified as INMEMORY are populated into the In-Memory Column Store (IM column store) or not. The default value is DEFAULT. When this value is in effect, the IM column store is populated only with tables and materialized views specified as INMEMORY. If OFF is specified, then even if the IM column store is configured on this instance, no tables or materialized are populated in memory.
    • DefaultHalf the effective CPU thread count or the PGA_AGGREGATE_TARGET value divided by 512M, whichever is less.
    • Specifies the maximum number of background populate servers to use for In-Memory Column Store (IM column store) population, so that these servers do not overload the rest of the system
    • Default: ENABLE
    • Used to enable or disable in-memory queries for the entire database at the session or system level. This parameter is helpful when you want to test workloads with and without the use of the In-Memory Column Store (IM column store)
    • Default: 0
    • Sets the size of the In-Memory Column Store (IM column store) on a database instance. If a database does not have automatic memory management enabled, this parameter must be set to a nonzero value that reserves the amount of memory to use for the database’s IM column store. The default value is 0, which means that the IM column store is not used. The database must be restarted after setting this parameter to enable the IM column store. The minimum size to which this parameter can be set is 100 MB.
    • Default: 1
    • Limits the maximum number of background populate servers used for In-Memory Column Store (IM column store) repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers. The value for this parameter is a percentage of the INMEMORY_MAX_POPULATE_SERVERS initialization parameter value
    • Default: TRUE
    • Enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to false causes the optimizer to ignore the in-memory property of tables during the optimization of SQL statements. This behavior can also be achieved by setting theOPTIMIZER_FEATURES_ENABLE initialization parameter to values lower than
    • Undocumented
    • Per feedback by the Multitenant team:
      • Not functional in Oracle
    • Undocumented
    • Per feedback by the Multitenant team:
      • Not functional in Oracle
      • May be functional with a future PSU allwoing then OS user
        verfication/validation for PDBs


PS: Forgot to mention this single parameter which has disappeared in Oracle

    • Undocumented in Oracle
    • Disappeared in Oracle but did exist in Oracle


Roy today answered an interesting question on one of our internal mailing lists. And this reminds me to pick up that piece of information as we see this quite often on customer sites, especially after upgrades or migrations. People set OPTIMIZER_FEATURES_ENABLE (OFE) to revert the optimizer’s behaviour to another pre-current release. That’s what a lot of people think this parameter does.

But in fact this is not true. Even though our documentation states it:

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number. For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to

In my experience the following is true:
Setting this parameter reverts the optimizer settings in terms of parameters – but we still use the new optimizer code from that release you are on now. And as far as I know nobody ever tests if switching back OFE will turn back to exactly the behaviour as it was known in the release OFE is now switched to. So it’s simply guessing and assuming. But as code got changed there’s very little chance to get back to the old behaviour.

What people sometimes  experience:
Turning back OFE brings back good performance. This can happen. But if we act really really really precise than the performance should always be better with the optimizer running with the new settings – and not when the wheel is turned back. So in some cases this should be treated as a bug unless new behaviour leads to predictable worse results (such as more buffer gets etc). And I get so angry when Oracle Support people recommend to switch OFE to this or that setting to cure one or the otther misbehaviour. That’s like throwing a big rock to kill a fly.

But the real danger is described in the following note – and I’m pretty sure a lot of people are not aware of this:
Reverting the optimizer parametrization with OFE can turn back to misbehaviour which got fixed already in the current release. MOS Note:1581465.1 describes this pretty well.

And in addition regarding upgrades you may want to read this note here as well:
MOS Note: 1362332.1 Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter after an Upgrade
(Thanks Roy!)

Stay away from tweaking anything with OFE. Use Real Application Testing’s SQL Performance Analyzer (SPA) to find out which plans get changed and use SQL Plan Management (SPM) to nail down misbehaving plans in 11g or 12c.


Upgrade – and an interesting surprise

Patchset is out there for a long, looong time. But still Roy and me – and unfortunately our customers – sometimes experience some nice surprises after upgrade.

Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy’s entry below on the change with JOB_QUEUE_PROCESSES in

Yesterday I’ve received a text message from Roy to have a closer look into a service request the customer did open due to massive performance problems after go-live. The customer has seen MUTEX S CONTENTION in the AWR and ADDM reports slowing down the whole cluster. And the support people from BDE did a good job to drill down and diagnose the issue. But the solution is an interesting surprise.

The April 2011 PSU (Patch 11724916) did contain a fix:
Bug:10187168 Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold

And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it “the old ‘hidden bug fix’ trick…” the code is there but you’ll have to enable it. MOS Note 10187168.8 describes what you’ll have to do.

Actually you’ll have to set this hidden parameter:
and besides that this nice event in your init.ora/spfile:
events= “106001 trace name
context forever, level 1024”

Then bounce the instance … an voila … the patch is now enabled.

Scary, isn’t it …?
As I’ve spent 6 years in Oracle’s RDBMS Support as well I was first checking the patch readme to see whether this is mentioned somewhere – but the only remark pointing to this bug is:

Anyway, what is this parameter for?

It’s an enhancement request to allow parent cursor to be obsoleted if the version count exceeds a defined threshold. This threshold gets set with the new underscore _cursor_obsolete_threshold. But due to the fact that other patches included in this PSU don’t know about this newly introduced underscore event 106001 has to be set instead. The value equals the threshold.

To make things more complicated and enable this fix now (see my comments above) the underscore _cursor_features_enabled has to be set as well. And its value is dependend on the patch level – for Oracle it is 1026 whereas for Oracle it would be 18.

And let me mention that these parameters will have to be set and adjusted just under Oracle’s Support guidance!