Top 10 Hottest Objects in Oracle by Touch Count using tables x$bh obj$ and user$

Purpose

This Oracle SQL query identifies the top 10 "hottest" objects within your database. In this context, "hot" refers to objects that have been accessed (or "touched") the most frequently. Understanding which objects are frequently used can help you:

• Optimize performance: By identifying frequently accessed objects, you can consider strategies like indexing, data partitioning, or materializing views to improve query response times.
• Capacity planning: Knowing your "hot" objects can assist in predicting storage and resource needs as your database grows.
• Troubleshooting: High touch counts on certain objects could indicate areas of contention or bottlenecks within your database.

Sample SQL Command

 1col owner 	format a20 trunc
 2col object_name	format a30
 3col touches 	format 9,999,999
 4select	*
 5from	(
 6	select	count(*)
 7	,	sum(tch) TOUCHES
 8	,	u.name OWNER
 9	,	o.name OBJECT_NAME
10	from 	x$bh x
11	,	obj$ o
12	,	user$ u
13	where	x.obj = o.obj#
14	and	o.owner# = u.user#
15	group 	by u.name, o.name
16  	order by 2 desc
17	)
18where	rownum < 11
19/

Code Breakdown

• x$bh: This is a dynamic performance view that tracks buffer headers. The tch column within it indicates the touch count for a buffer.
• obj$ and user$: These are static data dictionary views containing information about database objects and users, respectively.
• The query joins these views to associate touch counts with the corresponding object and owner names.
• group by: The results are grouped by OWNER and OBJECT_NAME to calculate the total touch count for each object.
• order by 2 desc: The output is sorted in descending order based on the TOUCHES column, showing the most frequently accessed objects first.
• rownum < 11: The query limits the output to the top 10 rows.

Explanations:

• Column Formatting: The col commands at the beginning are used to format the output columns for better readability.
• Inline View: The inner select query acts as an inline view, calculating the touch counts and retrieving relevant information.
• Outer select: The outer query simply selects all columns from the inline view and applies the rownum filter to limit the results.

References:

• Oracle X$BH
• Oracle OBJ$