The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, 10.2.0.4 and 22.214.171.124 on Win32).
Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.
All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.
There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):
1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.
2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.
Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?
Basicfile LOBS 3
1 day ago