【讨论】麻烦帮看下这样SQL或者视图需不需要调整...

seebise · 发表于 2014-6-10 09:22

小弟刚刚被放到公司管ORACLE 的岗位上,让我去一个数据库上找慢SQL,我直接看ADDM,发现每次里面都有下面这样的SQL占用使用大量USER IO,占用大量的数据库时间,使用对调 SQL 完全不懂,请大家帮忙。。。

这是那条语句,看起来时查个视图
SELECT "A1"."EMPLOYEENO","A1"."CARDNO","A1"."BGNAME" FROM "RECRUIT_PERSONINFO_HN_V" "A1" WHERE "A1"."INCOMPANYDAY"=TRUNC(SYSDATE@!)

这是RECRUIT_PERSONINFO_HN_V视图的结构

  CREATE OR REPLACE FORCE VIEW "RECRUIT_PERSON_LH"."RECRUIT_PERSONINFO_HN_V" ("CARDNO", "EMPLOYEENO", "PERSONNAME", "SE
X", "EMPIDNO", "INCOMPANYDAY", "PROVINCE", "PROVINCENAME", "REGION", "REGIONNAME
", "COUNTY", "COUNTYNAME", "LINE", "FACTORYID", "FACTORYNAME", "ADDRESSCODE", "A
DDRESSNAME", "PERSONTYPEID", "PERSONTYPENAME", "DATASOURCEID", "DATASOURCE", "GI
VEBG", "BGNAME", "SCHOOL", "COMEDATE", "PHONE", "CREATEDATE", "CREATEUSER") AS
  select a.cardno,
   a.employeeno,
   a.personname,
   a.sex,
   a.empidno,
   a.incompanyday,
   a.province,
   b.provincename,
   a.region,
   c.regionname,
   a.county,
   d.countyname,
   a.line,
   a.factoryid,
   e.factoryname,
   a.addresscode,
   f.addressname,
   a.persontypeid,
   i.persontypename,
   a.datasource datasourceid,
   g.addressname datasource,
   a.givebg,
   h.bgname,
   a.school,
   a.comedate,
   a.phone,
   a.createdate,
   a.createuser
   from bs_person_information_his a,bs_province b,bs_region c,bs_county d,bs_factory e,bs_address f
,bs_datasource g,bs_bg h,bs_persontype i
   where a.factoryid in ('FACTORY035','FACTORY999')
         and a.province = b.provincecode(+)
         and a.region = c.regioncode(+)
         and a.county = d.countycode(+)
         and a.factoryid = e.factoryid(+)
         and a.addresscode = f.addresscode(+)
         and a.datasource = g.addresscode(+)
         and a.givebg = h.bgcode(+)
         and a.persontypeid = i.persontypeid(+)

其中只有bs_person_information_his这表有400多万行,其他的只有20行左右,看了SELECT的执行计划全是hash jion 和全表扫描。。。

但是这样的东西需要调吗？改sql,加索引?
怎么说明问题啊?

seebise · 发表于 2014-6-10 09:25

我表述的可能不是很清楚，请大家见谅啊.

2009532140 · 发表于 2014-6-10 09:29

计划发来看看？

hh7yx · 发表于 2014-6-10 09:46

bs_person_information_his 表建INCOMPANYDAY 和factoryid 的组合索引看看、

seebise · 发表于 2014-6-10 09:51

SQL> select * from table(dbms_xplan.display_cursor('a3zjumftjfzy3'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  a3zjumftjfzy3, child number 0
-------------------------------------
SELECT "A1"."EMPLOYEENO","A1"."CARDNO","A1"."BGNAME" FROM "RECRUIT_PERSONINFO_HN_V" "A1" WHERE
"A1"."INCOMPANYDAY"=TRUNC(SYSDATE@!)

Plan hash value: 2492492916

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                         |    |    | 40262 (100)|    |
|*  1 |  HASH JOIN RIGHT OUTER    |                         |  1010 | 140K| 40262 (1)| 00:08:04 |
| 2 | TABLE ACCESS FULL       | BS_COUNTY                | 402 |  3216 |    3 (0)| 00:00:01 |
|*  3 | HASH JOIN RIGHT OUTER    |                         |  1010 | 132K| 40259 (1)| 00:08:04 |
| 4 | TABLE ACCESS FULL       | BS_REGION                | 87 | 609 |    3 (0)| 00:00:01 |
|*  5 | HASH JOIN RIGHT OUTER    |                         |  1010 | 125K| 40255 (1)| 00:08:04 |
| 6 |    TABLE ACCESS FULL       | BS_ADDRESS             | 62 | 434 |    3 (0)| 00:00:01 |
| 7 |    NESTED LOOPS OUTER    |                         |  1010 | 118K| 40252 (1)| 00:08:04 |
|*  8 |    HASH JOIN RIGHT OUTER |                         |  1010 | 112K| 40252 (1)| 00:08:04 |
| 9 |    TABLE ACCESS FULL    | BS_FACTORY             | 41 | 492 |    3 (0)| 00:00:01 |
|* 10 |    HASH JOIN RIGHT OUTER  |                         |  1010 | 100K| 40248 (1)| 00:08:03 |
|  11 |       TABLE ACCESS FULL    | BS_BG                   | 35 | 595 |    3 (0)| 00:00:01 |
|* 12 |       HASH JOIN RIGHT OUTER |                         |  1010 | 85850 | 40245 (1)| 00:08:03 |
|  13 |       TABLE ACCESS FULL | BS_PROVINCE             |    9 | 63 |    3 (0)| 00:00:01 |
|* 14 |       HASH JOIN RIGHT OUTER|                         |  1010 | 78780 | 40241 (1)| 00:08:03 |
|  15 |       TABLE ACCESS FULL | BS_PERSONTYPE          |    4 | 12 |    3 (0)| 00:00:01 |
|* 16 |       TABLE ACCESS FULL | BS_PERSON_INFORMATION_HIS |  1010 | 75750 | 40238 (1)| 00:08:03 |
|* 17 |    INDEX UNIQUE SCAN    | BS_DATASOURCE_P          |    1 |    6 |    0 (0)|    |
----------------------------------------------------------------------------------------------------------

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

1 - access("A"."COUNTY"="D"."COUNTYCODE")
3 - access("A"."REGION"="C"."REGIONCODE")
5 - access("A"."ADDRESSCODE"="F"."ADDRESSCODE")
8 - access("A"."FACTORYID"="E"."FACTORYID")
  10 - access("A"."GIVEBG"="H"."BGCODE")
  12 - access("A"."PROVINCE"="B"."PROVINCECODE")
  14 - access("A"."PERSONTYPEID"="I"."PERSONTYPEID")
  16 - filter((INTERNAL_FUNCTION("A"."FACTORYID") AND "A"."INCOMPANYDAY"=TRUNC(SYSDATE@!)))
  17 - access("A"."DATASOURCE"="G"."ADDRESSCODE")