核心系统表关联顺序错误导致的低效SQL

发表于由

同事发来一个核心业务库的TOP SQL，占据并浪费了大量的服务器CPU资源，此SQL执效率信息如下：

SELECT a.ID,
       a.SEG_FLIGHT_ID,
       a.SEG_DEP_STATION_CODE,
       a.SEG_ARR_STATION_CODE,
       a.SEG_CLASS_SEQUENCE,
       a.SEG_TRAFFIC_RESTRICTION_CODE,
       a.SEG_DEP_TERMINAL,
       a.SEG_ARR_TERMINAL,
       a.SEG_OC_TYPE,
       a.SEG_OC_MAP_NUMBER,
       a.SEG_OC_CLASS_MAPPING,
       a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
  (SELECT id
   FROM
     (SELECT t.id
      FROM T_FLIGHT t,
           T_TASK ta
      WHERE 1=1
        AND ta.id IN ('11008',
                      '11003',
                      '11014',
                      '11012',
                      '14001',
                      '11001')
        AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
        AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
        AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
        AND t.FLT_OC_FLIGHT_ID IS NULL
        AND t.IS_VALID = 1
        AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
   LEFT JOIN t_dep_schema s ON id = s.flt_id
   WHERE s.flt_id IS NULL
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
              OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
              OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
  AND a.SEG_FLIGHT_ID = f.ID
  AND a.IS_VALID = 1

SQL执行计划如下：

从执行计划中看到，估算行数与实际行数差别巨大，实际行数只是899行，但是NESTED LOOP之后估算结果集为46K，并且TASK表与S表选择了HASH JOIN，在未收集个性化扩展统计信息的情况，针对这种复杂情况，估算不准的情况是比较常见的，通常需要利用HINT来调整表连接顺序来优化。此SQL的涉及表都不存在索引缺失的情况，只是行数估算错误导致表连接顺序有误，从而选择了低效的执行计划。

继续阅读 →

原平均1762秒提升至0.035秒的TOP SQL优化处理过程

发表于由

同事在日常巡检中发现一套数据库资源瓶颈严重，如下图所示：

活动会话多数在等待read by other session与CPU，根据硬件，多数为SQL问题导致，协助同事最终定位了TOP SQL，该SQL执行计划与执行效率信息如下图所示：

从执行计划中得知，最消耗资源的为TABLE ACCESS FULL全表扫描这步，该SQL有上百行，截取执行计划中此步SQL_TEXT如下：

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100

Plan hash value: 3299198703

------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |   657K(100)|          |    100 |00:00:07.90 |     263K|
|*  1 |  COUNT STOPKEY          |      |      1 |        |            |          |    100 |00:00:07.90 |     263K|
|   2 |   VIEW                  |      |      1 |     15M|   657K  (1)| 02:11:28 |    100 |00:00:07.90 |     263K|
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |     15M|   657K  (1)| 02:11:28 |    100 |00:00:07.90 |     263K|
|*  4 |     TABLE ACCESS FULL   | T    |      1 |     15M| 71738   (1)| 00:14:21 |     17M|00:00:03.36 |     263K|
------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   4 - SEL$2 / iti@SEL$2

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

   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)

可以看到，该SQL用了232K个BUFFER，大约用了7秒多的时间。根据SQL逻辑,我们可以建立created,object_id两个字段索引，使得执行计划只扫描100行所需要的buffer，理想执行计划是走索引的INDEX FULL SCAN DESCENDING，A-ROWS只有100：

SQL> create index t_idx01 on t(created,object_id);

Index created.

再次测试SQL:

SQL>
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100

Plan hash value: 4274603649

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     4 (100)|          |    100 |00:00:00.01 |     110 |      2 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |            |          |    100 |00:00:00.01 |     110 |      2 |
|   2 |   VIEW                        |         |      1 |     15M|     4   (0)| 00:00:01 |    100 |00:00:00.01 |     110 |      2 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |     15M|     4   (0)| 00:00:01 |    100 |00:00:00.01 |     110 |      2 |
|*  4 |     INDEX FULL SCAN DESCENDING| T_IDX01 |      1 |      1 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |      10 |      2 |
------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   3 - filter("iti"."OBJECT_TYPE"<>'INDEX')
   4 - access("iti"."OBJECT_ID">88)
       filter("iti"."OBJECT_ID">88)

我们看到，执行计划自动选择了INDEX FULL SCAN DESCENDING，无需单独指定降序扫描，优化器会自动选择此方式；A-ROWS只有100行（需求是order by + 小于等于100 row），相对于非全表扫描后过滤出100行的方式，BUFFER GET只需要110个即可。

在生产库上创建索引后性能对比：

最终执行计划如下：


-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                         |      1 |        |  1181 (100)|          |    104 |00:00:00.02 |    2476 |
|*  1 |  FILTER                           |                         |      1 |        |            |          |    104 |00:00:00.02 |    2476 |
|   2 |   NESTED LOOPS                    |                         |      1 |    496 |  1181   (1)| 00:00:15 |    508 |00:00:00.01 |    1542 |
|   3 |    NESTED LOOPS                   |                         |      1 |     89 |   673   (0)| 00:00:09 |    100 |00:00:00.01 |     963 |
|   4 |     VIEW                          |                         |      1 |    100 |    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  5 |      COUNT STOPKEY                |                         |      1 |        |            |          |    100 |00:00:00.01 |     401 |
|   6 |       VIEW                        |                         |      1 |    100 |    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| ITINE                   |      1 |     22M|    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  8 |         INDEX SKIP SCAN DESCENDING| ITIN_IDX_01             |      1 |    104 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |     301 |
|*  9 |     TABLE ACCESS BY INDEX ROWID   | SPNR                    |    100 |      1 |     6   (0)| 00:00:01 |    100 |00:00:00.01 |     562 |
|* 10 |      INDEX RANGE SCAN             | SPNR_PK                 |    100 |      9 |     3   (0)| 00:00:01 |    960 |00:00:00.01 |     291 |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | ITINE                   |    100 |      6 |     6   (0)| 00:00:01 |    508 |00:00:00.01 |     579 |
|* 12 |     INDEX RANGE SCAN              | ITINE_PK                |    100 |      6 |     3   (0)| 00:00:01 |    508 |00:00:00.01 |     271 |
|* 13 |   TABLE ACCESS BY INDEX ROWID     | ITINE                   |    404 |      1 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |     934 |
|* 14 |    INDEX UNIQUE SCAN              | ITINE_PK                |    404 |      1 |     3   (0)| 00:00:01 |    404 |00:00:00.01 |     530 |
-----------------------------------------------------------------------------------------------------------------------------------------------

执行效率高但“低效”的SQL优化

发表于由

近期整理案例时发现，同事曾咨询过一个问题，数据库服务器CPU频繁报警，同事最终定位到一个TOP SQL，但是执行效率已经”算不错“，是否还有优化的余地？

SQL_TEXT如下：

SELECT DISTINCT S.*,
                BD.*,
                SD.*,
                UD.*
FROM T S
LEFT JOIN T1 BD ON S.DATA_OBJECT_ID = BD.DATA_OBJECT_ID
AND S.OBJECT_ID = BD.OBJECT_ID
LEFT JOIN T2 SD ON S.DATA_OBJECT_ID = SD.DATA_OBJECT_ID
AND S.OBJECT_ID = SD.OBJECT_ID
LEFT JOIN T3 UD ON S.DATA_OBJECT_ID = UD.DATA_OBJECT_ID
AND S.OBJECT_ID = UD.OBJECT_ID
WHERE S.OBJECT_ID = 88
  AND 1 = 1

原执行计划如下：
Plan hash value: 1035031963

----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        | 84081 (100)|          |      1 |00:00:00.60 |     309K|
|   1 |  HASH UNIQUE          |      |      1 |      1 | 84081   (1)| 00:16:49 |      1 |00:00:00.60 |     309K|
|*  2 |   HASH JOIN OUTER     |      |      1 |      1 | 84080   (1)| 00:16:49 |      1 |00:00:00.60 |     309K|
|*  3 |    HASH JOIN OUTER    |      |      1 |      1 | 79935   (1)| 00:16:00 |      1 |00:00:00.55 |     294K|
|*  4 |     HASH JOIN OUTER   |      |      1 |      1 | 75791   (1)| 00:15:10 |      1 |00:00:00.50 |     278K|
|*  5 |      TABLE ACCESS FULL| T    |      1 |      1 | 71647   (1)| 00:14:20 |      1 |00:00:00.44 |     263K|
|*  6 |      TABLE ACCESS FULL| T3   |      1 |    168 |  4144   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
|*  7 |     TABLE ACCESS FULL | T2   |      1 |    168 |  4144   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
|*  8 |    TABLE ACCESS FULL  | T1   |      1 |    168 |  4145   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
----------------------------------------------------------------------------------------------------------------

 

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

   2 - access("S"."OBJECT_ID"="BD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="BD"."DATA_OBJECT_ID")
   3 - access("S"."OBJECT_ID"="SD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="SD"."DATA_OBJECT_ID")
   4 - access("S"."OBJECT_ID"="UD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="UD"."DATA_OBJECT_ID")
   5 - filter("S"."OBJECT_ID"=88)
   6 - filter("UD"."OBJECT_ID"=88)
   7 - filter("SD"."OBJECT_ID"=88)
   8 - filter("BD"."OBJECT_ID"=88)

SQL涉及四张表T,T1,T2,T3，数据量均在100万+。OBJECT_ID选择率非常好（相当于主键），因为缺乏多个索引，执行计划选择全表扫描+HASH_JOIN的方式针对此SQL是严重低效的，即使执行效率还能接受，平均单次执行时间需要0.237秒，但是309K的BUFFER GET。SQL执行效率高不等于一定是高效率的。

优化建议：T表谓词创建索引，返回行数少（驱动表），剩余表连接列上创建索引（被驱动表），使得SQL走Nested Loop关联方式，小结果集驱动大结果集，最终只需要4000多个BUFFER即可。

模拟案例SQL：

create index t_idx01 on t(object_id);
create index t1_idx01 on t1(data_object_id);
create index t2_idx01 on t2(data_object_id);
create index t3_idx01 on t3(data_object_id);

Plan hash value: 3722872203

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	   |	  1 |	     |	 782 (100)|	     |	    1 |00:00:00.01 |	4018 |
|   1 |  HASH UNIQUE			|	   |	  1 |	   1 |	 782   (1)| 00:00:10 |	    1 |00:00:00.01 |	4018 |
|   2 |   NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 781   (0)| 00:00:10 |	    1 |00:00:00.01 |	4018 |
|   3 |    NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 522   (0)| 00:00:07 |	    1 |00:00:00.01 |	2680 |
|   4 |     NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 263   (0)| 00:00:04 |	    1 |00:00:00.01 |	1342 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T	   |	  1 |	   1 |	   4   (0)| 00:00:01 |	    1 |00:00:00.01 |	   4 |
|*  6 |       INDEX RANGE SCAN		| T_IDX01  |	  1 |	   1 |	   3   (0)| 00:00:01 |	    1 |00:00:00.01 |	   3 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| T1	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|*  8 |       INDEX RANGE SCAN		| T1_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | T2	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|* 10 |      INDEX RANGE SCAN		| T2_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
|* 11 |    TABLE ACCESS BY INDEX ROWID	| T3	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|* 12 |     INDEX RANGE SCAN		| T3_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
------------------------------------------------------------------------------------------------------------------------------

很多时候，执行效率浪费资源严重的TOP SQL往往被日益逐渐增强的系统资源所掩盖，记得一位前辈说过的话，TOP SQL不要看单纯的执行效率，要看所谓的”TOP”来综合判断是否有优化的余地，例如参考平均buffer get与平均返回行数等其他SQL性能指标，是否是合适，还是“血亏”。

ROW CACHE LOCK导致数据库CPU高案例分析

发表于由

近期整理案例，发现前不久同事反馈一个数据库服务器CPU居高不下，无法找到根因过来询问，详情如下：

EVENT                                      COUNT(*)
---------------------------------------- ----------
row cache lock                                 1007
                                                213
buffer busy waits                               165
log file switch (checkpoint incomplete)         137
free buffer waits                               114
db file async I/O submit                         75
log file sync                                    73
db file sequential read                          62
read by other session                            36
log file parallel write                          15
resmgr:cpu quantum                               15
enq: TX - row lock contention                    13
enq: HW - contention                              9
direct path read                                  8
enq: SQ - contention                              4
latch: undo global data                           3
write complete waits                              2
log file sequential read                          1
null event                                        1
db file scattered read                            1
Log archive I/O                                   1
LNS wait on SENDREQ                               1
enq: US - contention                              1

从ASH中看到，CPU高的时间段内，等待事件最多的为ROW CACHE LOCK，

TIME                              USER_ID SQL_ID        EVENT                                            P1
------------------------------ ---------- ------------- ---------------------------------------- ----------
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13

确认该时间段内的具体SQL，发现均存在SEQUENCE调用

SQL_ID          COUNT(*)
------------- ----------
7265mcg5kwyc3        357
11xs00r4qzb7n        341
c25ar03n0p5sz        277
f9ym730zzf1s2         12
9dnygr7asqmy9         12
dksd9txy8vuk6          6
9qzwmgp034kx2          2

SQL> SELECT DISTINCT sql_id,
                sql_text
FROM v$sql
WHERE sql_id IN ('dksd9txy8vuk6',
                 '11xs00r4qzb7n',
                 '9qzwmgp034kx2',
                 '9dnygr7asqmy9',
                 '7265mcg5kwyc3',
                 'c25ar03n0p5sz',
                 'f9ym730zzf1s2');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------
9qzwmgp034kx2 INSERT INTO opfqresultdata VALUES(OPFQRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 )
f9ym730zzf1s2 select SUBSFLT_HISTORY_SEQUENCE_ID.nextval from dual
7265mcg5kwyc3 INSERT INTO pnrresultdata VALUES(PNRRESULTDATA_SEQUENCE_ID.NEXTVAL,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 )
c25ar03n0p5sz INSERT INTO etresultdata VALUES(ETRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 )
9dnygr7asqmy9 select SUBSFLTREQUEST_SEQUENCE_ID.nextval from dual
11xs00r4qzb7n INSERT INTO unknowdata VALUES(UNKNOWDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 )
dksd9txy8vuk6 INSERT INTO fltresultdata VALUES(FLTRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:1

SQL> select DISTINCT P1
FROM
  (SELECT to_char(SAMPLE_TIME,'YYYYMMDD hh24:mi:ss') time,
          USER_ID,
          SQL_ID,
          EVENT,
          P1
   FROM dba_hist_active_sess_history
   WHERE SAMPLE_TIME>to_date('20200929 01:00:00','YYYYMMDD hh24:mi:ss')
     AND SAMPLE_TIME<to_date('20200929 01:15:00','YYYYMMDD hh24:mi:ss')
     AND EVENT = 'row cache lock');


        P1
----------
        13  --正是dc_sequences

SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER                              GETS  GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- -------------
dc_sequences                      766929021       5065     766929020

发现此sequence中的cache size均为0，出现row cache lock与cpu高便不足为奇

将频繁调用的sequence cache size调整至500后，等待与cpu高的现象随之解决，不再复现。

SQL执行计划突变案例分析

发表于由

前几日，同事发现一套库数据库的活动会话数激增，大量活动会话集中等待gc buffer busy acquire上，如下图所示

经过更一步的查看，发现这些等待均在sql_id：f7hnmtx8bd5q3上，利用如下sql进行查看发现：

SQL_TEXT如下：
SELECT *
FROM
  (SELECT p.*
   FROM t p,
        TD_PLAN_LEG d
   WHERE p.THR_DEP = d.THR_DEP
     AND p.THR_ARR = d.THR_ARR
     AND p.FLTNO = d._FLTNO
     AND pDATE = d.DATE
     AND (p.THR_DEP = :1
          OR p.THR_ARR = :2)
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND d.LT_ID IS NULL
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND p.OP_TM > :3
   ORDER BY p.OP_TM)
WHERE ROWNUM <= :4


SQL历史执行效率：
TIME      PLAN_HASH_VALUE EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME GET_ONETIME   ROWS_PRO ROWS_ONETIME ET_MS_ONCE
------------ --------------- ------- ----------- ----------- --------- --------- ------- --------- ------- ------- ----------- ---------- ------------ ----------
2020092201        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092201        2495209816    3333  1351590946           0      2403      2409       0         0       0       0      405518        261            0        721
2020092202        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092202        2495209816    3238  1381884655           0      2449      2477       0         0       0       0      426771         82            0        756
2020092203        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092203        2495209816    3228  1380940555           0      2464      2475       0         0       0       0      427801         12            0        763
2020092204        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092204        2495209816    3249           0           0      2469      2486       0         0       0       0           0          0            0        760
2020092205        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092205        2495209816    3239  1401077673           0      2496      2510       0         0       0       0      432565          0            0        771
2020092206        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092206        2495209816    3240  1374013765           0      2449      2467       0         0       0       0      424078          0            0        756
2020092207        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092207        2495209816    3319           0           0      2584      2589       0         0       0       0           0        181            0        778
2020092208        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092208        2495209816    3656  1628720696           0      2827      2826       0         0       0       0      445493       1439            0        773
2020092209        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092209        2495209816    3238  1434887002           0      2610      2611       0         0       0       0      443140       2253            1        806
2020092210        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092210        2495209816    3240           0           0      2623      2629       0         0       0       0           0       2046            1        809
2020092211        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092211        2495209816    3240  1442329567           0      2672      2669       0         0       0       0      445163       2659            1        825
2020092212        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092212        2495209816    3240  1437033232           0      2676      2675       0         0       0       0      443529       2561            1        826
2020092213        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092213        2495209816    3222           0           0      2687      2689       0         0       0       0           0       2888            1        834
2020092214        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092214        2495209816    3240  1440743191           0      2706      2707       0         0       0       0      444674       3367            1        835
2020092215        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092215        2495209816    3243  1444795359           0      2747      2747       0         0       0       0      445512       2677            1        847
2020092216        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092216        2495209816    3243           0           0      2742      2749       0         0       0       0           0       2975            1        846
2020092217        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092217        2495209816    3256  1464276872           0      2774      2771       0         0       0       0      449716       2929            1        852
2020092218        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092218        2495209816    3240  1459846368           0      2802      2801       0         0       0       0      450570       3543            1        865
2020092219        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092219        2495209816    3240           0           0      2806      2806       0         0       0       0           0       3022            1        866
2020092220        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092220        2495209816    3221  1443354745           0      2819      2820       0         0       0       0      448108       2694            1        875
2020092221        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092221        2495209816    3239  1458055393           0      2891      2879       0         0       0       0      450156       2592            1        892
2020092222        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092222        2495209816    3240           0           0      2883      2887       0         0       0       0           0       2059            1        890
2020092223        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092223        2495209816    3240  1458681730           0      2926      2928       0         0       0       0      450210       1936            1        903
2020092300        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092300        2495209816    3238  1437599879           0      2925      2923       0         0       0       0      443978       1370            0        903
2020092301        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092301        2495209816    3251  1265725098           0      2330      2326       0         0       0       0      389334        695            0        717
2020092302        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092302        2495209816    3240  1322541308           0      2443      2447       0         0       0       0      408192        223            0        754
2020092303        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092303        2495209816    3240           0           0      2466      2471       0         0       0       0           0         41            0        761
2020092304        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092304        2495209816    3240  1309926437           0      2432      2431       0         0       0       0      404298          0            0        751
2020092305        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092305        2495209816    3250  1347086218           0      2466      2458       0         0       0       0      414488          0            0        759
2020092306        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092306        2495209816    3248  1327059467           0      2452      2452       0         0       0       0      408577          0            0        755
2020092307        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092307        2495209816    3242           0           0      2491      2488       0         0       0       0           0        194            0        768
2020092308        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092308        2495209816    3245  1405256862           0      2544      2538       0         0       0       0      433053       1625            1        784
2020092309        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092309        2495209816    3246  1421208667           0      2590      2579       0         0       0       0      437834       2518            1        798
2020092310        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092310        2495209816    3248           0           0      2619      2609       0         0       0       0           0       1961            1        806
2020092311        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092311        2495209816    3239  1426602319           0      2637      2632       0         0       0       0      440445       2673            1        814
2020092312        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092312        2495209816    3195  1403306564           0      2610      2603       0         0       0       0      439220       3029            1        817
2020092313        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092313        2495209816    3247           0           0      2698      2687       0         0       0       0           0       2976            1        831
2020092314        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092314        2495209816    3238  1418853144           0      2696      2690       0         0       0       0      438188       3496            1        833
2020092315        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092315        2495209816    3236  1443916772           0      2736      2728       0         0       0       0      446204       3600            1        846
2020092316        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092316        2495209816    3239           0           0      2731      2726       0         0       0       0           0       3630            1        843
2020092317        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092317        2495209816    3239  1435088513           0      2780      2768       0         0       0       0      443065       3446            1        858
2020092318        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092318        2495209816    3256  1422988985           0      2794      2782       0         0       0       0      437036       3043            1        858
2020092319        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092319        2495209816    3239           0           0      2816      2811       0         0       0       0           0       3108            1        869
2020092320        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092320        2495209816    3237  1414992685           0      2818      2813       0         0       0       0      437131       2993            1        870
2020092321        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092321        2495209816    3239  1450253448           0      2867      2859       0         0       0       0      447747       2686            1        885
2020092322        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092322        2495209816    3239           0           0      2866      2853       0         0       0       0           0       2661            1        885
2020092323        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092323        2495209816    3239  1440828417           0      2906      2889       0         0       0       0      444837       2007            1        897
2020092400        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092400        2495209816    3256  1405971757           0      2892      2884       0         0       0       0      431810       1569            0        888
2020092401        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092401        2495209816    3239   262264721           0      2332      2331       0         0       0       0       80971        570            0        720
2020092402        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092402        2495209816    3204  1328880815           0      2433      2428       0         0       0       0      414757        252            0        759
2020092403        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092403        2495209816    3240  1367144118           0      2459      2453       0         0       0       0      421958        101            0        759
2020092404        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092404        2495209816    3237  1318574101           0      2437      2432       0         0       0       0      407344         35            0        753
2020092405        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092405        2495209816    3220           0           0      2438      2435       0         0       0       0           0          0            0        757
2020092406        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092406        2495209816    3254  1346142138           0      2480      2469       0         0       0       0      413688          0            0        762
2020092407        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092407        2495209816    3238  1397020466           0      2503      2492       0         0       0       0      431445        160            0        773
2020092408        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092408        2495209816    3235           0           0      2529      2520       0         0       0       0           0       1401            0        782
2020092409        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092409        2495209816    3239  1424896212           0      2566      2556       0         0       0       0      439919       2275            1        792
2020092410        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092410        2495209816    3237  1421132007           0      2572      2569       0         0       0       0      439027       1956            1        795
2020092411        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092411        2495209816    3248           0           0      2629      2614       0         0       0       0           0       2913            1        809
2020092412        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092412        2495209816    3249  1448536971           0      2666      2651       0         0       0       0      445841       2722            1        820
2020092413        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092413        2495209816    3238  1437662381           0      2675      2668       0         0       0       0      443997       2445            1        826
2020092414        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092414        2495209816    3202           0           0      2680      2672       0         0       0       0           0       3238            1        837
2020092415        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092415        2495209816    3256  1450466091           0      2768      2758       0         0       0       0      445475       2920            1        850
2020092416        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092416        2495209816    3240  1450111226           0      2794      2779       0         0       0       0      447565       3109            1        862
2020092417        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092417        2495209816    3237           0           0      2824      2804       0         0       0       0           0       2818            1        873
2020092418        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092418        2495209816    3239  1424042026           0      2840      2831       0         0       0       0      439655       3346            1        877
2020092419        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092419        2495209816    3256  1435714284           0      2894      2885       0         0       0       0      440944       3120            1        889
2020092420        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092420        2495209816    3237           0           0      2914      2902       0         0       0       0           0       2788            1        900
2020092421        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092421        2495209816    3240  1433834204           0      2967      2946       0         0       0       0      442541       2857            1        916
2020092422        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092422        2495209816    3200  1413189546           0      2938      2924       0         0       0       0      441622       2346            1        918
2020092423        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092423        2495209816    3256           0           0      3032      3014       0         0       0       0           0       2108            1        931
2020092500        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092500        2495209816    3238  1417260989           0      3005      2982       0         0       0       0      437696       1358            0        928
2020092501        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092501        2495209816    3233  1296496694           0      2456      2444       0         0       0       0      401020        416            0        760
2020092502        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092502        2495209816    3220   473519624           0      2521      2518       0         0       0       0      147056        164            0        783
2020092503        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092503        2495209816    3257  1419336329           0      2546      2548       0         0       0       0      435780          9            0        782
2020092504        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092504        2495209816    3239  1410697902           0      2554      2548       0         0       0       0      435535          0            0        789
2020092505        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092505        2495209816    3239           0           0      2562      2548       0         0       0       0           0          0            0        791
2020092506        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092506        2495209816    3238  1422005253           0      2555      2551       0         0       0       0      439162          0            0        789
2020092507        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092507        2495209816    3244  1442245766           0      2591      2583       0         0       0       0      444589        276            0        799
2020092508        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092508        2495209816    3247           0           0      2642      2634       0         0       0       0           0       1679            1        814
2020092509        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092509        2495209816    3236  1467804298           0      2665      2656       0         0       0       0      453586       2599            1        824
2020092510        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092510        2495209816    3238  1462271228           0      2677      2670       0         0       0       0      451597       2009            1        827
2020092511        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092511        2495209816    3235           0           0      2720      2709       0         0       0       0           0       3010            1        841
2020092512        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092512        2495209816    3254  1465449842           0      2739      2723       0         0       0       0      450353       2555            1        842
2020092513        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092513        2495209816    3240  1459543406           0      2726      2709       0         0       0       0      450476       2664            1        841
2020092514        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092514        2495209816    3185           0           0      2702      2694       0         0       0       0           0       3784            1        848
2020092515        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092515        2495209816    3240  1447314954           0      2746      2742       0         0       0       0      446702       3116            1        848
2020092516        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092516        2495209816    3258  1475259620           0      2867      2821       0         0       0       0      452811       3183            1        880
2020092517        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092517        2495209816    3239           0           0      2841      2822       0         0       0       0           0       3168            1        877
2020092518        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092518        2495209816    3237  1455741217           0      2835      2815       0         0       0       0      449719       3258            1        876
2020092519        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092519        2495209816    3237  1456570263           0      2858      2846       0         0       0       0      449975       3135            1        883
2020092520        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092520        2495209816    3238           0           0      2881      2869       0         0       0       0           0       3033            1        890
2020092521        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092521        2495209816    3238  1464946512           0      2912      2898       0         0       0       0      452423       2918            1        899
2020092522        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092522        2495209816    3256  1468725511           0      2946      2929       0         0       0       0      451083       2386            1        905
2020092523        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092523        2495209816    3240           0           0      2948      2936       0         0       0       0           0       2258            1        910
2020092600        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092600        2495209816    3238  1437269657           0      2915      2904       0         0       0       0      443876       1648            1        900
2020092601        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092601        2495209816    3239  1358782568           0      2399      2387       0         0       0       0      419507        577            0        741
2020092602        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092602        2495209816    3240  1385828878           0      2525      2519       0         0       0       0      427725        158            0        779
2020092603        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092603        2495209816    3203  1391299844           0      2507      2503       0         0       0       0      434374         23            0        783
2020092604        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092604        2495209816    3237  1351047932           0      2500      2497       0         0       0       0      417377          0            0        772
2020092605        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092605        2495209816    3243           0           0      2529      2522       0         0       0       0           0          0            0        780
2020092606        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092606        2495209816    3235  1392017833           0      2526      2517       0         0       0       0      430299          0            0        781
2020092607        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092607        2495209816    3239  1424101671           0      2559      2544       0         0       0       0      439673        189            0        790
2020092608        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092608        2495209816    3239   496269954           0      2593      2583       0         0       0       0      153217       1619            0        800
2020092609        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092609        2495209816    3244  1473823605           0      2627      2617       0         0       0       0      454323       2200            1        810
2020092610        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092610        2495209816    3253  1473324036           0      2670      2658       0         0       0       0      452912       1995            1        821
2020092611        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092611        2495209816    3238           0           0      2716      2707       0         0       0       0           0       2491            1        839
2020092612        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092612        2495209816    3237  1477721737           0      2741      2733       0         0       0       0      456510       2460            1        847
2020092613        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092613        2495209816    3239  1483323345           0      2768      2755       0         0       0       0      457957       2757            1        855
2020092614        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092614        2495209816    3241  1487746111           0      2808      2792       0         0       0       0      459039       3145            1        866
2020092615        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092615        2495209816    3214  1478749632           0      2820      2809       0         0       0       0      460096       2790            1        878
2020092616        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092616        2495209816    3239           0           0      2869      2858       0         0       0       0           0       3045            1        886
2020092617        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092617        2495209816    3240  1483068741           0      2880      2871       0         0       0       0      457737       2691            1        889
2020092618        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092618        2495209816    3256  1496586513           0      2925      2913       0         0       0       0      459640       3351            1        898
2020092619        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092619        2495209816    3240   686804354           0      2943      2923       0         0       0       0      211977       2956            1        908
2020092620        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092620        2495209816    3239  1492171972           0      2955      2941       0         0       0       0      460689       2728            1        912
2020092621        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092621        2495209816    3242  1499007445           0      2971      2961       0         0       0       0      462371       2518            1        917
2020092622        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092622        2495209816    3254           0           0      2983      2974       0         0       0       0           0       2323            1        917
2020092623        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092623        2495209816    3201  1489464912           0      3019      3006       0         0       0       0      465312       1919            1        943
2020092700        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092700        2495209816    3239  1489900471           0      3025      3011       0         0       0       0      459988       1195            0        934
2020092701        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092701        2495209816    3240  1357716042           0      2434      2429       0         0       0       0      419048        521            0        751
2020092702        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092702        2495209816    3244  1448164591           0      2566      2560       0         0       0       0      446413        276            0        791
2020092703        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092703        2495209816    3252           0           0      2571      2559       0         0       0       0           0         72            0        791
2020092704        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092704        2495209816    3221  1417052962           0      2503      2501       0         0       0       0      439942          2            0        777
2020092705        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092705        2495209816    3240  1435598638           0      2533      2534       0         0       0       0      443086          0            0        782
2020092706        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092706        2495209816    3244           0           0      2527      2528       0         0       0       0           0          0            0        779
2020092707        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092707        2495209816    3254  1470802612           0      2605      2599       0         0       0       0      451998        204            0        801
2020092708        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092708        2495209816    3239  1466922394           0      2601      2589       0         0       0       0      452894       1735            1        803
2020092709        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092709        2495209816    3238  1462310461           0      2647      2640       0         0       0       0      451609       2688            1        818
2020092710        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092710        2495209816    3244  1460262764           0      2656      2651       0         0       0       0      450143       2056            1        819
2020092711        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092711        2495209816    3254  1485315112           0      2720      2708       0         0       0       0      456458       2956            1        836
2020092712        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092712        2495209816    3239  1475673310           0      2718      2710       0         0       0       0      455595       2987            1        839
2020092713        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092713        2495209816    3240  1480502924           0      2752      2741       0         0       0       0      456945       3107            1        849
2020092714        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092714        2495209816    3242           0           0      2770      2758       0         0       0       0           0       3774            1        855
2020092715        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092715        2495209816    3254  1485748247           0      2802      2783       0         0       0       0      456591       3499            1        861
2020092716        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092716        2495209816    3185           0           0      2774      2759       0         0       0       0           0       3173            1        871
2020092717        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092717        2495209816    3239  1488000864           0      2849      2839       0         0       0       0      459401       3113            1        880
2020092718        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092718        2495209816    3238  1480569006           0      2868      2860       0         0       0       0      457248       3294            1        886
2020092719        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092719        2495209816    3243  1479350901           0      2897      2886       0         0       0       0      456167       2813            1        893
2020092720        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092720        2495209816    3240  1466487471           0      2915      2902       0         0       0       0      452620       2953            1        900
2020092721        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092721        2495209816    3254  1473511907           0      2981      2966       0         0       0       0      452831       2821            1        916
2020092722        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092722        2495209816    3240           0           0      2987      2976       0         0       0       0           0       2468            1        922
2020092723        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092723        2495209816    3240  1485591039           0      3023      3010       0         0       0       0      458516       2491            1        933
2020092800        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092800        2495209816    3239  1492094762           0      3032      3023       0         0       0       0      460665       1508            0        936
2020092801        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092801        2495209816    3244  1381156470           0      2472      2464       0         0       0       0      425757        752            0        762
2020092802        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092802        2495209816    3251           0           0      2585      2581       0         0       0       0           0        265            0        795
2020092803        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092803        2495209816    3239  1465140224           0      2599      2588       0         0       0       0      452343         10            0        802
2020092804        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092804        2495209816    3190  1446640595           0      2553      2540       0         0       0       0      453492          0            0        800
2020092805        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092805        2495209816    3253           0           0      2607      2600       0         0       0       0           0          0            0        801
2020092806        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092806        2495209816    3240  1467000606           0      2597      2590       0         0       0       0      452778          0            0        802
2020092807        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092807        2495209816    3242  1472818082           0      2610      2604       0         0       0       0      454293        183            0        805
2020092808        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092808        2495209816    3236           0           0      2631      2616       0         0       0       0           0       1622            1        813
2020092809        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092809        2495209816    3240  1489167496           0      2670      2650       0         0       0       0      459620       2737            1        824
2020092810        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092810        2495209816    3239  1480634051           0      2677      2666       0         0       0       0      457127       2304            1        826
2020092811        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092811        2495209816    3240           0           0      2729      2716       0         0       0       0           0       3116            1        842
2020092812        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092812        2495209816    3244  1490574897           0      2738      2727       0         0       0       0      459487       2975            1        844
2020092813        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092813        2495209816    3239  1495331918           0      2775      2758       0         0       0       0      461665       3305            1        857
2020092814        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092814        2495209816    3253           0           0      2811      2791       0         0       0       0           0       3988            1        864
2020092815        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092815        2495209816    3239  1494368501           0      2810      2799       0         0       0       0      461367       3532            1        867
2020092816        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092816        2495209816    3208           0           0      2798      2786       0         0       0       0           0       3395            1        872
2020092817        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092817        2495209816    3253  1503663984           0      2890      2868       0         0       0       0      462239       3418            1        888
2020092818        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092818        2495209816    3239  1492963087           0      2880      2870       0         0       0       0      460933       3405            1        889
2020092819        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092819        2495209816    3239           0           0      2924      2914       0         0       0       0           0       3268            1        903
2020092820        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092820        2495209816    3243  1502574273           0      2932      2925       0         0       0       0      463328       3413            1        904
2020092821        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092821        2495209816    3254  1511131260           0      2960      2945       0         0       0       0      464392       2994            1        910
2020092822        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092822        2495209816    3240           0           0      2963      2950       0         0       0       0           0       2852            1        915
2020092823        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092823        2495209816    3240  1511966632           0      3007      2992       0         0       0       0      466656       2395            1        928
2020092900        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092900        2495209816    3206  1483716066           0      2963      2954       0         0       0       0      462794       1355            0        924
2020092901        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092901        2495209816    3254           0           0      2412      2406       0         0       0       0           0        406            0        741
2020092902        1636324798      99   352210787    13407233     21201      2845    7253      9775       0       6     3557685         37            0     214150
2020092902        2495209816    2178   981309957           0      1711      1707       0         0       0       0      450556         66            0        786
2020092903        1636324798     318  1160978121    38861241     60087     10011    3682     42585       0      31     3650875         56            0     188954
2020092903        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092904        1636324798     315  1138406066    37854487     59574     10038    3329     41809       0      29     3613988          0            0     189123
2020092904        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092905        1636324798     313  1143701796    38668999     59974     10059    3587     41987       0      33     3653999          0            0     191611
2020092905        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092906        1636324798     306           0    42268789     62487      9733    4179     44183       0      31           0          0            0     204205
2020092906        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092907        1636324798     323  1158824752    41644602     61905     10292    4001     43486       0      40     3587693        201            1     191657
2020092907        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092908        1636324798     309  1167837295    37852269     63066     10364    3967     44390       0      42     3779409       2124            7     204097
2020092908        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092909        1636324798     310  1118885638    36122238     63801      9831    4341     45415       0      46     3609309       3514           11     205811
2020092909        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092910        1636324798     275           0    33482927     65518      9199    9300     43109       0      48           0       3574           13     238246
2020092910        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092911        1636324798     235   915691746    71725197     66676      9162    7097     46978       0      44     3896561       4472           19     283727
2020092911        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092912        1636324798     257   942550994    60478871     65961      9210    6233     46797       0      42     3667514       5398           21     256657
2020092912        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092913        1636324798     163   646322619    45033189     68480      8699   10553     46613       0      86     3965169       4002           25     420122
2020092913        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092914        1636324798     178   655193101    34549897     67433      8435    9002     46876       0      78     3680860       4798           27     378839
2020092914        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092915        1636324798     162           0    42181497     69005      8512    9628     48119       0      73           0       5236           32     425955
2020092915        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092916        1636324798     180   659119160    37016987     67935      8320    8938     47657       0      74     3661773       6284           35     377415
2020092916        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092917        1636324798     178   668652544    36068743     66633      8204    8260     47129       0      66     3756475       4672           26     374341
2020092917        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092918        1636324798     182   690411953    47612967     68886      8662    9144     48329       0      64     3793472       6218           34     378496
2020092918        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092919        1636324798     180   716579287    38972462     68804      8613    8099     49062       0      68     3980996       5828           32     382243
2020092919        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092920        1636324798     198   725411011    42912011     66999      8657    8366     46635       0      72     3663692       4517           23     338377
2020092920        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092921        1636324798     182           0    56233403     68130      9001    9442     46655       0      79           0       3744           21     374340
2020092921        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092922        1636324798     200   761626806    56780398     67513      9089    8585     47243       0      60     3808134       3849           19     337564
2020092922        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092923        1636324798     217   785661905    49927309     66720      9066    7530     46950       0      59     3620562       3528           16     307464
2020092923        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093000        1636324798     203   827248817    65561662     67630      9780    8513     46470       0      70     4075117       2246           11     333155
2020093000        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093001        1636324798     325  1200248776   124828228     62141     10674    6906     40984       0      19     3693073        619            2     191202
2020093001        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093002        1636324798     310  1110431303    39739255     57335      9643    2766     40456       0      23     3582036        185            1     184951
2020093002        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093003        1636324798     320  1149651569    39694801     61120      9942    3466     43492       0      27     3592661         47            0     191000
2020093003        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093004        1636324798     327           0    40854731     62249     10587    3609     43766       0      31           0         21            0     190365
2020093004        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093005        1636324798     319  1125342936    38863217     59590     10107    3507     41533       0      32     3527721          0            0     186801
2020093005        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093006        1636324798     309  1145849658    38889950     62572     10367    3745     44353       0      34     3708251          0            0     202500
2020093006        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093007        1636324798     327  1171387941    39245953     63327     10553    3852     44758       0      40     3582226        241            1     193662
2020093007        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093008        1636324798     296           0    38143234     64608     10248    4218     45887       0      41           0       2377            8     218271
2020093008        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093009        1636324798     291  1070234826    36419287     65024      9485    4331     46952       0      42     3677783       3606           12     223449
2020093009        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093010        1636324798     297  1060264318    35236356     65779      9293    4500     47678       0      43     3569914       3473           12     221476
2020093010        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0

404 rows selected.

从DBA_HIST_SQLSTAT中看到，该SQL有两个执行计划:

PLAH_HASH_VALUE：(1) 1636324798 (2)2495209816其中2495209816效率相对较高，1636324798一直存在但是在9月29日之前并没有启用过，一直在用2495209816，直到2020092902（2点）开始启用，并且在2020092903（3点）完全使用。但是ORACLE为何突然使用了差的执行计划了？


Inst: 1   Child: 0    Plan hash value: 2495209816

                      --------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                       | Name                         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      --------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                |                              |        |       |   372K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY                  |                              |        |       |            |          |       |       |          |
                      |   2 |   VIEW                          |                              |      1 |   176 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY        |                              |      1 |   125 |   372K  (1)| 01:14:32 |  1024 |  1024 |          |
                      |   4 |     NESTED LOOPS                |                              |      1 |   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |   5 |      NESTED LOOPS               |                              |    120K|   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  6 |       TABLE ACCESS FULL         | T1                           |    120K|  4482K| 10211   (1)| 00:02:03 |       |       |          |
                      |*  7 |       INDEX RANGE SCAN          | T_POSITIONS_KEY              |      1 |       |     3   (0)| 00:00:01 |       |       |          |
                      |*  8 |      TABLE ACCESS BY INDEX ROWID| T                            |      1 |    87 |     3   (0)| 00:00:01 |       |       |          |
                      --------------------------------------------------------------------------------------------------------------------------------------------

Inst: 1   Child: 38   Plan hash value: 1636324798

                      ----------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation               | Name               | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      ----------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT        |                    |        |       |       |  1039K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY          |                    |        |       |       |            |          |       |       |          |
                      |   2 |   VIEW                  |                    |    118K|    19M|       |  1039K  (1)| 03:27:50 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY|                    |    118K|    14M|    16M|  1039K  (1)| 03:27:50 |  1024 |  1024 |          |
                      |*  4 |     HASH JOIN           |                    |    118K|    14M|  6024K|  1035K  (1)| 03:27:10 |  9516K|  3276K|   10M (0)|
                      |*  5 |      TABLE ACCESS FULL  | T1                 |    123K|  4576K|       | 10211   (1)| 00:02:03 |       |       |          |
                      |*  6 |      TABLE ACCESS FULL  | T                  |    118K|     9M|       |  1024K  (1)| 03:24:57 |       |       |          |
                      ----------------------------------------------------------------------------------------------------------------------------------

经过分析发现，此SQL涉及对象在9月28日时触发了统计信息收集,并且因此游标滚动失效，9月29日零时进行了硬解析。因数据库中绑定变量窥探关闭（_optim_peek_user_binds，被设定为FALSE），无法”窥视“直方图，根据数据分布与bind值分析，因数据倾斜严重+关闭“窥探直方图”情况下被认为平均分布，优化器选择了选择了”CBO认为效率高，实际效率差”的执行计划。针对此情形，可以利用SQL_PROFILE进行绑定（借助coe_load_sql_profile.sql）脚本绑定后

数据库GC类等待消失，SQL效率恢复至毫秒级别。

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous报错修复

发表于由

休假前，同事在异地恢复一套数据库时报错：RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous。以下内容为还原报错场景与修复手段：

ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@ora977b

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:13:01 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/04/2020 10:13:08
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous


SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
         1          2 1663399389 PROD3                    1 20200904 09:23:41
        49         50 1663401346 PROD3                    1 20200904 09:56:18

报错根因为此数据库在CATALOG库中注册了2次（期间重新建立过DB-create d atabase操作），所以在备份还原controfile时需要指定dbid：

RMAN> set dbid=1663401346       

executing command: SET DBID
database name is "PROD3" and DBID is 1663401346

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

为了避免后续再次出现此类问题，可以将已经废弃的数据库dbid从catalog中unregister,避免每次set dbid操作

[ora11g@Travelskydba-ora app]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:35:56 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid=1663399389 

executing command: SET DBID
database name is "PROD3" and DBID is 1663399389

RMAN> unregister database;

database name is "PROD3" and DBID is 1663399389

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> 

SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
        49         50 1663401346 PROD3                    1 20200904 09:56:18

SQL> 

[ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:37:32 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

shutdown abort导致事务提交？

发表于由

前不久，同事问了我一个这样的问题，并且附上了实验记录，在oracle中，为何shudown abort后导致了事务的提交，而并非回滚？现将他的实验记录贴出来，细心的朋友可能会马上发现问题。

SQL> conn scott/scott
Connected.
SQL> set line 100 pagesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7499 ALLEN      SALESMAN	      7698 19810220 00:00:00	   1600        300	   30
      7521 WARD       SALESMAN	      7698 19810222 00:00:00	   1250        500	   30
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 19810928 00:00:00	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7844 TURNER     SALESMAN	      7698 19810908 00:00:00	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

14 rows selected.

SQL> delete from emp where comm is not null;

4 rows deleted.

SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size		    2270072 bytes
Variable Size		 3.6776E+10 bytes
Database Buffers	 9.7979E+10 bytes
Redo Buffers		  197844992 bytes
Database mounted.
Database opened.
SQL> conn scott/scott
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

同事前来问我，为何重启后，事务提交了？不是应该回滚的么？

细心的同学可能已经发现了问题，执行完事务后，会话进行shutdown abort失败（权限不足），使用了conn 命令进行了用户的切换，引用Oracle官方文档中的描述”：

Usage

CONNECT commits the current transaction to the database, disconnects the current username from Oracle Database, and reconnects with the specified username.

这个问题虽然看起来过于细节也很基础，但是稍不注意便会造成数据的隐式COMMIT，特别是在SQLPLUS中操作数据的过程中，需要特别小心。

开发不规范引发的TOP SQL

发表于由

近日分析优化了一个TOP SQL，从这个SQL中可以得出，开发规范的重要性，数据库的性能平稳与数据库SQL开发与应用规范密不可分。

--原SQL代码较长，文本处理后的SQL如下：
SELECT ROWNUM, T.*
  FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE.....
          FROM T1_PART TB
         where TB.OBJECT_TYPE = :1 
           and TB.CREATED = :2
         ORDER BY TB.OBJECT_ID) T
 WHERE ROWNUM BETWEEN 1 AND 1000

SQL执行统计信息如下：

此条SQL存在两个执行计划，平均行数都是0行，但是buffer get却非常之大，最差的执行计划逻辑读平均要3百万+，此表示一个分区表，并且按照CREATED日期字段做了INTERVAL按天分区，并且CREATED字段上存在索引。

从执行计划中获悉，虽然谓词部分包含了分区键，但是仍然走了PARTITION RANG ALL的执行计划，并且以全表扫描扫描了所有分区，没有跨分区访问，为何没有分区裁剪呢？

继续阅读 →

缺失合理索引导致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 |
----------------------------------------------------------------------------------------------------------------------------