Moneycontrol Brokerage Recos

Tuesday, September 20, 2011

How do you see how many memory segments are acquired by Oracle Instances?



Check on x$bh it show you what objects are in the buffer.

 Try this query:

 SELECT /*+ ordered use_hash(o) use_hash(bh) */ bp.NAME BUFFER_POOL u.NAME owner so.object_type segment_type o.NAME segment_name COUNT (*) cached_blocks FROM v$buffer_pool bp SYS.seg$ s SYS.sys_objects so SYS.obj$ o SYS.user$ u x$bh bh WHERE bh.obj o.dataobj# AND o.obj# so.object_id AND o.type# so.object_type_id AND o.owner# u.user# AND so.segment_type_id s.type# AND so.ts_number s.ts# AND so.header_file s.file# AND so.header_block s.block# AND DECODE (s.cachehint 0 3 s.cachehint) bp.ID GROUP BY u.NAME so.object_type o.NAME bp.NAME ORDER BY bp.NAME cached_blocks DESC;

No comments:

Post a Comment