借助sys_connect_by_path函数的SQL改写

近期在网上看到一个这样的SQL需求,需求源自scott.emp表中的数据:

create or replace view v1 as select * from scott.emp;
create or replace view v2 as select * from scott.emp;

SQL> select * from v1;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.


SQL的需求是,得到员工编号、员工姓名、工作类别、主管编号、主管姓名信息。网上的网友是这么实现的:

SQL> select a.empno,a.ename,a.job,a.mgr as v1_mgr,b.empno as v2_mgr,b.ename from v1 a left join v2 b on a.MGR=b.empno order by 1;

     EMPNO ENAME      JOB	    V1_MGR     V2_MGR ENAME
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902	 7902 FORD
      7499 ALLEN      SALESMAN	      7698	 7698 BLAKE
      7521 WARD       SALESMAN	      7698	 7698 BLAKE
      7566 JONES      MANAGER	      7839	 7839 KING
      7654 MARTIN     SALESMAN	      7698	 7698 BLAKE
      7698 BLAKE      MANAGER	      7839	 7839 KING
      7782 CLARK      MANAGER	      7839	 7839 KING
      7788 SCOTT      ANALYST	      7566	 7566 JONES
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN	      7698	 7698 BLAKE
      7876 ADAMS      CLERK	      7788	 7788 SCOTT
      7900 JAMES      CLERK	      7698	 7698 BLAKE
      7902 FORD       ANALYST	      7566	 7566 JONES
      7934 MILLER     CLERK	      7782	 7782 CLARK

14 rows selected.

Plan hash value: 2322654302

--------------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	   7 (100)|	     |	   14 |00:00:00.01 |	  12 |
|   1 |  SORT ORDER BY	    |	   |	  1 |	  14 |	   7  (15)| 00:00:01 |	   14 |00:00:00.01 |	  12 |
|*  2 |   HASH JOIN OUTER   |	   |	  1 |	  14 |	   6   (0)| 00:00:01 |	   14 |00:00:00.01 |	  12 |
|   3 |    TABLE ACCESS FULL| EMP  |	  1 |	  14 |	   3   (0)| 00:00:01 |	   14 |00:00:00.01 |	   6 |
|   4 |    TABLE ACCESS FULL| EMP  |	  1 |	  14 |	   3   (0)| 00:00:01 |	   14 |00:00:00.01 |	   6 |
--------------------------------------------------------------------------------------------------------------

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

   2 - access("MGR"="EMPNO")

此方法最终实现了目标需求,利用了自关联的方式,也就是emp表需要扫描2次,小表是OK的,emp表中主管与员工是存在层级关系的,我们能否利用上层级查询消除自关联?

SQL> set line 1000 pagesize 200
col new_name for a20
col new_mgr for a20
col mgr_name for a60
select empno,ename,job,mgr,mgr_name from (
select empno,ename,job,mgr,path,regexp_substr(path,'[^,]+',1,decode((lv-1),0,null,(lv-1))) as mgr_name from (
SELECT empno,
       ename,
       job,
       mgr,
       LEVEL lv,
       sys_connect_by_path(ename,',') AS path
FROM emp connect by prior empno=mgr start with mgr is null));SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9

     EMPNO ENAME      JOB	       MGR MGR_NAME
---------- ---------- --------- ---------- ------------------------------------------------------------
      7839 KING       PRESIDENT
      7566 JONES      MANAGER	      7839 KING
      7788 SCOTT      ANALYST	      7566 JONES
      7876 ADAMS      CLERK	      7788 SCOTT
      7902 FORD       ANALYST	      7566 JONES
      7369 SMITH      CLERK	      7902 FORD
      7698 BLAKE      MANAGER	      7839 KING
      7499 ALLEN      SALESMAN	      7698 BLAKE
      7521 WARD       SALESMAN	      7698 BLAKE
      7654 MARTIN     SALESMAN	      7698 BLAKE
      7844 TURNER     SALESMAN	      7698 BLAKE
      7900 JAMES      CLERK	      7698 BLAKE
      7782 CLARK      MANAGER	      7839 KING
      7934 MILLER     CLERK	      7782 CLARK

14 rows selected.


Plan hash value: 422387769

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |	|      1 |	  |	3 (100)|	  |	14 |00:00:00.01 |	2 |
|   1 |  VIEW					 |	|      1 |	3 |	3  (34)| 00:00:01 |	14 |00:00:00.01 |	2 |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|	|      1 |	  |	       |	  |	14 |00:00:00.01 |	2 |
|   3 |    TABLE ACCESS FULL			 | EMP	|      1 |     14 |	2   (0)| 00:00:01 |	14 |00:00:00.01 |	2 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("MGR"=PRIOR NULL)
       filter("MGR" IS NULL)

利用sys_connect_by_path与regexp_substr利用层级关系截取,消除了自关联,并且emp表只访问了1次。

MRP0进程Hung?

前天晚上,同事发来微信求助,监控到一套2节点数据库RAC 数据库主备库同步状态异常,并且同事经过分析发现造成此问题的是因为Dataguard MRP0进程存在,但是停止在某一个seqence#上,应用的sequnce#与block不进行恢复。

随即登录备库数据库查看同步状态,发现MRP0进程停滞在sequnce:197788与block:186030上,实际上并没有进行“APPLYING_LOG”。

SQL> set linesize 200
select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0
SQL>   2  ;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1     199217     123144             0            0
RFS       IDLE         LGWR              2     142409      77956             0            0
MRP0      APPLYING_LOG N/A               1     197788     186030             9            9

此日志197788归档日志文件存在与备库,并且同事反馈尝试alter database register logfile、主库RMAN重新恢复此197788归档日志,均无效。

查询v$archived_log确认197788这个归档文件为20210109 15:00左右主库产生的,分别查看一下主库/备库的alert日志,看看那时数据库有无异常情况发生:

节点1,传输至备时出现异常:
Thread 1 advanced to log sequence 197788 (LGWR switch)
  Current log# 5 seq# 197788 mem# 0: +DG_REDO/Travelskydba/onlinelog/group_5.287.1003922183
  Current log# 5 seq# 197788 mem# 1: +DG_REDO/Travelskydba/onlinelog/group_5.286.1003922183
Sat Jan 09 15:32:25 2021
Archived Log entry 275332 added for thread 1 sequence 197787 ID 0x9c755e67 dest 1:
Sat Jan 09 15:32:25 2021
ARCj: Standby redo logfile selected for thread 1 sequence 197787 for destination LOG_ARCHIVE_DEST_2
Sat Jan 09 15:33:14 2021
LNS: Standby redo logfile selected for thread 1 sequence 197788 for destination LOG_ARCHIVE_DEST_2
Sat Jan 09 15:43:17 2021
Thread 1 advanced to log sequence 197789 (LGWR switch)
  Current log# 1 seq# 197789 mem# 0: +DG_REDO/Travelskydba/onlinelog/group_1.295.1003922179
  Current log# 1 seq# 197789 mem# 1: +DG_REDO/Travelskydba/onlinelog/group_1.294.1003922179
Sat Jan 09 15:43:17 2021
Archived Log entry 275335 added for thread 1 sequence 197788 ID 0x9c755e67 dest 1:
Sat Jan 09 15:47:06 2021
WARN: ARC1: Terminating pid 4047 hung on an I/O operation
Killing 1 processes with pids 4047 (Process by index) in order to remove hung processes. Requested by OS process 31580 on instance 1
ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2
ARCo: Standby redo logfile selected for thread 1 sequence 197788 for destination LOG_ARCHIVE_DEST_2

节点2,传输至备库时出现异常:
Sat Jan 09 15:47:15 2021
Archived Log entry 275337 added for thread 2 sequence 141662 ID 0x9c755e67 dest 1:
Sat Jan 09 15:47:35 2021
WARN: ARC2: Terminating pid 27590 hung on an I/O operation
Killing 1 processes with pids 27590 (Process by index) in order to remove hung processes. Requested by OS process 27465 on instance 2
ARC2: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2
Sat Jan 09 15:47:37 2021
NSA2 started with pid=29, OS id=2625
Sat Jan 09 15:47:42 2021


备库节点:
Archived Log entry 77427 added for thread 1 sequence 197786 ID 0x9c755e67 dest 1:
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/2_141661_853598219.arc
Media Recovery Waiting for thread 2 sequence 141662 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937
Media Recovery Waiting for thread 1 sequence 197788 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 197788 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_7.282.1003923169
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_7.280.1003923169

备库节点在1月9日 16:24:31时已经应用完成197788日志
Sat Jan 09 16:24:31 2021
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/1_197788_853598219.arc

197788这个日志已经应用完毕,并且197789日志也完好的存在与备库节点 ,那么很有可能目前备库MRP0进程不工作的原因是在等其他日志应用完毕,。备库应用日志需要连续,那么是否有可能其他节点或本节点的其他日志?

我们可以利用主库如下sql查询进行确认:

SQL> SELECT FIRST_CHANGE#,
       THREAD#,
       DEST_ID,
       STATUS,
       FAL,
       APPLIED,
       DELETED,
       to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,
       to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') COMPLETION_TIME,
       sequence#
FROM v$archived_log
WHERE to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') BETWEEN '20210109 15:00:17' AND '20210109 16:00:17'  order by 1,5  2    3    4    5    6    7    8    9   10   11   12  ;

   FIRST_CHANGE#          THREAD#          DEST_ID S FAL APPLIED   DEL FIRST_TIME        COMPLETION_TIME          SEQUENCE#
---------------- ---------------- ---------------- - --- --------- --- ----------------- ----------------- ----------------
   9555246783243                2                2 A NO  YES       NO  20210109 14:32:30 20210109 15:04:31           141659
   9555246783243                2                1 D NO  NO        YES 20210109 14:32:30 20210109 15:04:32           141659
   9555247407959                1                1 D NO  NO        YES 20210109 14:54:15 20210109 15:04:31           197783
   9555247407959                1                2 A NO  YES       NO  20210109 14:54:15 20210109 15:04:31           197783
   9555247700807                1                2 A NO  YES       NO  20210109 15:04:30 20210109 15:15:10           197784
   9555247700807                1                1 D NO  NO        YES 20210109 15:04:30 20210109 15:15:10           197784
   9555247701783                2                2 A NO  YES       NO  20210109 15:04:31 20210109 15:32:20           141660
   9555247701783                2                1 D NO  NO        YES 20210109 15:04:31 20210109 15:32:21           141660
   9555248017764                1                1 D NO  NO        YES 20210109 15:15:10 20210109 15:25:40           197785
   9555248017764                1                2 A NO  YES       NO  20210109 15:15:10 20210109 15:25:40           197785
   9555248313954                1                2 A NO  YES       NO  20210109 15:25:40 20210109 15:33:14           197786
   9555248313954                1                1 D NO  NO        YES 20210109 15:25:40 20210109 15:32:19           197786
   9555248508993                1                1 D NO  NO        YES 20210109 15:32:19 20210109 15:32:25           197787
   9555248508993                1                2 A YES YES       NO  20210109 15:32:19 20210109 15:32:26           197787
   9555248509181                2                1 D NO  NO        YES 20210109 15:32:20 20210109 15:32:23           141661
   9555248509181                2                2 A NO  YES       NO  20210109 15:32:20 20210109 15:32:23           141661
   9555248510236                2                1 D NO  NO        YES 20210109 15:32:23 20210109 15:47:15           141662
   9555248510774                1                1 D NO  NO        YES 20210109 15:32:25 20210109 15:43:17           197788
   9555248831803                1                1 D NO  NO        YES 20210109 15:43:17 20210109 15:47:14           197789
   9555248831803                1                2 A YES NO        NO  20210109 15:43:17 20210109 15:52:30           197789
   9555248943929                1                1 D NO  NO        YES 20210109 15:47:14 20210109 15:52:14           197790
   9555248944032                2                1 D NO  NO        YES 20210109 15:47:15 20210109 15:47:42           141663
   9555248944032                2                2 A YES NO        NO  20210109 15:47:15 20210109 15:53:18           141663
   9555248950448                2                1 D NO  NO        YES 20210109 15:47:42 20210109 15:53:18           141664
   9555248950448                2                2 A YES NO        NO  20210109 15:47:42 20210109 15:55:13           141664

25 rows selected.

上述查询确认后,每个sequence应当归档到dest_id=1(本地),dest_id=2(备库),但是发现thread 2的141662,未显示归档到DEST_ID=2,并且下一个日志便是197788
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/2_141661_853598219.arc
Media Recovery Waiting for thread 2 sequence 141662 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937
Media Recovery Waiting for thread 1 sequence 197788 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 197788 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_7.282.1003923169
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_7.280.1003923169
Sat Jan 09 15:49:02 2021
RFS[31]: Possible network disconnect with primary database
后续,备库一值在读取141662这个日志文件,但

Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937

Mon Jan 18 20:42:42 2021
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937

此时,主库141662这个归档认为已经传输到备库并且已经删除,但备库实际并未接收到。Threa#1 的197788无法应用的根因为Thread# 2 归档141662并未应用。

让同事将141662归档从主库归档备份中恢复出来 ,拷贝至备库注册或等待备库(fal_client)从主库(fal_server)中获取。同事恢复此归档文件后,备库MRP0进程立即开始工作,进行正常的日志应用,同时主库的v$archived_log中的日志记录变成了复合预期的状态:

SQL> SELECT FIRST_CHANGE#,
       THREAD#,
       DEST_ID,
       STATUS,
       FAL,
       APPLIED,
       DELETED,
       to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,
       to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') COMPLETION_TIME,
       sequence#
FROM v$archived_log
WHERE sequence#=141662  2    3    4    5    6    7    8    9   10   11   12  ;

   FIRST_CHANGE#          THREAD#          DEST_ID S FAL APPLIED   DEL FIRST_TIME        COMPLETION_TIME          SEQUENCE#
---------------- ---------------- ---------------- - --- --------- --- ----------------- ----------------- ----------------
   9555248510236                2                1 D NO  NO        YES 20210109 15:32:23 20210109 15:47:15           141662
   9555248510236                2                1 A NO  NO        NO  20210109 15:32:23 20210118 21:39:53           141662
   9555248510236                2                2 A YES YES       NO  20210109 15:32:23 20210118 21:40:33           141662

利用sysmetric_history与dba_hist_event_histogram定位IO性能问题

近期发现,公司生产4套数据库从12月9日起,每周三22:00-22:30时间段均有应用事务DML缓慢的现象,IO等待事件明显增多:

IO等待类事件明显均有显著抬头的现象,例如log file sync等,但是我们如何定位是否真正的数据库IO真正出现问题?是因还是果?是否是io requeset变多还是IO真正出现问题?

问题时间段的AWR:

AWR中的Avg Wait极有可能被平均
另外一套库的显示的较为直观,log file sync平均等待高达17ms之多

换一个角度,我们从Oracle提供的数据库内部的v$metric_history与dba_hist_sysmetric_history动态视图中可以从“ Average Synchronous Single-Block Read Latency、I/O Megabytes per Second、I/O Requests per Second”等指标中辅助定位:

select to_char(end_time, 'yyyymmdd hh24:mi:ss') as time,
       max(case
             when metric_name = 'Average Synchronous Single-Block Read Latency' then
              round(value,2)
             else null
           end) as "Single Block Read Latency",
       max(case
             when metric_name = 'I/O Megabytes per Second' then
              round(value,2)
             else null
           end) as "IO Megabytes per Second",
       max(case
             when metric_name = 'I/O Requests per Second' then
              round(value,2)
             else null
           end) as "I/O Requests per Second"
  from dba_hist_sysmetric_history where instance_number=1 and to_char(end_time, 'yyyymmdd hh24:mi:ss') between '20210106 22:00:00' and '20210106 23:00:00'
 group by to_char(end_time, 'yyyymmdd hh24:mi:ss') order by 1

TIME              Single Block Read Latency IO Megabytes per Second I/O Requests per Second
----------------- ------------------------- ----------------------- -----------------------
20210106 21:00:44                      1.75                   23.69                 2060.17
20210106 21:01:44                       2.1                   13.32                 1474.73
20210106 21:02:43                      1.99                    18.6                 1716.56
20210106 21:03:43                      1.43                   11.54                 1089.31
20210106 21:04:43                      1.69                   20.77                 1567.28
20210106 21:05:43                      1.61                   17.92                 1806.92
20210106 21:06:44                      2.21                   27.95                 2466.91
20210106 21:07:44                      1.63                   11.29                 1052.18
20210106 21:08:44                      2.01                   17.23                 1729.85
20210106 21:09:43                      1.63                   12.53                  1309.2
20210106 21:10:43                      1.36                   15.53                 1180.81
20210106 21:11:43                      2.57                   12.97                 1399.38
20210106 21:12:43                      1.42                   16.71                 1323.95
20210106 21:13:44                      1.65                   10.37                 1367.22
20210106 21:14:44                      1.42                   20.49                    1493
20210106 21:15:44                      1.66                    8.21                 1186.28
20210106 21:16:43                      2.39                   23.23                 2012.56
20210106 21:17:43                       1.3                   11.34                 1219.04
20210106 21:18:43                      1.15                   15.06                 1219.79
20210106 21:19:43                      1.66                       7                 1057.58
20210106 21:20:44                      1.38                   19.74                 1402.93
20210106 21:21:44                      2.59                    7.66                 1086.08
20210106 21:22:44                      1.22                   16.51                 1307.65
20210106 21:23:43                      1.39                   12.68                 1391.17
20210106 21:24:43                      1.43                   15.38                 1255.99
20210106 21:25:43                      1.37                   13.82                 1489.76
20210106 21:26:44                       1.9                   24.64                 2456.92
20210106 21:27:44                      1.49                    7.35                 1232.54
20210106 21:28:44                      1.06                   50.22                  1287.7
20210106 21:29:44                      2.06                   31.16                 1516.66
20210106 21:30:43                      1.21                   67.67                 1750.49
20210106 21:31:43                      2.52                   67.19                  1398.3
20210106 21:32:43                      1.68                   38.94                 1831.17
20210106 21:33:44                      1.46                    9.81                 1366.68
20210106 21:34:44                      1.61                   17.64                 1447.77
20210106 21:35:44                      1.52                   15.25                 1860.36
20210106 21:36:44                      1.61                   26.07                 2350.26
20210106 21:37:43                      2.18                   11.01                 1161.19
20210106 21:38:43                      1.38                   16.08                 1438.71
20210106 21:39:44                      1.56                   10.44                 1005.47
20210106 21:40:44                      1.23                   15.76                 1202.61
20210106 21:41:44                      2.21                    8.56                 1374.73
20210106 21:42:44                      1.71                    19.9                 1298.54
20210106 21:43:44                      1.49                    9.08                 1196.41
20210106 21:44:43                      1.79                   18.07                 1715.96
20210106 21:45:43                      1.86                   11.46                 1055.36
20210106 21:46:44                      1.36                   22.71                  1960.6
20210106 21:47:44                      1.13                    27.1                 1589.31
20210106 21:48:44                      1.39                   18.62                 1142.78
20210106 21:49:44                      1.88                    6.48                  904.06
20210106 21:50:44                      1.22                   20.12                  1420.4
20210106 21:51:43                      1.79                    7.54                 1054.08
20210106 21:52:44                      1.32                   16.79                 1246.04
20210106 21:53:44                       1.5                   12.89                 1509.54
20210106 21:54:44                      1.59                   14.58                  961.99
20210106 21:55:44                      1.75                    8.41                 1114.03
20210106 21:56:44                      1.42                   29.02                 2388.62
20210106 21:57:44                       1.7                    6.22                  815.38
20210106 21:58:43                      1.36                   14.27                 1024.69
20210106 21:59:44                      1.89                   10.66                 1133.77
20210106 22:00:44                      1.02                   52.87                 3232.29
20210106 22:01:44                       .71                   94.45                 4846.43
20210106 22:02:44                      5.28                   83.54                 2627.49
20210106 22:03:44                      7.04                   69.19                 2067.08
20210106 22:04:44                      7.07                   74.68                 2382.61
20210106 22:05:43                      4.87                   79.73                 2654.56
20210106 22:06:44                      5.94                   90.77                 3937.21
20210106 22:07:44                      8.01                   31.11                 1074.18
20210106 22:08:44                      6.16                   26.27                 1639.99
20210106 22:09:44                      2.71                    31.5                 1797.32
20210106 22:10:44                      5.12                   37.47                 1533.45
20210106 22:11:44                      5.04                   62.63                 1520.07
20210106 22:12:44                      3.81                   35.91                 1402.06
20210106 22:13:44                      3.66                   18.25                 1626.73
20210106 22:14:44                      3.01                   61.43                  2179.6
20210106 22:15:44                      2.59                   75.41                 1789.91
20210106 22:16:44                      3.13                    91.9                 2745.88
20210106 22:17:44                      2.97                   86.56                  1798.5
20210106 22:18:45                      2.33                   84.64                 1505.69
20210106 22:19:44                      1.74                    72.1                 1378.42
20210106 22:20:44                      1.84                   91.02                 1957.69
20210106 22:21:44                      2.72                   68.14                 1447.02
20210106 22:22:44                      2.18                   80.76                 1649.19
20210106 22:23:44                      2.75                   75.58                 1676.43
20210106 22:24:44                       2.2                   36.45                 1502.78
20210106 22:25:45                      2.21                   21.77                 1429.71
20210106 22:26:44                      2.61                   40.56                 2667.25
20210106 22:27:44                      2.09                   19.95                 1068.11
20210106 22:28:44                      1.39                   57.66                 1419.02
20210106 22:29:44                      1.61                   44.66                 1701.81
20210106 22:30:44                       1.2                   74.61                 2328.65
20210106 22:31:45                      1.29                   83.01                 2020.88
20210106 22:32:45                       .76                   94.96                 2706.07
20210106 22:33:44                      1.02                   22.84                 2032.87
20210106 22:34:44                       .74                   58.67                 2297.62
20210106 22:35:44                      1.76                   81.66                 1727.17
20210106 22:36:44                       .58                    66.2                 4839.24
20210106 22:37:44                       .87                   29.04                 1617.19
20210106 22:38:45                      1.54                   53.16                 1539.99
20210106 22:39:45                      1.21                    51.9                  1420.5
20210106 22:40:44                         1                   49.76                 1646.52
20210106 22:41:44                      2.23                   74.05                 1761.78
20210106 22:42:44                      1.17                  146.94                 1505.77
20210106 22:43:44                      1.39                   75.94                 1620.12
20210106 22:44:45                      1.07                   73.43                 2045.32
20210106 22:45:45                       .44                   82.36                 2572.26
20210106 22:46:44                         2                   54.02                 1909.32
20210106 22:47:44                      1.61                   43.08                 1479.45
20210106 22:48:44                      1.29                   47.81                 1296.04
20210106 22:49:44                      1.39                    50.6                 1337.25
20210106 22:50:44                       1.3                   55.93                 1497.06
20210106 22:51:45                      1.85                   43.28                 1074.38
20210106 22:52:45                      1.06                   93.48                 1559.25
20210106 22:53:44                      1.17                   39.43                 2132.45
20210106 22:54:44                       .54                   50.37                 2259.39
20210106 22:55:44                      1.35                   47.12                 1347.34
20210106 22:56:44                      2.09                   57.55                 2647.21
20210106 22:57:45                      1.33                   46.67                  980.51
20210106 22:58:45                       .68                   74.52                 1480.33
20210106 22:59:45                      1.19                    51.5                 1871.55

可以从上述三个指标中观察,Single Block Read Latency高的时候,IO请求量并不匹配,并非因为IO请求量导致IO效率下降,延时变高。
延时指标高,IO量并不大
继续阅读

关于QB_NAME的hint

近期,在优化一个SQL时用到了QB_NAME的HINT,此HINT我经常使用的原因目的是让子查询单独作为一个整体,与其他表关联时可以进行调整驱动顺序,举例:

SELECT b.owner,
       b.object_name,
       b.created,
       b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN')
执行计划如下:
Plan hash value: 3977992979

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |   569 (100)| 	 |    119 |00:00:00.05 |    1445 |
|*  1 |  HASH JOIN RIGHT SEMI	     |	       |      1 |  64830 |   569  (11)| 00:00:07 |    119 |00:00:00.05 |    1445 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P       |      1 |   2192 |   116   (0)| 00:00:02 |    119 |00:00:00.01 |      17 |
|*  3 |    INDEX RANGE SCAN	     | P_IDX01 |      1 |   2192 |     6   (0)| 00:00:01 |    119 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS FULL	     | FC      |      1 |   3000K|   439  (11)| 00:00:06 |    100K|00:00:00.02 |    1428 |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / C@SEL$2
   3 - SEL$5DA710D3 / C@SEL$2
   4 - SEL$5DA710D3 / B@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' '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" "B"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$2" ("P"."OWNER"))
      LEADING(@"SEL$5DA710D3" "B"@"SEL$1" "C"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "C"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
   3 - access("C"."OWNER"='NFS_SN')
继续阅读

诡异的ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

今年以来,公司多套RAC数据库(版本:11.2.0.4)ASM实例频繁报出ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O错误。这些数据库早在2016年时印象中便出现了此类报错,并且报错并非单个数据库偶发现象,而是大面积爆发,从3月至今仍然有些许数据库会抛出零星错误出现。

什么是ORA-27090?<参考 Doc ID 579108.1 >文中提到,此类报错的原因是操作系统异步IO请求参数 aio-max-nr 过低并且需要调整并发未完成的异步请求数目,避免异步IO请求失败的情况, Oracle官方文档中的建议值如下:

The "aio-max-nr" kernel limit is too low.
io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),

但是很诡异的是,我们的数据库早在2016年左右(第一次触发此问题)时,已经将该参数调整至4194304,理论上是不应该触发异步IO不足的问题。并且此报错风险性较高,ASM实例异步IO失败会导致数据库面临crash问风险,例如DBWR将脏块落盘(数据文件)时,如果此时DBWR进程无法进行时,数据库崩溃等其他异常行为会有极大可能性的。虽然可以将systcl.conf中的aio-max-nr一直调大,但是总归不是长久之计,并且在某年某月时会再次“爆发”。

最可怕的是,通过分析alert中的trace文件时,并没有发现任何有价值信息,所有的线索就是异步IO请求超过最大限制,导致某些IO请求失败抛出错误。。。

通过查看cat /proc/sys/fs/aio-nr查看当前的异步IO请求数量,此值偶尔下降但总量一直处于增长态势, 此时严重怀疑是否数据库中有某些进程执行异步IO请求后并没有释放IO描述符?造成异步IO描述符泄露?

我们数据库集群后发现异步IO请求变回归到正常范围内,但是态势仍然处于上升趋势:

ora11g@travelskydba-rac$ cat /proc/sys/fs/aio-nr 
22784
继续阅读

ORA-00704: bootstrap process failure故障修复

近日,同事反馈一套测试测试数据库宕机,进行重启数据库操作时发现数据库无法open,抛出ORA-00704: bootstrap process failure错误,版本为11.2.0.4

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57338
Session ID: 96 Serial number: 3

错误一并抛出了ORA-00942: table or view does not exist错误,应该是系统表丢失导致的bootstrap失败,并且alert日志中没有任何其他日志输出。

将数据库启动到nomount与mount状态,利用10046 event跟踪bootstrap过程:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57585
Session ID: 96 Serial number: 3

访问/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc发现访问access$表时抛出异常:

继续阅读

核心系统表关联顺序错误导致的低效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高的现象随之解决,不再复现。