This seems to be a repeatable case ( in 12.2.0.1 and 19c) , if I tried to do a "select count(*) from all_objects" as "system" user in cdb$root container, this will take forever,
But these cases are OK.
- Run as "system" user , do a select count(*) from all_objects in the PDB container,
- Run as "SYS" user or non "SYSTEM" commoner , do a select count(*) from all_objects in PDB container and in CDB$ROOT
- Run as "system" user , do a select count(*) from dba_objects in the PDB container and in CDB$ROOT
In this particular case, i.e "system" user login ,
Let's say, do this,
step 1, in the PDB , do a select count(*) from all_objects, all good,
Step 2, set container to cdb$root, then do a select count(*) from all_objects,
Then we can see the plan is changed, and running forever,
Any idea ?