"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:
- The dep_graph CTE
And:
- 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.
"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:
And even on fast hardware these numbers would be on the slow side.
Reason: the main contributors to the above figures are as follows:
And:
Their respective contributions are as follows:
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.