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 |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1459 consistent gets
1454 physical reads
0 redo size
6278979 bytes sent via SQL*Net to client
41109 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
55500 rows processed
可以看到使用order by用到了额外的12M内存
3. 在cust_last_name创建b*索引
没建索引:
sqlexplain plan for select cust_last_name from customers order by cust_last_name;
sqlSQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2792773903
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 433K| | 610 (1)| 00:00:08 |
| 1 | SORT ORDER BY | | 55500 | 433K| 880K| 610 (1)| 00:00:08 |
| 2 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 433K| | 405 (1)| 00:00:05 |
----------------------------------------------------------------------------------------
建立索引:
sqlcreate index lastname_idx on customers(cust_last_name);
sqlexplain plan for select /*+ index(c lastname_idx) */ cust_last_name from customers order by cust_last_name;
执行计划:
sqlSQL> explain plan for select /*+ index(c lastname_idx) */ cust_last_name from customers order by cust_last_name;
已解释。
已用时间: 00: 00: 00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 3470560620
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 433K| 143 (1)| 00:00:02 |
| 1 | INDEX FULL SCAN | LASTNAME_IDX | 55500 | 433K| 143 (1)| 00:00:02 |
---------------------------------------------------------------------------------
可以发现在一列建立索引后,对该列进行排序操作不需要再执行排序操作,
他会根据索引来进行查询(索引中本来就已经排好序)
结论
建立索引可以节省排序操作的时间。
4. 查询前十条记录
通常我们都会:
sqlselect cust_last_name from customers where rownum<=20 order by 1;
sqlSQL> select cust_last_name from customers where rownum<=20 order by 1;
CUST_LAST_NAME
--------------------------------------------------------------------------------
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Everett
Ruddy
Ruddy
CUST_LAST_NAME
--------------------------------------------------------------------------------
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
Ruddy
已选择20行。
这样是错误的,因为在oracle中where永远都是先执行的也就先取20条再排序。
正确的做法是使用子查询:
sqlselect cust_last_name from (select * from customers order by cust_last_name) where rownum<10;
sqlSQL>select cust_last_name from (select * from customers order by cust_last_name) where rownum<10;
CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
已选择20行。
没建索引:
sqlPLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1285511559
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | | 2612 (1)| 00:00:32 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 55500 | 1192K| | 2612 (1)| 00:00:32 |
|* 3 | SORT ORDER BY STOPKEY| | 55500 | 9810K| 12M| 2612 (1)| 00:00:32 |
| 4 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 9810K| | 406 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
已选择17行。
建立索引后:
sqlPLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3026242074
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 198 | 4 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN| LASTNAME_IDX | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
已选择15行。
建立索引后,子查询效率明显提高
同时select cust_last_name from customers where rownum<=10 order by cust_last_name;和上面的结果已经大不相同。
sqlSQL> select cust_last_name from customers where rownum<=10 order by cust_last_name;
CUST_LAST_NAME
--------------------------------------------------------------------------------
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
Aaron
已选择10行。
执行计划:
sqlSQL> explain plan for
2 select cust_last_name from customers where rownum<=10 order by cust_last_name;
已解释。
已用时间: 00: 00: 00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2820001957
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| LASTNAME_IDX | 55500 | 433K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<=10)
已选择14行。
竟然不是刚刚那样乱的。
结论
建立索引能够对提高子查询的性能,并且查询前十条记录不再需要使用子查询, 因为建立好索引后已经是排好序的,只要根据索引从表中拿出前十条记录即可。
5. 分组与索引
首先先建立一个表
sqlSQL> create table s as select * from sales;
表已创建。
s表与sales表的数据完全是一致的但s表没有sales表上的索引。
然后我们看一下执行计划:
sqlSQL> explain plan for
2 select cust_id,avg(amount_sold) from s group by cust_id;
已解释。
已用时间: 00: 00: 00.23
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1912481676
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 785K| 19M| 1271 (4)| 00:00:16 |
| 1 | HASH GROUP BY | | 785K| 19M| 1271 (4)| 00:00:16 |
| 2 | TABLE ACCESS FULL| S | 785K| 19M| 1236 (1)| 00:00:15 |
---------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
已选择13行。
我们再看一下sales表(即建立了索引的表):
sqlSQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2820001957
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| LASTNAME_IDX | 55500 | 433K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<=10)
已选择14行。
结论
group by的一列上建立索引group by 性能更佳。
下面是关于order by 升序降序的执行计划可以看出建立索引升降序对排序不会影响性能
升序:
sqlSQL> select cust_last_name from customers where rownum <= 10 order by cust_last_name;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2820001957
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| LASTNAME_IDX | 55500 | 433K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<=10)
已选择14行。
降序:
sqlSQL> select cust_last_name from customers where rownum <= 10 order by cust_last_name;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 1596600344
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 72 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN DESCENDING| LASTNAME_IDX | 55500 | 433K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(ROWNUM<10)
已选择14行。