plscope_tab_usage is a bit slow

[rvo-cs]

"Slow" is highly subjective, and it depends vastly on the underlying hardware, OS load, etc. But the point is, it's consistently slow, no matter how narrow the scope set by plscope_context.set_attr.

Typical figures from a sample execution on my test PDB are as follows:

Buffers	Reads	Writes
1723K	18262	18742

And even on fast hardware these numbers would be on the slow side.

Reason: the main contributors to the above figures are as follows:

1. The dep_graph CTE
   And:
2. Base views used in the PLSCOPE_IDENTIFIERS view; DBA_IDENTIFIERS, DBA_STATEMENTS, and DBA_SOURCE

Their respective contributions are as follows:

Contributor	Buffers	Reads	Writes
dep_graph CTE	1619K	11367	11367
Base views used in PLSCOPE_IDENTIFIERS	94060	6355	6355

So we can see that the dep_graph CTE causes more than 90% of the logical reads, and roughly 2/3 of the physical I/Os.

There's not much that can be done about the base views, so I'll leave that aside.

On the other hand, the primary reason for the high cost of the dep_graph CTE (regardless of scope, as said above) is that the hierarchy of dependencies is built "from the bottom", beginning with every table from DBA_TABLES, and every dependency in DBA_DEPENDENCIES for any object of type view, materialized view, or synonym; and then going up the dependency chains within that subset of all database dependencies. But that subset is independent from the scope set by plscope_context.set_attr, and it is rather large: on my test PDB, dep_graph_base returns 258 K rows, which reduce to 93 K distinct rows out of the dep_graph CTE. Unless the scope of the analysis is very broad, this could mean a lot of unnecessary work.

Solution: refactor the query in order to build the dependency chains "from the top", beginning with dependent objects referenced in PLSCOPE_IDENTIFIERS, and going down the dependency hierarchy from dependent object to referenced objects. This way, the dependency chains would be built only for objects within the scope set by plscope_context.set_attr.

[PhilippSalvisberg]

I agree that accessing plscope_tab_usage is slow.

What you say make sense. I'll have to test if the top-down approach produces the same result. If it does then it is for sure a good idea to change all related queries accordingly.