获取sql_id的方法

语句的SQL_ID可以在AWR或ASH报告中找到，或者使用V$SQL视图从数据库数据字典中找到。
1.如果SQL可以用一个特定的可识别的字符串或某种独特的注释来识别，如：/* TARGET SQL */，那么这将使其更容易被找到。

SELECT /* TARGET SQL */ * FROM dual;
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrtn5gkpjs       272002086 SELECT /* TARGET SQL */ * FROM dual

--替换变量的方式查询
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

2.如果在v$sql中找不到这个SQL，你可以在AWR历史视图中找到它: DBA_HIST_SQLTEXT 和 DBA_HIST_SQLSTAT

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id;

3. 从sqlarea和sql_his联合查询，快速获取

SET LINESIZE 250 PAGESIZE 600
COL sql_text FORMAT a150 TRUNC
COL sql_id FORMAT a14
SELECT *
FROM (SELECT 'sqlarea' AS sql_loc,
             sql_id,
             TO_CHAR (SUBSTR (sql_text, 1, 200)) AS sql_text
      FROM gv$sqlarea
      WHERE     UPPER (sql_text) LIKE UPPER ('%查询的sql%')
            AND sql_text NOT LIKE '%SQL_TEXT%'
      UNION
      SELECT 'sql_his', sql_id, TO_CHAR (SUBSTR (sql_text, 1, 200))
      FROM dba_hist_sqltext
      WHERE UPPER (sql_text) LIKE UPPER ('%查询的sql%'))
WHERE ROWNUM <= 100;