刚完成服务器的迁移，想测试一下迁移后的效果。却很惊讶的发现新的服务器跑起来比旧的还慢。
新服务器上耗时00:02:51.46，旧服务器上只需要00:00:10.27
查看了一下执行计划，发现新服务器与旧服务器上的执行计划唯一不同就是变成并行了。
看了一下参数，原来是新服务器上设置了
parallel_degree_policy
string
AUTO
parallel_automatic_tuning
boolean
TRUE
修改这两个参数为默认值后，效果非常的明显，查询速度一下子就上去了。
服务器8个cpu
新服务器的执行计划：
11:13:31 SQL> select /*+ RESULT_CACHE */ userid,serverip,operation,version,clientip,to_char(createtime,'yyyy-mm-dd hh24:mi:ss')from (select * from oray_ddns.ddns_log where userid='1061946' order by createtime desc) where rownumS | QC (ORDER) |
| 5 |VIEW
|
|2001 | 134K| 894 (1)| 00:00:11 | | |Q1,01 | PC
WP |
|
|*6 | SORT ORDER BY STOPKEY
|
|2001 | 127K| 894 (1)| 00:00:11 | | |Q1,01 | PC
WP |
|
| 7 |PX RECEIVE
|
|2001 | 127K| 892 (0)| 00:00:11 | | |Q1,01 | PC
WP |
|
| 8 | PX SEND RANGE
| :TQ10000
|2001 | 127K| 892 (0)| 00:00:11 | | |Q1,00 | P-
>P | RANGE|
| 9 |
PX PARTITION HASH SINGLE |
|2001 | 127K| 892 (0)| 00:00:11 | 1 | 1 |Q1,00 | PC
WC |
|
|* 10 | SORT ORDER BY STOPKEY
|
|99 |6831 |
|
| | |Q1,00 | PC
WP |
|
|11 |
TABLE ACCESS BY LOCAL INDEX ROWID| DDNS_LOG
|2001 | 127K| 892 (0)| 00:00:11 | | |Q1,00 | PC
WP |
|
|* 12 |
INDEX RANGE SCAN
| USERID_IDX
|2094 | |18 (0)| 00:00:01 | | |Q1,00 | PC
WP |
|
------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM select /*+ RESULT_CACHE */ userid,serverip,operation,version,clientip,to_char(createtime,'yyyy-mm-dd hh24:mi:ss')from (select * from oray_ddns.ddns_log where userid='1061946' order by createtime desc) where rownum<100;
99 rows selected.
Elapsed: 00:00:38.58
Execution Plan
----------------------------------------------------------
Plan hash value: 590693594
--------------------------------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|99 |6831 |1607 (1)| 00:00:20 | | |
| 1 |RESULT CACHE
| 4jy66rc88bmthapq59pd4wv6n2 | | |
|
| | |
|*2 | COUNT STOPKEY
|
| | |
|
| | |
| 3 |PARTITION RANGE ALL
|
|2001 | 134K|1607 (1)| 00:00:20 | 9 | 1 |
| 4 | VIEW
|
|2001 | 134K|1607 (1)| 00:00:20 | | |
|*5 |SORT ORDER BY STOPKEY
|
|2001 | 127K|1607 (1)| 00:00:20 | | |
| 6 | PARTITION HASH SINGLE
|
|2001 | 127K|1606 (0)| 00:00:20 | 1 | 1 |
| 7 |TABLE ACCESS BY LOCAL INDEX ROWID| DDNS_LOG
|2001 | 127K|1606 (0)| 00:00:20 | | |
|*8 | INDEX RANGE SCAN
| USERID_IDX
|2094 | |33 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<100)
5 - filter(ROWNUM<100)
8 - access("USERID"=1061946)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=6; dependencies=(ORAY_DDNS.DDNS_LOG); attributes=(ordered); parameters=(nls); name="select /*+ RESULT_CACHE */ userid,se
rverip,operation,version,clientip,to_char(createtime,'yyyy-mm-dd hh24:mi:ss')from (select "
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
170471consistent gets
148916physical reads
12944redo size
5116bytes sent via SQL*Net to client
589bytes received via SQL*Net from client
8SQL*Net roundtrips to/from client
3sorts (memory)
0sorts (disk)
99rows processed |
