这条语句有什么办法优化一下？在库里有什么要注意的地方？

Question

生产库中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')|||||||||||||||||||||||||||||||||||||||||||||||||

Answer 1

由于调优这块我也是初步接触，个人感觉有下面的东西需要注意
||||||||||||||||||||||||||||
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. 看有什么办法排序一下，优化它的执行
|||||||||||||||||
请各路高手指正一下

Answer 2

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') 
优化索引

Answer 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

Answer 4

RY_JBXX 应建立 RYBH的索引
AJ_XYRXX表应建立 XYR_RYBH,XYR_AJBH的组合索引
AJ_CLCS表应建立 CLCS_RYBH,CLCS_AJBH的组合索引
然后分别在对三个表中的CZBS建立索引(如果值的个数较少,可建立BITMAP索引)

Answer 5

and AJ_XYRXX.XYR_AJBH=AJ_CLCS.CLCS_AJBH 
and AJ_XYRXX.XYR_RYBH=AJ_CLCS.CLCS_RYBH 
这是两个单独的查询，为什么需要建立组合索引啊？？

Answer 6

AWR report里好像没看到详细的执行计划，晚一点我在生产库上执行一下再贴上来吧

Answer 7

建立索引是必须的，然后局部改造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') 

Answer 8

除了从索引方面来考虑，没什么好办法了。或者考虑按CZBS 分区。

Answer 9

客户一直没联系上，暂时等等，晚点结贴吧。。<3  不如 <=2，
好像是这个意思啊。顶一下，我再查查。。