Oracle 固定SQL執行計劃

小樱桃爱吃肉

239

收藏 2025-11-25

在日常数据库运维过程中，DBA与开发团队常常会面临由于错误使用 Hint 导致执行计划异常，或在周末维护后周一出现 SQL 执行计划变更的情况，进而影响系统性能。此时需要对执行计划进行分析与调整。

当环境中未部署企业管理器（EM）时，可通过创建监控视图 EM_MONITOR 来辅助识别高负载的 SQL 语句及其对应的 SQL_ID。具体建表语句如下：

CREATE OR REPLACE FORCE VIEW JOBUSER.EM_MONITOR
(
RESOURCE_USAGE,
SQL_ID,
SQL_TYPE
)
AS
SELECT RESOURCE_USAGE, SQL_ID, SQL_TYPE
FROM (WITH SQL_COUNT
AS (SELECT *
FROM gv$active_session_history
WHERE     sample_time > SYSDATE - 320 / 86400
AND sample_time <= SYSDATE
AND sql_id IS NOT NULL)
SELECT SQL_iD,
COUNT (SQL_ID),
(SELECT COUNT (*) FROM SQL_COUNT),
ROUND (COUNT (SQL_ID) / (SELECT COUNT (*) FROM SQL_COUNT),
4)
* 100
RESOURCE_USAGE,
DECODE (
sql_opcode,
3, 'SELECT',
6, 'UPDATE',
7, 'DELETE',
2, 'INSERT',
47, 'PL/SQL EXECUTE',
'For more information please reference doc about V$session')
SQL_TYPE
FROM SQL_COUNT
GROUP BY SQL_COUNT.SQL_iD, sql_opcode
ORDER BY COUNT (SQL_ID) DESC);

SELECT 'ALTER SYSTEM KILL SESSION  '''||SID||','||SERIAL#||'''immediate;' ,program FROM V$SESSION WHERE SQL_ID='xxx';

~~~檢查SQL等待事件是否閒置~~~
select event,machine,sql_id,logon_time,status from v$session where upper(wait_class) not in ('IDLE') -


~~~檢查這個表是否正在被調用~~~
 select * from v$session where SID IN
   (select /*+ rule() */SID from  v$access where OBJECT='TABLE_NAME' and OWNER='OWNER_NAME')
   AND STATUS='ACTIVE';

~~~刪掉原有的執行計劃~~~
call DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_9z8mr8jbt0k55_nthwFfQsCw'); 


~~~查看shared_cursor~~~
select * from V$SQL_SHARED_CURSOR where SQL_ID='XXX';


~~~查詢這張表被誰鎖定~~~

    SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    s.blocking_session
FROM 
    gv$locked_object lo
JOIN 
    dba_objects do ON lo.object_id = do.object_id
JOIN 
    gv$session s ON lo.session_id = s.sid
WHERE 
    do.object_name = 'TABLE_NAME'
    AND do.owner = 'OWNER_NAME';

第一步：统计 SQL 的执行频率与执行次数

通过查询 V$SQL 视图获取指定 SQL_ID 的详细执行信息，包括执行次数、平均耗时、最近执行时间等关键指标，便于判断其性能趋势。

SELECT A.PLAN_HASH_VALUE, 
       A.PROGRAM_ID,
       A.PROGRAM_LINE#,
       A.SQL_PROFILE,
       child_number,
       A.LAST_ACTIVE_TIME,
       ELAPSED_TIME,
       ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000000 AS AVG_ELAPSED_SEC,
       SYSDATE,
       A.EXECUTIONS,
       A.ELAPSED_TIME,
       SQL_ID,
       SQL_TEXT,
       A.MODULE,
       A.SQL_FULLTEXT 
FROM V$SQL A 
WHERE SQL_ID='XXXX' 
ORDER BY LAST_ACTIVE_TIME DESC;

查看当前活跃会话的运行状态

可用于实时监控正在执行且持续时间较长的用户会话，帮助快速发现潜在阻塞或长时间运行的 SQL。

SELECT sid,
       serial#, 
       sql_id, 
       PLSQL_ENTRY_OBJECT_ID,
       username,
       program, 
       MACHINE,
       LAST_CALL_ET,
       WAIT_CLASS#  
FROM v$session 
WHERE status <> 'INACTIVE' 
  AND WAIT_CLASS# <> 6  
  AND TYPE = 'USER'  
  AND last_call_et > 100 
ORDER BY LAST_CALL_ET DESC;

案例：开发反馈某存储过程响应缓慢，如何定位相关 SQL 及其 SQL_ID？

方法一：基于对象 ID 进行追踪
首先根据存储过程名称查询其在数据库中的 OBJECT_ID，再结合会话信息查找正在执行该对象的 SQL 语句。

SELECT * FROM dba_objects WHERE object_name = '存储过程名字';

获取到 OBJECT_ID 后（如示例中为 270993），进一步查询当前调用该存储过程的会话详情：

SELECT SID,
       SERIAL#,
       SQL_ID,
       EVENT,
       LAST_CALL_ET,
       MACHINE  
FROM v$session 
WHERE PLSQL_ENTRY_OBJECT_ID = 270993;

方法二：通过应用服务器主机名定位会话
若开发提供了调用来源的应用服务器机器名，可直接筛选出相关会话，并结合 EM 或其他性能工具查看其 TOP SQL。

SELECT * 
FROM V$SESSION 
WHERE MACHINE IN ('ZK-KVM05-IMES47N','ZK-KVM01-iMES01','ZF-KVM33-iMES02','ZF-KVM11-IMES42N','ZF-KVM12-IMES45N');

[此处为图片2]

第二步：分析 SQL 的历史执行计划

为了判断执行计划是否发生变化，需追溯该 SQL 历史上的多种执行路径。

2.1 查询指定 SQL_ID 的所有历史执行计划

利用 DBA_HIST_SQL_PLAN 视图查看某个 SQL 在不同时间点所采用的执行计划。

SELECT * FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = '5qfvmykmvrpg4';

2.2 检查是否存在手工固定的执行计划（SQL Profile）

某些 SQL 可能已被 DBA 使用 SQL Profile 固定执行计划，应检查是否存在此类干预措施。

SELECT * FROM DBA_SQL_PROFILES;

2.3 查阅会话的历史执行记录

结合 AWR 或 ASH 数据，可以深入分析特定会话或 SQL 的历史行为模式，辅助诊断性能波动原因。

[此处为图片3]

-- 查询 DBA_HIST_ACTIVE_SESS_HISTORY 视图中的所有记录
select * from DBA_HIST_ACTIVE_SESS_HISTORY;

SELECT 'ALTER SYSTEM KILL SESSION  '''||SID||','||SERIAL#||'''immediate;' ,program FROM V$SESSION WHERE SQL_ID='xxx';

~~~檢查SQL等待事件是否閒置~~~
select event,machine,sql_id,logon_time,status from v$session where upper(wait_class) not in ('IDLE') -


~~~檢查這個表是否正在被調用~~~
 select * from v$session where SID IN
   (select /*+ rule() */SID from  v$access where OBJECT='TABLE_NAME' and OWNER='OWNER_NAME')
   AND STATUS='ACTIVE';

~~~刪掉原有的執行計劃~~~
call DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_9z8mr8jbt0k55_nthwFfQsCw'); 


~~~查看shared_cursor~~~
select * from V$SQL_SHARED_CURSOR where SQL_ID='XXX';


~~~查詢這張表被誰鎖定~~~

    SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    s.blocking_session
FROM 
    gv$locked_object lo
JOIN 
    dba_objects do ON lo.object_id = do.object_id
JOIN 
    gv$session s ON lo.session_id = s.sid
WHERE 
    do.object_name = 'TABLE_NAME'
    AND do.owner = 'OWNER_NAME';

-- 2.4 Oracle 19c 环境下获取 SQL 文本及存储过程相关信息
-- 通过 v$sqlarea 获取指定 SQL_ID 的完整 SQL 内容、程序单元信息以及最后活动时间
select SQL_TEXT, SQL_FULLTEXT, PROGRAM_ID, PROGRAM_LINE#, LAST_ACTIVE_TIME 
from v$sqlarea 
where sql_id = '';

[此处为图片2]

-- 2.5 统计信息管理操作：查看统计信息收集进度，并评估表的规模，可初步尝试手动收集统计信息

-- 执行统计信息收集的脚本示例（使用 DBMS_STATS 包）
execute dbms_stats.gather_table_stats(
    ownname          => 'XXX',
    tabname          => 'XXX',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'for all columns size auto',
    cascade          => true,
    degree           => 6
);

-- 查询特定表的索引统计信息及其上次分析时间
select a.OWNER, a.INDEX_NAME, a.TABLE_NAME, a.LAST_ANALYZED 
from dba_ind_statistics a 
where table_name = 'XXX' 
  and table_owner = 'XXX';

[此处为图片3]

-- 查询指定表的表级统计信息，包括所有者和最近分析时间
select b.TABLE_NAME, b.OWNER, b.LAST_ANALYZED 
from dba_tab_statistics b 
where table_name = 'XXX' 
  and owner = 'XXX';

-- 解锁某张表的统计信息锁定状态，允许重新收集
exec dbms_stats.unlock_table_stats('OWNER', 'TABLE_NAME');

[此处为图片4]

-- 第三步：当存在多个执行计划时，可先固定所需执行计划，再尝试将其他非优计划从共享池中清除（生产环境需谨慎使用）

-- 获取指定 SQL_ID 对应的内存地址与 hash 值
select address, hash_value 
from v$sqlarea 
where sql_id = 'xxx';

-- 使用 DBMS_SHARED_POOL.PURGE 过程清理特定 SQL 在共享池中的缓存
exec SYS.DBMS_SHARED_POOL.PURGE('000000B71884A4B0,1140385327', 'C', 65);

-- 验证清除效果或查看当前执行计划结构
select * 
from V$sql_plan 
where sql_id = 'xxx';

[此处为图片5]

-- 第四步：手工固定执行计划的操作步骤

-- 4.1 查看问题 SQL 中各绑定变量的捕获值
select * 
from V$SQL_BIND_CAPTURE 
where sql_id = 'xxx';

[此处为图片6]

-- 4.2 将上一步获取的绑定变量值代入原始 SQL 并执行，以模拟实际运行条件
SELECT x.WIP_ID
FROM DMPDB2.v_WIP_LOG x, DMPDB2.STATION B
WHERE     x.STATION_ID = B.ID
      AND x.WIP_ID = '988645458'
      AND x.STATION_TIME > to_date('08/12/2021 21:27:25', 'mm/dd/yyyy hh24:mi:ss')
      AND B.CODE = 'CA'
      AND x.DEL_FLAG = 0
      AND B.DEL_FLAG = 0;

[此处为图片7]

-- 4.3 查询当前游标的执行计划详情，用于比对与优化分析
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'OUTLINE'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID'));

[此处为图片8]

-- 4.4 根据前一步查得的 SQL_ID，提取新的执行计划中的 Outline Hints 信息

-- 提取执行计划中 OTHER_XML 字段包含的 Hint 提示，用于创建 SQL Profile
SELECT 'q''['||REPLACE(EXTRACTVALUE(VALUE(d), '/hint'), '', '''')||']'', AS outline_hints
FROM XMLTABLE(
    '/*/outline_data/hint'
    PASSING (
        SELECT xmltype(other_xml) AS xmlval
        FROM v$sql_plan
        WHERE sql_id = '850d3dfn2xuqn'  -- 替换为实际获取到的 SQL_ID
          AND child_number = 0
          AND other_xml IS NOT NULL
    )
) d;

-- 若无 child_number，可根据 plan_hash_value 判断不同执行路径的情况进行处理

DECLARE
    V_SQLID_1            V$SQL.SQL_ID%TYPE := 'dgqzybhsr3558'; 
    V_CHILDNO_1          V$SQL.CHILD_NUMBER%TYPE := 0;  
    v_sqlprofile         SYS.sqlprof_attr;
    v_new_profile_name   VARCHAR2(255);
    v_sql_text           V$SQL.SQL_FULLTEXT%TYPE;
BEGIN
    SELECT sql_fulltext
      INTO v_sql_text
      FROM v$sql
     WHERE sql_id = V_SQLID_1 
       AND child_number = V_CHILDNO_1;

    SELECT 'coe_' || DBMS_RANDOM.STRING('A', 24)
      INTO v_new_profile_name
      FROM DUAL;

    DBMS_SQLTUNE.import_sql_profile (
        sql_text   => v_sql_text,
        name       => v_new_profile_name,
        profile    => sqlprof_attr (
            q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
            q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
            q'[DB_VERSION('19.1.0')]',
            q'[OPT_PARAM('_b_tree_bitmap_plans' 'false')]',
            q'[OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')]',
            q'[OPT_PARAM('optimizer_index_cost_adj' 25)]',
            q'[OPT_PARAM('optimizer_index_caching' 90)]',
            q'[ALL_ROWS]',
            q'[OUTLINE_LEAF(@"SEL$36B8B3E0")]',
            q'[CONNECT_BY_ELIM_DUPS(@"SEL$4")]',
            q'[OUTLINE_LEAF(@"SEL$5C160134")]',
            q'[MERGE(@"SEL$335DD26A" >"SEL$1")]',
            q'[OUTLINE(@"SEL$4")]',
            q'[OUTLINE(@"SEL$1")]',
            q'[OUTLINE(@"SEL$335DD26A")]',
            q'[MERGE(@"SEL$3" >"SEL$2")]',
            q'[OUTLINE(@"SEL$2")]',
            q'[OUTLINE(@"SEL$3")]',
            q'[INDEX(@"SEL$5C160134" "C"@"SEL$3" ("C_SITE_AREA_T"."ID"))]',
            q'[FULL(@"SEL$5C160134" "T"@"SEL$3")]',
            q'[FULL(@"SEL$5C160134" "B"@"SEL$3")]',
            q'[FULL(@"SEL$5C160134" "A"@"SEL$3")]',
            q'[INDEX_RS_ASC(@"SEL$5C160134" "T"@"SEL$2" ("xxx"."CARD_TIME"))]',
            q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5C160134" "T"@"SEL$2")]'
        ),
        replace => TRUE
    );
END;
/

4.5 将第四步生成的输出结果插入到上述脚本中，替换 sqlprof_attr() 函数内的提示列表内容。注意：需删除最后一个多余的逗号，确保语法正确。此操作用于为指定 SQL ID 创建并导入一个新的 SQL Profile，从而固化执行计划。

SELECT 'ALTER SYSTEM KILL SESSION  '''||SID||','||SERIAL#||'''immediate;' ,program FROM V$SESSION WHERE SQL_ID='xxx';

~~~檢查SQL等待事件是否閒置~~~
select event,machine,sql_id,logon_time,status from v$session where upper(wait_class) not in ('IDLE') -


~~~檢查這個表是否正在被調用~~~
 select * from v$session where SID IN
   (select /*+ rule() */SID from  v$access where OBJECT='TABLE_NAME' and OWNER='OWNER_NAME')
   AND STATUS='ACTIVE';

~~~刪掉原有的執行計劃~~~
call DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_9z8mr8jbt0k55_nthwFfQsCw'); 


~~~查看shared_cursor~~~
select * from V$SQL_SHARED_CURSOR where SQL_ID='XXX';


~~~查詢這張表被誰鎖定~~~

    SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    s.blocking_session
FROM 
    gv$locked_object lo
JOIN 
    dba_objects do ON lo.object_id = do.object_id
JOIN 
    gv$session s ON lo.session_id = s.sid
WHERE 
    do.object_name = 'TABLE_NAME'
    AND do.owner = 'OWNER_NAME';

以下查询语句用于从数据库历史执行计划中提取特定 SQL 的 Outline Hints 信息：

SELECT 'q''['||REPLACE(EXTRACTVALUE(VALUE(d), '/hint'),''','''')||']'',' AS outline_hints
FROM XMLTABLE(
    '/*/outline_data/hint'
    PASSING (
        SELECT XMLTYPE(other_xml) AS xmlval
        FROM DBA_HIST_SQL_PLAN 
        WHERE sql_id = 'bps0cnr8t1t33'
          AND plan_hash_value = '3429550748'
          AND id = 1
    )
) d;

该查询会解析 other_xml 字段中的 XML 数据，提取出所有的 Hint，并以适用于 sqlprof_attr 的格式输出，便于后续直接使用。

在进行SQL调优的过程中，可能会遇到多种执行计划相关的问题。以下是一些常见的处理方式与优化建议的整理：

首先，在执行计划固定（Outline）阶段，若发现原有的提示（Hints）未能有效引导优化器生成理想的执行路径，可尝试调整或补充以下一系列查询块级别的优化指令：

q'[LEADING(@"SEL$5C160134" "C"@"SEL$3" "T"@"SEL$3" "B"@"SEL$3" "A"@"SEL$3" "T"@"SEL$2")]',
q'[USE_MERGE(@"SEL$5C160134" "T"@"SEL$3")]',
q'[USE_HASH(@"SEL$5C160134" "B"@"SEL$3")]',
q'[USE_HASH(@"SEL$5C160134" "A"@"SEL$3")]',
q'[USE_HASH(@"SEL$5C160134" "T"@"SEL$2")]',
q'[SWAP_JOIN_INPUTS(@"SEL$5C160134" "B"@"SEL$3")]',
q'[SWAP_JOIN_INPUTS(@"SEL$5C160134" "A"@"SEL$3")]',
q'[PQ_FILTER(@"SEL$5C160134" SERIAL)]',
q'[NO_CONNECT_BY_FILTERING(@"SEL$36B8B3E0")]',
q'[CONNECT_BY_COMBINE_SW(@"SEL$36B8B3E0")]',

上述提示集合可通过存储过程参数化注入执行计划基线中，确保其被正确绑定。注意在操作时需设置 REPLACE => TRUE 以及 force_match => TRUE，以保证新生成的执行计划能够覆盖原有条目，并支持模糊匹配不同字面量的SQL语句。

SELECT 'ALTER SYSTEM KILL SESSION  '''||SID||','||SERIAL#||'''immediate;' ,program FROM V$SESSION WHERE SQL_ID='xxx';

~~~檢查SQL等待事件是否閒置~~~
select event,machine,sql_id,logon_time,status from v$session where upper(wait_class) not in ('IDLE') -


~~~檢查這個表是否正在被調用~~~
 select * from v$session where SID IN
   (select /*+ rule() */SID from  v$access where OBJECT='TABLE_NAME' and OWNER='OWNER_NAME')
   AND STATUS='ACTIVE';

~~~刪掉原有的執行計劃~~~
call DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_9z8mr8jbt0k55_nthwFfQsCw'); 


~~~查看shared_cursor~~~
select * from V$SQL_SHARED_CURSOR where SQL_ID='XXX';


~~~查詢這張表被誰鎖定~~~

    SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    s.blocking_session
FROM 
    gv$locked_object lo
JOIN 
    dba_objects do ON lo.object_id = do.object_id
JOIN 
    gv$session s ON lo.session_id = s.sid
WHERE 
    do.object_name = 'TABLE_NAME'
    AND do.owner = 'OWNER_NAME';

第五步，如果经过前述的固定执行计划手段后问题依旧存在，则可以进一步借助Oracle内置的自动调优工具——SQL Tuning Advisor（STA），让数据库自行分析并推荐更优的执行策略。

具体操作流程如下：

启动一个调优任务，设定时间限制为60秒，并指定目标SQL_ID和任务名称：

declare
v_name varchar2(200);
begin
v_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(time_limit => 60, sql_id => 'xxx', task_name => 't2_test');
end;
/

执行该调优任务：

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME => 't2_test');

随后查看系统生成的调优报告：

select DBMS_SQLTUNE.REPORT_TUNING_TASK('t2_test') from dual;

最后，在完成分析后清理测试任务，释放资源：

exec DBMS_SQLTUNE.DROP_TUNING_TASK(TASK_NAME => 't2_test');

通过以上步骤，可以在无法手动干预的情况下，利用Oracle自身的智能分析能力识别性能瓶颈，并提供索引建议、重写建议或统计信息更新等优化方案。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

栏目导航