今天,某省的同事来告诉我,表重组后,他用于统计的一个sql脚本运行变慢了,之前只需要17、8分钟能出来的结果,现在1小时40分钟左右才能出来结果。
我们一起来看看脚本中的一个sql:
SQL
>
explain
plan
for
2 select a . startdate , b . subsid from tab_1 a , tab_2 b where
3 a . servid = ' 025001003681 ' and a . status != ' C ' and a . mid = b . mid ;
Explained .
Elapsed : 00 : 00 : 00.03
SQL > select * from table ( dbms_xplan . display )
SQL > /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --
-------------------------------------------------------------------------------------------------------------- --
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------- --
| 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | |
| 1 | NESTED LOOPS | | 369 | 23985 | 980 | | |
| 2 | PARTITION HASH ALL | | | | | 1 | 4 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_1 | 369 | 14022 | 242 | 1 | 4 |
|* 4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | | 10 | 1 | 4 |
| 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_2 | 1 | 27 | 2 | KEY | KEY |
|* 7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------- --
Predicate Information ( identified by operation id ) :
------------------------------------------------- --
3 - filter ( " A " . " STATUS " <> ' C ' )
4 - access ( " A " . " SERVID " = ' 025001003681 ' )
7 - access ( " A " . " MID " = " B " . " MID " )
Note : cpu costing is off
22 rows selected .
Elapsed : 00 : 00 : 00.56
2 select a . startdate , b . subsid from tab_1 a , tab_2 b where
3 a . servid = ' 025001003681 ' and a . status != ' C ' and a . mid = b . mid ;
Explained .
Elapsed : 00 : 00 : 00.03
SQL > select * from table ( dbms_xplan . display )
SQL > /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --
-------------------------------------------------------------------------------------------------------------- --
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------- --
| 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | |
| 1 | NESTED LOOPS | | 369 | 23985 | 980 | | |
| 2 | PARTITION HASH ALL | | | | | 1 | 4 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_1 | 369 | 14022 | 242 | 1 | 4 |
|* 4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | | 10 | 1 | 4 |
| 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_2 | 1 | 27 | 2 | KEY | KEY |
|* 7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------- --
Predicate Information ( identified by operation id ) :
------------------------------------------------- --
3 - filter ( " A " . " STATUS " <> ' C ' )
4 - access ( " A " . " SERVID " = ' 025001003681 ' )
7 - access ( " A " . " MID " = " B " . " MID " )
Note : cpu costing is off
22 rows selected .
Elapsed : 00 : 00 : 00.56
我们看到这个sql是通过索引后在走nested loops,我们做一个sqltrace来观察一下它的执行时间和consistent gets:
SQL
>
set
timing
on
SQL > set autotrace traceonly
SQL > select a . startdate , b . subsid from tab_1 a , tab_2 b where
2 a . servid = ' 025001003681 ' and a . status != ' C ' and
SQL > set autotrace traceonly
SQL > select a . startdate , b . subsid from tab_1 a , tab_2 b where
2 a . servid = ' 025001003681 ' and a . status != ' C ' and