今天从statspack发现bw开发服务器上有个语句很慢,打了个trace发现每个表都实际返回很多行,挺奇怪的,因为开发机器上,测试数据几乎很少,目前刚建立,查找后发现,只是因为有一个表,原来的数据为空,开发人员导入数据后没对表进行分析造成的,过程如下。
1、语句如下:
2、trace的结果:
3、查询表相关信息:
4、查询表实际数据,发现/BIC/EZ1PP_C01 表,实际数据发生明显变化:
5、对/BIC/EZ1PP_C01 表收集信息:
6、重新打一个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"
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)
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
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
SQL> exec dbms_stats.gather_table_stats(user,'/BIC/EZ1PP_C01',cascade=>true); PL/SQL procedure successfully completed.
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25702/viewspace-464273/