8个小时的SQL优化到1秒钟

沃趣技术 2019-03-01

415

| 优化方法

AWR中此sql执行8个多小时。

DataGrip生成的sql，无法在软件中更改sql文本，要想改变执行计划用hint就不行了。

下面我们具体看下sql与执行计划，sql样子很熟悉，with as，都是数据字典表，并且在from后，很敏感想到view merge，查询每个子句都很快，那么执行计划也不用看了，直接在后加/*+materialize*/将其固化不做view merge操作，尝试一下。

文本如下：

with O1 as ( select object_id,
                object_type,
                object_name
         from sys.all_objects
         where owner = :1 
           --  and last_ddl_time >= :since
           --  and object_name in ( :[*major_object_names] )
           and object_type in ('TYPE', 'TYPE BODY', 'PACKAGE', 'PACKAGE BODY',
                               'PROCEDURE', 'FUNCTION',
                               'MATERIALIZED VIEW', 'VIEW', 'TRIGGER') ),
 O2 as ( select object_id as referenced_id,
                owner as referenced_schema_name,
                object_type as referenced_type,
                object_name as referenced_name
         from sys.all_objects ), -- don't use #CAT here
 DE as ( select type as object_type,
                name as object_name,
                referenced_owner as referenced_schema_name,
                referenced_type,
                referenced_name
         from sys.all_dependencies
         where owner = :2 
            and not (owner = referenced_owner and name = referenced_name and type = referenced_type || ' BODY')
            and not (referenced_owner = 'SYS' and referenced_type = 'PACKAGE' and referenced_name = 'STANDARD')
            and type not in ('SYNONYM','UNDEFINED','JAVA CLASS','JAVA DATA')
            and referenced_type not like 'JAVA%' )
select /* object_id, referenced_id */O1.object_id, O2.referenced_id /* object_id, referenced_id */
from O1 natural join DE natural join O2

将SQL中的绑定变量代入具体值，再次查询。

with O1 as ( select /*+ materialize */object_id,
                object_type,
                object_name
         from sys.all_objects
         where owner = 'SYS'
           --  and last_ddl_time >= :since
           --  and object_name in ( :[*major_object_names] )
           and object_type in ('TYPE', 'TYPE BODY', 'PACKAGE', 'PACKAGE BODY',
                               'PROCEDURE', 'FUNCTION',
                               'MATERIALIZED VIEW', 'VIEW', 'TRIGGER') ),
 O2 as ( select /*+ materialize */ object_id as referenced_id,
                owner as referenced_schema_name,
                object_type as referenced_type,
                object_name as referenced_name
         from sys.all_objects ), -- don't use #CAT here
 DE as ( select /*+ materialize */ type as object_type,
                name as object_name,
                referenced_owner as referenced_schema_name,
                referenced_type,
                referenced_name
         from sys.all_dependencies
         where owner = 'SYS' 
            and not (owner = referenced_owner and name = referenced_name and type = referenced_type || ' BODY')
            and not (referenced_owner = 'SYS' and referenced_type = 'PACKAGE' and referenced_name = 'STANDARD')
            and type not in ('SYNONYM','UNDEFINED','JAVA CLASS','JAVA DATA')
            and referenced_type not like 'JAVA%' )
select /* object_id, referenced_id */O1.object_id, O2.referenced_id /* object_id, referenced_id */
from O1 natural join DE natural join O2;

1秒钟执行完。

执行计划如下：

此处略去好多行… …

那么在不改变sql的情况下，怎么将不同sql id好的执行计划绑定到目标sql中呢？想到了coe_load_sql_profile.sql

下面我们就将这个/*+materialize */ hint的执行计划绑定到老的SQL中。

SQL>@coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 1kn67w4a4ch53

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 99hfmmfxdmu95


 PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
      2037728849                1.148

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 2037728849

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "1kn67w4a4ch53"
MODIFIED_SQL_ID: "99hfmmfxdmu95"
PLAN_HASH_VALUE: "2037728849"
… …此处省略一直到结尾

| 效率对比

SELECT SQL_ID,
   CHILD_NUMBER,
   PLAN_HASH_VALUE,
   SORTS,
   DISK_READS,
   DIRECT_WRITES,
   BUFFER_GETS,
   ROUND(ELAPSED_TIME / 1000 / 1000, 2) ELAPSED_S,
   ROUND(CPU_TIME / 1000 / 1000, 2) CPU_S,
   EXECUTIONS
FROM V$SQL
WHERE SQL_ID = '1kn67w4a4ch53';

| 另一种方法

除了使用/*+materialize */，到这里其实还有一种方式优化这条sql 大家不妨想一想。

… …

下面给出答案。

with O1 as ( select object_id,
                object_type,
                object_name
         from sys.all_objects
         where owner = 'SYS'
           and object_type in ('TYPE', 'TYPE BODY', 'PACKAGE', 'PACKAGE BODY',
                               'PROCEDURE', 'FUNCTION',
                               'MATERIALIZED VIEW', 'VIEW', 'TRIGGER') ),
 O2 as ( select object_id as referenced_id,
                owner as referenced_schema_name,
                object_type as referenced_type,
                object_name as referenced_name
         from sys.all_objects ), 
 DE as ( select type as object_type,
                name as object_name,
                referenced_owner as referenced_schema_name,
                referenced_type,
                referenced_name
         from sys.all_dependencies
         where owner = 'SYS' 
            and not (owner = referenced_owner and name = referenced_name and type = referenced_type || ' BODY')
            and not (referenced_owner = 'SYS' and referenced_type = 'PACKAGE' and referenced_name = 'STANDARD')
            and type not in ('SYNONYM','UNDEFINED','JAVA CLASS','JAVA DATA')
            and referenced_type not like 'JAVA%' )
select /*+NO_MERGE(O1) NO_MERGE(O2) NO_MERGE(DE) */ O1.object_id, O2.referenced_id 
from O1 natural join DE natural join O2;