生产库中topsql里面出现一条语句，如下，想请教下有什么需要注意的地方，有什么好的优化方法？||||||||||||||||||||||||||||||||||||||||||||||||||
生产库中top sql里面出现一条语句，如下，想请教下有什么需要注意的地方，有什么好的优化方法？
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT RY_JBXX.RYBH, RY_JBXX.GMSFHM, RY_JBXX.XM, RY_JBXX.CYM, RY_JBXX.XB, RY_JBXX.MZ, RY_JBXX.CSRQ, RY_JBXX.GJDQ, RY_JBXX.HJQH, RY_JBXX.HJXZ, AJ_XYRXX.XYR_AJBH, AJ_XYRXX.XYR_RYBH, AJ_XYRXX.XZZ, AJ_XYRXX.XYR_WFSS, AJ_CLCS.CLCS_BH, AJ_CLCS.CLCS_AJBH, AJ_CLCS.CLCS_CSLB, AJ_CLCS.CLCS_LY, AJ_CLCS.CLCS_FLYJ, AJ_CLCS.CLCS_RYBH, AJ_CLCS.CLCS_CBR, AJ_CLCS.CLCS_CBSJ, AJ_CLCS.CLCS_CBDWYJ, AJ_CLCS.CLCS_CBDWSPJG, AJ_CLCS.CLCS_CBDW, AJ_CLCS.CLCS_ZZDLDSPSJ, AJ_CLCS.CLCS_ZZDLDSPR, AJ_CLCS.CLCS_ZZDLDSPBM, AJ_CLCS.CLCS_ZZDLDSPJG, AJ_CLCS.CLCS_ZZDLDSPYJ, AJ_CLCS.CLCS_ZZDFZSHSJ, AJ_CLCS.CLCS_ZZDFZSHR, AJ_CLCS.CLCS_ZZDFZSHBM, AJ_CLCS.CLCS_ZZDFZSHJG, AJ_CLCS.CLCS_ZZDFZSHYJ, AJ_CLCS.CLCS_FZR, AJ_CLCS.CLCS_FZRTXSJ, AJ_CLCS.CLCS_SHYJ, AJ_CLCS.CLCS_SHJG, AJ_CLCS.CLCS_SHBM, AJ_CLCS.CLCS_SHR, AJ_CLCS.CLCS_SHSJ, AJ_CLCS.CLCS_SPYJ, AJ_CLCS.CLCS_SPJG, AJ_CLCS.CLCS_SPBM, AJ_CLCS.CLCS_SPR, AJ_CLCS.CLCS_SPSJ, AJ_CLCS.CLCS_CBRYJ, AJ_CLCS.CLCS_PSJG, AJ_CLCS.CLCS_PSBM, AJ_CLCS.CLCS_PSR, AJ_CLCS.CLCS_PSSJ, AJ_CLCS.CLCS_PSYJ, AJ_CLCS.CLCS_QSSJ, AJ_CLCS.CLCS_JDQX, AJ_CLCS.CLCS_ZZSJ, AJ_CLCS.CLCS_JDSJ, AJ_CLCS.CLCS_JDJG, AJ_CLCS.CLCS_WSWH, AJ_CLCS.CLCS_BZ, AJ_CLCS.CLCS_ZZJG, AJ_CLCS.CLCS_KJFZSHYJ, AJ_CLCS.CLCS_KJFZSHJG, AJ_CLCS.CLCS_KJFZSHBM, AJ_CLCS.CLCS_KJFZSHR, AJ_CLCS.CLCS_KJFZSHSJ, AJ_CLCS.CLCS_CJSHYJ, AJ_CLCS.CLCS_CJSHJG, AJ_CLCS.CLCS_CJSHBM, AJ_CLCS.CLCS_CJSHR, AJ_CLCS.CLCS_CJSHSJ, AJ_CLCS.CLCS_JFZSHYJ, AJ_CLCS.CLCS_JFZSHJG, AJ_CLCS.CLCS_JFZSHR, AJ_CLCS.CLCS_JFZSHSJ, AJ_CLCS.CLCS_JFZSHBM, AJ_CLCS.CLCS_JSPYJ, AJ_CLCS.CLCS_JSPJG, AJ_CLCS.CLCS_JSPBM, AJ_CLCS.CLCS_JSPR, AJ_CLCS.CLCS_JSPSJ, AJ_CLCS.CLCS_SJSPYJ, AJ_CLCS.CLCS_SJSPJG, AJ_CLCS.CLCS_SJSPBM, AJ_CLCS.CLCS_SJSPR, AJ_CLCS.CLCS_SJSPSJ, AJ_CLCS.CLCS_PLCLBH, AJ_CLCS.CLCS_BZJ, AJ_CLCS.CLCS_GLQTCSBH, AJ_XYRXX.CLCS_XSSXZM from RY_JBXX, AJ_XYRXX, AJ_CLCS where RY_JBXX.RYBH=AJ_XYRXX.XYR_RYBH and AJ_XYRXX.XYR_AJBH=AJ_CLCS.CLCS_AJBH and AJ_XYRXX.XYR_RYBH=AJ_CLCS.CLCS_RYBH and(RY_JBXX.CZBS<'3')and (AJ_XYRXX.CZBS<'3')and (AJ_CLCS.CZBS<'3')
|||||||||||||||||||||||||||||||||||||||||||||||||
16 个解决方案
由于调优这块我也是初步接触，个人感觉有下面的东西需要注意
||||||||||||||||||||||||||||
RY_JBXX, AJ_XYRXX, AJ_CLCS 三张表
其中要求 RY_JBXX.RYBH，AJ_XYRXX.XYR_RYBH，AJ_XYRXX.XYR_AJBH，AJ_CLCS.CLCS_AJBH ，AJ_XYRXX.XYR_RYBH，AJ_CLCS.CLCS_RYBH，RY_JBXX.CZBS，AJ_XYRXX.CZBS，AJ_CLCS.CZBS这些字段上最好有index. 看有什么办法排序一下，优化它的执行
|||||||||||||||||
请各路高手指正一下
from RY_JBXX, AJ_XYRXX, AJ_CLCS
where
RY_JBXX.RYBH=AJ_XYRXX.XYR_RYBH
and AJ_XYRXX.XYR_AJBH=AJ_CLCS.CLCS_AJBH
and AJ_XYRXX.XYR_RYBH=AJ_CLCS.CLCS_RYBH
and(RY_JBXX.CZBS <'3')
and (AJ_XYRXX.CZBS <'3')
and (AJ_CLCS.CZBS <'3')
优化索引
看看执行计划
哪些是全表扫描的
哪些索引效率低的
优化优化
这个表结构这么像我们生产库的啊？
人员基本信息 案件嫌疑人信息..... 命名规则都差不多
贴上执行计划啊
SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
3,099 941 41 75.59 39.95 6486cwtpu6pgt SELECT RY_JBXX.RYBH, RY_JBXX.G...
191 189 740 0.26 2.46 a7aab3s4q5u6p select AJ_CLCS.CLCS_BH, AJ_CLC...
70 26 1 69.86 0.90 4nnpjvkpm6bn3 select * from RY_JBXX, AJ_XYRX...
37 37 52 0.71 0.48 3rrpjrbdqcky1 select * from JCJ_JJXX, JCJ_CJ...
18 18 23 0.77 0.23 c00fjhgp1bxrf select * from JCJ_JJXX, JCJ_CJ...
18 18 6 2.96 0.23 86291zpgaybgn oracle@jl123 (TNS V1-V3) SELECT SYS_GUID(), "A1"."JJSJ"...
17 17 38 0.44 0.22 fwj20m101kp1r select * from RY_JBXX, AJ_XYRX...
17 17 17 0.97 0.21 2hz586f1g440g select * from JCJ_JJXX, JCJ_CJ...
15 15 1 14.59 0.19 0x14kjutpwqks select * from view_xsaj_cs wh...
13 13 148 0.09 0.17 b7jruwb9f03z7 select * from AJ_SAWP, AJ_WP_C...
Back to SQL Statistics
Back to Top
SQL ordered by CPU Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
941 3,099 41 22.95 39.95 6486cwtpu6pgt SELECT RY_JBXX.RYBH, RY_JBXX.G...
189 191 740 0.26 2.46 a7aab3s4q5u6p select AJ_CLCS.CLCS_BH, AJ_CLC...
37 37 52 0.71 0.48 3rrpjrbdqcky1 select * from JCJ_JJXX, JCJ_CJ...
26 70 1 26.43 0.90 4nnpjvkpm6bn3 select * from RY_JBXX, AJ_XYRX...
18 18 23 0.77 0.23 c00fjhgp1bxrf select * from JCJ_JJXX, JCJ_CJ...
18 18 6 2.96 0.23 86291zpgaybgn oracle@jl123 (TNS V1-V3) SELECT SYS_GUID(), "A1"."JJSJ"...
17 17 38 0.44 0.22 fwj20m101kp1r select * from RY_JBXX, AJ_XYRX...
17 17 17 0.97 0.21 2hz586f1g440g select * from JCJ_JJXX, JCJ_CJ...
15 15 1 14.57 0.19 0x14kjutpwqks select * from view_xsaj_cs wh...
13 13 148 0.09 0.17 b7jruwb9f03z7 select * from AJ_SAWP, AJ_WP_C...
Back to SQL Statistics
Back to Top
SQL ordered by Gets
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
Total Buffer Gets: 336,747,017
Captured SQL account for 19.5% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
19,091,721 740 25,799.62 5.67 189.50 190.59 a7aab3s4q5u6p select AJ_CLCS.CLCS_BH, AJ_CLC...
18,968,003 41 462,634.22 5.63 940.90 3099.13 6486cwtpu6pgt SELECT RY_JBXX.RYBH, RY_JBXX.G...
2,802,897 2 1,401,448.50 0.83 12.56 12.56 dq7gd4va0vx0b select * from view_xsaj_cs wh...
2,236,283 52 43,005.44 0.66 36.69 37.18 3rrpjrbdqcky1 select * from JCJ_JJXX, JCJ_CJ...
1,849,481 1 1,849,481.00 0.55 14.57 14.59 0x14kjutpwqks select * from view_xsaj_cs wh...
1,686,752 6 281,125.33 0.50 17.75 17.75 86291zpgaybgn oracle@jl123 (TNS V1-V3) SELECT SYS_GUID(), "A1"."JJSJ"...
1,046,201 2 523,100.50 0.31 7.23 7.24 11w8mfybg1fw0 select * from view_xsaj_cs wh...
917,987 23 39,912.48 0.27 17.78 17.78 c00fjhgp1bxrf select * from JCJ_JJXX, JCJ_CJ...
908,157 17 53,421.00 0.27 16.55 16.56 2hz586f1g440g select * from JCJ_JJXX, JCJ_CJ...
854,929 16 53,433.06 0.25 11.74 11.74 f3anqrcatvw0m select count(*) from JCJ_JJXX,...
Back to SQL Statistics
Back to Top
SQL ordered by Reads
Total Disk Reads: 11,350,910
Captured SQL account for 93.9% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
10,503,291 41 256,177.83 92.53 940.90 3099.13 6486cwtpu6pgt SELECT RY_JBXX.RYBH, RY_JBXX.G...
146,124 1 146,124.00 1.29 26.43 69.86 4nnpjvkpm6bn3 select * from RY_JBXX, AJ_XYRX...
4,670 10 467.00 0.04 4.97 6.21 4gnr7jzz9g4yc select * from RY_JBXX, AJ_XYRX...
995 1 995.00 0.01 4.11 4.31 9jtt6h27gubzt select * from AJ_FLWS, RY_JBXX...
21 7 3.00 0.00 2.93 3.16 9tb6cxd3ma1m5 Select * From Ry_Jbxx, Aj_Jbx...
6 779 0.01 0.00 0.06 0.06 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_id...
4 103 0.04 0.00 0.03 0.04 cjq3rk3pg48v6 oraclezhkdb@hnzhk (TNS V1-V3) SELECT COUNT(*) FROM "GIAPSYS"...
2 110 0.02 0.00 0.02 0.03 bn8dbcb4w2jxt SELECT W.WPCSGL_MC, W.WPCSGL_S...
2 6,992 0.00 0.00 0.54 0.54 db78fxqxwxt7r select /*+ rule */ bucket, en...
2 658 0.00 0.00 0.06 0.06 ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_id...
Back to SQL Statistics
Back to Top
SQL ordered by Executions
Total Executions: 398,903
Captured SQL account for 25.8% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
10,676 8,277 0.78 0.00 0.00 96g93hntrzjtr select /*+ rule */ bucket_cnt,...
9,918 0 0.00 0.00 0.00 5zz90txjabju4 select * from AJ_WZMSTYCTZXX
9,918 99,180 10.00 0.00 0.00 7zsyckhnsytv2 select * from AJ_WZMSTKYXX
9,918 9,918 1.00 0.00 0.00 bsr48nmmr19vg select * from AJ_WZMSTTBBJXX
9,918 0 0.00 0.00 0.00 fmunz0ky9byrq select * from WZ_SJZP
6,992 117,520 16.81 0.00 0.00 db78fxqxwxt7r select /*+ rule */ bucket, en...
6,905 1,425 0.21 0.00 0.00 a8pk75r2qw3tj SELECT CLCS_JDSJ FROM AJ_CLCS ...
5,291 5,291 1.00 0.00 0.00 07dd3hhrcw3a1 SELECT CLCS_JDSJ FROM AJ_CLCS ...
2,352 2,352 1.00 0.00 0.00 7h35uxf5uhmm1 Realtime Connection select sysdate from dual
1,916 1,916 1.00 0.00 0.00 2ym6hhaq30r73 select type#, blocks, extents,...
Back to SQL Statistics
Back to Top
SQL ordered by Parse Calls
Total Parse Calls: 174,961
Captured SQL account for 36.3% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
9,918 9,918 5.67 5zz90txjabju4 select * from AJ_WZMSTYCTZXX
9,918 9,918 5.67 7zsyckhnsytv2 select * from AJ_WZMSTKYXX
9,918 9,918 5.67 bsr48nmmr19vg select * from AJ_WZMSTTBBJXX
9,918 9,918 5.67 fmunz0ky9byrq select * from WZ_SJZP
2,352 2,352 1.34 7h35uxf5uhmm1 Realtime Connection select sysdate from dual
1,916 1,916 1.10 2ym6hhaq30r73 select type#, blocks, extents,...
1,568 1,568 0.90 0kyt0jc76rat9 select AJ_WZMSTXX.WZM_GLCJBH f...
1,044 1,044 0.60 0vvyz6fkqwaz8 select sectNo from sect_sequen...
930 930 0.53 2gs750hfwgtq6 select sectNo from sect_sequen...
919 921 0.53 0h6b2sajwb74n select privilege#, level from ...
Back to SQL Statistics
Back to Top
RY_JBXX 应建立 RYBH的索引
AJ_XYRXX表应建立 XYR_RYBH,XYR_AJBH的组合索引
AJ_CLCS表应建立 CLCS_RYBH,CLCS_AJBH的组合索引
然后分别在对三个表中的CZBS建立索引(如果值的个数较少,可建立BITMAP索引)
可以针对每个字段建立单独的索引，但组合索引高效一些
AWR report里好像没看到详细的执行计划，晚一点我在生产库上执行一下再贴上来吧
建立索引是必须的，然后局部改造sql语句如下：
from RY_JBXX, AJ_XYRXX, AJ_CLCS
where
RY_JBXX.RYBH=AJ_XYRXX.XYR_RYBH
and AJ_XYRXX.XYR_AJBH=AJ_CLCS.CLCS_AJBH
and AJ_XYRXX.XYR_RYBH=AJ_CLCS.CLCS_RYBH
and(RY_JBXX.CZBS <='2')
and (AJ_XYRXX.CZBS <='2')
and (AJ_CLCS.CZBS <='2')
除了从索引方面来考虑，没什么好办法了。或者考虑按CZBS 分区。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 