Queries on dictionary tables are slow in Oracle Multitenant?

I love to learn something new every day. And on Friday afternoon last week I learned something new again. It started (as often) with a very simple question from a colleague working from the Oracle Malaga office in Spain. The customer has an issues with some queries being slower than before once they moved from on-prem to the cloud into a PDB. But are queries on dictionary tables are slow in Oracle Multitenant generally?

Photo by Pascal van de Vendel on Unsplash

What was the case?

The customer ran some queries against dictionary views in the PDB in Oracle Database 19c. But the timing in their cloud environment was much worse by factors to the timings they could measure in their own on-premise environment. Then the customer altered the query from accessing ALL_-views originally now to DBA_-views instead. Interestingly enough, the performance was good again. So, it had obviously nothing to do with on-prem vs cloud.

This is the original query the customer ran:

select * from
 (select null table_qualifier, o1.owner table_owner, o1.object_name table_name, decode(o1.owner,'SYS', decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM', decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type) table_type, null remarks
 from all_objects o1
 where o1.object_type in
  ('TABLE', 'VIEW') union select null table_qualifier, s.owner table_owner, s.synonym_name table_name, 'SYNONYM' table_type, null remarks
  from all_objects o3, all_synonyms s
  where o3.object_type in ('TABLE','VIEW') and s.table_owner= o3.owner and s.table_name = o3.object_name
   union select null table_qualifier, s1.owner table_owner, s1.synonym_name table_name, 'SYNONYM' table_type, null remarks
    from all_synonyms s1
    where s1.db_link is not null )
  tables
  WHERE 1=1 AND TABLE_NAME='MYTAB1' AND table_owner='HUGO'
  ORDER BY 4,2,3;

The query took:

• On-prem:
  Elapsed: 00:00:00.24
• Cloud:
  Elapsed: 00:00:02.56

Then, the customer modified it to access the DBA_-views instead of the ALL_-views.

select * from
 (select null table_qualifier, o1.owner table_owner, o1.object_name table_name, decode(o1.owner,'SYS', decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM', decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type) table_type, null remarks
 from dba_objects o1
 where o1.object_type in ('TABLE', 'VIEW')
 union
  select null table_qualifier, s.owner table_owner, s.synonym_name table_name, 'SYNONYM' table_type, null remarks
  from dba_objects o3, all_synonyms s
  where o3.object_type in ('TABLE','VIEW') and s.table_owner= o3.owner and s.table_name = o3.object_name
   union
    select null table_qualifier, s1.owner table_owner, s1.synonym_name table_name, 'SYNONYM' table_type, null remarks
    from dba_synonyms s1
    where s1.db_link is not null ) tables
   WHERE 1=1 AND TABLE_NAME='MYTAB1' AND table_owner='HUGO'
   ORDER BY 4,2,3;

And the result was interesting then. Now, the modified query was quite on-par with the expected result.

• On-prem:
  Elapsed: 00:00:00.09
• Cloud:
  Elapsed: 00:00:00.34

At first, this is a smart customer. I like their approach a lot narrowing down things.

And of course, I had a quick recommendation, too.

My first reflex

I remembered some cases where the obvious solution were statistics or an underscore. For instance, I blogged about an old time zone problem where you had to use alter session set “_with_subquery”=materialize; to workaround the issue. Or an issue with ALL_SYNONYMS in Oracle 12c being quite slow. Proper stats collection was the solution.

So, I gave my usual recommendation to gather stats on the dictionary properly:

begin
  dbms_stats.gather_schema_stats('SYS');
  dbms_stats.gather_schema_stats('SYSTEM');
  dbms_stats.gather_fixed_objects_stats;
end;
/

And it did solve … nothing.

In fact, the customer had gathered stats already – not directly on SYS and SYSTEM but they did think about it, too. I just had no effect. As I wrote before, smart customer. It would have been too easy this way.

The solution

Well. as said before, I love to learn new things. Later on Friday evening, Paloma and Jose, my colleagues from Oracle Spain in Malaga, contacted me with the solution. And Alex Zaballa from my own team confirmed right away that he has seen this issue multiple times in his previous life as a consultant. I have no idea why I haven’t seen it before – but now I know.

The solution was changing the value of the parameter CONTAINER_DATA. The documentation tells you:

Use CONTAINER_DATA to control whether queries on extended data-linked objects from within a PDB return rows from both the root and the PDB, or rows from only the PDB.
This parameter controls the behavior of queries on the following types of objects:

• Extended data-linked Oracle-supplied data dictionary objectsThis type of object stores data pertaining to the CDB root, as well as data pertaining to individual PDBs.When this type of object is queried from the CDB root, only data pertaining to the CDB root is returned.When this type of object is queried from a PDB, the value of the CONTAINER_DATA initialization parameter controls the query result as follows:
  • ALL: The query returns data pertaining to both the CDB root and the PDB
  • CURRENT: The query returns only data pertaining to the PDB
  • CURRENT_DICTIONARY: The query returns only data pertaining to the PDB

If you wonder why CURRENT and CURRENT_DICTIONARY have the same effect, there are differences for application common objects. See the documentation for further details, please.

More information, and no recommendation

Now I did check My Oracle Support and the BugDB of course. And I found some hits like this one:

• MOS Note: 2915211.1 – Query to ALL_SYNONYMS takes very long time after 19c upgrade)
  • Recommendation: Set CONTAINER_DATA=CURRENT_DICTIONARY in both, CDB and PDB, to improve performance when you query dictionary objects. It links to bugs  32477224 and 26038007 as an example.

Still, don’t change the parameter now blindly but instead try to understand the implications, please:

• MOS Note: 2989081.1 – Since Container_data Is Not Set, The Common Users Can Only Query The Data of CDB$ROOT From the CDB_XXX View Which Is Different From the Query Result Of SYS User
• Tim Hall (oracle-base.com) on CONTAINER_DATA

Paloma showed me the execution time of the original query after the customer had changed CONTAINER_DATA=CURRENT:

• On-prem:
  Elapsed: 00:00:00.24
• Cloud:
  Elapsed: 00:00:00.04

Now it’s fast again – and faster than before in Oracle Cloud.

Let me emphasize that this blog post isn’t a recommendation to set this parameter generally. I use the blog as my memory dumpster which allows me to find things quicker in case they happen again. So, this may be useful and worth trying it out in case you hit a similar problem.

Further Links and Information

• Alex Zaballa: A Year of Transformation: Finding Balance, Health and Happiness at Oracle
• Oracle 19c Documenation: CONTAINER_DATA
• MOS Note: 2915211.1 – Query to ALL_SYNONYMS takes very long time after 19c upgrade) 
• MOS Note: 2989081.1 – Since Container_data Is Not Set, The Common Users Can Only Query The Data of CDB$ROOT From the CDB_XXX View Which Is Different From the Query Result Of SYS User

–Mike