Finding most used objects in the databases

To find the type of objects used by Oracle database/application, we can use the data dictionary view v4db_object_cache as below


SELECT type, COUNT(executions)
FROM v$db_object_cache 
GROUP BY type
ORDER BY 2 DESC


OBJECT TYPE       COUNT(EXECUTION)
-----------------------------------------------
CURSOR                 28532
CURSOR STATS 9900
TABLE                    1360
JAVA CLASS         744
VIEW                      646
TYPE                      486
PACKAGE             311
PACKAGE BODY 144
FUNCTION            9



Now, we notice that the Cursor execution is far greater than Package/function. This means that the application is using more standalone SQL statements thus increasing the network. It is highly recommended to embed the stand alone SQL into procedures/functions or packages.