2015年5月20日19:14:43 - 排序

1. 查看排序区内存的大小以及设置

实际排序所用到的内存、磁盘的统计信息：

pga_aggregate_target：此参数用来指定所有session总计可以使用最大PGA内存 olap:50% oltp:20%

sqlSQL> show parameter pga_aggre

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

pga_aggregate_target big integer 0

SQL> show parameter workarea_size_policy

workarea_size_policy：此参数用于开关PGA内存自动管理功能 auto:自动分配sort_area_size 属于workarea 如果需要经常排序就需要把这个值设置大点

sqlSQL> show parameter workarea_size_policy

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

workarea_size_policy string AUTO

select name, value from v$sysstat where name like ‘sort%’; select * from v$pgastat;

sqlSQL> select name , value from v$sysstat where name like 'sort%';

NAME VALUE

-------------------------------------------------------------------------------------------------------------------------------- ----------

sorts (memory) 4283

sorts (disk) 0

sorts (rows) 40823

2. 比较以下操作

select * from customers;

sqlSQL> set autotrace traceonly

SQL> select * from customers;

已选择55500行。

已用时间: 00: 00: 01.93

执行计划

----------------------------------------------------------

Plan hash value: 2008213504

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 55500 | 9810K| 406 (1)| 00:00:05 |

| 1 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9810K| 406 (1)| 00:00:05 |

-------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

5057 consistent gets

1455 physical reads

0 redo size

10855625 bytes sent via SQL*Net to client

41109 bytes received via SQL*Net from client

3701 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

55500 rows processed

select * from customers order by cust_last_name;

sqlSQL> select * from customers order by cust_last_name;

已选择55500行。

已用时间: 00: 00: 01.93

执行计划

----------------------------------------------------------

Plan hash value: 2792773903

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 55500 | 9810K| | 2612 (1)| 00:00:32 |

| 1 | SORT ORDER BY | | 55500 | 9810K| 12M| 2612 (1)| 00:00:32 |

| 2 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 9810K| | 406 (1)| 00:00:05 |

----------------------------------------------------------------------------------------

统计信息