下午发现一条SQL,消耗大量IO。
SQL文本
(部分表名和字段名做了替换)
SELECT "A3"."apptype",
"A3"."appno",
"A2"."edate",
"A3"."cno",
"A1"."rcode",
"A3"."chnl",
"A1"."pm",
(SELECT SUM("A5"."pm")
FROM "t_p" "A5"
WHERE "A5"."cno" = "A3"."cno"),
"A3"."pdate",
"A3"."sdate",
"A3"."acode"
FROM "t_c" "A3", "t_e" "A2", "t_p" "A1"
WHERE "A2"."etype" = 'CT'
AND "A2"."estate" = '0'
AND "A3"."cno" = "A1"."cno"
AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
AND "A3"."CONTTYPE" = '1'
AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
AND "A1"."pno" = "A1"."MAINpno"
AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
AND EXISTS
(SELECT 1
FROM "t_ag" "A4"
WHERE "A4"."acode" = TRIM("A3"."acode")
AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
执行情况
IN SPID SID MACHINE OBJECT_NAME SQL_EXEC_START EVENT SQL_ID SQL_CHILD_NUMBER
-- --------- ---------- ------------------------- ------------------- ------------------- ------------------------ ------------- ----------------
2 20817 4255 xxxx t_e 2015-01-12 12:42:32 direct path read 0x7p8vx1hrmyn 0
PLAN_HASH_VALUE EXEC AVG_MIN AVG_BUFF_GET AVG_DF_READ_MB AVG_ROWS MIN BUFFER_GET DF_READ_MB SUM_ROWS
--------------- ---------- ---------- ------------ -------------- ---------- ---------- ---------- ---------- ----------
3632503727 1 107.27 462219484 3510012.76 0 107.27 462219484 3510012.76 0
由于连接条件是两个字段,or的关系。因此这个条件走了filter。
因此不得不在循环里面对t_e做全表扫描,单次执行物理读统计到的是3.5T(事后分析这条SQL应返回21万行,因此实际物理读远不止3.5T)。
14:46:45 SQL> select * from table(dbms_xplan.display_cursor('0x7p8vx1hrmyn', null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0x7p8vx1hrmyn, child number 0
-------------------------------------
SELECT "A3"."apptype","A3"."appno","A2"."edate","A3"."CON
T
SQL文本
(部分表名和字段名做了替换)
SELECT "A3"."apptype",
"A3"."appno",
"A2"."edate",
"A3"."cno",
"A1"."rcode",
"A3"."chnl",
"A1"."pm",
(SELECT SUM("A5"."pm")
FROM "t_p" "A5"
WHERE "A5"."cno" = "A3"."cno"),
"A3"."pdate",
"A3"."sdate",
"A3"."acode"
FROM "t_c" "A3", "t_e" "A2", "t_p" "A1"
WHERE "A2"."etype" = 'CT'
AND "A2"."estate" = '0'
AND "A3"."cno" = "A1"."cno"
AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
AND "A3"."CONTTYPE" = '1'
AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
AND "A1"."pno" = "A1"."MAINpno"
AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
AND EXISTS
(SELECT 1
FROM "t_ag" "A4"
WHERE "A4"."acode" = TRIM("A3"."acode")
AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
执行情况
IN SPID SID MACHINE OBJECT_NAME SQL_EXEC_START EVENT SQL_ID SQL_CHILD_NUMBER
-- --------- ---------- ------------------------- ------------------- ------------------- ------------------------ ------------- ----------------
2 20817 4255 xxxx t_e 2015-01-12 12:42:32 direct path read 0x7p8vx1hrmyn 0
PLAN_HASH_VALUE EXEC AVG_MIN AVG_BUFF_GET AVG_DF_READ_MB AVG_ROWS MIN BUFFER_GET DF_READ_MB SUM_ROWS
--------------- ---------- ---------- ------------ -------------- ---------- ---------- ---------- ---------- ----------
3632503727 1 107.27 462219484 3510012.76 0 107.27 462219484 3510012.76 0
由于连接条件是两个字段,or的关系。因此这个条件走了filter。
因此不得不在循环里面对t_e做全表扫描,单次执行物理读统计到的是3.5T(事后分析这条SQL应返回21万行,因此实际物理读远不止3.5T)。
14:46:45 SQL> select * from table(dbms_xplan.display_cursor('0x7p8vx1hrmyn', null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0x7p8vx1hrmyn, child number 0
-------------------------------------
SELECT "A3"."apptype","A3"."appno","A2"."edate","A3"."CON
T