分类目录归档：Oracle性能优化

oracle性能优化

failed parse elapsed time过大分析案例

发表于 2019 年 6 月 11 日 由 惜分飞

朋友公司做压力测试,awr比较异常,让给看看
1-1

这里显示数据库db time较大,数据库应该比较繁忙,主要等待事件为:library cache: mutex X
1-4
但是Load Profile显示Parses (SQL)和Hard parses (SQL)均不大
1-2
但是发现failed parse elapsed time特别大,也就是说这个库出现该问题,主要可能是由于sql语句执行解析失败导致,而解析失败最大的可能性就是sql语句语法/权限错误.对于这类问题可以通过设置event 10035进行跟踪
1-3
演示设置event 10035进行跟踪的效果

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';

System altered.

SQL> ALTER SESSION SET EVENTS '10035 trace name context forever, level 1';

Session altered.

SQL> select 1;
select 1
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select * from xifenfei_t;
select * from xifenfei_t
              *
ERROR at line 1:
ORA-00942: table or view does not exist

查看alert日志,观察错误语句
1-5

发表在 Oracle性能优化 | 标签为 | 评论关闭

sql plan baseline简单介绍

发表于 2019 年 4 月 5 日 由 惜分飞

Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），可以认为是OUTLINE（大纲）或者SQL PROFILE的改进版本，基本上它的主要作用可以归纳为如下两个：
1、稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等因子的改变对SQL语句的执行计划产生影响
2、减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上(可以通过OPTIMIZER_USE_SQL_PLAN_BASELINE实现)
3、sql baseline对于sql 大小写，sql空格可以生效，但是对于非绑定变量sql，如果使用不同变量无法生效(无force_matching功能)
确认当前无sql baseline启用

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected

创建场景
模拟一个表有index,如果再不强制的情况下,查询直接使用index,但是我这边要通过sql baseline模拟使用走全表扫描,实现不修改sql的情况下直接修改执行计划

SQL> create table t_xifenfei tablespace users as select * from dba_objects;

Table created.

SQL> create index i_xifenfei on t_xifenfei(object_id) tablespace users;

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)


19 rows selected.


SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)


19 rows selected.

从shared pool->library cache中直接加载sql plan baseline

SQL> set serveroutput on
SQL>  DECLARE
  2      ret PLS_INTEGER;
  3    BEGIN
  4      ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => 'b9hj14ntjgmtr',
  5                                                   plan_hash_value => null);
  6      dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  7    END;
  8    /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)

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


23 rows selected.

利用第一个baseline的sql_handle创建新执行计划的baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5   sql_id=>'aqgv7stwu6w5t',
  6   plan_hash_value=>548923532,sql_handle=>'SQL_ed6b78bdb7b643ad'
  7   );
  8  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  9   end;
 10   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL>  select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

删除第一个baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad'
     ,plan_name=>'SQL_PLAN_fuuvsrqvvchxd04acd9ab');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.


SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

验证baseline生效，实现sql语句执行计划的改变

SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

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


22 rows selected.

利用coe脚本利用baseline快速绑定sql执行计划

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad',
      plan_name=>'SQL_PLAN_fuuvsrqvvchxdf0c521d1');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected


SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
aqgv7stwu6w5t
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

7jdqvvnpxb9z5
select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%'

b9hj14ntjgmtr
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100


SQL> select sql_id,PLAN_HASH_VALUE from v$sql where sql_id in('b9hj14ntjgmtr','aqgv7stwu6w5t');

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
aqgv7stwu6w5t       548923532
b9hj14ntjgmtr      1926396081

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)


19 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)


19 rows selected.




SQL> @coe_load_sql_baseline.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: b9hj14ntjgmtr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: aqgv7stwu6w5t


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
           548923532                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 548923532



SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES


SQL> alter system flush shared_pool ;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

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


22 rows selected.

sql空格和大小写改变不影响baseline效果

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dwfxd7x6kwx6u, child number 1
-------------------------------------
select     OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

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


22 rows selected.

SQL> select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5spn2x6ac44af
select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OB
JECT_ID=100%'

8tytmh8r6w80n
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100


SQL>  select * from table(dbms_xplan.display_cursor('8tytmh8r6w80n','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8tytmh8r6w80n, child number 1
-------------------------------------
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

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


22 rows selected.

但是sql变量不一样导致baseline失效

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fp9u8wkp5cuw1, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=101

Plan hash value: 1926396081

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:01 |

------------------------------------------------------------------------------------------


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

   2 - access("OBJECT_ID"=101)


19 rows selected.
发表在 Oracle性能优化 | 评论关闭

使用_unnest_subquery优化sql

发表于 2018 年 5 月 17 日 由 惜分飞

一个复杂的sql查询,使用了大量EXISTS和NOT EXISTS 关联导致sql执行效率低下,这里挑选出来最核心的部分进行演示

SQL> explain plan for   select  
  2   a.aab034, a.aac001
  3    from si_dp.ac01_ac02 a
  4   where exists (select 1
  5            from ic40
  6           where aac001 = a.aac001
  7             and aae045 <= '201803'
  8             and aae120 = '0')
  9     and not exists (select 1
 10            from ic15
 11           where aac001 = a.aac001
 12             and aae002 <= '201803')
 13     and not EXISTS (select aab001
 14            from ab01
 15           where aab019 in ('91', '93')
 16             AND aab001 = a.aab001)
 17    and exists (select 1
 18            from ac13
 19           where aac001 = a.aac001
 20             and aae140 = '11'
 21             and aae114 in ('0', '1')
 22             and aae002 <= '201803')
 23     AND EXISTS (SELECT 1
 24            FROM AC13
 25           WHERE AAC001 = A.AAC001
 26             and aae140 = '11'
 27             AND AAE143 = '02'
 28             AND AAE003 < '201707'
 29             AND AAE002 BETWEEN '201801' AND '201803'
 30             and aae114 = '1')
 31     AND not EXISTS (SELECT 1
 32         FROM AC13
 33           WHERE AAC001 = A.AAC001
 34             and aae140 = '11'
 35          AND AAE002 < '201801')
 36     AND not EXISTS (SELECT 1
 37            FROM ac02
 38           WHERE AAC001 = A.AAC001
 39             and aae140 = '11'
 40             AND AAE036 < date '2018-1-1');
 
Explained.

Elapsed: 00:00:00.36

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1 |   202 |       | 11172   (2)|
|   1 |  NESTED LOOPS SEMI                |                    |     1 |   202 |       | 11172   (2)|
|   2 |   NESTED LOOPS ANTI               |                    |     1 |   175 |       | 11168   (2)|
|   3 |    NESTED LOOPS SEMI              |                    |     1 |   150 |       | 11164   (2)|
|   4 |     NESTED LOOPS ANTI             |                    |     1 |   126 |       | 11160   (2)|
|   5 |      NESTED LOOPS SEMI            |                    |     1 |   104 |       | 11158   (2)|
|   6 |       NESTED LOOPS ANTI           |                    |     1 |    67 |       | 11145   (2)|
|   7 |        HASH JOIN ANTI             |                    |     1 |    50 |  8640K| 11143   (2)|
|   8 |         TABLE ACCESS FULL         | AC01_AC02          |   245K|  5755K|       |   356   (2)|
|   9 |         TABLE ACCESS FULL         | AC02               |   559K|    13M|       |  9346   (2)|
|  10 |        TABLE ACCESS BY INDEX ROWID| AB01               |     2 |    34 |       |     2   (0)|
|  11 |         INDEX UNIQUE SCAN         | PK_AB01            |     1 |       |       |     1   (0)|
|  12 |       TABLE ACCESS BY INDEX ROWID | AC13               |   325K|    11M|       |    13   (0)|
|  13 |        INDEX RANGE SCAN           | I_AC13_AAE143      |   446 |       |       |     4   (0)|
|  14 |      INDEX RANGE SCAN             | PK_IC15            |  1771K|    37M|       |     2   (0)|
|  15 |     TABLE ACCESS BY INDEX ROWID   | IC40               |    17M|   395M|       |     4   (0)|
|  16 |      INDEX RANGE SCAN             | PK_IC40            |     1 |       |       |     3   (0)|
|  17 |    TABLE ACCESS BY INDEX ROWID    | AC13               |    51M|  1236M|       |     4   (0)|
|  18 |     INDEX RANGE SCAN              | RELATION_233112_FK |     3 |       |       |     3   (0)|
|  19 |   TABLE ACCESS BY INDEX ROWID     | AC13               |    52M|  1350M|       |     4   (0)|
|  20 |    INDEX RANGE SCAN               | RELATION_233112_FK |     3 |       |       |     3   (0)|
-----------------------------------------------------------------------------------------------------

这条sql,在一个10.2.0.3的系统中执行了十几个小时无法出结果,开发商反馈,该大部分客户的11.2的环境中,大概十几分钟出结果.从来没有遇到此类情况.让我们给他优化sql.看到这个sql,第一反应就是很可能大量的NESTED LOOPS效率低下,怀疑统计信息错误,结果收集完统计信息之后,执行计划依旧,我就在思考怎么调整sql,让其不这样大量嵌套执行.想起来的_unnest_subquery是控制子查询嵌套转换的,从9i开始默认为true,尝试设置为false测试.

SQL> alter session set "_unnest_subquery"=false;

Session altered.

Elapsed: 00:00:00.00
SQL> explain plan for   select 
  2   a.aab034, a.aac001
  3    from si_dp.ac01_ac02 a
  4   where exists (select 1
  5            from ic40
  6           where aac001 = a.aac001
  7             and aae045 <= '201803'
  8             and aae120 = '0')
  9     and not exists (select 1
 10            from ic15
 11           where aac001 = a.aac001
 12             and aae002 <= '201803')
 13     and not EXISTS (select aab001
 14            from ab01
 15           where aab019 in ('91', '93')
 16             AND aab001 = a.aab001)
 17    and exists (select 1
 18            from ac13
 19          where aac001 = a.aac001
 20             and aae140 = '11'
 21             and aae114 in ('0', '1')
 22             and aae002 <= '201803')
 23     AND EXISTS (SELECT 1
 24            FROM AC13
 25           WHERE AAC001 = A.AAC001
 26             and aae140 = '11'
 27             AND AAE143 = '02'
 28             AND AAE003 < '201707'
 29             AND AAE002 BETWEEN '201801' AND '201803'
 30             and aae114 = '1')
 31     AND not EXISTS (SELECT 1
 32            FROM AC13
 33           WHERE AAC001 = A.AAC001
 34             and aae140 = '11'
 35             AND AAE002 < '201801')
 36     AND not EXISTS (SELECT 1
 37            FROM ac02
 38           WHERE AAC001 = A.AAC001
 39             and aae140 = '11'
 40             AND AAE036 < date '2018-1-1');
Explained.

Elapsed: 00:00:00.07

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


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |   185K|    19M|       |  2991K  (2)|
|   1 |  FILTER                       |                  |       |       |       |            |
|   2 |   HASH JOIN RIGHT SEMI        |                  |   185K|    19M|    16M|   758K  (3)|
|   3 |    TABLE ACCESS BY INDEX ROWID| AC13             |   353K|    12M|       |  4556   (1)|
|   4 |     INDEX SKIP SCAN           | I_AC13_AAB001    | 23608 |       |       |  2287   (1)|
|   5 |    HASH JOIN SEMI             |                  |   201K|    14M|    11M|   751K  (3)|
|   6 |     HASH JOIN SEMI            |                  |   201K|  9452K|  8640K|   123K  (3)|
|   7 |      TABLE ACCESS FULL        | AC01_AC02        |   245K|  5755K|       |   357   (2)|
|   8 |      TABLE ACCESS FULL        | IC40             |    21M|   481M|       | 86122   (3)|
|   9 |     TABLE ACCESS FULL         | AC13             |    52M|  1350M|       |   530K  (3)|
|  10 |   INDEX RANGE SCAN            | PK_IC15          |     2 |    44 |       |     3   (0)|
|  11 |   VIEW                        | index$_join$_009 |     1 |    17 |       |     3  (34)|
|  12 |    HASH JOIN                  |                  |       |       |       |            |
|  13 |     INDEX RANGE SCAN          | PK_AB01          |     1 |    17 |       |     2   (0)|
|  14 |     INLIST ITERATOR           |                  |       |       |       |            |
|  15 |      INDEX RANGE SCAN         | IDX_AB01_AAB019  |     1 |    17 |       |     8   (0)|
|  16 |   TABLE ACCESS BY INDEX ROWID | AC13             |     2 |    50 |       |     5   (0)|
|  17 |    INDEX RANGE SCAN           | I_AC13_SEARCH    |   152 |       |       |     4   (0)|
|  18 |   TABLE ACCESS BY INDEX ROWID | AC02             |     1 |    26 |       |     4   (0)|
|  19 |    INDEX RANGE SCAN           | PK_AC02          |     1 |       |       |     3   (0)|
-----------------------------------------------------------------------------------------------

让开发设置该参数,然后执行sql,结果3分钟不到出结果,非常圆满完成任务.该sql还有进一步优化空间,但是考虑到已经满足要求,不再折腾.

发表在 Oracle性能优化 | 标签为 | 评论关闭