利用Oracle SPM调整数据库效率（oracle spm设置）

利用Oracle SPM调整数据库效率

Abstract

在Oracle数据库管理中，调整数据库效率是至关重要的一项工作。本文介绍了一个实用的方法——使用Oracle SPM（SQL Plan Management）调整数据库性能。Oracle SPM是一个强大的工具，可以帮助您收集数据库的执行计划，并生成合适的SQL执行计划来提高数据库的查询效率。

Introduction

Oracle SPM（SQL Plan Management）是Oracle数据库的一个功能模块，可以自动识别出性能问题并对其进行调整。它通过自动收集SQL执行计划并将其与原始执行计划进行比较，从而找到最优的执行计划。这样一来，您就可以通过Oracle SPM来提高数据库的效率，使其能够在短时间内提供更快的数据查询结果，从而提高业务用户的满意度。

Oracle SPM的使用前提

在使用Oracle SPM之前，您需要对其具有一定的了解并具有一些预备知识。以下是使用Oracle SPM的前提条件：

1. 安装了Oracle 11g版本以上的数据库。

2. 拥有管理员权限和常用的命令行工具。

3. 熟悉SQL语句、数据表及其字段属性。

4. 理解Oracle SPM的实现原理及其使用方式。

5. 熟练掌握Oracle调优的一些基本思想。

Oracle SPM的使用方法

一、创建SPM baseline（基线）

在执行计划变化较大时，建议创建SQL Plan Baseline来捕获多个执行计划。这里我们创建SPM基线的步骤如下：

1. 手动执行SQL任务，获得完整执行计划，命令如下：

SQL> expln plan for select * from customers where cust_name like ‘%10086%';

2. 保存执行计划到oracle_spm.sql_plan_table表中，命令如下：

SQL> @?/rdbms/admin/spcreate.sql SQL_PLAN_TABLE

3. 将执行计划加载到数据库中：

SQL> @?/rdbms/admin/sprelsql.sql

4. 捕捉SQL Plan Baseline：

SQL> SELECT sql_handle, plan_name, enabled, accepted
  2 FROM dba_sql_plan_baselines
  3 WHERE sql_text LIKE 'SELECT * FROM customers WHERE cust_name like ''%10086%''';

二、激活SPM基线

通过执行SQL Plan Baseline，您可以使SPM基线生效，使Oracle数据库优先选取最快的查询方法。以下是SPM基线激活的步骤：

1. 使用如下命令启用Oracle数据库的SPMV2功能：

SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = true scope=both;

2. 验证SPM基线是否生效：

SQL> SELECT sql_handle, plan_name, enabled, accepted
  2 FROM dba_sql_plan_baselines
  3 WHERE sql_text LIKE 'SELECT * FROM customers WHERE cust_name like ''%10086%''';

三、测试SQL Plan Baseline

在使用Oracle SPM之前，请确保您已经了解了您所使用的SQL语句。以下是测试SQL Plan Baseline的步骤：

1. 执行SQL Plan Baseline：

SQL> SELECT /*+ SQL_PLAN_BASELINE(select /*+ full(customers) */ * from customers where cust_name like '%10086%') */
  2   *
  3 FROM customers
  4 WHERE cust_name like '%10086%';

2. 检查性能：

SQL> SELECT elapsed_time, sql_id, plan_hash_value, operation, options
  2 FROM v$sql WHERE sql_id IN ('gbdzzjku5n9ks', 'hpfz699g953ha');

运行结果显示两个SQL的hash值，这是因为Oracle已经开始进行自动替换最优执行计划。

四、监控SPMV2的性能

您可以使用Oracle在V$SYSTEM_FIX_CONTROL表中提供的内部参数来监控SPMV2的性能，以确保您的SQL Plan Baseline一直处于最优状态。以下是在Oracle中监控SPMV2性能的步骤：

1. 使用以下命令找到SQL Plan Baseline：

SQL> SELECT PLAN_NAME, SQL_TEXT
  2 FROM DBA_SQL_PLAN_BASELINES
  3 WHERE SQL_TEXT LIKE '%select * from customers where cust_name like ';

2. 使用以下命令列出SQL Plan Baseline的详细信息：

SQL> SELECT ENABLED, ACCEPTED_COUNT, REPRODUCED_COUNT, EXECUTIONS, FIRST_CAPTURED, LAST_CAPTURED
  2 FROM DBA_SQL_PLAN_BASELINES
  3 WHERE SQL_HANDLE = 'g7pvu5zvyygny';

3. 监视SPMV2 IO性能：

SQL> SELECT FILE# || BLOCK# AS IO_OFFSET, SQL_ID, SQL_EXEC_ID, PLAN_HASH_VALUE
  2 FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&sql_id'
  3 AND SQL_EXEC_ID= &sql_exec_id
  4 AND PLAN_HASH_VALUE= &plan_hash_value
  5 ORDER BY 1, 2, 3, 4;

您可以输入SQL ID、EXEC ID和PLAN HASH VALUE 3个参数，以获得最好的查询效果。

结论

通过使用Oracle SPM，您可以提高数据库的查询效率，从而更快地获取到数据，减少业务用户的等待时间，提高其满意度。祝您的数据库调优工作取得良好的成果！