--追踪共享池没有绑定变量的SQL
SELECT FORCE_MATCHING_SIGNATURE,to_char(FORCE_MATCHING_SIGNATURE ) as sing,
COUNT(1) AS REPLACE_NUM,
ROUND(SUM(SHARABLE_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(PERSISTENT_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(RUNTIME_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(CPU_TIME) / SUM(EXECUTIONS) / 1000, 3) AS CPU_TIME_AS_HAO,
ROUND(SUM(ELAPSED_TIME) / SUM(EXECUTIONS) / 1000, 3) AS ELAPSED_TIME_AS_HAO
FROM V$SQL
WHERE FORCE_MATCHING_SIGNATURE > 0
AND EXECUTIONS > 0
AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(1) > 1
ORDER BY 2 DESC;
SELECT X.*
FROM V$SQL X
WHERE FORCE_MATCHING_SIGNATURE IN ('&FORCE_MATCHING_SIGNATURE') ;
SELECT FORCE_MATCHING_SIGNATURE,to_char(FORCE_MATCHING_SIGNATURE ) as sing,
COUNT(1) AS REPLACE_NUM,
ROUND(SUM(SHARABLE_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(PERSISTENT_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(RUNTIME_MEM) / 1024 / 1024) AS SHARED_MB,
ROUND(SUM(CPU_TIME) / SUM(EXECUTIONS) / 1000, 3) AS CPU_TIME_AS_HAO,
ROUND(SUM(ELAPSED_TIME) / SUM(EXECUTIONS) / 1000, 3) AS ELAPSED_TIME_AS_HAO
FROM V$SQL
WHERE FORCE_MATCHING_SIGNATURE > 0
AND EXECUTIONS > 0
AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(1) > 1
ORDER BY 2 DESC;
SELECT X.*
FROM V$SQL X
WHERE FORCE_MATCHING_SIGNATURE IN ('&FORCE_MATCHING_SIGNATURE') ;