• 博客访问: 96646
  • 博文数量: 44
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-10 22:30
  • 认证徽章:
个人简介

暂无介绍

文章分类

全部博文(44)

文章存档

2011年(5)

2010年(39)

我的朋友

分类: Linux操作系统

2010-04-13 08:38:53

项目中一个应用反映如下查询速度缓慢,查询整体下来50多秒。:
select *  from V_CIRCUIT$OC where (CIRCUITCODE like '%XXYY%') and belongcity in('XZ0116','XZ011699','XZ011654','XZ011640','XZ011641','XZ011642','XZ011643','XZ011644','XZ011645','XZ011646','XZ011648','XZ011649','XZ011650','XZ011651','XZ011652','XZ011653') order by circuitcode ,belongcity;
根据这个查询来看sql上来看,sql本身需要改进的地方不多,所以着重观察所选定的目标对象视图。这个视图的创建sql语句如下:
create VIEW "V_CIRCUIT$OC" ("CIRCUITCODE", "USERNAME", "STATION1", "STATION2", "STATIONA", "STATIONZ", "CITY1", "CITY2", "SERIAL", "X_PURPOSE", "CREATETIME", "USETIME", "RATE", "PROPERTY", "HIRECIRCUITCODE", "PATH", "EQUIPCODE_A", "NENAME_A", "FRAMESERIAL_A", "SLOTSERIAL_A", "PACKSERIAL_A", "PORTSERIALNO1", "SLOT1", "EQUIPCODE_Z", "NENAME_Z", "FRAMESERIAL_Z", "SLOTSERIAL_Z", "PACKSERIAL_Z", "PORTSERIALNO2", "SLOT2", "AREA", "SYSTEMCODE", "LEASER", "HIREDATE", "SCHEDULERID", "REQUISITIONID", "STATE", "REMARK", "UPDATEPERSON", "UPDATEDATE", "CIRCUITSERIAL", "CIRCUITLEVEL", "ZB_SCHEDULEID", "VENDOR", "USERENDTOEND", "DLDH", "ISMAIN", "ISLOCAL", "BELONGCITY", "EQUIPLABELA", "PORTNAMEA", "EQUIPLABELB", "PORTNAMEZ", "ZBDH", "SEQUENCEID", "DDFNAME_A", "DDFCODE_A", "DDFNAME_Z", "DDFCODE_Z", "PROVINCENAME", "JH_DDF_A", "JH_DDF_Z", "JH_MODULE_A", "JH_MODULE_Z", "JH_STATION_A", "JH_STATION_Z", "BELONG", "NEWPURPOSE", "NEWCIRCUIT", "CS_DDF_A", "CS_DDF_Z" ) AS
SELECT a."CIRCUITCODE",
       a."USERNAME",
       a."STATION1",
       a."STATION2",
       sa."STATIONCODE" stationa,      
       sz."STATIONCODE" stationz,  
       a."CITY1",
       a."CITY2",
       a."SERIAL",
       a."X_PURPOSE",
       a."CREATETIME",
       a."USETIME",
       a."RATE",
       a."PROPERTY",
       a."HIRECIRCUITCODE",
       a."PATH",
       ea."EQUIPCODE",
       ea."NENAME",
       qa."FRAMESERIAL",
       qa."SLOTSERIAL",
       qa."PACKSERIAL",
       a."PORTSERIALNO1",
       a."SLOT1",
       ez."EQUIPCODE",
       ez."NENAME",
       qz."FRAMESERIAL",
       qz."SLOTSERIAL",
       qz."PACKSERIAL",
       a."PORTSERIALNO2",          
       a."SLOT2",
       a."AREA",
       a."SYSTEMCODE",      
       a."LEASER",
       a."HIREDATE",
       a."SCHEDULERID",
          a."REQUISITIONID",
          a."STATE",
          a."REMARK",
          a."UPDATEPERSON",
          a."UPDATEDATE",
          a."CIRCUITSERIAL",
          a."CIRCUITLEVEL",
          a."ZB_SCHEDULEID",
          a."VENDOR",
          a."USERENDTOEND",
          a."DLDH",
          a."ISMAIN",
          a."ISLOCAL",
          a."BELONGCITY",
          getEQUIPLabel(a.portserialno1) EQUIPLABELA,
       getportlabel (a.portserialno1) port1,
          getEQUIPLabel(a.portserialno2) EQUIPLABELB,
       getportlabel (a.portserialno2) port2,
          '' zbdh,
          ''sequenceid,
         (SELECT ddfa.ddfportname  FROM v_sdh_ddf_link ddfa WHERE ddfa.sdhptp = a.portserialno1 AND ROWNUM < 2) AS ddfname_a,
          (SELECT ddfa.ddfptp
             FROM v_sdh_ddf_link ddfa
            WHERE ddfa.sdhptp = a.portserialno1 AND ROWNUM < 2) AS ddfcode_a,
          (SELECT ddfa.ddfportname FROM v_sdh_ddf_link ddfa WHERE ddfa.sdhptp = a.portserialno2 AND ROWNUM < 2) AS ddfname_z,
          (SELECT ddfa.ddfptp FROM v_sdh_ddf_link ddfa  WHERE ddfa.sdhptp = a.portserialno2 AND ROWNUM < 2) AS ddfcode_z,
          xt.xtxx provincename,
          a.jh_ddf_a,
          a.jh_ddf_z,
          a.jh_module_a,
          a.jh_module_z,
          a.jh_station_a ,
          a.jh_station_z,
          a.belong,
          a.newpurpose,
          a.newcircuit,
          a.cs_ddf_a,
          a.cs_ddf_z
     FROM circuit a LEFT JOIN (equiplogicport qa JOIN equipment ea ON (qa.equipcode =
                                                                          ea.equipcode
                                                                      )
          JOIN station sa ON (ea.stationcode = sa.stationcode))
          ON (qa.logicport = a.portserialno1)
          LEFT JOIN (equiplogicport qz JOIN equipment ez ON (qz.equipcode =
                                                                  ez.equipcode
                                                            )
          JOIN station sz ON (ez.stationcode = sz.stationcode))
          ON (qz.logicport = a.portserialno1)
          JOIN xtbm xt ON (a.belongcity = xt.xtbm AND xt.xtbm<>'0' )
 
了解到v_sdh_ddf_link是一个创建视图,而且是动态视图,所以查询问题或许归结到子视图上。这个视图中,提取如下语句:
SELECT portserialno1 sdhptp, porttype1 t1, portserialno2 ddfptp, porttype2 t2,decode(porttype2,'ZY23010499',getOdfportlabel(portserialno2),getddfportlabel(portserialno2)) ddfportname
  FROM matrix_stos a
 WHERE a.porttype1 like 'ZY0307040_' AND (a.porttype2 = 'ZY23010499' OR a.porttype2 = 'ZY13010499')
UNION
SELECT portserialno2 sdhptp, porttype2 t1, portserialno1 ddfptp, porttype1 t2,decode(porttype1,'ZY23010499',getOdfportlabel(portserialno1 ),getddfportlabel(portserialno1 ))ddfportname
  FROM matrix_stos a
  WHERE a.porttype2 like 'Z’;
发现本身这个视图建立的查询很慢。看来性能故障就在此。
由于本库sqlplus的sql_trace无无法正常显示,所以采用了建立会话级别跟踪查询计划方式:
alter session set sql_trace=true;
运行该查询,在udump下生成hbcsdb_ora_17772.trc文件,然后采用tkprof进行格式化
tkprof  hbcsdb_ora_17772.trc session.log
打开log文件
TKPROF: Release 10.2.0.1.0 - Production on M-PM-GM-FM-ZM-RM-; 4M-TM-B 12 15:30:30 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Trace file: hbcsdb_ora_17772.trc
Sort options: default
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace=true

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 114 
********************************************************************************
SELECT portserialno1 sdhptp, porttype1 t1, portserialno2 ddfptp, porttype2 t2,decode(porttype2
,'ZY23010499',getOdfportlabel(portserialno2),getddfportlabel(portserialno2)) ddfportname
...skipping...
TKPROF: Release 10.2.0.1.0 - Production on M-PM-GM-FM-ZM-RM-; 4M-TM-B 12 15:30:30 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Trace file: hbcsdb_ora_17772.trc
Sort options: default
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace=true

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 114 
********************************************************************************
SELECT portserialno1 sdhptp, porttype1 t1, portserialno2 ddfptp, porttype2 t2,decode(porttype2,'ZY23010499',getOdfportlabel(portserialno2),get
ddfportlabel(portserialno2)) ddfportname
  FROM matrix_stos a
 WHERE a.porttype1 like 'ZY0307040_' AND (a.porttype2 = 'ZY23010499' OR a.porttype2 = 'ZY13010499')
UNION
SELECT portserialno2 sdhptp, porttype2 t1, portserialno1 ddfptp, porttype1 t2,decode(porttype1,'ZY23010499',getOdfportlabel(portserialno1 ),ge
tddfportlabel(portserialno1 ))ddfportname
  FROM matrix_stos a
  WHERE a.porttype2 like 'ZY0307040_' AND (a.porttype1 = 'ZY23010499'  OR a.porttype1 = 'ZY13010499')
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3839      3.24       3.40          0       1643          0       57561
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3841      3.25       3.41          0       1643          0       57561
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 114 
Rows     Row Source Operation
-------  ---------------------------------------------------
  57561  SORT UNIQUE (cr=404760 pr=0 pw=0 time=5361001 us)
  57561   UNION-ALL  (cr=404760 pr=0 pw=0 time=5182488 us)
  53106    TABLE ACCESS FULL MATRIX_STOS (cr=822 pr=0 pw=0 time=53196 us)
   4455    TABLE ACCESS FULL MATRIX_STOS (cr=821 pr=0 pw=0 time=5549 us)
********************************************************************************
SELECT '(' || D.EQUIPNAME || ')' || P.DDFDDMSERIAL || '-' || P.PORTSERIAL
FROM
 EN_DDF D,EN_DDFPORT P WHERE P.EQUIPCODE = D.EQUIPCODE AND P.DDFPORTCODE =
  :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  57306      0.71       0.63          0          0          0           0
Fetch    57306      1.64       1.41          0     401360          0       57306
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   114613      2.35       2.04          0     401360          0       57306
query的值引起了我的注意。
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 114     (recursive depth: 1)
********************************************************************************
SELECT '(' || D.EQUIPNAME || ')' || P.ODFODMSERIAL || '-' || P.PORTSERIAL
FROM
 EN_ODF D,EN_ODFPORT P WHERE P.EQUIPCODE = D.EQUIPCODE AND P.ODFPORTCODE =
  :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    255      0.00       0.00          0          0          0           0
Fetch      255      0.00       0.00          0       1757          0         248
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      511      0.00       0.01          0       1757          0         248
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 114     (recursive depth: 1)
********************************************************************************
 
上面的SELECT '(' || D.EQUIPNAME || ')' || P.DDFDDMSERIAL || '-' || P.PORTSERIAL
FROM
 EN_DDF D,EN_DDFPORT P WHERE P.EQUIPCODE = D.EQUIPCODE AND P.DDFPORTCODE =
  :B1 引起了我的注意,询问现场说是getDDFPortLabel 函数的值,该函数的定义如下:
FUNCTION getDDFPortLabel(portCode in VARCHAR2)
return VARCHAR2 AS
  --thePort       equiplogicport.portserial%type;
  ret           VARCHAR2(100);
BEGIN
 select '(' || d.equipname || ')' || p.ddfddmserial || '-' || p.portserial into ret
 from en_ddf d,en_ddfport p
 where p.equipcode = d.equipcode
 and   p.ddfportcode = portCode;
 return ret;
END;
对涉及到的表查看数据量信息:
SQL> select count(*) from EN_DDF;
  COUNT(*)
----------
     18556
SQL> select count(*) from en_ddfport;
  COUNT(*)
----------
    664347
根据oracle表连接性能查询来看,驱动表应该选取数据量小的表,而本程序中恰好犯了一个错误。
然后建议将两个表的连接顺序进行对换,即为:
 select '(' || d.equipname || ')' || p.ddfddmserial || '-' || p.portserial into ret
 from en_ddfport p,en_ddf d
 where p.equipcode = d.equipcode
 and   p.ddfportcode = portCode;
然后检查相关连接字段索引,对不存在的建立好。重新测试上面的查询,发现速度的确得到根本上的提升,哈哈,结果杂眼就出来,可行通过工具无法在这给显示截图。
总之,一句话:数据库的性能提升根本上还是在与程序的改善。
 
 

 
阅读(1981) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册