How to find the number of times a query has been executed

The following query was given to me by an experienced colleague. It shows the number of times different variations of a particular query has been executed. The information is collected from V$SQL which samples the contents in the Library Cache, a part of the Shared Pool.

SELECT SQL_ID, COUNT(DISTINCT SQL_ID) NUM_SQL_IDS, COUNT(CHILD_NUMBER) ANT_CHILD, SUM(EXECUTIONS) 
FROM V$SQL
WHERE SQL_TEXT LIKE ' select count(*) TOTALS from   scott.emp%'
GROUP BY ROLLUP(SQL_ID)
;

Here is parts of the output:

SQL_ID	NUM_SQL_IDS	ANT_CHILD	SUM(EXECUTIONS)
01b71y5wmr2bx	1	1	0
081ha7tszas19	1	3	2
0bfn4yj267xy0	1	2	34
0dk42bqfz6fbr	1	3	6
g1vq03475hqgc	1	3	10
g3hky4g8vu108	1	3	9
--------- Abbreviated ---------
	274	549	1940

From the output we can see that there are a number of variations of the query being executed. The numbers are being rolled up nicely at the end, letting us know that there are 274 unique SQL_IDs, 549 different child cursors with a total of 1940 executions.

This information can certainly be useful during an effort to tune the library cache.