ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个简单的解决sql慢的过程。。。

一个简单的解决sql慢的过程。。。

原创 Linux操作系统 作者:nmgzw 时间:2019-04-28 19:45:06 0 删除 编辑
今天从statspack发现bw开发服务器上有个语句很慢,打了个trace发现每个表都实际返回很多行,挺奇怪的,因为开发机器上,测试数据几乎很少,目前刚建立,查找后发现,只是因为有一个表,原来的数据为空,开发人员导入数据后没对表进行分析造成的,过程如下。
1、语句如下:
SELECT /*+ STAR_TRANSFORMATION FACT( T_00 )  */
 T_07."SID_0BASE_UOM" "S____163",
 T_02."S__0P_PLANT" "S____524",
 T_03."SID_0CALMONTH2" "S____372",
 T_03."SID_0CALYEAR" "S____016",
 COUNT(*) "1ROWCOUNT",
 SUM(T_00."/BIC/Z1CL") "Z1CL"
  FROM "/BIC/EZ1PP_C01"  T_00,
       "/BIC/DZ1PP_C011" T_01,
       "/BI0/XSTOR_LOC"  T_02,
       "/BIC/DZ1PP_C01T" T_03,
       "/BI0/XMATERIAL"  T_04,
       "/BIC/SZ1PZ"      T_05,
       "/BIC/DZ1PP_C01P" T_06,
       "/BIC/DZ1PP_C01U" T_07
 WHERE (T_00."KEY_Z1PP_C011" = T_01."DIMID")
   AND (T_01."SID_0STOR_LOC" = T_02."SID")
   AND (T_00."KEY_Z1PP_C01T" = T_03."DIMID")
   AND (T_00."KEY_Z1PP_C013" = T_04."SID")
   AND (T_04."S__Z1PZ" = T_05."SID")
   AND (T_00."KEY_Z1PP_C01P" = T_06."DIMID")
   AND (T_00."KEY_Z1PP_C01U" = T_07."DIMID")
   AND T_05."/BIC/Z1PZ" BETWEEN '30' AND '37'
   AND T_06."SID_0RECORDTP" = 0
   AND T_06."SID_0REQUID" 
2、trace的结果:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      3.91       3.87        577     657265          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      3.93       3.90        577     657265          0          20
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22  

Rows     Row Source Operation
-------  ---------------------------------------------------
     20  SORT GROUP BY (cr=657265 r=577 w=0 time=3873966 us)
  15818   NESTED LOOPS  (cr=657265 r=577 w=0 time=3841369 us)
  31329    NESTED LOOPS  (cr=625934 r=577 w=0 time=3648891 us)
  31329     NESTED LOOPS  (cr=531945 r=577 w=0 time=3373027 us)
 106271      NESTED LOOPS  (cr=425672 r=577 w=0 time=2670467 us)
 106271       NESTED LOOPS  (cr=319399 r=576 w=0 time=2005590 us)
 106271        NESTED LOOPS  (cr=213126 r=576 w=0 time=1397401 us)
 106271         NESTED LOOPS  (cr=106853 r=576 w=0 time=738057 us)
 106271          TABLE ACCESS FULL /BIC/EZ1PP_C01 (cr=580 r=576 w=0 time=133818 us)
 106271          TABLE ACCESS BY INDEX ROWID /BIC/DZ1PP_C01U (cr=106273 r=0 w=0 
		     time=429803 us)
 106271           INDEX UNIQUE SCAN /BIC/DZ1PP_C01U~0 (cr=2 r=0 w=0 time=122681 us)
		    (object id 77024)
 106271         TABLE ACCESS BY INDEX ROWID /BIC/DZ1PP_C01P (cr=106273 r=0 w=0 
		   time=476292 us)
 106271          INDEX UNIQUE SCAN /BIC/DZ1PP_C01P~0 (cr=2 r=0 w=0 time=117356 us)
		  (object id 77018)
 106271        TABLE ACCESS BY INDEX ROWID /BIC/DZ1PP_C011 (cr=106273 r=0 w=0 
		 time=435359 us)
 106271         INDEX UNIQUE SCAN /BIC/DZ1PP_C011~0 (cr=2 r=0 w=0 time=123499 us)
		  (object id 77006)
 106271       TABLE ACCESS BY INDEX ROWID /BI0/XSTOR_LOC (cr=106273 r=1 w=0 
		 time=467451 us)
 106271        INDEX UNIQUE SCAN /BI0/XSTOR_LOC~0 (cr=2 r=0 w=0 time=138641 us)
		 (object id 70503)
  31329      TABLE ACCESS BY INDEX ROWID /BIC/DZ1PP_C01T (cr=106273 r=0 w=0 
		time=558104 us)
 106271       INDEX UNIQUE SCAN /BIC/DZ1PP_C01T~0 (cr=2 r=0 w=0 time=141684 us)
		(object id 77021)
  31329     TABLE ACCESS BY INDEX ROWID /BI0/XMATERIAL (cr=93989 r=0 w=0
		 time=216730 us)
  31329      INDEX UNIQUE SCAN /BI0/XMATERIAL~0 (cr=62660 r=0 w=0 time=113759 us)
		(object id 75574)
  15818    TABLE ACCESS BY INDEX ROWID /BIC/SZ1PZ (cr=31331 r=0 w=0 time=139109 us)
  31329     INDEX UNIQUE SCAN /BIC/SZ1PZ~001 (cr=2 r=0 w=0 time=39654 us)
		(object id 53555)
3、查询表相关信息:
TABLE_NAME                       NUM_ROWS LAST_ANAL     BLOCKS
------------------------------ ---------- --------- ----------
/BI0/XMATERIAL                     202031 18-OCT-06       2702
/BI0/XSTOR_LOC                        456 18-OCT-06          2
/BIC/DZ1PP_C011                        34 18-OCT-06          1
/BIC/DZ1PP_C01P                         2 18-OCT-06          1
/BIC/DZ1PP_C01T                       559 18-OCT-06          3
/BIC/DZ1PP_C01U                         2 18-OCT-06          1
/BIC/EZ1PP_C01                          0 18-OCT-06          1
/BIC/SZ1PZ                             57 18-OCT-06          1
4、查询表实际数据,发现/BIC/EZ1PP_C01 表,实际数据发生明显变化:
SQL>
select '/BIC/EZ1PP_C01' table_name,count(*) from "/BIC/EZ1PP_C01"  T_00 union
select '/BIC/DZ1PP_C011',count(*) from "/BIC/DZ1PP_C011" T_01 union
select '/BI0/XSTOR_LOC' ,count(*) from "/BI0/XSTOR_LOC"  T_02 union
select '/BIC/DZ1PP_C01T',count(*) from "/BIC/DZ1PP_C01T" T_03 union
select '/BI0/XMATERIAL',count(*) from "/BI0/XMATERIAL"  T_04 union
select '/BIC/SZ1PZ',count(*) from "/BIC/SZ1PZ"      T_05 union
select '/BIC/DZ1PP_C01P',count(*) from "/BIC/DZ1PP_C01P" T_06 union
select '/BIC/DZ1PP_C01U',count(*) from "/BIC/DZ1PP_C01U";   

TABLE_NAME        COUNT(*)
--------------- ----------
/BI0/XMATERIAL      202038
/BI0/XSTOR_LOC         463
/BIC/DZ1PP_C011         34
/BIC/DZ1PP_C01P          1
/BIC/DZ1PP_C01T        559
/BIC/DZ1PP_C01U          2
/BIC/EZ1PP_C01      106271
/BIC/SZ1PZ              57
5、对/BIC/EZ1PP_C01 表收集信息:
SQL> exec dbms_stats.gather_table_stats(user,'/BIC/EZ1PP_C01',cascade=>true);

PL/SQL procedure successfully completed.
6、重新打一个trace发现,语句不再很慢:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.06          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.86       1.25       2113       3308          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.94       1.32       2113       3308          0          20

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22  

Rows     Row Source Operation
-------  ---------------------------------------------------
     20  SORT GROUP BY (cr=3308 r=2113 w=0 time=1254067 us)
  15818   HASH JOIN  (cr=3308 r=2113 w=0 time=1223458 us)
    463    TABLE ACCESS FULL /BI0/XSTOR_LOC (cr=4 r=2 w=0 time=8125 us)
  15818    HASH JOIN  (cr=3304 r=2111 w=0 time=1195118 us)
    151     TABLE ACCESS FULL /BIC/DZ1PP_C01T (cr=5 r=1 w=0 time=2245 us)
  56813     HASH JOIN  (cr=3299 r=2110 w=0 time=1145395 us)
     34      TABLE ACCESS FULL /BIC/DZ1PP_C011 (cr=3 r=1 w=0 time=11102 us)
  56813      HASH JOIN  (cr=3296 r=2109 w=0 time=1066244 us)
      2       TABLE ACCESS FULL /BIC/DZ1PP_C01U (cr=3 r=0 w=0 time=28 us)
  56813       HASH JOIN  (cr=3293 r=2109 w=0 time=1003396 us)
      1        TABLE ACCESS FULL /BIC/DZ1PP_C01P (cr=3 r=0 w=0 time=23 us)
  56813        HASH JOIN  (cr=3290 r=2109 w=0 time=941190 us)
  24135         HASH JOIN  (cr=2710 r=2109 w=0 time=747362 us)
      8          TABLE ACCESS BY INDEX ROWID /BIC/SZ1PZ (cr=2 r=0 w=0 time=60 us)
      8           INDEX RANGE SCAN /BIC/SZ1PZ~0 (cr=1 r=0 w=0 time=33 us)
	          (object id 53554)
 202038          TABLE ACCESS FULL /BI0/XMATERIAL (cr=2708 r=2109 w=0 
		 time=612287 us)
 106271         TABLE ACCESS FULL /BIC/EZ1PP_C01 (cr=580 r=0 w=0 time=59987 us)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25702/viewspace-464273/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-18

  • 博文量
    71
  • 访问量
    65413