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 12.1.0.2: 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 12.1.0.2 SPM Evolve Advisor Task:
- Oracle Database 12.1.0.2 SQL Tuning Guide: Managing the SPM Evolve Advisor Task
–Mike
After Migration to 12c(linux) from 12c(windows 2012), i got an errors in alert log and the issue is regarding Automatic space advisor as follows
Errors in file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_j002_26469.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_244"
ORA-13613: The requested operation is not supported for this advisor object.
ORA-06512: at "SYS.PRVT_ADVISOR", line 5018
ORA-06512: at "SYS.DBMS_ADVISOR", line 103
ORA-06512: at "SYS.DBMS_SPACE", line 2486
ORA-06512: at "SYS.DBMS_SPACE", line 2643
Doc ID 2144368.1 states that to drop the oracle_ocm user and I did it but still yet not resolved the issue.
i’m amazed to know about you Mr.Mike Dietrich as a master of DB upgrade and migrate
Since an year, i am following your blog and posts. its simply the best of all.
Please look in to the matter, its a humble request.
Thanks for your feedback and for pointing me to the MOS note.
What I’m a bit worried about:
OCM gets dropped during an upgrade to 12c as it is not necessary anymore. So I’m wondering why OCM has been present in your 12c database.
Furthermore, please check if the OCM user drop succeeded and if there are any OCM jobs hanging around somewhere. Check the scheduler tables.
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS …
Do you get the same ORA-600 as noted in the MOS Note?
ORA-00600: internal error code, arguments: [kkdlGetBaseUser2:authIdType], [0], [68], [EJBCLIENT]
Otherwise you may please need to open an SR.
Cheers
Mike
Thanks alot for your reply.
No, i didn’t get any ORA-600 as noted in the MOS Note but the rest is same .
Is the issue is related to OCM?
I need one more advice related to upgrade and migrate:
Currently production database is 10G(10.2.0.5) on windows 2003.
My action plan as follows:
1.Database Upgrade to 12c(12.1.0.2) on windows 2012
2.Migrate From windows 2012 non-CDB to PDB on Linux 6.7
Everything is going fine except above issue.
And i would like to know that the above action plan will be perfect or not
Then please let Support figure this out. I can’t solve it for you neither have I seen this issue so far. Please open a Sev2 SR (or Sev1 if this is a blocking issue not allowing you to upgrade your production in a given time line).
Thanks!
Mike
Mr.Mike Dietrich,
Fortunately, i got the solution.its a bug as follows
Bug 22546728 : ORA-12012: ERROR ON AUTO EXECUTE OF JOB.
Patch n.o: 19370504
i would like to know that for the bug 22546728 can i apply patch 19370504, will it be appropriate to apply?
Thanks for the update – this is very helpful.
And please – as you have an SR – let the support engineer check if the combination of 22546728 with patch 19370504 works or if you will need either another backport or must request this on top of 22546728.
You can do also the opatch conflict check upfront – but you may end with support anyways.
Cheers
Mike
Hello Mike,
I am sure there is a logic behind allowing SQL Tuning Advisor to run in CDB$ROOT only but cover SQL statements from PDBs. What I am trying to figure out is….how a local user in PDB can view the outcome of SQL Tuning Advisor task runs?
If it helps, my question is about 19c
Hi Narendra,
please see this MOS Note:
Oracle Support Document 2892713.1 ( Automatic SQL Tuning Advisor in CDB and PDB) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2892713.1
It just points to the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-a-cdb-with-sql-plus.html#GUID-DF42C0D3-8968-4810-9327-9D176337D356
Cheers
Mike