缺失合理索引导致SQL效率低下案例

近日同事在梳理生产系统TOP SQL时发现有一个SQL消耗了很多资源,情况如下:

SQL统计信息

SQL文本如下:

SELECT t.ID, t.FLT_OC_FLIGHT_ID, t.FLT_AIRLINE_CODE, t.FLT_FLIGHT_NO, t.FLT_FLIGHT_DATE
	, t.FLT_ALL_STATIONS, t.FLT_UPDATE_AGENT, t.FLT_UPDATE_TIME, t.FLT_NIGHT_FLIGHT_FLAG, t.FLT_INTERNATIONAL
	, t.FLT_ORI_STATION_CODE, t.FLT_DES_STATION_CODE, t.SEG_DATA_SOURCE, t.IS_VALID, t.INSERT_TIME
	, t.UPDATE_TIME, t.FLT_MSG_TID
FROM T_DEP_FDC_FLIGHT t
	INNER JOIN T_DEP_FDC_STATION s ON t.ID = s.STA_FLIGHT_ID 
WHERE t.FLT_AIRLINE_CODE IN (:1, :2, :3, :4)
	AND instr(t.FLT_ALL_STATIONS, :5) BETWEEN 1 AND length(t.FLT_ALL_STATIONS) - 3
	AND s.STA_STATION_CODE = :6
	AND s.STA_SCH_DEP_TIME <= :7
	AND s.STA_SCH_DEP_TIME >= :8
	AND t.IS_VALID = 1
	AND t.FLT_OC_FLIGHT_ID IS NULL

因为该SQL执行时间超过5s,Oracle会自动monitor,我们可以得到该SQL的SQL_MONITOR

从该条SQL的执行计划来看,T_DEP_FDC_FLIGHT 与T_DEP_FDC_STATION采用了Nested loop的方式进行JOIN,并且 T_DEP_FDC_FLIGHT 为驱动表。但是被驱动表扫描索引678K后,回表再过滤只剩下58行记录,并且 T_DEP_FDC_STATION 谓词条件的数据分布均匀,那么我们完全可以创建合适的索引,使得 T_DEP_FDC_STATION 表驱动 T_DEP_FDC_FLIGHT (小结果集驱动大结果集)。

模拟SQL:

select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |  4640 (100)|          |     19 |00:00:03.66 |   18746 |
|   1 |  NESTED LOOPS                |         |      1 |     26 |  4640   (1)| 00:00:56 |     19 |00:00:03.66 |   18746 |
|   2 |   NESTED LOOPS               |         |      1 |    135 |  4640   (1)| 00:00:56 |  69487 |00:00:03.62 |   17632 |
|*  3 |    TABLE ACCESS FULL         | A       |      1 |    135 |  4497   (1)| 00:00:54 |   1076K|00:00:02.83 |   16476 |
|*  4 |    INDEX RANGE SCAN          | S_IDX01 |   1076K|      1 |     1   (0)| 00:00:01 |  69487 |00:00:00.57 |    1156 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| S       |  69487 |      1 |     2   (0)| 00:00:01 |     19 |00:00:00.03 |    1114 |
--------------------------------------------------------------------------------------------------------------------------

创建索引:
SQL> create index s_idx01 on s(status,NAMESPACE);

Index created.


select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null


Plan hash value: 3503728021
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |    84 (100)|          |     35 |00:00:00.04 |      64 |      1 |
|   1 |  NESTED LOOPS                 |         |      1 |      5 |    84   (0)| 00:00:02 |     35 |00:00:00.04 |      64 |      1 |
|   2 |   NESTED LOOPS                |         |      1 |     83 |    84   (0)| 00:00:02 |     50 |00:00:00.03 |      52 |      1 |
|   3 |    TABLE ACCESS BY INDEX ROWID| S       |      1 |     83 |     4   (0)| 00:00:01 |     54 |00:00:00.03 |      21 |      1 |
|*  4 |     INDEX RANGE SCAN          | S_IDX01 |      1 |     83 |     2   (0)| 00:00:01 |     54 |00:00:00.03 |       5 |      1 |
|*  5 |    INDEX UNIQUE SCAN          | A_PK1   |     54 |      1 |     0   (0)|          |     50 |00:00:00.01 |      31 |      0 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | A       |     50 |      1 |     1   (0)| 00:00:01 |     35 |00:00:00.01 |      12 |      0 |
------------------------------------------------------------------------------------------------------------------------------------

高逻辑读Top Sql 优化案例一则

前不久,有个高逻辑读的sql需要进行优化处理,详情如下:

SQL_TEXT如下:
SELECT DISTINCT object_name
FROM t2
WHERE (substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
	AND object_name IS NOT NULL
	AND object_type IS NULL
	AND object_name NOT IN (
		SELECT object_name
		FROM t3
		WHERE object_name IS NOT NULL
	)
	AND regexp_like(object_name, '[[:upper:]]{4}'))

SQL的统计信息如下 :
Historical Plans Summary(dba_hist_sqlstat):

  RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS      EXECS FIRST_SNAP          LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- ---------- ------------------- -------------------
   1       527727976      113.451       32.239      907578     899049         18          8 2020-05-20 10:00:15 2020-05-28 10:00:51


Current Plans Summary(gv_sql):

 RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME      LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
  1       527727976      154.255       45.998     1131102    1130264         23          1 2020-05-28/09:00:02  2020-05-28 09:02:36


执行计划如下:
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 4047468152

--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   239 (100)|          |     51 |00:00:00.03 |     863 |
|   1 |  HASH UNIQUE        |      |      1 |      1 |   239   (2)| 00:00:03 |     51 |00:00:00.03 |     863 |
|*  2 |   HASH JOIN ANTI    |      |      1 |      1 |   238   (1)| 00:00:03 |     77 |00:00:00.03 |     863 |
|*  3 |    TABLE ACCESS FULL| T2   |      1 |     70 |   153   (1)| 00:00:02 |     77 |00:00:00.01 |     557 |
|*  4 |    TABLE ACCESS FULL| T3   |      1 |  85011 |    85   (2)| 00:00:02 |  71958 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

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

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE
              ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('105FF26E1D000000FCE30302000000000000000000000000703EDC060000
              0000000000000000000000000000000000003600000000000000605FF26E1D00000004000000010000000000000081000000')
               ) AND SUBSTR("CREATED",0,10)=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd') AND "OBJECT_NAME" IS NOT NULL))
   4 - filter("OBJECT_NAME" IS NOT NULL)

生产中,T2表63127459行记录(8g),T3表5703行记录(3m),join时都使用了全表扫描,其中cretad ndv很高,每天的数据量记录数并不多,可以创建如下索引:

create index t2_idx on t2(substr(created, 0, 10));
create index T3_IDX01 on t3(object_name);

执行计划如下,仍然不是最优的执行计划,更高效的应该是走NESTED LOOPS ANTI

-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 1600442906

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |    93 (100)|          |    247 |00:00:00.03 |     317 |
|   1 |  HASH UNIQUE                  |        |      1 |      5 |    93   (3)| 00:00:02 |    247 |00:00:00.03 |     317 |
|*  2 |   HASH JOIN ANTI              |        |      1 |      5 |    92   (2)| 00:00:02 |    404 |00:00:00.02 |     317 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2     |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    TABLE ACCESS FULL          | T3     |      1 |  85303 |    85   (2)| 00:00:02 |  72028 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T2@SEL$1
   5 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$1" "T2_IDX")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

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

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('5815F42D1D000000FC
              E30302000000000000000000000000703EDC0600000000000000000000000000000000000000003600000000000000A815F42D1D0000000400
              0000010000000000000081000000') ) AND "OBJECT_NAME" IS NOT NULL))
   4 - access("T2"."SYS_NC00005$"=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd'))
   5 - filter("OBJECT_NAME" IS NOT NULL)

我们可以添加HINT改变执行计划:

方式1:使用qb_name hint

select /*+ use_nl(t2,t3@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

SQL_ID  guj3z7b3w630x, child number 0
-------------------------------------
select /*+ use_nl(t2,t3@bl) */ distinct object_name   from t2  where
substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')    and
object_name is not null    and object_type is null    and object_name
not in        (select /*+ qb_name(bl) */          object_name from t3
       where object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式2:使用hint:nl_aj + qb_name方式

select /*+ nl_aj(@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式3: nl_aj ,在子查询中加入hint,可以不用指定查询块

select distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ nl_aj */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')


SQL_ID  7hb1ksq4s1v6p, child number 0
-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null
and object_type is null    and object_name not in        (select /*+
nl_aj */          object_name           from t3          where
object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

生产系统SQL低效索引案例

前不久,无意中看到生产中一则TOP SQL,消耗资源较多,感觉有很大优化空间

SQL_ID  1agm6bmnhh609, child number 24
--------------------------------------
select *
  from UMETRIP_EVENTLOG
where FLIGHTDATE = :1 
   and EVENTTYPE = 'GATE_CHG' 
   and PASSREASON = '-91000

TIME         EXECU_D      BUFFER        DISK      ET_D     ROWS_PRO                                        
------------ ------- ----------- ----------- --------- ------------                                        
2020052004         2      229527       75055       205         4201                                        
2020052101         2      288633       67290       166         4061                                        
2020052202         2      226528       74570       181         4224                                        
2020052203         2      205266       50359       134         4421                                        
2020052303         2      206985       67949       156         4638                                        
2020052306         1      152840       76679       196         1723                                        
2020052601         2      213294       47103       125         3441                                        
2020052603         2      209591        9441        36         3765                                        
2020052810         2      248329       30198        91         4765                                        
2020052813         2      271982       46553       123         4849

这条SQL执行次数并不多,却在AWR中进入了TOP 10 SQL,执行1-2次消耗了200000+的逻辑读,表记录数25042676行,NDV如下:

COLUMN_NAME                       NDV NUL  NUM_NULLS     DATA_TYPE      LAST_ANALYZED        HISTOGRAM
------------------------------ ---------- --- ---------- ------------ -------------------- ---------
CITYCODE                             2379 Y         6073 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
DESTA                                2365 Y          849 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
EVENTTYPE                              59 Y            0 VARCHAR2     2020-05-27 22:09:30  FREQUENCY
EXTRAINFO                         2189824 Y     14600645 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDATE                           1891 Y          723 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDYNFIELD                         45 Y         3651 VARCHAR2     2020-05-27 22:09:30  NONE
FLIGHTNO                            58972 Y            0 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
ID                               25042676 Y            0 NUMBER       2020-05-27 22:09:30  HEIGHT BA
INFOAFTER                          713856 Y          336 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
INFOBEFOR                          243808 Y     21175542 VARCHAR2     2020-05-27 22:09:30  NONE
INSERTTIME                         626176 Y            0 DATE         2020-05-27 22:09:30  HEIGHT BA
ISUSED                                  2 Y          165 NUMBER       2020-05-27 22:09:30  FREQUENCY
PASSREASON                             91 Y      3295111 NUMBER       2020-05-27 22:09:30  FREQUENCY


Plan hash value: 3303868666
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       | 22722 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| UMETRIP_EVENTLOG      |    72 |  7776 | 22722   (1)| 00:04:33 |
|*  2 |   INDEX RANGE SCAN          | UMETRIP_EVENTLOG_IDX2 | 17964 |       |  5724   (1)| 00:01:09 |
-----------------------------------------------------------------------------------------------------

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


   1 - filter("PASSREASON"=(-91000))
   2 - access("EVENTTYPE"='GATE_CHG' AND "FLIGHTDATE"=:1)
       filter("FLIGHTDATE"=:1)

目前SQL执行计划走了UMETRIP_EVENTLOG_IDX2的索引,EVENTTYPE/ID/CITYCODE/FLIGHTDATE,起到过滤作用的只有EVENTTYPE,而FLIGHTDATE只能起到过滤作用。

继续阅读

Cursor 触发了OPTIMIZER_MISMATCH=Y的Bug?

近日,一个数据服务器CPU基本每日触发报警,Idle已经接近于0,并且存在一定的周期性规律,CPU Idle如下图所示:

20200105-11

定位到具体进程后,发现CPU TIME主要被两个SQL因为创建合适的索引导致大量全表扫描耗尽。经过简单优化后,SQL效率提升,但却发现了一个奇怪的问题:

优化前:

20200105-12

优化后:

20200105-13

使得游标立即失效后,此SQL会立即进行硬解析,但是却硬解析了2个Child Cursor:

20200105-14

20200105-15可以看到,两个Cursor确认是因为客户端NLS_SORT与NLS_COMP参数不一致,导致了Cursor 2无法利用上索引,从而选择了全表扫描的执行计划。但是从V$SQL_SHARED_CURSOR中发现了异常现象,LANGUAGE_MISMATCH很好理解(因为NLS_SORT与NLS_COMP参数不一),但是OPTIMIZER_MISMATCH为何也是’Y’?

20200105-16

利用alter system set events ‘trace[rdbms.SQL_Optimizer.*][sql_id]’ ;查看Trace文件与查询v$sql_optimizer_env确认游标优化器参数时,并没有发现异常现象:

20200105-17

20200105-18如果优化器参数不一,那么cursor执行计划中的Outline Data CBO参数部分会有所区别,但是上图中两个Cursor 执行计划中显示的CBO参数是一致的。虽然Oracle Support中并未发现相关Bug记录,但是猜测很有可能是触发了Bug导致。

Oracle 12c中分页查询增强-TOP N SQL

继之前文章中提到的分页查询SQL优化思路提到的分页框架:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+ index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

我们前文中提到过,需要在排序列中创建索引,借助索引有序特性来避免排序并且利用正确的分页框架实现COUNT STOPKEY特性,在Oracle 12c版本之后,Oracle提供了新的分页TOP N功能,来实现上述分页需求并且极大的减少了代码复杂程度。

参考Oracle 官方文档中的的描述:

20191208

我们可以利用FETCH ROWS ONLY来实现文章分页TOP-N需求代码:

20191208-2

继续阅读

Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

今天,公司同事问了我一个问题,Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性,是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状?

答案是否定的,我们先看一下如下测试实验:

我们在12c环境下执行如下SQL:

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制(12C 默认为TRUE),我们分别将该参数改为FLASE,与TRUE:

(1)关闭TABLE ACCESS BY INDEX ROWID BATCHED特性,同ora11g版本:

2019203-2

继续阅读

分页语句SQL优化思路总结-3(多表JOIN)

前两篇文章中讲述总结了几种分页语句的优化方法后,碰巧生产系统中有如下SQL需要优化,我们看到这个SQL是多表JOIN的需求,也是我们在生产系统中常见的分页语句场景:

经过将分页框架等价改写正确后,生产系统SQL如下:

SELECT  * FROM ( SELECT * FROM (
 SELECT rownum AS rn, r.owner, r.object_id
 FROM ( SELECT t.owner, t.object_id, t1.account_status
 FROM t, t1 WHERE t.owner = t1.username AND t.owner = 'SYS'
 ORDER BY t.owner) r)
 WHERE rownum <= 10)
WHERE rn >= 1;

多表JOJN-1 20191117

继续阅读

分页语句SQL优化思路总结-2(分页语句中存在谓词条件)

继《分页语句SQL优化思路总结-1(分页语句中无谓词条件)》中我们实验了目标分页SQL存在排序的优化,该分页SQL是不存在谓词过滤条件的“select owner,object_id from t order by object_id”,但是我们99%的情况遇到的应用SQL都是存在谓词过滤条件,例如”select owner,object_id from t where owner=’TRAVELSKYDBA’ order by object_id;”的情况。我们上文提到,可以利用索引有序特性将object_id(排序列)创建索引,但是如果遇到等值查询,我们是否仍需要将排序列创建索引呢?我们继续试验论证,我们来看一下测试数据表T的OWNER列的数据分布:

20191117-分页(4)

我们执行如下目标SQL:

SELECT /*+ gather_plan_statistics */ *
  FROM (
    SELECT *
        FROM (
   SELECT rownum AS rn, r.owner, r.object_id
      FROM (
   SELECT /*+index(t t_idx01) */ owner, object_id
   FROM t WHERE OWNER='TRAVELSKYDBA'
    ORDER BY object_id) r
  )
   WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(5)

继续阅读

分页语句SQL优化思路总结-1(分页语句中无谓词条件)

今日,继续总结SQL优化相关原理总结-分页语句。现有如下SQL:

20191117-分页(1)

此SQL的目的是在T表中取出已排序后的10条记录,该SQL中走的是T表的全表扫描执行计划,并且进行排序后才取出10条记录,显然此执行计划并非是最优的,如果表的数据量很大,会造成严重的性能问题(全表扫描+排序),也就是说将表中数据全部扫描处理后再进行排序。但是,该SQL的目的是取出已排序好的10条记录,那么我们是否可以利用INDEX索引有序的特征,扫描到第10条后进行count stopkey停止扫描呢?

因为目标SQL是以OBJECT_ID列作为排序列,我们可以在此列上建立索引,根据索引原理,索引数据本身是有序的,我们可以利用这一特性减少目标SQL对表的访问block

我们将目标SQL先改写为如下形式:

create index t_idx01 on t(object_id,0)

select /*+ gather_plan_statistics */ * from (select /*+ index(t
t_idx01) */ owner,object_id from t order by object_id) where rownum <=10

我们来看一下执行计划:

20191117-分页(2)

我们用HINT强制SQL走了T_IDX01的索引的执行计划,并CBO选择了INDEX_FULL_SCAN索引全扫描,但是我们发现,A-ROWS只扫描了10行数据,处理的总行数等同于我们需要返回的行数,并且执行计划中我们没有看到SORT ORDER BY,在此案例中并没有进行排序,而是利用索引有序的特性,直接扫描INDEX返回即可。

我们将目标SQL改写成如下方式即可:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(3)

犹如预期所愿,目标SQL只扫描处理了10行便COUNT STOPKEY,这是理想的执行计划。

经过以上实验,我们可以得出几个个结论,

(1)分页语句中如果存在排序列(本例中为order by object_id),可以在此列上建立索引,可以避免排序SORT ORDER BY;

(2)例如本文开头的案例,扫描了所有的行数后最后却只要10行数据,那么在错误的分页框架中即使存在索引时,执行计划也会走INDEX FULL SCAN扫描所有的INDEX LEAF BLOCK,所以我们要利用ROWNUM来实现COUNT STOPKEY从而控制扫描的行数,避免扫描多余的行数,尽量只需要处理我们需要返回的行数即可。

 

 

一则系统迁移过后的SQL缓慢案例

11月初,某重要航司的一套生产库进行了机房迁移工作,原数据库实例A(Oracle 11.2.0.3版本)利用DG迁移至新机房,数据库实例A(Oracle 11.2.0.3),数据库版本维持原样,没有进行软件升级,但是,一个应用SQL在新环境下运行时非常缓慢,数据库服务器CPU飙高,Idle接近于0,SQL信息如下:

SELECT /*+ parallel(16) */
 nodeValue
 from (SELECT DISTINCT LPAD(BEGTIME, 4, '0') || '-' ||
 LPAD(ENDTIME, 4, '0') nodeValue
 FROM TRAVELSKYDBA
 WHERE CATEGORY = 'OND'
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA1
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA2
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA3
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA4
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA5)
 WHERE UPPER(nodeValue) <> 'ALL'

既然数据库并没有进行版本升级,CBO优化器方面应该没有不同之处,新老环境的执行计划如下:

老环境:

od-1

继续阅读