The script will list top 10 objects that used Buffer Cache Block per Object SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*); The output should be something like this OBJECT_NAME NUMBER_OF_BLOCKS -------------------------------------- ---------------- MOVNS 18906 ENVIA_EMLS 7430 QURE_PK 6193 VWDEDOR_ALL 3528 MOV__TX_MEN_FK4 3472 CANDITOS 3369 QUESTSPAS 3161 ... ...
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*);
OBJECT_NAME NUMBER_OF_BLOCKS -------------------------------------- ---------------- MOVNS 18906 ENVIA_EMLS 7430 QURE_PK 6193 VWDEDOR_ALL 3528 MOV__TX_MEN_FK4 3472 CANDITOS 3369 QUESTSPAS 3161 ... ...