Oracle下查询数据库SQL ID

以下是常用查询SQLID的方法：

1. 通过 V$SQL 视图查询（常用）

-- 根据SQL文本模糊查询SQLID
SELECT sql_id, sql_text, executions, elapsed_time/1000000/executions as avg_elapsed_sec
FROM v$sql
WHERE sql_text LIKE '%你的SQL关键词%'
AND executions > 0
ORDER BY elapsed_time DESC;

-- 查询特定用户的SQL
SELECT sql_id, sql_text, executions, first_load_time
FROM v$sql
WHERE parsing_schema_name = '用户名'
AND sql_text NOT LIKE '%BEGIN%'
ORDER BY first_load_time DESC;

2. 通过 V$SESSION 查询当前会话的SQLID

-- 查看当前所有会话正在执行的SQL
SELECT s.sid, s.serial#, s.username, s.sql_id, s.event, s.seconds_in_wait,
       sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
AND s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

-- 查看特定会话的SQLID
SELECT sid, serial#, sql_id, sql_child_number, event
FROM v$session
WHERE sid = 123;  -- 替换为实际的SID

3. 通过 AWR 报告查询历史SQLID

-- 查询TOP SQL（需要安装AWR）
SELECT sql_id, 
       executions,
       elapsed_time/1000000 as elapsed_sec,
       cpu_time/1000000 as cpu_sec,
       buffer_gets,
       disk_reads
FROM (
    SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
    FROM dba_hist_sqlstat
    WHERE snap_id = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
    ORDER BY elapsed_time DESC
)
WHERE rownum <= 20;

4. 通过 ASH 查询正在执行的SQLID

-- 查询当前ASH中的慢SQL
SELECT sql_id, 
       COUNT(*) as sample_count,
       MAX(sample_time) as last_seen
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24  -- 最近1小时
AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY sample_count DESC;

5. 通过 V$SQLSTATS 查询统计信息

-- 查询性能最差的SQL
SELECT sql_id, 
       sql_text,
       executions,
       elapsed_time/1000000/executions as avg_elapsed,
       cpu_time/1000000/executions as avg_cpu
FROM v$sqlstats
WHERE executions > 100
ORDER BY elapsed_time DESC;

6. 查询执行计划缓存中的SQLID

-- 通过SQL文本精确查询
SELECT sql_id, sql_text, executions, last_active_time
FROM v$sql
WHERE sql_text = '你的完整SQL语句'
AND sql_text IS NOT NULL;

7. 通过 DBA_HIST_SQLTEXT 查询历史SQL

-- 查询历史SQL文本对应的SQLID
SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE sql_text LIKE '%你的SQL关键词%'
AND rownum <= 10;

8. 快速定位慢SQL的完整脚本

-- 查找当前最慢的SQL
SELECT s.sql_id,
       s.sql_text,
       s.executions,
       ROUND(s.elapsed_time/1000000, 2) as total_elapsed_sec,
       ROUND(s.elapsed_time/1000000/NULLIF(s.executions, 0), 2) as avg_elapsed_sec,
       s.cpu_time/1000000/NULLIF(s.executions, 0) as avg_cpu_sec,
       s.buffer_gets/NULLIF(s.executions, 0) as avg_buffer_gets,
       s.last_active_time
FROM v$sql s
WHERE s.executions > 0
AND s.last_active_time > SYSDATE - 1/24
ORDER BY s.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

9. 查询特定会话的SQLID和详细信息

-- 获取完整诊断信息
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.machine,
    s.sql_id,
    s.sql_child_number,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    sq.sql_text,
    sq.executions,
    sq.elapsed_time/1000000 as elapsed_sec
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
AND s.sql_id IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

10. 查询SQLID后查看执行计划

-- 方法1：通过DBMS_XPLAN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('你的SQLID', 0, 'ALLSTATS LAST'));

-- 方法2：通过V$SQL_PLAN
SELECT * FROM v$sql_plan WHERE sql_id = '你的SQLID' ORDER BY id;

-- 方法3：查看执行计划的历史
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('你的SQLID'));

实用查询示例

-- 场景1：查找执行超过1秒的SQL
SELECT sql_id, 
       ROUND(elapsed_time/1000000, 2) as elapsed_sec,
       executions,
       sql_text
FROM v$sql
WHERE elapsed_time/1000000 > 1
AND executions > 0
ORDER BY elapsed_time DESC;

-- 场景2：查找绑定变量窥探问题的SQL
SELECT sql_id, executions, sql_text
FROM v$sql
WHERE executions > 1000
AND sql_text LIKE '%WHERE%'
ORDER BY executions DESC;

-- 场景3：查找消耗Buffer最多的SQL
SELECT sql_id, 
       sql_text,
       buffer_gets,
       ROUND(buffer_gets/NULLIF(executions, 0), 0) as avg_gets
FROM v$sql
WHERE buffer_gets > 100000
ORDER BY buffer_gets DESC;

获取SQLID后的下一步

-- 1. 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQLID', child_number));

-- 2. 查看绑定变量
SELECT name, value_string, datatype_string
FROM v$sql_bind_capture
WHERE sql_id = 'SQLID';

-- 3. 固定执行计划
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'SQLID');

-- 4. 查看执行历史
SELECT snap_id, begin_interval_time, 
       elapsed_time_total, executions_total
FROM dba_hist_sqlstat
WHERE sql_id = 'SQLID'
ORDER BY snap_id DESC;

总结：

• 快速定位：用 v$session 查看正在执行的SQL
• 性能分析：用 v$sql 按耗时/执行次数排序
• 历史分析：用 dba_hist_sqlstat 看AWR数据
• 实时监控：用 v$active_session_history 看当前活动