系统有2个cpu,每个4核,raid 1+0,db为oracle 10gr2 64bit
1. 先直接来个查询看看
SQL> set autot trace
SQL> SELECT COUNT(*)
FROM channels
WHERE channels.snapshot_time > to_date('10/28/2008 00:00'
,'MM/DD/YYYY HH24:MI:SS')
AND channels.snapshot_time <= to_date('10/29/2008 23:50'
,'MM/DD/YYYY HH24:MI:SS');
Elapsed: 00:01:15.96
Execution Plan
----------------------------------------------------------
Plan hash value: 1848729565
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 533K (7)| 01:46:45 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 89M| 936M| 533K (7)| 01:46:45 | KEY | KEY |
|* 4 | INDEX FAST FULL SCAN | CHANNELS_UNQ | 89M| 936M| 533K (7)| 01:46:45 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')
23:50','MM/DD/YYYY HH24:MI:SS'))
4 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND
"CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))
Statistics
----------------------------------------------------------
8962 recursive calls
0 db block gets
308209 consistent gets
306423 physical reads
0 redo size
518 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
141 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,默认走了索引,用时1分16秒左右
2. 强制走全表扫描看看
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL>
SELECT /*+ full(channels)*/
COUNT(*)
FROM channels
WHERE channels.snapshot_time > to_date('10/28/2008 00:00'
,'MM/DD/YYYY HH24:MI:SS')
AND channels.snapshot_time <= to_date('10/29/2008 23:50'
,'MM/DD/YYYY HH24:MI:SS');
Elapsed: 00:00:49.31
Execution Plan
----------------------------------------------------------
Plan hash value: 4225188383
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 714K (6)| 02:22:51 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 89M| 936M| 714K (6)| 02:22:51 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | CHANNELS | 89M| 936M| 714K (6)| 02:22:51 | KEY | KEY |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')
<%}%>
23:50','MM/DD/YYYY HH24:MI:SS'))
4 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS')
AND "CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))
Statistics
----------------------------------------------------------
8922 recursive calls
0 db block gets
404835 consistent gets
403115 physical reads
0 redo size
518 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
141 sorts (memory)
0 sorts (disk)
1 rows processed
全表扫描,用时49秒
3. 并行度为4的查询
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL>
SELECT /*+ full(channels) parallel(channels,4) */
COUNT(*)
FROM channels
WHERE channels.snapshot_time > to_date('10/28/2008 00:00'
,'MM/DD/YYYY HH24:MI:SS')
AND channels.snapshot_time <= to_date('10/29/2008 23:50'
,'MM/DD/YYYY HH24:MI:SS');
Elapsed: 00:00:11.96
Execution Plan
----------------------------------------------------------
Plan hash value: 166461870
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 198K (6)| 00:39:37 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | | | | |
|* 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 11 | | | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 11 | | | | | Q1,00 | PCWP | |
|* 5 | FILTER | | | | | | | | Q1,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 89M| 936M| 198K (6)| 00:39:37 | KEY | KEY | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| CHANNELS | 89M| 936M| 198K (6)| 00:39:37 | KEY | KEY | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')
5 - filter(TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS')
7 - filter("CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('10/29/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND
"CHANNELS"."SNAPSHOT_TIME">TO_DATE('10/28/2008 00:00','MM/DD/YYYY HH24:MI:SS'))
Statistics
----------------------------------------------------------
9661 recursive calls
4 db block gets
405834 consistent gets
403140 physical reads
632 redo size
518 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
152 sorts (memory)
0 sorts (disk)
1 rows processed
全表加并行,用时11秒.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/441887/viewspace-664324/,如需转载,请注明出处,否则将追究法律责任。
下一篇:
用pl/sql分割字符串
![user_pic_default.png](http://blog.itpub.net/images/user_pic_default.png)
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/441887/viewspace-664324/