Monday Sep 14, 2015

SQL Plan Management - Known Issues in Oracle

Our Support colleagues released the patch recommendation note for SQL Plan Management (SPM) for Oracle Database


SPM Note

In the unlikely event you'd like to upgrade to Oracle Database or (very very unlikely hopefully) please see these matching notes:


Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no extra cost
  • SPM is THE feature to ensure plan stability tackling changes such as (of course) upgrades and migrations
  • SPM has been improved a lot internally in Oracle Database
    • We now store entire plans instead of a large accumulation of hints in the SQL Management Base (SMB) in SYSAUX tablespace
    • The "Evolve" task does happen automatically (SYS_AUTO_SPM_EVOLVE_TASK) as part of the Automatic SQL Tuning Task 
  • You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink)
    • SQL> exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
  • See the Oracle Database 12c documentation about SPM:



Wednesday Aug 05, 2015

Grid Infrastructure Management Repository (GIMR) - Datapatch?

I have blogged about the Grid Infrastructure Management Repository (GIMR) a while back:

And Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:

Since Oracle Database the GIMR database will be created by default - and it is a single tenant database having a CDB$ROOT and one active PDB. 

Recently the question came up if - in the likely event of applying a PSU or BP to the GI Home - you'll have to run datapatch manually to adopt the SQL changes for the PSU/BP into the GIMR database as well?

Simple answer: No.

SQL changes will be automatically applied to the GIMR database by default. This got introduced in Oracle with the PSU1 already and is tracked by ER BUG 14830129 - MGMT DATABASE PATCH ACTIONS NEED TO RUN DURING GI POST PATCH PHASE

You can verify this by looking at your logs (Thanks Santosh!) - you should see something similar as:

<grid_home>/cfgtoollogs/crsconfig/crspatch_xxxx file

2015-07-15 15:36:51: Mgmtdb is running on node: racnode1; local node: racnode1
2015-07-15 15:36:51: Mgmtdb is running on the local node
2015-07-15 15:36:51: Starting to patch Mgmt DB ...
2015-07-15 15:36:51: Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:36:51: Running as user oracle: /opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB
2015-07-15 15:36:51:   Invoking "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB" as user "oracle"
2015-07-15 15:36:51: Executing /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:36:51: Executing cmd: /bin/su oracle -c "/opt/oracle/app/12.1.0/grid/sqlpatch/sqlpatch -db -MGMTDB"
2015-07-15 15:37:50: Command output:
>  SQL Patching tool version on Tue Jul 15 15:36:51 2015
>  Copyright (c) 2015, Oracle.  All rights reserved.

>  Connecting to database...OK
>  Note:  Datapatch will only apply or rollback SQL fixes for PDBs
>         that are in an open state, no patches will be applied to closed PDBs.
>         Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
>         (Doc ID 1585822.1)
>  Determining current state...done
>  Adding patches to installation queue and performing prereq checks...done
>  Installation queue:
>    For the following PDBs: CDB$ROOT PDB$SEED CRS
>      Nothing to roll back
>      The following patches will be applied:
>        20831110 (Database Patch Set Update : (20831110))

>  Installing patches...
>  Patch installation complete.  Total patches installed: 3

>  Validating logfiles...done
>  SQL Patching tool complete on Tue Jul 21 15:37:50 2015


For all the skeptical people (Germans especially) let me add that in Oracle Database the Grid Infrastructure Management Repository (GIMR) is not mandatory - but its existence will be mandatory for a future upgrade to Grid Infrastructure 12.2.


Friday Aug 22, 2014

Automatic Maintenance Jobs in every PDB?
New SPM Evolve Advisor Task in Oracle

A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:

"According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB - but the slides are not clear here. So what is the truth?"

Ok, that's good question. In my understanding all tasks will get executed within each PDB - that's why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and guess what will happen when 25 PDBs start gathering object statistics at the same time ...

The documentation indeed says:

Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged. A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement.

This sounds reasonable. But when we have a look into our PDBs or into the CDB_AUTOTASK_CLIENT view the result is different from what the doc says. In my environment I did create just two fresh empty PDBs (CON_ID 3 and 4):

SQL> select client_name, status, con_id from cdb_autotask_client;

CLIENT_NAME                           STATUS         CON_ID
------------------------------------- ---------- ----------
auto optimizer stats collection       ENABLED             1
sql tuning advisor                    ENABLED             1
auto space advisor                    ENABLED             1
auto optimizer stats collection       ENABLED             4
sql tuning advisor                    ENABLED             4
auto space advisor                    ENABLED             4
auto optimizer stats collection       ENABLED             3
sql tuning advisor                    ENABLED             3
auto space advisor                    ENABLED             3

9 rows selected.

I haven't verified the reason why this is different from the docs but it may have been related to one change in Oracle Database The new SPM Evolve Advisor Task ( SYS_AUTO_SPM_EVOLVE_TASK) for automatic plan evolution for SQL Plan Management. This new task doesn't appear as a stand-alone job (client) in the maintenance window but runs as a sub-entity of the Automatic SQL Tuning Advisor task. And (I'm just guessing) this may be one of the reasons why every PDB will have to have its own Automatic SQL Tuning Advisor task 

Here you'll find more information about how to enable, disable and configure the new Oracle SPM Evolve Advisor Task:


Grid Infrastructure Management Repository (GIMR)
database now mandatory in Oracle GI

During the installation of Oracle Grid Infrastructure you've had the following option to choose YES/NO to install the Grid Infrastructure Management Repository (GIMR) database MGMTDB:

With Oracle Grid Infrastructure this choice has become obsolete and the above screen does not appear anymore. The GIMR database has become mandatory

What gets stored in the GIMR?

See the changes in Oracle Clusterware here:

  • Automatic Installation of Grid Infrastructure Management Repository

    The Grid Infrastructure Management Repository is automatically installed with Oracle Grid Infrastructure 12crelease 1 ( The Grid Infrastructure Management Repository enables such features as Cluster Health Monitor, Oracle Database QoS Management, and Rapid Home Provisioning, and provides a historical metric repository that simplifies viewing of past performance and diagnosis of issues. This capability is fully integrated into Oracle Enterprise Manager Cloud Control for seamless management.

Furthermore what the doc doesn't say explicitly:

  • The -MGMTDB has now become a single-tenant deployment having a CDB with one PDB
    • This will allow the use of a Utility Cluster that can hold the CDB for a collection of GIMR PDBs
  • When you've had already an Oracle GIMR this database will be destroyed and recreated
    • Preserving the CHM/OS data can be acchieved with OCULMON to dump it out into node view
  • The data files associated with it will be created within the same disk group as OCR or VOTING
    •  The OUI will get the disk groups for OCR and Voting and chooses the first one - which usually is the first OCR. This may lead to serious space issues. It is tracked internally as Bug:19661882  In a future release there may be an option offered to put in into a separate disk group.
      Workaround would be to move the affected OCR to another disk group (use ocrconfig command for it) - see MOS Note:1589394.1
  • Some important MOS Notes:
    • MOS Note 1568402.1
      FAQ: 12c Grid Infrastructure Management Repository, states there's no supported procedure to enable Management Database once the GI stack is configured
    • MOS Note: 1921105.1
      Managing the Cluster Health Monitor Repository (incl how to resize)  
    • MOS Note 1589394.1
      How to Move GI Management Repository to Different Shared Storage
      (shows how to delete and recreate the MGMTDB)
    • MOS Note 1631336.1
      Cannot delete Management Database (MGMTDB) in 12.1
    • MOS Note 1945558.1
      _mgmtdb Service Registered with All Local Listeners in a Grid Infrastructure Environment
  • Average growth size per day per node is roughly 650-750 MB. E.g. a 4 node cluster would lead at the default retention of 3 days to an approximate size of  5.9-6.8 GB
  • Change the retention
    $CRS_HOME/bin/oclumon manage -repos changeretentiontime 260000
  • Change the retention
    $CRS_HOME/bin/oclumon manage -repos checkretentiontime 260000


Markus Michalewicz, our Director of Product Management, Oracle Real Application Clusters (RAC), has published a very interesting and helpful insight article about GIMR on July 30, 2015. Read it here:


PS: Kudos to Sebastian Solbach who updated me on the things to add (retention, average growth, OUI choosing the first disk group displayed for the MGMTDB) - cheers!

Wednesday Oct 19, 2011

It's always the Optimizer, isn't it?! - Part 1

Wouldn't you agree?

My colleagues from the Optimizer Development might forgive me but a lot of people would say: "Everything went fine after upgrade - except for those tiny 3 queries!". And I know, you have tested a lot. I'd like to tell you about a few issues we've seen post upgrade with

  1. You have captured your plans in Oracle 10.2 pre-upgrade.
  2. You have moved these plans from your SQL Tuning Set into the SQL Plan Baseline in Oracle 11.2.
  3. But the optimizer doesn't pick them although you have:
    (a) ACCEPTED and
    (b) FIXED them.


It might be worth a try to set this underscore parameter and test again:


What does _optimizer_use_feedback mean?
You'd find some detailed information in our Upgrade Companion (MOS Note:785351.1):

Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to "relearn" something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)

Besides that there were known issue in (Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback (Doc ID 8608703.8) - Bug 9342979 - Suboptimal plan change with cardinatilty feedback (Doc ID 9342979.8)) - but they seem to be fixed with patch set and above.

While doing some research in our support portal (MOS) I accidentally found these interesting notes:

To be continued ...

Please don't take this as a general recommendation to set this underscore and switch off the functionality as this isn't a general recommendation!!! Be always careful with underscore/hidden parameters and always check back with Oracle Support - in most cases underscores slow down the upgrade and using underscores from previous releases has often a bad impact on performance!


Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« October 2015
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
This week on my Rega & Pono
Upgrade Reference Papers