sql执行效率突变的原因及处置办法

导致Oracle SQL运行速度突然变慢的原因可能有很多。下面列举了一些常见的原因，以及判断和处理的方法。

一. 执行计划改变及处置方法

执行计划的变化通常是由于多种因素相互作用的结果。以下是一些可能导致执行计划变化的原因，以及详细解释：

1. 统计信息变化

Oracle优化器依赖统计信息来生成最佳执行计划。当表或索引的统计信息发生变化时（例如，数据量变化、数据分布变化等），优化器可能会为SQL选择不同的执行计划。统计信息可能由以下操作更新：手动收集统计信息、自动任务收集统计信息或者导入统计信息。
以下方法检查统计信息是否发生变化

统计信息收集时间

select OWNER,TABLE_NAME,TABLESPACE_NAME,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') from dba_tables where table_name='$table_name';

统计信息是否过期

col SUBPARTITION_NAME for a50
SELECT SUBPARTITION_NAME,OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS,OBJECT_TYPE,PARTITION_NAME from dba_tab_stATistics where owner='$owner' and TABLE_NAME='$table_name';

2. 数据库参数变更

数据库参数（如，optimizer_mode、optimizer_index_cost_adj等）会影响优化器的行为和决策。当这些参数发生变化时，可能会导致执行计划变化。数据库参数可以在会话级别或系统级别进行修改。

3. 对象定义的改变

表、索引或其他数据库对象的定义发生变化时，可能会影响执行计划。例如，添加、删除或修改索引；更改表的存储参数；更改表的分区方案等。

4. SQL语句本身的更改

当SQL语句本身发生变化（例如，更改谓词条件、更改JOIN顺序等）时，可能会导致执行计划变化。即使是微小的更改，也可能导致优化器选择不同的执行计划。

5. 优化器行为的变化

Oracle数据库的不同版本可能具有不同的优化器行为。在升级数据库版本或应用补丁后，优化器可能会选择不同的执行计划。此外，对于带有绑定变量的SQL，当绑定变量的值发生变化时，优化器可能会为不同的值选择不同的执行计划（这称为绑定变量窥探）。

1. 当优化器解析一个带有绑定变量的SQL语句时，它会查看绑定变量的实际值，并根据这些值评估谓词的选择性（Selectivity）。
2. 优化器根据谓词的选择性生成执行计划。在具有不同数据分布的情况下，不同的绑定变量值可能导致不同的执行计划。
3. 对于相同的SQL语句，优化器会将生成的执行计划缓存到库存游标（Cursor Cache）中，以便后续的相同SQL语句可以直接使用缓存的执行计划。
4. 当绑定变量的值发生变化时，优化器可以根据新值重新评估谓词的选择性，并在必要时生成新的执行计划。这有助于在不同绑定变量值的情况下为SQL语句选择最优的执行计划。

尽管绑定变量窥视在许多情况下可以提高带有绑定变量的SQL语句的性能，但在某些情况下，它可能导致性能下降。例如，当绑定变量值在不同执行之间具有高度波动性时，优化器可能会为某些值选择次优的执行计划。在这种情况下，可以考虑使用Adaptive Cursor Sharing（ACS）或SQL Plan Baselines等技术来解决性能问题。

6. 系统资源可用性变化

当系统资源（例如，CPU、内存、I/O等）发生变化时，可能会影响优化器的决策。例如，当系统负载较高时，优化器可能会倾向于选择较少资源消耗的执行计划。

要诊断和处理执行计划变化，可以采用以下方法：

7、处置方法

1. SQL Plan Baselines

SQL Plan Baselines是一种用于捕获、选择和管理执行计划的技术，以确保SQL语句的执行计划稳定性。通过使用SQL Plan Baselines，可以锁定特定的执行计划并防止因执行计划变化而导致性能下降。

1. 启用自动捕获SQL Plan Baselines：

要启用自动捕获功能，需要将参数optimizer_capture_sql_plan_baselines设置为TRUE。可以通过以下命令来设置：

sql
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

2. 手动捕获SQL Plan Baselines：
   如果不希望自动捕获SQL Plan Baselines，可以手动捕获它们。可以使用DBMS_SPM包中的LOAD_PLANS_FROM_CURSOR_CACHE或LOAD_PLANS_FROM_SQLSET函数来实现这一目的。以下是一个示例：

首先，从SQL语句的库存游标（Cursor Cache）中捕获SQL Plan Baselines：

sql
DECLARE
  num_plans_loaded INTEGER;
BEGIN
  num_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id');
END;
/

或者，从SQL Tuning Set（STS）中捕获SQL Plan Baselines：

sql
DECLARE
  num_plans_loaded INTEGER;
BEGIN
  num_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'your_sqlset_name', basic_filter => 'sql_id = "your_sql_id"');
END;
/

一旦创建了SQL Plan Baselines，Oracle优化器将尝试在执行SQL语句时使用它们。请注意，为了避免性能问题，优化器可能会在运行时选择一个比基线计划更优的执行计划。可以通过DBMS_SPM包中的函数来管理和维护SQL Plan Baselines，例如EVOLVE_SQL_PLAN_BASELINE、DROP_SQL_PLAN_BASELINE等。管理SQL Plan Baselines主要涉及到创建、修改、演进、删除和查看它们。以下是一些使用DBMS_SPM包进行SQL Plan Baselines管理的常用方法：

3. 创建SQL Plan Baselines：
   如前所述，可以使用LOAD_PLANS_FROM_CURSOR_CACHE或LOAD_PLANS_FROM_SQLSET函数从库存游标或SQL Tuning Set中捕获SQL Plan Baselines。

4. 查看SQL Plan Baselines：
   可以通过查询DBA_SQL_PLAN_BASELINES视图来查看现有的SQL Plan Baselines。

SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM DBA_SQL_PLAN_BASELINES;

5. 演进SQL Plan Baselines：
   使用EVOLVE_SQL_PLAN_BASELINE函数可以比较当前执行计划与已知的基线计划，确定新计划是否更优。如果新计划更优，它将被添加到执行计划基线中。

DECLARE
  l_plans_evolved_count INTEGER;
BEGIN
  l_plans_evolved_count := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'your_sql_handle');
END;
/

6. 修改SQL Plan Baselines属性：
   使用ALTER_SQL_PLAN_BASELINE函数可以修改SQL Plan Baselines的属性，如启用、禁用、接受或固定某个执行计划基线。

BEGIN
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name',
    attribute_name => 'ATTRIBUTE_NAME',
    attribute_value => 'ATTRIBUTE_VALUE'
  );
END;
/

7. 删除SQL Plan Baselines：
   使用DROP_SQL_PLAN_BASELINE函数可以删除不再需要的SQL Plan Baselines。

BEGIN
  DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name'
  );
END;
/

8. 验证SQL Plan Baselines：
   可以使用DBMS_SPM.TEST_SQL_PLAN_BASELINE函数验证SQL Plan Baselines。这将对基线执行计划执行性能测试，并返回相关的性能数据。例如：

DECLARE
  l_result CLOB;
BEGIN
  l_result := DBMS_SPM.TEST_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name'
  );
  DBMS_OUTPUT.PUT_LINE(l_result);
END;
/

9. 指定验证时间阈值：

可以使用SET_EVOLVE_TASK_PARAMETER函数设置SQL Plan Baselines演进任务的参数。：

BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'your_task_name',
    parameter_name => 'TIME_LIMIT',
    parameter_value => 300
  );
END;
/

10. 创建和执行SQL Plan Baselines演进任务：
    使用CREATE_EVOLVE_TASK函数创建SQL Plan Baselines演进任务，并使用EXECUTE_EVOLVE_TASK函数执行任务。

BEGIN
  DBMS_SPM.CREATE_EVOLVE_TASK(
    task_name => 'your_task_name',
    sql_handle => 'your_sql_handle'
  );
  DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => 'your_task_name');
END;
/

11. 查看任务结果：
    使用REPORT_EVOLVE_TASK函数生成SQL Plan Baselines演进任务的报告。例如：

DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.REPORT_EVOLVE_TASK(task_name => 'your_task_name', report_level => 'ALL');
  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

2. SQL Profiles

SQL Profiles 是 Oracle 为 SQL 优化器提供的一种调整机制，它可以影响优化器在生成执行计划时使用的统计信息和优化器参数。SQL Profiles 可以帮助优化器更准确地估计执行计划的成本，并为性能问题的 SQL 语句提供更好的执行计划。以下是如何使用 SQL Profiles 的步骤：

1. 使用 SQL Tuning Advisor（SQL 调优顾问）：
   SQL Profiles 通常是通过 SQL Tuning Advisor 自动生成的。可以创建调优任务并运行 SQL Tuning Advisor，以查找 SQL 语句的潜在性能问题和建议的 SQL Profiles。例如：

DECLARE
  l_task_id  VARCHAR2(100);
BEGIN
  l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'your_sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_id);
END;
/

2. 查看调优任务报告：
   使用 DBMS_SQLTUNE.REPORT_TUNING_TASK 函数查看调优任务的报告，以查找建议的 SQL Profiles。

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 200
SET PAGESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => 'your_task_id') AS report
FROM DUAL;

3. 接受 SQL Profiles：
   如果调优任务报告中建议了 SQL Profiles，可以使用 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 函数接受它们。例如：

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'your_task_id',
    name => 'your_profile_name'
  );
END;
/

4. 查看和管理 SQL Profiles：
   可以使用以下 SQL 查询查看现有的 SQL Profiles：

SELECT name, sql_text, category, status, created
FROM DBA_SQL_PROFILES;

要删除不再需要的 SQL Profiles，使用 DBMS_SQLTUNE.DROP_SQL_PROFILE 函数。

BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'your_profile_name');
END;
/

5. 使用 SQL Plan Baselines 和 SQL Profiles：
   在某些情况下，可能需要同时使用 SQL Plan Baselines 和 SQL Profiles。SQL Plan Baselines 用于确保执行计划的稳定性，而 SQL Profiles 可以帮助优化器为性能问题的 SQL 语句生成更好的执行计划。

当同时使用这两个功能时，优化器首先会在 SQL Plan Baselines 中查找可用的执行计划。如果找到一个合适的执行计划，优化器将使用该计划。如果没有找到合适的执行计划，优化器将尝试应用 SQL Profiles 生成新的执行计划。这样，可以在保持执行计划稳定性的同时，为具有性能问题的 SQL 语句提供更好的执行计划。

6. 监控 SQL Profiles 的影响：
   定期监控 SQL Profiles 对 SQL 语句性能的影响。可以通过查询 DBA_SQL_PROFILES 和 V$SQL 视图来监控 SQL Profiles 的使用情况。例如，可以使用以下查询查看受 SQL Profiles 影响的 SQL 语句：

SELECT p.name, s.sql_id, s.sql_text, s.executions, s.elapsed_time, s.cpu_time
FROM DBA_SQL_PROFILES p, V$SQL s
WHERE p.sql_id = s.sql_id;

总之，使用 SQL Profiles 可以帮助优化器更准确地估计执行计划的成本，并为具有性能问题的 SQL 语句生成更好的执行计划。同时监控和分析 SQL Profiles 的使用情况，以确保它们对数据库性能产生积极的影响。在对生产环境进行任何更改之前，请确保在测试环境中验证更改效果，以免对生产环境造成负面影响。

3. 使用SQL Hint

在某些情况下，可能需要手动干预执行计划的选择。使用SQL Hint可以为优化器提供额外的信息，从而影响执行计划的选择。例如，可以指定JOIN方法、驱动表等。

SQL Hints 是一种告诉 Oracle 优化器如何执行特定 SQL 语句的方法。使用 Hints，可以强制优化器采用认为最佳的执行计划。然而，请注意，过度使用 Hints 可能会导致不良的性能影响。应始终将 Hints 视为最后手段，在考虑其他性能调优方法后使用。以下是一些常用的 SQL Hints：

1. LEADING：
   指定驱动表顺序。例如：

SELECT /*+ LEADING(e, d) */ ...
FROM employees e, departments d
WHERE ...

2. USE_NL：
   指示优化器使用嵌套循环连接。例如：

SELECT /*+ USE_NL(e, d) */ ...
FROM employees e, departments d
WHERE ...

3. USE_HASH：
   指示优化器使用哈希连接。例如：

SELECT /*+ USE_HASH(e, d) */ ...
FROM employees e, departments d
WHERE ...

4. USE_MERGE：
   指示优化器使用合并连接。例如：

SELECT /*+ USE_MERGE(e, d) */ ...
FROM employees e, departments d
WHERE ...

5. FULL：
   指示优化器执行全表扫描。例如：

SELECT /*+ FULL(e) */ ...
FROM employees e
WHERE ...

6. INDEX：
   指示优化器使用特定索引。例如：

SELECT /*+ INDEX(e emp_index) */ ...
FROM employees e
WHERE ...

7. NO_INDEX：
   告诉优化器不要使用特定索引。例如：

SELECT /*+ NO_INDEX(e emp_index) */ ...
FROM employees e
WHERE ...

8. PARALLEL：
   指示优化器使用并行执行。例如：

SELECT /*+ PARALLEL(e, 4) */ ...
FROM employees e
WHERE ...

9. PUSH_PRED：
   推动连接谓词到视图中。例如：

SELECT /*+ PUSH_PRED(v) */ ...
FROM employees e, (SELECT ... FROM ...) v
WHERE ...

10. STAR_TRANSFORMATION：
    执行星型转换。例如：

SELECT /*+ STAR_TRANSFORMATION */ ...
FROM fact_table, dimension_table
WHERE ...

11. MERGE_AJ：
    在执行外连接时使用合并外连接。例如：

SELECT /*+ MERGE_AJ */ ...
FROM employees e, departments d
WHERE ...

12. PQ_DISTRIBUTE：
    控制并行查询分布。例如：

SELECT /*+ PQ_DISTRIBUTE(employees, HASH, HASH) */ ...
FROM employees, departments
WHERE ...

13. CARDINALITY：
    设置优化器在生成执行计划时预测的某个表或视图的返回行数。例如：

SELECT /*+ CARDINALITY(e, 100) */ ...
FROM employees e
WHERE ...

14. GATHER_PLAN_STATISTICS：
    收集生成执行计划的实际运行时统计信息，有助于调试和性能分析。例如：

SELECT /*+ GATHER_PLAN_STATISTICS */ ...
FROM employees e
WHERE ...

15. RESULT_CACHE：
    将查询结果存储在结果缓存中以提高性能。例如：

SELECT /*+ RESULT_CACHE */ ...
FROM employees e
WHERE ...

16. CURSOR_SHARING_EXACT：
    强制某个 SQL 语句在解析时使用精确的游标共享模式，即使系统默认设置为不同模式。例如：

SELECT /*+ CURSOR_SHARING_EXACT */ ...
FROM employees e
WHERE ...

17. FIRST_ROWS：
    指示优化器优先返回查询的前几行，尽快返回查询结果的前几行。例如：

SELECT /*+ FIRST_ROWS(10) */ ...
FROM employees e
WHERE ...

18. ALL_ROWS：
    指示优化器优化整个查询的执行，不考虑返回前几行的速度。例如：

SELECT /*+ ALL_ROWS */ ...
FROM employees e
WHERE ...

19. OPT_ESTIMATE：
    调整优化器对查询的某个部分的成本估算。例如：

SELECT /*+ OPT_ESTIMATE(TABLE e, ROWS=1000) */ ...
FROM employees e
WHERE ...

20. ORDERED：
    指示优化器按 FROM 子句中列出的顺序连接表。例如：

SELECT /*+ ORDERED */ ...
FROM employees e, departments d
WHERE ...

4. 监控和诊断工具

详使用Oracle提供的监控和诊断工具，如Automatic Workload Repository (AWR)、Active Session History (ASH)、SQL Tuning Advisor等，可以帮助识别和解决执行计划变化引起的性能问题。

1. AWR（自动工作负载存储库）报告：
   AWR 报告包含一段时间内数据库的性能统计信息。可以生成 AWR 报告，然后比较报告中的执行计划和统计信息，以发现问题的根源。使用以下命令生成 AWR 报告：

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

2. ASH（活动会话历史）报告：
   ASH 报告提供了数据库活动会话的实时统计信息。使用 ASH 报告可以帮助了解 SQL 执行计划变化的实时影响。使用以下命令生成 ASH 报告：

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

3. SQL Monitor 报告：
   SQL Monitor 报告提供了正在运行的 SQL 语句的详细执行信息。可以使用 SQL Monitor 报告来诊断正在执行的 SQL 语句的执行计划变化。使用以下命令生成 SQL Monitor 报告：

@$ORACLE_HOME/rdbms/admin/sqlmon.sql

4. VSQL_PLAN 和 VSQL_PLAN_STATISTICS_ALL 视图：
   通过查询 VSQL_PLAN 和 VSQL_PLAN_STATISTICS_ALL 视图，可以查看 SQL 语句的执行计划和统计信息。这有助于了解 SQL 执行计划的变化。

例如：

SELECT *
FROM V$SQL_PLAN
WHERE sql_id = 'your_sql_id'
AND child_number = your_child_number
ORDER BY id;

5. 使用 DBMS_XPLAN.DISPLAY_CURSOR：
   DBMS_XPLAN.DISPLAY_CURSOR 函数允许显示特定 SQL 语句的执行计划。通过比较不同时间点的执行计划，可以了解执行计划的变化。

例如：

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', your_child_number));

6. 统计信息：
   当表或索引的统计信息发生变化时，可能会导致执行计划变化。要查看表和索引的统计信息，可以查询 DBA_TAB_STATISTICS、DBA_TAB_COL_STATISTICS 和 DBA_INDEX_STATISTICS 视图。

7. 查看优化器参数变化：
   优化器参数的变化可能会影响 SQL 执行计划。通过查询 VSPPARAMETER 和 VSYSTEM_PARAMETER 视图，可以检查优化器参数的历史和当前设置。

SELECT name, value
FROM V$SYSTEM_PARAMETER
WHERE name LIKE 'optimizer%';

8. 使用 SQL Plan Management (SPM)：
   SQL Plan Management 可以帮助捕获、选择和保留最优的执行计划。当 SQL 语句的执行计划发生变化时，可以使用 SPM 来锁定已知的高性能计划，从而防止性能下降。

例如，使用以下命令创建一个 SQL Plan Baseline：

BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id');
END;
/

9. 分析执行计划变化的影响：
   为了了解执行计划变化的影响，可以使用 DBMS_SQLTUNE.REPORT_SQL_MONITOR 函数生成一个 SQL 监视报告。这有助于确定执行计划变化是否对性能产生负面影响。

例如：

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'your_sql_id') AS report
FROM dual;

10. 查看对象定义变化：
    对象（如表、视图、索引等）的定义变化可能会导致 SQL 执行计划变化。要检查对象定义的变化，可以查询 DBA_OBJECTS 视图，了解对象的最后一个 DDL 时间。

例如：

SELECT object_name, last_ddl_time
FROM DBA_OBJECTS
WHERE owner = 'your_schema'
AND object_name = 'your_object_name';

5. SQL Plan Management (SPM) 评估和演进

SQL Plan Management (SPM) 是 Oracle 数据库中的一个功能，用于捕获、选择和保留 SQL 语句的最佳执行计划。SPM 通过 SQL Plan Baseline 保留已知的高性能计划，以防止由于执行计划变化导致的性能下降。下面是如何使用 SPM 进行评估和演进的步骤：

1. 捕获 SQL 计划：
   SPM 会自动捕获典型的 SQL 语句。还可以手动从光标缓存加载执行计划，例如：

BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id');
END;
/

2. 查看 SQL Plan Baseline：
   要查看现有的 SQL Plan Baseline，可以查询 DBA_SQL_PLAN_BASELINES 视图。例如：

SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM DBA_SQL_PLAN_BASELINES;

3. 演进 SQL Plan Baseline：
   当新的执行计划可用时，可以使用演进过程来评估它们。演进过程会将新计划与现有计划进行比较，并将性能更好的计划添加到 SQL Plan Baseline 中。可以使用 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 函数进行演进。例如：

DECLARE
  l_plans_evolved_count PLS_INTEGER;
BEGIN
  l_plans_evolved_count := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'your_sql_handle');
  DBMS_OUTPUT.PUT_LINE('Number of plans evolved: ' || l_plans_evolved_count);
END;
/

4. 禁用或启用 SQL Plan Baseline：
   可以禁用或启用特定的 SQL Plan Baseline，以防止某些执行计划被优化器选择。使用 DBMS_SPM.ALTER_SQL_PLAN_BASELINE 函数进行操作。例如：

BEGIN
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle    => 'your_sql_handle',
    plan_name     => 'your_plan_name',
    attribute_name => 'ENABLED',
    attribute_value => 'NO'
  );
END;
/

5. 删除 SQL Plan Baseline：
   如果确定某个执行计划不再需要保留，可以使用 DBMS_SPM.DROP_SQL_PLAN_BASELINE 函数删除它。例如：

BEGIN
  DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name  => 'your_plan_name'
  );
END;
/

6. 导入和导出 SQL Plan Baseline：
   为了在不同的环境（例如测试和生产）之间共享 SQL Plan Baseline，可以使用 DBMS_SPM.PACK_STGTAB_BASELINE 和 DBMS_SPM.UNPACK_STGTAB_BASELINE 函数将计划导出到 STGTAB 表中，然后将它们导入到另一个环境。例如：

-- 导出
BEGIN
  DBMS_SPM.PACK_STGTAB_BASELINE(
    staging_table_name =>'your_staging_table',
    staging_schema_owner => 'your_schema'
  );
END;
/

-- 导入
BEGIN
  DBMS_SPM.UNPACK_STGTAB_BASELINE(
    staging_table_name => 'your_staging_table',
    staging_schema_owner => 'your_schema'
  );
END;
/

6. 创建和使用SQL Patches

SQL Patches 是一个 Oracle 数据库功能，允许对特定的 SQL 语句应用优化器提示，而不需要更改 SQL 语句的源代码。这在 SQL 语句性能存在问题，但无法直接修改源代码时非常有用。

以下是如何创建和使用 SQL Patches 的步骤：

1. 确定要优化的 SQL 语句的 SQL_ID。
   可以通过查询 VSQL 或 VSQLAREA 视图找到 SQL_ID。例如：

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_sql_statement%';

2. 使用 DBMS_SQLDIAG.CREATE_SQL_PATCH 函数创建 SQL Patch。
   以下示例演示了如何为指定的 SQL_ID 创建一个 SQL Patch，其中包含一个优化器提示（在这里是 USE_HASH，提示优化器使用散列连接）：

DECLARE
  l_sql_text CLOB;
BEGIN
  SELECT sql_fulltext
  INTO l_sql_text
  FROM v$sql
  WHERE sql_id = 'your_sql_id';

  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_text => l_sql_text,
    hint_text => 'USE_HASH(@@"SEL$1" "your_table_alias")',
    name => 'your_sql_patch_name'
  );
END;
/

3. 验证 SQL Patch 是否应用成功。
   可以查询 DBA_SQL_PATCHES 视图以查看已应用的 SQL Patch。例如：

SELECT name, sql_text, hint_text, status
FROM dba_sql_patches;

如果 SQL Patch 成功应用，应该能在结果中看到它。现在，当再次运行受影响的 SQL 语句时，它将使用包含在 SQL Patch 中的优化器提示。

4. 删除 SQL Patch。
   如果发现 SQL Patch 对性能没有帮助或导致问题，可以使用 DBMS_SQLDIAG.DROP_SQL_PATCH 函数删除它。例如：

BEGIN
  DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'your_sql_patch_name');
END;
/

7. 利用Adaptive Cursor Sharing (ACS)

Adaptive Cursor Sharing (ACS) 是 Oracle 数据库中的一个功能，用于根据 SQL 语句的绑定变量值动态选择最佳执行计划。它解决了当多个用户使用不同的绑定变量值执行相同的 SQL 语句时可能出现的性能问题。ACS 通过评估不同执行计划的性能，为不同的绑定变量值选择最佳计划。

以下是如何利用 Adaptive Cursor Sharing (ACS) 的步骤：

1. 确保启用 ACS。
   在 Oracle 数据库 11g 及更高版本中，ACS 默认启用。可以通过以下 SQL 查询来验证是否启用了 ACS：

SELECT name, value FROM v$parameter WHERE name = 'optimizer_adaptive_cursor_sharing';

如果返回的值为 TRUE，则表示已启用 ACS。如果未启用，可以通过以下 SQL 语句启用它：

ALTER SYSTEM SET optimizer_adaptive_cursor_sharing = TRUE;

2. 编写具有绑定变量的 SQL 语句。
   确保在 SQL 语句中使用绑定变量，而不是硬编码值。例如，使用：

SELECT * FROM employees WHERE department_id = :department_id;

而不是：

SELECT * FROM employees WHERE department_id = 10;

3. 监控 ACS 的效果。
   可以通过查询 V$SQL_SHARED_CURSOR 视图来监控 ACS 如何为相同的 SQL 语句使用不同的执行计划。例如：

SELECT child_number, sql_id, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
FROM v$sql_shared_cursor
WHERE sql_id = 'your_sql_id';

在结果中，IS_BIND_SENSITIVE 列表示该 SQL 语句是否被认为是绑定敏感的，而 IS_BIND_AWARE 列表示该 SQL 语句是否具有绑定感知的执行计划。如果这两个值都为 Y，则表示 ACS 有效地为不同的绑定变量值选择了不同的执行计划。

4. 分析和调整。
   如果发现 ACS 对某些 SQL 语句的性能没有帮助，可以通过分析该语句的执行计划、收集相关对象的统计信息或使用 SQL Plan Management (SPM) 来进一步优化。

请注意，在某些情况下，ACS 可能无法为特定 SQL 语句提供理想的性能。在这种情况下，可能需要采用其他性能优化技术，如 SQL Profiles、SQL Patches 或改进 SQL 语句的编写。在进行任何更改之前，请确保在测试环境中验证更改效果，以免对生产环境造成负面影响。

二. 数据库资源争用

数据库资源争用会导致 SQL 执行效率降低。以下是一些可能导致 SQL 执行速度变慢的资源争用类型，以及如何判断和处理这些资源争用：

1. 锁争用（Lock contention）

SELECT * FROM v$lock WHERE request > 0;

处理方法：识别和解决导致锁争用的事务，例如通过重新设计应用程序逻辑或调整事务隔离级别。
用于查看阻塞链

@swc sid||':'||serial#||':'||event||':'||username||':'||inst_id||':'||username 1=1

@dash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name||':'||objt  session_type='FOREGROUND' "timestamp'2023-04-20 00:00:00'" "timestamp'2023-04-21 00:00:00'" 

@dash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name  session_type='FOREGROUND' sysdate-1 sysdate

@ash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name||':'||objt  session_type='FOREGROUND' "timestamp'2023-04-20 00:00:00'" "timestamp'2023-04-21 00:00:00'" 

@ash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name  session_type='FOREGROUND' sysdate-1 sysdate

2. I/O 争用

要确定是否存在 I/O 争用，可以通过以下方法进行检查：

1. 检查系统 I/O 状态：

   使用操作系统提供的工具（如 iostat（Linux/Unix），Perfmon（Windows））检查磁盘的 I/O 使用情况。关注磁盘队列长度、读写延迟、I/O 吞吐量等指标。如果这些指标超过正常水平，可能存在 I/O 争用。

2. 分析 Oracle AWR 报告：

   Oracle 的 Automatic Workload Repository (AWR) 报告提供了数据库性能的详细分析。在 AWR 报告中，关注以下部分：

   • 查找 “I/O Profile” 和 “Instance Activity Stats” 部分，检查 I/O 相关的等待事件和统计数据，如 “db file sequential read”、“db file scattered read” 等。
   • 在 “Top Timed Events” 部分，如果 I/O 相关的等待事件（如 “db file sequential read”、“db file scattered read”、“log file parallel write” 等）占据了较大的等待时间比例，可能存在 I/O 争用。

3. 检查 Oracle 等待事件：

   可以查询 v$system_event 和 v$session_event 视图，分析 I/O 相关的等待事件。例如：

   SELECT event, total_waits, time_waited
   FROM v$system_event
   WHERE event LIKE 'db file%';
   

   如果 I/O 相关事件的等待时间较长，可能存在 I/O 争用。

4. 检查 Oracle 的 I/O 压力：

   可以通过查询 v$filestat 和 v$tempstat 视图检查数据文件和临时文件的 I/O 情况。例如：

   SELECT file#, phyrds, phywrts, readtim, writetim
   FROM v$filestat;
   

   关注具有较高 I/O 操作或 I/O 时间的文件，分析是否存在 I/O 争用。

如何处理 I/O 争用：

1. 优化 SQL 语句以减少 I/O 请求，例如通过添加索引、重新编写查询或分区表等方法。
2. 考虑使用更快的硬件，例如 SSD 或更快的磁盘阵列。
3. 调整数据库文件布局，将热数据文件放在不同的磁盘上，以减轻 I/O 争用。
4. 考虑使用 Oracle 功能，如 Automatic Storage Management (ASM) 或 Direct NFS (dNFS)，以更好地管理 I/O 负载。
5. 调整 Oracle 参数，例如增加 SGA 大小，以减少对磁盘 I/O 的依赖。

3. CPU 争用

要确定是否存在 CPU 争用导致 SQL 执行效率变慢，可以通过以下方法进行检查：

1. 检查系统 CPU 使用率：

   使用操作系统提供的工具（如 top（Linux/Unix），Task Manager（Windows））检查系统的 CPU 使用情况。如果系统的 CPU 使用率持续较高，可能存在 CPU 争用。

2. 分析 Oracle AWR 报告：

   Oracle 的 Automatic Workload Repository (AWR) 报告提供了数据库性能的详细分析。在 AWR 报告中，关注以下部分：

   • 查找 “Top Timed Events” 部分，如果 CPU 相关的等待事件（如 “CPU time”）占据了较大的等待时间比例，可能存在 CPU 争用。
   • 在 “OS Statistics” 部分，检查 “%Busy” 和 “%User” 等指标，如果这些指标持续较高，可能存在 CPU 争用。

3. 检查 Oracle 等待事件：

   可以查询 v$system_event 和 v$session_event 视图，分析 CPU 相关的等待事件。例如：

   SELECT event, total_waits, time_waited
   FROM v$system_event
   WHERE event = 'CPU time';
   

   如果 CPU 时间占据了较大比例的等待时间，可能存在 CPU 争用。

4. 检查 Oracle 的系统统计信息：

   查询 v$sys_time_model 和 v$osstat 视图，分析 CPU 使用情况。例如：

   SELECT stat_name, value
   FROM v$sys_time_model
   WHERE stat_name IN ('DB time', 'DB CPU');
   
   SELECT stat_name, value
   FROM v$osstat
   WHERE stat_name LIKE 'BUSY%';
   

   关注较高的 CPU 使用率和系统负载，分析是否存在 CPU 争用。

如何处理 CPU 争用：

1. 优化 SQL 语句以减少 CPU 负载，例如通过重写查询、添加索引或使用更有效的聚合函数等方法。
2. 考虑在必要时升级硬件以增加 CPU 资源。
3. 调整 Oracle 参数，例如调整 PGA_AGGREGATE_TARGET、CPU_COUNT 或 PARALLEL_MAX_SERVERS 等，以更好地管理 CPU 资源。
4. 调整应用程序逻辑，例如通过减少事务冲突或并发请求数，以降低 CPU 负载。
5. 对于并行查询，可以调整并行度，以充分利用 CPU 资源或减轻 CPU 争用。

4. 前后台进程争用：

当前台进程（例如用户会话）和后台进程（例如日志写入进程）争用资源时，可能会导致 SQL 执行速度变慢。要判断前后台进程争用导致 SQL 执行效率变慢，可以通过以下方法进行检查：

1. 分析 Oracle AWR 报告：

   Oracle 的 Automatic Workload Repository (AWR) 报告提供了数据库性能的详细分析。在 AWR 报告中，关注以下部分：

   • 查找 “Top Timed Events” 部分，如果与前后台进程相关的等待事件（如 “latch: shared pool”、“log file sync”、“log buffer space” 等）占据了较大的等待时间比例，可能存在前后台进程争用。
   • 在 “Foreground Wait Events” 和 “Background Wait Events” 部分，检查与前后台进程相关的等待事件，以了解是否存在争用。

2. 检查 Oracle 等待事件：

   可以查询 v$system_event 和 v$session_event 视图，分析与前后台进程相关的等待事件。例如：

   SELECT event, total_waits, time_waited
   FROM v$system_event
   WHERE event IN ('latch: shared pool', 'log file sync', 'log buffer space');
   

   如果与前后台进程相关的等待事件具有较长的等待时间，可能存在进程争用。

3. 检查 Oracle 会话和进程：

   查询 v$session 和 v$process 视图，了解前后台进程的状态和活动。例如：

   SELECT s.sid, s.serial#, s.username, s.program, s.status, p.spid, p.bg_flag
   FROM v$session s
   JOIN v$process p ON s.paddr = p.addr;
   

关注阻塞会话或具有较高资源消耗的进程，以了解是否存在前后台进程争用。

如何处理前后台进程争用：

1. 优化 SQL 语句以减少资源消耗，例如通过重写查询、添加索引或使用更有效的聚合函数等方法。

2. 调整 Oracle 参数，例如调整 LOG_BUFFER、LOG_CHECKPOINTS_TO_ALERT 或 SHARED_POOL_SIZE 等，以减轻前后台进程争用。

3. 对于与日志写入相关的争用，可以考虑使用更快的磁盘或配置多个日志组。

4. 识别并优化具有较高资源消耗的应用程序逻辑，以降低前后台进程争用。

5. 对于并行查询，可以调整并行度，以充分利用资源或减轻前后台进程争用。

   SELECT * FROM v$session_wait WHERE event LIKE 'latch:%';
   

   处理方法：调整相关参数，以平衡前后台进程之间的资源分配。

6. 缓冲区争用：
   当多个会话试图访问或修改同一缓冲区时，可能会导致缓冲区争用。要判断缓冲区争用导致 SQL 执行效率低下，可以通过以下方法进行检查：

7. 分析 Oracle AWR 报告：

   Oracle 的 Automatic Workload Repository (AWR) 报告提供了数据库性能的详细分析。在 AWR 报告中，关注以下部分：

   • 查找 “Top Timed Events” 部分，如果与缓冲区相关的等待事件（如 “buffer busy waits”、“gc buffer busy” 等）占据了较大的等待时间比例，可能存在缓冲区争用。
   • 在 “Instance Activity Stats” 部分，检查与缓冲区相关的性能指标（如 “db block gets”、“consistent gets” 等），以了解是否存在缓冲区争用。

8. 检查 Oracle 等待事件：

   可以查询 v$system_event 和 v$session_event 视图，分析与缓冲区相关的等待事件。例如：

   SELECT event, total_waits, time_waited
   FROM v$system_event
   WHERE event IN ('buffer busy waits', 'gc buffer busy');
   

   如果与缓冲区相关的等待事件具有较长的等待时间，可能存在缓冲区争用。

9. 查询 Oracle 的数据文件 I/O：

   可以查询 v$filestat 和 v$tempstat 视图，了解数据文件和临时文件的 I/O 活动。例如：

   SELECT file#, phyrds, phywrts, readtim, writetim
   FROM v$filestat;
   
   SELECT file#, phyrds, phywrts, readtim, writetim
   FROM v$tempstat;
   

   关注具有较高 I/O 活动的文件，以了解是否存在缓冲区争用。

如何处理缓冲区争用：

1. 优化 SQL 语句以减少缓冲区争用，例如通过重写查询、添加索引或使用更有效的聚合函数等方法。
2. 调整 Oracle 参数，例如调整 DB_CACHE_SIZE 或 DB_BLOCK_BUFFERS 等，以增加缓冲区资源。
3. 对于并行查询，可以调整并行度，以充分利用缓冲区资源或减轻缓冲区争用。
4. 对于磁盘 I/O 性能较差的情况，可以考虑使用更快的磁盘或配置 I/O 子系统。
5. 对于表分区不均衡导致的缓冲区争用，可以考虑重新分区表以实现更均衡的数据访问。
6. 考虑使用 Oracle 的自动段空间管理 (ASSM) 特性，以减少缓冲区争用。

三. 数据和索引碎片

数据和索引碎片会导致 SQL 执行效率变慢，主要原因是碎片会导致 I/O 操作增加和访问数据所需的时间增加。

1. 碎片可能导致表扫描速度变慢，因为需要读取更多的数据块来获取所需的数据。
2. 碎片可能导致索引扫描速度变慢，因为需要遍历更多的索引层次结构以找到相关的数据行。
3. 碎片可能导致数据块的使用效率降低，增加缓冲区缓存中的竞争，从而影响整个数据库的性能。

如何判断数据和索引碎片：

1. 使用 DBMS_SPACE 包中的段空间管理过程来评估表和索引的碎片状况。例如：

   DECLARE
     l_free_blocks      NUMBER;
     l_free_bytes       NUMBER;
     l_largest_free_blk NUMBER;
     l_unused_blocks    NUMBER;
     l_unused_bytes     NUMBER;
     l_last_used_extent NUMBER;
   BEGIN
     DBMS_SPACE.UNUSED_SPACE (
       segment_owner   => 'SCHEMA_NAME',
       segment_name    => 'TABLE_NAME',
       segment_type    => 'TABLE',
       free_blocks     => l_free_blocks,
       free_bytes      => l_free_bytes,
       largest_free_blk=> l_largest_free_blk,
       unused_blocks   => l_unused_blocks,
       unused_bytes    => l_unused_bytes,
       last_used_extent=> l_last_used_extent
     );
   END;
   /
   

   如果 l_unused_blocks、l_unused_bytes 或 l_largest_free_blk 的值较高，表明可能存在数据碎片。

2. 查询 dba_tables 和 dba_indexes 视图，查看表和索引的空间使用情况。例如：

   SELECT table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt
   FROM dba_tables
   WHERE owner = 'SCHEMA_NAME';
   
   SELECT index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
   FROM dba_indexes
   WHERE owner = 'SCHEMA_NAME';
   

   关注具有高 empty_blocks、chain_cnt 或高 blevel、低 avg_leaf_blocks_per_key 和 avg_data_blocks_per_key 值的表和索引，可能存在碎片问题。

如何处理数据和索引碎片：

3. 对于表碎片，可以使用以下方法：

   • 使用 ALTER TABLE table_name MOVE 语句重新组织表。
   • 使用 DBMS_REDEFINITION 包在线重新定义表。
   • 使用数据泵工具 (Data Pump) 进行数据导出和导入。

4. 对于索引碎片，可以使用以下方法：

   • 使用 ALTER INDEX index_name REBUILD 语句重新建立索引。
   • 使用 ALTER INDEX index_name COALESCE 语句合并相邻的空闲空间。
   • 使用 DBMS_SPACE.SHRINK_SPACE 过程收缩索引空间。例如：

   BEGIN
     DBMS_SPACE.SHRINK_SPACE(
       segment_owner     => 'SCHEMA_NAME',
       segment_name      => 'INDEX_NAME',
       segment_type      => 'INDEX',
       compact_segments  => TRUE
     );
   END;
   /
   

5. 优化表和索引设计：

   • 定期删除无用的数据以减少碎片产生。
   • 使用合适的存储参数，如 PCTFREE、PCTUSED 和 INITRANS，以减少碎片产生。
   • 对于表，可以考虑使用表分区以实现更好的数据管理和降低碎片产生。
   • 使用合适的索引类型，如 B-tree 索引、位图索引或函数基索引等，以满足查询需求。

6. 监控和维护数据库：

   • 定期生成 AWR 报告以监控数据库性能，并关注与碎片相关的性能指标。
   • 使用 Oracle Enterprise Manager (OEM) 或其他数据库管理工具监控和管理碎片。
   • 定期进行表和索引的碎片检查和维护，以保持数据库性能稳定。

请注意，不是所有的碎片都会对性能产生负面影响。在进行处理前，请确保已经通过分析和诊断确定碎片确实是性能问题的原因。

-- 检查表碎片
SELECT table_name, round((blocks*8),2) "size (kb)", extents, round((blocks*8)/extents,2) "avg_extent_size (kb)"
FROM user_tables
WHERE (blocks*8)/extents < <your_threshold_here>;

-- 检查索引碎片
SELECT index_name, round((leaf_blocks*8),2) "size (kb)", extents, round((leaf_blocks*8)/extents,2) "avg_extent_size (kb)"
FROM user_indexes
WHERE (leaf_blocks*8)/extents < <your_threshold_here>;

四. SQL语句本身问题

SQL 语句本身问题导致 SQL 变慢的原因有很多，以下是一些常见的原因及如何判断：

1. 低效的查询编写：

   • 使用子查询替代连接查询，或者使用笛卡尔积连接。
   • 使用嵌套循环查询，而不是使用连接或集合运算。
   • 使用 NOT IN、NOT EXISTS 而不是使用反向连接或外连接。

检查 SQL 语句的编写，看是否存在上述情况。如果有，尝试优化并比较执行计划和性能。

2. 未使用索引：

   • SQL 查询没有利用现有的索引。
   • 索引列上使用了函数或表达式。
   • 查询谓词中的条件不允许使用索引。

查看执行计划，检查是否使用了索引访问路径。如果没有使用索引，分析查询谓词以查找可能的原因。

3. 统计信息过时或不准确：

   • 数据库统计信息未及时更新。
   • 统计信息不准确，导致优化器选择了错误的执行计划。

检查数据表和索引的统计信息，如最后一次收集时间、数据行数、区分度等。如果统计信息过时或不准确，请使用 DBMS_STATS 包收集新的统计信息。

4. 多表连接顺序不当：

   • 多表连接顺序不合适，导致执行计划效率低下。
   • Oracle 优化器没有正确地确定连接顺序。

查看执行计划，检查连接顺序。尝试使用 SQL Hints（例如 LEADING、ORDERED 等）来优化连接顺序，并比较执行计划和性能。

5. 数据倾斜：

   • 数据分布不均匀，导致查询某些部分数据时性能较差。
   • 优化器基于总体统计信息选择了错误的执行计划。

查看数据分布情况。使用 DBMS_STATS 包收集扩展统计信息，如直方图，以帮助优化器更准确地选择执行计划。

6. 会话和系统级参数设置不当：

   • 排序区大小不足，导致磁盘排序。
   • 优化器参数设置不当，如 OPTIMIZER_MODE、OPTIMIZER_INDEX_COST_ADJ 等。

检查会话和系统级参数设置，如 SORT_AREA_SIZE、PGA_AGGREGATE_TARGET、优化器相关参数等。尝试调整参数并观察性能变化。

要判断 SQL 语句本身问题导致 SQL 变慢的原因，通常需要查看执行计划、统计信息、参数设置等方面的信息。可以使用Oracle 提供的工具（如 EXPLAIN PLAN、SQL*Plus、SQL Developer、Oracle Enterprise Manager 等）和诊断报告（如 AWR、ASH、ADDM 等）来获取相关信息。根据这些信息，可以采取以下措施来优化 SQL 语句：

7. 重写 SQL 查询：

   • 尝试使用连接查询替代子查询，或者将笛卡尔积连接替换为有效的连接条件。
   • 使用更高效的集合运算替代嵌套循环查询。
   • 使用 EXISTS 或反向外连接替代 NOT IN 和 NOT EXISTS。

8. 索引优化：

   • 创建合适的索引以提高查询性能。
   • 去除索引列上的函数或表达式，以便优化器可以使用索引。
   • 调整查询条件以充分利用现有索引。

9. 更新统计信息：

   • 使用 DBMS_STATS 包定期收集表和索引的统计信息。
   • 收集扩展统计信息，如直方图，以解决数据倾斜问题。

10. 优化连接顺序：

• 尝试使用 SQL Hints（例如 LEADING、ORDERED 等）来优化连接顺序。
• 比较不同连接顺序下的执行计划和性能，选择最优的执行计划。

11. 调整会话和系统级参数：

• 根据实际需求调整排序区大小、PGA_AGGREGATE_TARGET 等参数。
• 调整优化器参数（如 OPTIMIZER_MODE、OPTIMIZER_INDEX_COST_ADJ 等）以改善执行计划选择。

12. 利用 Oracle 优化器功能：

• 使用 SQL Plan Management (SPM) 来捕获、选择和演进执行计划。
• 使用 SQL Profiles 和 SQL Patches 来调整特定 SQL 语句的优化器行为。
• 利用 Adaptive Cursor Sharing (ACS) 和 Adaptive Execution Plans (AEP) 功能以适应不同的数据分布和查询需求。

13.使用10046和10053追踪会话
Oracle 提供了事件 10046 和 10053，分别用于跟踪 SQL 语句的执行情况和优化器决策过程。这两个事件可以帮助我们更深入地分析 SQL 执行效率低下的原因。

1. 使用事件 10046 跟踪 SQL 执行情况：

事件 10046 用于生成详细的 SQL 运行时跟踪信息，包括等待事件、解析、执行和获取等过程。首先，需要设置跟踪级别。跟踪级别从 1 到 12，数字越大，收集的信息越详细。常用的级别有 1（基本统计信息）、4（绑定变量信息）和 8（等待事件信息）。

执行以下步骤以启用 10046 事件跟踪：

a. 登录 SQL*Plus 并连接到数据库。
b. 执行以下命令以启用跟踪：

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

c. 运行需要跟踪的 SQL 语句。
d. 执行以下命令以停止跟踪：

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

e. 在用户跟踪文件目录（USER_DUMP_DEST）中找到生成的跟踪文件。文件名通常以 “ora” 开头，扩展名为 “.trc”。

分析跟踪文件可以找到 SQL 执行效率低下的原因，如 I/O 等待、CPU 使用、执行计划选择等。可以使用 TKPROF 工具将跟踪文件转换为更易读的格式。

2. 使用事件 10053 跟踪优化器决策过程：

事件 10053 生成优化器决策过程的详细跟踪信息。它记录了优化器在生成执行计划时所考虑的各种因素和步骤。要启用 10053 事件跟踪，执行以下步骤：

a. 登录 SQL*Plus 并连接到数据库。
b. 执行以下命令以启用跟踪：

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

c. 运行需要跟踪的 SQL 语句。
d. 执行以下命令以停止跟踪：

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

e. 在用户跟踪文件目录（USER_DUMP_DEST）中找到生成的跟踪文件。文件名通常以 “ora” 开头，扩展名为 “.trc”。

分析 10053 跟踪文件可以帮助我们了解优化器如何生成执行计划，包括表连接顺序、索引使用、优化器参数等。这有助于我们找到 SQL 执行效率低下的原因，例如执行计划选择不佳、索引未被使用等。针对这些问题，我们可以采取相应的优化措施，如重写 SQL 语句、创建或调整索引、调整优化器参数等。

在分析 10046 和 10053 跟踪文件时，请注意以下几点：

a. 阅读跟踪文件需要一定的经验和对 Oracle 数据库的深入了解。可以借助 Oracle 提供的 TKPROF 和 TRCANLZR 工具以及第三方工具（如 Trace Analyzer、Method R 等）来辅助分析。

b. 跟踪文件可能非常大，特别是在收集详细信息时。在分析跟踪文件时，要关注关键部分，如等待事件、执行计划、优化器估算等。

c. 在生产环境中使用跟踪事件时要谨慎，因为它可能对系统性能产生影响。建议在测试环境中重现问题并进行跟踪分析。

d. 分析跟踪文件后，针对性地优化 SQL 语句。注意，在对一个问题进行优化时，可能会引入另一个问题。因此，在每次更改后都要进行全面的测试以确保性能得到提升。

通过对 10046 和 10053 跟踪文件的分析，我们可以更深入地了解 SQL 执行效率低下的原因，并采取相应的优化措施。这需要数据库管理员具备一定的经验和技能，以便在复杂的场景下找到性能瓶颈并解决问题。

要在其他会话中启用 10046 和 10053 事件跟踪，请遵循以下步骤：

3. 确定目标会话的 SID 和 SERIAL#：

首先，需要获取目标会话的 SID 和 SERIAL#。可以通过查询 v$session 视图来获得这些信息。例如，假设要追踪用户名为 “SCOTT” 的会话，可以使用以下查询：

SELECT sid, serial#, username, status
FROM v$session
WHERE username = 'SCOTT';

记下查询结果中的 SID 和 SERIAL#，在接下来的步骤中将用到它们。

4. 使用 DBMS_SYSTEM 包启用 10046 和 10053 事件跟踪：

在已连接到数据库的 SQL*Plus 会话中，使用 DBMS_SYSTEM.SET_EV 过程启用事件跟踪。将以下命令中的 <SID> 和 <SERIAL#> 替换为实际的 SID 和 SERIAL# 值：

EXEC DBMS_SYSTEM.SET_EV(<SID>, <SERIAL#>, 10046, 12, '');  -- 启用 10046 事件跟踪，级别为 12
EXEC DBMS_SYSTEM.SET_EV(<SID>, <SERIAL#>, 10053, 1, '');   -- 启用 10053 事件跟踪，级别为 1

3. 等待目标会话执行 SQL 语句：

在此阶段，目标会话的 SQL 语句执行将被跟踪。根据事件级别，跟踪文件将包含执行计划、等待事件、绑定变量等详细信息。

4. 关闭事件跟踪：

在跟踪完成后，使用 DBMS_SYSTEM.CANCEL_EV 过程关闭事件跟踪。将以下命令中的 <SID> 和 <SERIAL#> 替换为实际的 SID 和 SERIAL# 值：

EXEC DBMS_SYSTEM.CANCEL_EV(<SID>, <SERIAL#>, 10046);  -- 关闭 10046 事件跟踪
EXEC DBMS_SYSTEM.CANCEL_EV(<SID>, <SERIAL#>, 10053);  -- 关闭 10053 事件跟踪

5. 分析跟踪文件：

跟踪文件通常位于数据库的 USER_DUMP_DEST 目录下，文件名以 “ora” 开头，扩展名为 “.trc”。使用 TKPROF 工具或其他第三方工具将跟踪文件转换为易于阅读的格式，然后分析该文件以找出 SQL 执行效率低下的原因。

请注意，在生产环境中启用跟踪事件可能对性能产生影响。建议在测试环境中重现问题并进行跟踪分析。

TKPROF 是一个 Oracle 提供的命令行工具，用于将 10046 跟踪文件转换成更易于阅读和分析的格式。以下是使用 TKPROF 分析 10046 跟踪文件的步骤：

a. 定位跟踪文件：

找到包含 10046 跟踪信息的文件。跟踪文件通常位于数据库的 USER_DUMP_DEST 目录下，文件名以 “ora” 开头，扩展名为 “.trc”。

b. 使用 TKPROF 分析跟踪文件：

在操作系统命令提示符下，使用以下命令运行 TKPROF。将 <input_trace_file> 替换为实际的跟踪文件名，将 <output_file> 替换为要生成的输出文件名。

tkprof <input_trace_file> <output_file> [options]

其中，[options] 是 TKPROF 支持的一些可选参数，例如：

• explain=<username>/<password>：使用指定的用户和密码解释 SQL 语句的执行计划。
• sort=<sort_options>：根据指定的选项对 SQL 语句进行排序。可用选项包括：prsela、prsepu、execpu、exerow、exeela、fchela、fchrow 等。
• sys=yes：在输出文件中包含系统调用的信息。
• table=<table_name>：将 SQL 语句的统计信息插入到指定的表中。

例如，以下命令将跟踪文件 “my_trace_file.trc” 分析为 “my_output_file.txt”，并使用 “scott/tiger” 用户的凭据解释执行计划，同时按照执行时间排序：

tkprof my_trace_file.trc my_output_file.txt explain=scott/tiger sort=exeela

b. 分析输出文件：

TKPROF 生成的输出文件包含 SQL 语句的执行统计信息，如 CPU 时间、等待事件、执行计划等。阅读输出文件，找出潜在的性能问题并采取相应的优化措施。

请注意，TKPROF 的输出文件通常包含大量信息，需要具备一定的数据库知识和经验才能有效地分析和解释这些信息。在分析跟踪文件时，关注关键部分，如等待事件、执行计划、优化器估算等。根据分析结果，可以针对性地优化 SQL 语句，例如重写 SQL 语句、创建或调整索引、调整优化器参数等。

五. 数据库参数变更

Oracle 数据库中的一些参数变更可能会导致 SQL 执行效率发生变化。以下是一些可能影响 SQL 执行效率的数据库参数：

1. optimizer_mode：优化器模式会影响 SQL 语句的执行计划。可选值包括：ALL_ROWS（默认值，以最小资源消耗优化）、FIRST_ROWS（以最快返回前几行为优化目标）、FIRST_ROWS_n（以最快返回前 n 行为优化目标，其中 n 可为 1、10、100 或 1000）。

2. optimizer_index_cost_adj：该参数用于调整索引访问的成本。较低的值会让优化器更倾向于使用索引访问。范围为 1 到 10000， 默认值为 100。

3. optimizer_index_caching：表示可用于缓存的索引块的百分比。较高的值会让优化器更倾向于使用索引访问。范围为 0 到 100， 默认值为 0。

4. db_file_multiblock_read_count：此参数表示多块读取操作一次读取的最大块数。较高的值会提高全表扫描和索引快速全扫描的效率，但可能会影响其他类型的访问。

5. cursor_sharing：该参数控制 SQL 语句绑定变量的替换策略。可选值包括：EXACT（默认值，不替换绑定变量）、FORCE（强制替换绑定变量，可能导致执行计划变化）、SIMILAR（在保证语义一致的情况下替换绑定变量，可能导致执行计划变化）。

6. pga_aggregate_target：指定进程的 PGA 工作区总大小。较小的值可能会导致大量磁盘排序和哈希操作，从而降低 SQL 执行效率。

7. sga_target 和 sga_max_size：这些参数控制系统全局区（SGA）的大小。SGA 较小可能会导致缓冲区缓存不足，增加磁盘 I/O 和库缓存争用。

8. _optimizer_adaptive_reporting_only：在 12c 版本中，该隐藏参数控制优化器自适应特性的启用。将此参数设置为 TRUE 可关闭自适应特性，从而影响执行计划。

9. sort_area_size：此参数用于设置用户会话的内存排序区域大小。较小的值可能会导致在磁盘上执行排序，从而降低 SQL 执行效率。

10. hash_area_size：此参数用于设置用户会话的哈希聚合操作的内存区域大小。较小的值可能会导致在磁盘上执行哈希操作，从而降低 SQL 执行效率。

11. bitmap_merge_area_size：此参数用于设置位图合并操作的内存区域大小。较小的值可能会导致位图合并操作性能较差。

12. star_transformation_enabled：此参数控制星型转换优化功能的启用。设置为 TRUE 时，优化器将尝试使用星型转换优化星型查询，从而可能改变 SQL 执行计划。

13. parallel_degree_policy：此参数控制并行度策略。可选值包括：MANUAL（手动，默认值）、AUTO（自动并行度）、LIMITED（受限并行度）、ADAPTIVE（自适应并行度）。更改此参数可能会影响并行执行的策略，进而影响 SQL 执行效率。

14. parallel_min_time_threshold：此参数用于设置自动并行执行的时间阈值。当执行计划的时间估计超过该阈值时，优化器将尝试并行执行。

15. result_cache_mode：此参数控制结果缓存模式。可选值包括：MANUAL（手动，默认值）和 FORCE（强制）。更改此参数可能会影响结果缓存的使用情况，进而影响 SQL 执行效率。

在调整这些参数之前，请务必充分了解它们的含义和可能的影响。建议在测试环境中验证参数更改对 SQL 执行效率的影响。在生产环境中，务必谨慎更改参数，并始终遵循 Oracle 支持的最佳实践。

六. 系统资源限制

系统资源限制可能导致 SQL 执行效率降低。以下是一些方法，用于判断系统资源限制是否导致 SQL 执行效率低下：

1. 检查 CPU 使用率：查看操作系统的性能监视器或使用数据库内置的工具（如 AWR 报告）检查 CPU 使用率。如果 CPU 使用率持续处于较高水平（如超过 80%），可能存在 CPU 资源争用问题。

2. 检查内存使用：检查操作系统的性能监视器或使用数据库内置的工具（如 AWR 报告）检查内存使用情况。如果内存使用率持续较高，可能存在内存资源争用问题。此外，查看数据库中 PGA 和 SGA 的配置和使用情况，以确定是否存在内存分配不足的情况。

3. 检查 I/O 性能：使用操作系统工具（如 iostat、sar 等）或数据库内置工具（如 AWR 报告、ADDM 报告等）检查磁盘 I/O 情况。关注 I/O 等待时间、磁盘队列长度等指标，以确定是否存在 I/O 争用问题。

4. 查看数据库等待事件：分析数据库等待事件（如 AWR 报告中的 Top 5 等待事件），确定 SQL 执行中是否存在资源争用问题。例如，‘db file sequential read’ 等待事件可能表示存在 I/O 争用，而 ‘latch: shared pool’ 等待事件可能表示存在共享池争用。

5. 检查并发会话和并行度：检查数据库中活跃会话的数量，以及并行执行的操作。过多的并发会话和过高的并行度可能导致系统资源争用，从而降低 SQL 执行效率。

6. 分析操作系统资源限制：查看操作系统的资源限制配置（如 ulimit、kernel 参数等），以确定是否存在操作系统资源限制导致的 SQL 执行效率问题。

7. 分析数据库资源配置：检查数据库资源配置，如数据库参数、资源计划、资源消耗组等，确定是否存在数据库资源限制导致的 SQL 执行效率问题。

通过以上方法，可以初步判断系统资源限制是否导致 SQL 执行效率低下。如果确定存在资源限制问题，可以根据具体情况调整系统和数据库配置，以提高 SQL 执行效率。

七. 网络延迟

网络延迟可能导致 SQL 执行效率降低，特别是在客户端和数据库服务器之间的网络通信中。以下是一些方法，用于判断网络延迟是否导致 SQL 执行效率低下：

1. ping 命令：使用 ping 命令测试客户端和数据库服务器之间的网络延迟。例如，输入 ping <数据库服务器IP或主机名>。观察返回的平均往返时间 (RTT)。如果 RTT 值较高，可能存在网络延迟问题。请注意，这仅能提供初步判断，并不能确切地确定网络延迟是否导致 SQL 执行效率低下。

2. traceroute 或 tracert 命令：使用 traceroute（Linux 系统）或 tracert（Windows 系统）命令来查看客户端和数据库服务器之间的网络路径。例如，输入 traceroute <数据库服务器IP或主机名>（Linux）或 tracert <数据库服务器IP或主机名>（Windows）。这将显示数据包在网络中经过的路由节点及其延迟。检查各个节点之间的延迟，以确定是否存在网络延迟问题。

3. 分析 SQLNet 等待事件：在 Oracle 数据库中，通过查看 AWR 报告或使用诸如 Enterprise Manager 的工具，分析 SQLNet 等待事件（例如 ‘SQLNet message from client’ 和 'SQLNet message to client’）。这些事件表示客户端和服务器之间的网络通信时间。如果这些事件的等待时间较长，可能存在网络延迟问题。

4. 使用 Oracle 提供的网络诊断工具：Oracle 提供了一个名为 tnsping 的工具，可以用于测试 TNS 监听器的可达性和响应时间。输入 tnsping <TNS服务名>，观察返回的响应时间。如果响应时间较长，可能存在网络延迟问题。

5. 分析应用程序日志：检查应用程序日志中是否有关于网络延迟的记录。例如，如果应用程序使用了 Oracle JDBC 驱动程序，可以通过设置日志级别来收集 JDBC 连接和执行的详细信息。这可以帮助确定网络延迟是否影响 SQL 执行效率。

通过以上方法，可以初步判断网络延迟是否导致 SQL 执行效率低下。如果确定存在网络延迟问题，可以与网络管理员协作，以诊断和解决网络相关问题。

如果你已经确定存在网络延迟问题，并且已经与网络管理员进行了协作，以下是一些建议和方法，用于优化网络性能和提高 SQL 执行效率：

1. 调整会话数据包大小：通过调整客户端和数据库服务器之间的会话数据包大小，可以减少网络传输中的开销。在 Oracle 数据库中，可以设置 SQL*Net 参数 SDU (Session Data Unit) 和 TDU (Transport Data Unit) 来调整数据包大小。

2. 使用连接池：为了减少网络延迟对 SQL 执行效率的影响，可以使用连接池来重用数据库连接。这将减少建立新连接所需的时间，从而降低网络延迟带来的影响。

3. 优化 SQL 语句：减少返回的数据量和减少不必要的数据传输。例如，只检索所需的列，使用分页查询以减少每次请求的数据量，使用 EXISTS 而不是 IN 子查询等。

4. 使用批处理操作：对于大量数据的插入、更新和删除操作，使用批处理操作来减少网络往返次数。

5. 考虑使用数据压缩：在客户端和服务器之间传输数据时，使用压缩技术可以减少网络传输的数据量，从而降低网络延迟带来的影响。

6. 优化网络基础设施：与网络管理员合作，检查并优化网络设备（如路由器、交换机等）的配置，以降低网络延迟。

7. 优化应用程序逻辑：评估和优化应用程序逻辑，以减少对数据库的访问次数和频率。例如，考虑将部分逻辑移至数据库层，通过存储过程、函数或触发器实现，以减少网络通信。

通过以上方法和建议，可以进一步优化网络性能，降低网络延迟对 SQL 执行效率的影响。请注意，针对特定环境的优化方法可能有所不同，因此在实施任何更改之前，请确保充分了解它们的潜在影响。

八. 数据库阻塞

数据库阻塞通常是由于一个或多个会话等待其他会话持有的锁或资源而导致的。以下是一些方法和步骤，用于判断数据库阻塞是否导致 SQL 执行效率低下：

1. 查看数据库等待事件：在 Oracle 数据库中，可以通过查看 AWR 报告、ASH 报告或使用 Enterprise Manager 等工具来分析数据库等待事件。如果发现锁等待事件（如 ‘enq: TX - row lock contention’、‘enq: HW - contention’ 等）占比较高，可能存在数据库阻塞问题。

2. 使用 vsession 和 vlock 视图：在 SQL*Plus 或其他 SQL 工具中，查询 vsession 和 vlock 视图，找出正在等待锁的会话。以下是一个查询示例：

   SELECT s.sid, s.serial#, s.username, s.status, s.wait_class, s.seconds_in_wait, l.type, l.lmode, l.request
   FROM v$session s
   JOIN v$lock l ON s.sid = l.sid
   WHERE s.wait_class != 'Idle' AND l.request > 0;
   

   如果查询结果显示有多个会话在等待锁，可能存在数据库阻塞问题。

3. 使用 DBA_WAITERS 和 DBA_BLOCKERS 视图：在 SQL*Plus 或其他 SQL 工具中，查询 DBA_WAITERS 和 DBA_BLOCKERS 视图。这两个视图提供了有关阻塞会话和等待会话的详细信息。以下是一个查询示例：

   SELECT w.waiting_session, w.blocking_session, w.wait_time, w.lock_type
   FROM DBA_WAITERS w
   JOIN DBA_BLOCKERS b ON w.blocking_session = b.holding_session;
   

   如果查询结果显示有多个会话在等待锁，可能存在数据库阻塞问题。

4. 使用自动数据库诊断监视器 (ADDM) 报告：ADDM 报告提供了有关数据库性能问题的详细信息，包括锁争用和阻塞。检查 ADDM 报告中的 “Global Cache” 部分，以查看是否存在锁争用或阻塞问题。

5. tanel poder几个脚本会很有用

@swc sid||':'||serial#||':'||event||':'||username||':'||inst_id||':'||username 1=1

@dash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name||':'||objt  session_type='FOREGROUND' "timestamp'2023-04-20 00:00:00'" "timestamp'2023-04-21 00:00:00'" 

@dash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name  session_type='FOREGROUND' sysdate-1 sysdate

@ash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name||':'||objt  session_type='FOREGROUND' "timestamp'2023-04-20 00:00:00'" "timestamp'2023-04-21 00:00:00'" 

@ash_wait_chains username||':'||':'||session_id||':'||SESSION_SERIAL#||':'||event2||':'||program2||':'||time_model_name  session_type='FOREGROUND' sysdate-1 sysdate

通过以上方法，可以初步判断数据库阻塞是否导致 SQL 执行效率低下。如果确定存在数据库阻塞问题，可以进一步分析锁争用的原因，并采取相应的优化措施。例如，优化事务处理逻辑，减少长时间持有锁的操作，或使用乐观锁等。

九. 应用程序设计问题

要判断应用程序设计问题是否导致 SQL 执行效率低下，可以参考以下方法和命令：

1. 检查 SQL 执行统计信息：
   使用 v$sql 或 v$sqlarea 视图查找执行次数高、CPU 时间长或磁盘读取次数多的 SQL 语句。例如：

   SELECT sql_id, executions, elapsed_time, cpu_time, disk_reads, buffer_gets
   FROM v$sql
   WHERE buffer_gets > 10000
   ORDER BY elapsed_time DESC;
   

2. 分析 SQL 语句执行计划：
   使用 EXPLAIN PLAN 命令或 DBMS_XPLAN.DISPLAY_CURSOR 函数查看 SQL 语句的执行计划，分析是否存在全表扫描、笛卡尔积连接等低效操作。例如：

   EXPLAIN PLAN FOR SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id;
   
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   

3. 分析会话等待事件：
   使用 v$session_event 或 v$session_wait 视图查看会话等待事件，检查是否存在锁等待、磁盘 I/O 等待等性能瓶颈。例如：

   SELECT event, total_waits, time_waited
   FROM v$session_event
   WHERE sid = 123;
   

4. 检查数据库性能诊断报告：
   生成 AWR 报告、ASH 报告或使用 Enterprise Manager 工具分析数据库性能，找出潜在的应用程序设计问题。例如，创建 AWR 报告：

   SELECT * FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_text(<DBID>, <INSTANCE_NUMBER>, <BEGIN_SNAP_ID>, <END_SNAP_ID>));
   

5. 使用 SQL Trace 和 TKPROF 工具：
   使用 SQL Trace 功能追踪会话中的 SQL 语句执行情况，然后使用 TKPROF 工具分析生成的 trace 文件。例如，启用 SQL Trace 和 TKPROF 分析：

   ALTER SESSION SET tracefile_identifier = 'my_trace';
   ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
   
   -- 在此执行 SQL 语句
   
   ALTER SESSION SET EVENTS '10046 trace name context off';
   

   使用 TKPROF 分析 trace 文件：

   tkprof my_trace.trc my_trace_analysis.txt sys=no explain=user/password
   

结合以上方法和命令，可以找出潜在的应用程序设计问题，从而针对性地优化 SQL 语句和应用程序代码。注意，针对性能问题的调优可能需要多次迭代，不断地监控和分析数据库性能，才能找到最佳的解决方案。

十. 其他数据库对象问题

其他数据库对象问题可能导致 SQL 执行效率变低，具体方法和命令如下：

1. 索引问题：

   • 索引损坏：使用 ANALYZE INDEX <index_name> VALIDATE STRUCTURE; 检查索引结构，修复损坏的索引。
   • 索引失效：重新收集索引统计信息，例如：EXEC DBMS_STATS.GATHER_INDEX_STATS('<schema_name>', '<index_name>');。
   • 使用不当的索引：检查 SQL 语句的执行计划，优化 SQL 语句或修改索引以提高执行效率。

2. 数据库对象统计信息过期或不准确：

   • 重新收集表、索引和列的统计信息，例如：EXEC DBMS_STATS.GATHER_SCHEMA_STATS('<schema_name>');。

3. 分区表问题：

   • 分区键不合理：分析分区键的选择，优化分区键以提高查询效率。
   • 分区表统计信息不准确：重新收集分区表和子分区的统计信息，例如：EXEC DBMS_STATS.GATHER_TABLE_STATS('<schema_name>', '<table_name>', granularity => 'PARTITION');。

4. 触发器和存储过程问题：

   • 检查触发器和存储过程中的 SQL 语句，优化逻辑以提高执行效率。
   • 检查触发器是否导致行级锁争用，优化触发器逻辑以减少锁争用。

5. 数据库对象依赖关系问题：

   • 检查视图、同义词和存储过程的依赖关系，优化对象结构以提高查询效率。
   • 使用 DBMS_UTILITY.COMPILE_SCHEMA 编译无效对象，例如：EXEC DBMS_UTILITY.COMPILE_SCHEMA('<schema_name>');。

6. LOB 对象问题：

   • 优化 LOB 存储参数以提高查询效率，例如：修改 LOB 段的存储子句，使用 CACHE 或 NOCACHE 选项。
   • 重新组织 LOB 数据以提高查询效率，例如：使用 DBMS_REDEFINITION 重新定义 LOB 列。

7. 其他对象问题：

   • 使用 DBMS_REDEFINITION 在线重新组织表以提高查询效率。
   • 清理无用的数据库对象，例如：删除不再使用的表、索引、视图和存储过程。

通过结合以上方法和命令，可以检查和优化数据库对象，从而提高 SQL 执行效率。请注意，性能调优可能需要多次迭代，不断地监控和分析数据库性能，才能找到最佳的解决方案。

十一、偷梁换柱替换执行计划

1、使用sql_profile直接替换

1、停用原基线
SQL> declare 
  2   rs pls_integer;
  3   begin
  4     rs:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  5     plan_name=>'SQL_PLAN_a6bmz0rdcvsk7ecfa9855',
  6     attribute_name=>'ENABLED',
  7     attribute_value=>'NO');
  8  end;
  9  /

PL/SQL procedure successfully completed.

2、连接新基线

SQL>   declare 
  2      ln_ps pls_integer;
  3   begin
  4      ln_ps:=dbms_spm.load_plans_from_cursor_cache
  5      (sql_id=>'8uvxtbn283qwz',                    --带hint的sql
  6      plan_hash_value=>3321871023,
  7      sql_handle=>'SQL_a32e7f05dacde247'           --不带hint的sql_handle
  8      );
  9   end;
 10   /

PL/SQL procedure successfully completed.

SQL> select count(*) from t2 where deptno=10;

  COUNT(*)
----------
    524289

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bt33t77y2w9mf, child number 1
-------------------------------------
select count(*) from t2 where deptno=:"SYS_B_0"

Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  1105 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |   524K|  1536K|  1105   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_a6bmz0rdcvsk71c6cf506 used for this statement

2、使用coe_xfr_sql_profile脚本替换执行计划

• 方法1、替换sql_plan_id

@coe_xfr_sql_profile.sql 6wk7ggz469b7u 1601196873 --分别传入sqlid和需要的执行计划，之后执行生成的脚本

-方法2、替换outline

@coe_xfr_sql_profile 6wk7ggz469b7u 2296882198  --慢

@coe_xfr_sql_profile 6wk7ggz469b7u 1601196873  --块

将慢sql得outline替换为快sql得outline

十二、其它常用脚本

--统计信息收集时间
select OWNER,TABLE_NAME,TABLESPACE_NAME,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') from dba_tables where table_name='xxx';

--统计信息是否过期
col SUBPARTITION_NAME for a50
SELECT SUBPARTITION_NAME,OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS,OBJECT_TYPE,PARTITION_NAME from dba_tab_stATistics where owner='xxx' and TABLE_NAME='xxxx' ;

--sql内容
select sql_id,SQL_TEXT from v$sql  where  SQL_TEXT like

--历史执行计划
SELECT distinct sql_id,plan_hash_value,to_char(timestamp, 'yyyy-mm-dd  hh24:mi:ss') timestamp from dba_hist_sql_plan where sql_id='0516w6n85ycsp' order by timestamp;

--历史执行计划详细
col options for a15;
    col operation for a20;
    col object_name for a20;
    select to_char(plan_hash_value),id,operation,options,object_name,depth,cost,to_char(timestamp,'yyyymmdd hh24:mi:ss') from dba_hist_sql_plan where sql_id='0516w6n85ycsp'  and plan_hash_value in (2178614564,2370451839) order by plan_hash_value,id,timestamp;

--awr执行计划
select* from table(dbms_xplan.display_awr('0516w6n85ycsp',null))；

select * from table(dbms_xplan.display_cursor('&sql_id',null,'TYPICAL PEEKED_BINDS')); 

--清理执行计划游标
select address,to_char(hash_value) from v$sqlarea where sql_id='0516w6n85ycsp';

exec dbms_shared_pool.purge('000000020430DBB0,274674453','C');

--历史执行计划
col snap_id for 99999999
col date_time for a30
col plan_hash for 9999999999
col executions for 99999999
col avg_etime_s heading 'etime/exec' for 9999999.99
col avg_lio heading 'buffer/exec' for 99999999999
col avg_pio heading 'diskread/exec' for 99999999999
col avg_cputime_s heading 'cputim/exec' for 9999999.99
col avg_row heading 'rows/exec' for 9999999
select * from(select distinct s.snap_id,to_char(s.begin_interval_time,'mm/dd/yy_hh24mi') || to_char(s.end_interval_time,'_hh24mi') date_time,
sql.plan_hash_value plan_hash,sql.executions_delta executions,(sql.elapsed_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_etime_s,
sql.buffer_gets_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_lio,
sql.disk_reads_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_pio,
(sql.cpu_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_cputime_s,
sql.rows_processed_total/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_row
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql.instance_number =(select instance_number from v$instance)
and sql.dbid =(select dbid from v$database)
and s.snap_id = sql.snap_id
and sql_id = trim('&sql_id') order by s.snap_id desc)
where rownum <= 100;


--检查spm
select sql_handle, plan_name, origin from dba_sql_plan_baselines where sql_text like 'select /*for_test*/ * from test1%';

--高水位
SELECT table_name,ROUND ( (blocks * 8), 2) "高水位空间 k",ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024)- blocks * 8 * 10 / 100),2)" 浪费空间 k" FROM dba_tables WHERE temporary = 'N'   and TABLE_NAME ='CRM_DATASERV_FIGURE'   ORDER BY 5 desc;

--yong.zhao
--查询SQL历史执行性能消耗情况，默认sysdate-7，可修改
select 'GV$' flag,
       0 snap_id,
       inst_id,
       plan_hash_value phv,
       executions execs,
       disk_reads reads,
       disk_reads / decode(executions, NULL, 1, 0, 1, executions) reads_per,
       buffer_gets gets,
       buffer_gets / decode(executions, NULL, 1, 0, 1, executions) gets_per,
       rows_processed,
       rows_processed / decode(executions, NULL, 1, 0, 1, executions) rows_per,
       elapsed_time/1000 elap_ms,
       (elapsed_time/1000) / decode(executions, NULL, 1, 0, 1, executions) elap_per_ms
  from gv$sql
 where sql_id='&sql_id'
 union all
select to_char(sht.begin_interval_time,'dd hh24:mi')||'--'||to_char(sht.end_interval_time,'hh24:mi') flag,
               sta.snap_id,
               sta.instance_number inst,
               sta.plan_hash_value phv,
               sta.executions_delta execs,
               sta.disk_reads_delta reads,
               sta.disk_reads_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) reads_per,
               sta.buffer_gets_delta gets,
               sta.buffer_gets_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) gets_per,
               sta.rows_processed_delta,
               sta.rows_processed_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) rows_per,
               sta.elapsed_time_delta/1000 elap_ms,
               (sta.elapsed_time_delta/1000) /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) elap_per_ms
          from dba_hist_sqlstat sta,dba_hist_snapshot sht
         where 1=1
            and sta.instance_number=sht.instance_number
            and sta.snap_id=sht.snap_id
            and sht.begin_interval_time>= sysdate-7
            and sta.sql_id='&sql_id'
 order by 1,2;