当前位置：首页 > news >正文

Oracle下查询数据库SQL ID

news 2025/10/21 16:36:08

以下是常用查询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_readsFROM dba_hist_sqlstatWHERE 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;

总结：