linux as 4 + oracle 10.2.0.3
******************1,关于 v$tempseg_usage 视图的疑问******************************
SQL> show parameter area
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_retained_size integer 0
sort_area_size integer 65536
workarea_size_policy string AUTO
SQL> select * from system.my_objects order by id desc; --- ----------------------在这里执行了一个排序操作
34330 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3173709044
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 13 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| MY_OBJECTS | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
695647 bytes sent via SQL*Net to client
25567 bytes received via SQL*Net from client
2290 SQL*Net roundtrips to/from client
1 sorts (memory) ---------------------------------------------------- 是在内存中完成的
0 sorts (disk)
34330 rows processed
SQL> select username,tablespace,sqlhash,segtype from V$TEMPSEG_USAGE; --------- 查询该视图 是有数据的
USERNAME TABLESPACE SQLHASH SEGTYPE
------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------- ---------- ---------------------------
SYS TEMP 349105966 DATA
SYS TEMP 349105966 LOB_DATA
SYS TEMP 349105966 INDEX
SYS TEMP 349105966 LOB_DATA
-----现在我的问题来了,为什么我是在内存中排序完成的,但是会在这个视图中还有数据存在呢?
这个视图是用来显示排序语句的? 还是用来显示哪些排序语句用到了temp表空间
***************************************2, 修改sort_area_size 产生的疑问**************************************
SQL> alter session set workarea_size_policy=manual; -----修改为手动
Session altered.
SQL> alter session set sort_area_size = 5242880; -----增大sort_area_size
Session altered.
SQL> show parameter area
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 10485760
sort_area_retained_size integer 0
sort_area_size integer 5242880 ---------参数已修改
workarea_size_policy string MANUAL ---------参数已修改
SQL> select * from system.my_objects order by id desc;
34330 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3173709044
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 13 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| MY_OBJECTS | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 recursive calls
50 db block gets
190 consistent gets
318 physical reads
0 redo size
695700 bytes sent via SQL*Net to client
25567 bytes received via SQL*Net from client
2290 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk) ---------------这里却在disk上进行排序
34330 rows processed
我的问题是 按理说我增大了 sort_area_size参数 应该更多的利用内存才对呀,可为什么 我增加了sort_area_size的大小,但是却在disk上进行了排序了呢?