October 2017

SQL Monitoring Report

One of the most useful tuning tools available is Oracle’s SQL Monitor Report with a rich amount of detail about a single execution of a query. There are a number of ways to get this. Here is a simple and direct way: 1) Add this hint to your query: /*+ MONITOR */ [Note: don’t include […]

Read more →

See the contents of the buffer cache

If you’re looking for what is in the buffer cache, use this: select COUNT(*) ct, o.object_name, o.subobject_name, s.blocks from v$bh bh, dba_objects o, dba_segments s WHERE o.data_object_id = bh.objd AND o.owner like ‘SCHEMA_OWNER’ AND o.owner = s.owner AND o.object_name = s.segment_name AND NVL(o.subobject_name,’x’) = NVL(s.partition_name,’x’) AND s.blocks > 0 AND bh.status != ‘free’ GROUP BY […]

Read more →