暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / Oracle-SQL性能优化（6）-异常SQL处理

Oracle-SQL性能优化（6）-异常SQL处理

原创 大柏树 2022-11-04
1844

本节主要介绍如何查找需要进行优化的异常SQL并进行处理。
影响SQL执行效率的因素主要包括以下几点：
统计信息

  • 陈旧的统计信息
  • 错误的优化器参数配置
  • 数据库迁移前后优化器的改变
  • 频繁的数据量变化
    游标共享问题
  • 变量窥探
  • 使用常量
    SQL语句编写问题
    资源争用问题
  • 硬件资源不足
  • 锁或者latch等的争用问题
    以上几种原因都会导致主机CPU的使用率增加、主机I/O异常繁忙、语句执行时间异常增加、数据库整体性能下降、应用超时等问题。

1.定位问题SQL

其他人：应用反应慢，看数据库有啥问题没 ？
并没有具体的SQL，这时候就需要我们去定位SQL，然后优化。一般我们可以通过生成AWR报告、查看等待事件、查看TOP进程、根据应用定位等方法来定位问题SQL。

1.1.AWR报告

--默认每小时生成一个快照
SQL> @?/rdbms/admin/awrrpt.sql
--如果需要更短时间的，可以手动生成快照
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SQL>

1.2.根据TOP进程

主机资源异常，找到排行靠前点的进程（top process），然后在数据库找到执行的sql

select p.inst_id,
       p.spid,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine
   from gv$session a,gv$process p
   where a.inst_id=p.inst_id
   and   a.paddr=p.addr
   and   p.spid=47340;

1.3.应用

业务反馈超时，检查对应的应用模块正在执行的SQL语句。

1.4.等待事件

数据库性能整体下降，检查异常等待事件

set linesize 150 pages 100
col event for a60
select event#,event,count(*) from v$session where status='ACTIVE' and event not like '%message%' group by event#, event order by 3;

select a.inst_id,
       a.process,
       a.sid
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine
from gv$session a where event='db file scattered read';

2.SQL健康检查

介绍脚本(SQL Tuning Health Check，SQLHC)脚本的基础知识。官方提供的一个用于sql调优收集信息的脚本。

2.1.下载

Doc ID：1366133.1
我下载下来传到墨天轮了。在这里，无需墨汁
或者：https://www.modb.pro/download/1097703

2.2.上传到服务器解压

[oracle@single ~]$ cd sqlhc/
[oracle@single sqlhc]$ ll
total 420
-rw-r--r-- 1 oracle oinstall  48747 Mar 18  2021 sqldx.sql
-rw-r--r-- 1 oracle oinstall 306623 Jun 17 09:09 sqlhc.sql
-rw-r--r-- 1 oracle oinstall   3611 Mar 18  2021 sqlhc_sta.sql
-rw-r--r-- 1 oracle oinstall   2082 Aug 21  2021 sqlhc_tcb.sql
-rw-r--r-- 1 oracle oinstall  60394 Mar 18  2021 util_planx.sql
drwxr-xr-x 4 oracle oinstall   4096 Oct 30  2021 utl
[oracle@single sqlhc]$

2.3.权限

需要DBA权限

REM   1. Execute as SYS or user with DBA role or user with access
REM      to data dictionary views.
REM   2. The SQL for which this script is executed must be
REM      memory-resident.

2.4.使用

可以看到，总共有5个sql脚本。直接来试试具体的作用。

REM PARAMETERS
REM   1. Oracle Pack license (Tuning or Diagnostics or None) T|D|N
REM   2. SQL_ID of interest.
REM
REM EXECUTION
REM   1. Start SQL*Plus connecting as SYS or user with DBA role or
REM      user with access to data dictionary views.
REM   2. Execute script sqlhc.sql passing values for parameters.
REM
REM EXAMPLE
REM   # sqlplus / as sysdba
REM   SQL> START [path]sqlhc.sql [T|D|N] [SQL_ID]
REM   SQL> START sqlhc.sql T 51x6yr9ym5hdc

使用的时候主要有两个参数

  • 功能： T：调优，较为常用 D：诊断 N：无
  • SQL_ID： 可以从awr或者v$sql等获取
--先随便找一个实验sql的sql_id
SQL> SELECT /* TARGET SQL */ * from test where owner='SCOTT';
SQL> SELECT sql_id, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
fjw0mg8pm3zuy
SELECT /* TARGET SQL */ * from test wher

--1.sqldx.sql
这个脚本需要两个参数，T：调优  H：指定输出格式为html
SQL> start sqldx.sql T H fjw0mg8pm3zuy
--可以看到，生成了一个压缩包
[oracle@single sqlhc]$ ll
total 512
-rw-r--r-- 1 oracle oinstall  91748 Nov  4 14:47 sqldx_20221104_144728.zip
-rw-r--r-- 1 oracle oinstall  48747 Mar 18  2021 sqldx.sql
-rw-r--r-- 1 oracle oinstall 306623 Jun 17 09:09 sqlhc.sql
-rw-r--r-- 1 oracle oinstall   3611 Mar 18  2021 sqlhc_sta.sql
-rw-r--r-- 1 oracle oinstall   2082 Aug 21  2021 sqlhc_tcb.sql
-rw-r--r-- 1 oracle oinstall  60394 Mar 18  2021 util_planx.sql
drwxr-xr-x 4 oracle oinstall   4096 Nov  4 14:40 utl
[oracle@single sqlhc]$
--生成的内容太详细了，都懒得看  -_-
[oracle@single sqlhc]$ ll
total 608
-rw-r--r-- 1 oracle oinstall  12319 Nov  4 14:47 sqldx_20221104_144728_12242140451241408934_force_html.zip
-rw-r--r-- 1 oracle oinstall  28543 Nov  4 14:47 sqldx_20221104_144728_fjw0mg8pm3zuy_html.zip
-rw-r--r-- 1 oracle oinstall   7736 Nov  4 14:47 sqldx_20221104_144728_fjw0mg8pm3zuy_log.zip
-rw-r--r-- 1 oracle oinstall  23150 Nov  4 14:47 sqldx_20221104_144728_global_html.zip
-rw-r--r-- 1 oracle oinstall  18904 Nov  4 14:47 sqldx_20221104_144728_table_html.zip
-rw-r--r-- 1 oracle oinstall  91748 Nov  4 14:47 sqldx_20221104_144728.zip
2.sqlhc.sql
SQL> start sqlhc.sql T fjw0mg8pm3zuy
[oracle@single sqlhc]$ ll
total 480
-rw-r--r-- 1 oracle oinstall  48747 Mar 18  2021 sqldx.sql
--同样，生成了一个压缩包
-rw-r--r-- 1 oracle oinstall  59777 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy.zip
-rw-r--r-- 1 oracle oinstall 306623 Jun 17 09:09 sqlhc.sql
-rw-r--r-- 1 oracle oinstall   3611 Mar 18  2021 sqlhc_sta.sql
-rw-r--r-- 1 oracle oinstall   2082 Aug 21  2021 sqlhc_tcb.sql
-rw-r--r-- 1 oracle oinstall  60394 Mar 18  2021 util_planx.sql
--解压
[oracle@single sqlhc]$ ll
-rw-r--r-- 1 oracle oinstall    353 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy_10_sql_tuning_advisor.out
-rw-r--r-- 1 oracle oinstall   7907 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy_1_health_check.html
-rw-r--r-- 1 oracle oinstall 165607 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy_2_diagnostics.html
-rw-r--r-- 1 oracle oinstall   3562 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy_3_execution_plans.html
-rw-r--r-- 1 oracle oinstall   4661 Nov  4 14:53 sqlhc_20221104_1452_fjw0mg8pm3zuy_4_sql_detail.html
--其实从名字就可以大概看出生成的报告的内容
sql_tuning_advisor.out  优化建议
health_check.html    统计信息、索引、表等信息
diagnostics.html     sql文本、执行计划绑定情况、等待事件等信息
execution_plans.html   sql文本，当前和历史执行计划
sql_detail.html      sql绑定、直方图等信息
3.sqlhc_sta.sql
SQL> start sqlhc_sta.sql T fjw0mg8pm3zuy
sqlhc_sta_fjw0mg8pm3zuy.out   --优化建议
--可以看到，此sql是sqlhc.sql脚本的一部分，这是单独生成优化建议的部分，其实就是运行了sql_tuning。
4.sqlhc_tcb.sql
用于把目标SQL导出，然后导入到其他库测试。
--其实就是执行了如下的包，生成xml文件，用于导入
[oracle@single sqlhc]$ cat sqlhc_tcb_fjw0mg8pm3zuy.out
SQL> 
SQL> set serveroutput on
SQL> declare
  2    tc_out clob;
  3  begin
  4  dbms_sqldiag.export_sql_testcase(directory=>'^^3', sql_id=>'^^2', exportMetadata=>TRUE, exportData=>FALSE, testcase=>tc_out);
  5  end;
  6  /
Enter value for 3: DMP

PL/SQL procedure successfully completed.

SQL> 
SQL> spool off
--生成文件如下：
-rw-r----- 1 oracle oinstall 192512 Nov  4 15:12 oratcb1_000F026F0001dpexp.dmp
-rw-r--r-- 1 oracle oinstall    766 Nov  4 15:12 oratcb1_000F026F0001dpexp.log
-rw-r--r-- 1 oracle oinstall   4842 Nov  4 15:11 oratcb1_000F026F0001dpexp.sql
-rw-r--r-- 1 oracle oinstall   4195 Nov  4 15:12 oratcb1_000F026F0001dpimp.sql
-rw-r--r-- 1 oracle oinstall   1908 Nov  4 15:12 oratcb1_000F026F0001main.xml
-rw-r--r-- 1 oracle oinstall    244 Nov  4 15:11 oratcb1_000F026F0001ol.xml
-rw-r--r-- 1 oracle oinstall   2768 Nov  4 15:11 oratcb1_000F026F0001README.txt
-rw-r--r-- 1 oracle oinstall   1190 Nov  4 15:11 oratcb1_000F026F0001sql.xml
-rw-r--r-- 1 oracle oinstall    847 Nov  4 15:12 oratcb1_000F026F0001ssimp.sql
-rw-r--r-- 1 oracle oinstall 109520 Nov  4 15:12 oratcb1_000F026F0001.trc
-rw-r--r-- 1 oracle oinstall     45 Nov  4 15:11 oratcb1_000F026F0001ts.xml
-rw-r--r-- 1 oracle oinstall    462 Nov  4 15:12 oratcb1_000F026F0001xplf.sql
-rw-r--r-- 1 oracle oinstall    821 Nov  4 15:12 oratcb1_000F026F0001xplo.sql
-rw-r--r-- 1 oracle oinstall    436 Nov  4 15:12 oratcb1_000F026F0001xpls.sql
-rw-r--r-- 1 oracle oinstall   2650 Nov  4 15:12 oratcb1_000F026F0001xpl.txt
--导入操作如下：
--     Usually, you only need to reference the last file (metadata file) 
--     for importing a test case.
--
--     The following is an example PL/SQL script for TCB IMPORT.
--     It uses the metadata file name (xxxxmain.xml) as an input argument
--     when calling the import API.
--     (You may have to modify this script for the right arguments)
-----------------------------------------------------------------

grant connect, dba, resource, query rewrite to tcb identified by tcb;

create directory TCB_IMP_DIR as '<DIRECTORY_PATH_4_TCB_IMPORT>';

conn tcb/tcb;

exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,         -
                                      filename  => '<TCB_METADATA>main.xml');
5.util_planx.sql
SQL> start util_planx.sql T fjw0mg8pm3zuy
--生产了如下文件，内容为目标SQL的详细执行历史
planx_fjw0mg8pm3zuy_20221104_152126.txt

SQLHC脚本我们最常用的就是sqlhc.sql，可以生成详细的信息以及优化建议。

3.SQL计划管理

Oracle 11g R1引入了SQL Plan Management（SPM，SQL计划管理），SPM是一种预防性机制，使优化程序可以自动管理执行计划，从而确保数据库使用的是已知的或者经过验证的最优计划。
当系统开启自动SQL Plan Baseline（SQL计划基线）捕获时，CBO会记录会话内执行的任意SQL，并把SQL的相关信息存储为SQL计划基线。第一次执行的语句由于没有基础数据，因此会被当成最优的执行计划。当第二次执行时，CBO会与存储在SQL计划基线中的计划进行比较，如果新执行计划的性能有所改善，那么SPM会把新的执行计划标记为该语句最优的执行计划。
默认情况下，CBO会使用SQL计划基线中最优的执行计划。而对于异常SQL自动捕获，则需要设置参数optimizer_capture_sql_plan_baselines的值为true，默认是false；

SQL> show parameter optimizer_capture_sql_plan_baselines
NAME				     TYPE	 VALUE
------------------------------------ ----------- --------------------------
optimizer_capture_sql_plan_baselines boolean	 FALSE
SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile;
System altered.
--重启
SQL> show parameter optimizer_capture_sql_plan_baselines

NAME				     TYPE	 VALUE
------------------------------------ ----------- --------------------------
optimizer_capture_sql_plan_baselines boolean	 TRUE
SQL>

示例：手动加载执行计划到SQL计划基线

--1.执行SQL
SQL> set autotrace on
SQL> set line 150 pagesize 0
SQL> select * from scott.dept where deptno=10;
	10 ACCOUNTING	  NEW YORK


Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669

---------------------------------------------------------------------------------------
| Id  | Operation		    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	      |     1 |    20 |     1	(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1	(0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | PK_DEPT |     1 |       |     0	(0)| 00:00:01 |
---------------------------------------------------------------------------------------
--查看对应的plan_hash_value
SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%scott.dept where deptno=10%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ---------------------------------------------
3pt0tqrg7u34x      2852011669 select * from scott.dept where deptno=10
--2.将以上SQL语句加载至SQL计划基线中
SQL> DECLARE
 my_plans PLS_INTEGER;
BEGIN
my_plans:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'3pt0tqrg7u34x',PLAN_HASH_VALUE=>2852011669,FIXED=>'NO',ENABLED=>'YES');
END;
/   
PL/SQL procedure successfully completed.
SQL>
--3.查看DBA_SQL_PLAN_BASELINES视图以确认：
SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXT 
from DBA_SQL_PLAN_BASELINES 
where ACCEPTED='YES' 
AND SQL_TEXT LIKE '%scott.dept where deptno=10%%'
order by LAST_MODIFIED;  

SQL_HANDLE		       PLAN_NAME		      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_TEXT
---------------------------------------------------------------------------
SQL_59f9d6822a74ea01	       SQL_PLAN_5myfqh8p79uh10348d329 YES YES
select * from scott.dept where deptno=10
--4.在以上的select语句中加入hint改变执行计划，强制全表扫描。
SQL> select /*+ full(dept) */ * from scott.dept where deptno=10;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
--5.查找对应的PLAN HASH VALUE
SQL> select sql_id,plan_hash_value,substr(sql_text,1,40) from v$sql where sql_text like '%select /*+ full(dept) */%';

SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
SUBSTR(SQL_TEXT,1,40)
--------------------------------------------------------------------------------
54f86ypst44ar	   3383998547
select /*+ full(dept) */ * from scott.de

02qywzbj0v69p	    903671040
select sql_id,plan_hash_value,substr(sql
--6.将加有hint的执行计划加载到原SQL的SPM中
SQL> DECLARE
 my_plans PLS_INTEGER;
BEGIN my_plans:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'54f86ypst44ar',PLAN_HASH_VALUE=>3383998547,FIXED=>'NO',ENABLED=>'YES');
END;
/  2    3    4    5    6  
PL/SQL procedure successfully completed.
SQL>
--7.查看DBA_SQL_PLAN_BASELINES视图以确认
SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXT 
from DBA_SQL_PLAN_BASELINES 
where ACCEPTED='YES' 
AND SQL_TEXT LIKE '%scott.dept where deptno=10%%'
order by LAST_MODIFIED;   2    3    4    5  

SQL_HANDLE		       PLAN_NAME		      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_59f9d6822a74ea01	       SQL_PLAN_5myfqh8p79uh10348d329 YES YES
select * from scott.dept where deptno=10

SQL_e3f11e7b1ca9bc27	       SQL_PLAN_f7w8ygcfamg17e44a17a0 YES YES
select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXT
from DBA_SQL_PLAN_BASELINE

SQL_5a9252a5db7c8cdb	       SQL_PLAN_5p4kknrdrt36v0e23be79 YES YES
select /*+ full(dept) */ * from scott.dept where deptno=10

SQL_HANDLE		       PLAN_NAME		      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SQL>
--8.删除第一个SQL执行计划
SQL> DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_59f9d6822a74ea01',PLAN_NAME=>'SQL_PLAN_5myfqh8p79uh10348d329');
END;
/   

PL/SQL procedure successfully completed.

SQL> 

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXT
  2  from DBA_SQL_PLAN_BASELINES
  3  where ACCEPTED='YES'
  4  AND SQL_TEXT LIKE '%scott.dept where deptno=10%%'
  5  order by LAST_MODIFIED;

SQL_HANDLE                     PLAN_NAME                      ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ ------- -------- --------------------------------------------------------------------------------
SQL_e3f11e7b1ca9bc27           SQL_PLAN_f7w8ygcfamg17e44a17a0 YES     YES      select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXT
                                                                               from DBA_SQL_PLAN_BASELIN
SQL_5a9252a5db7c8cdb           SQL_PLAN_5p4kknrdrt36v0e23be79 YES     YES      select /*+ full(dept) */ * from scott.dept where deptno=10

SQL> 

--9.重新执行SQL语句
SQL> select * from scott.dept where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    20 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

注：本文参考于：《DBA攻坚指南》

墨力计划oracle
最后修改时间：2022-11-08 10:21:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Oracle 性能优化（持续更新中）（共15篇）
性能优化

评论