今天 在查看一个sql的执行计划是发现Operation 列 有一项是buffer sort 请教大家一下是如何查收的 ,排序在SGA中shared pool 中进行的嘛?
SQL> set autotrace traceonly;
SQL>
SQL> select sys_guid(),o.enterprise_id,o.id as outworker_id,15 as day_of_month,
2 case when cheSys.Check_In_Flag = 1 then attTime.Start_Work_Time else attTime.End_Work_Time end as SCHEDULE_TIME,
3 cheSys.Check_In_Flag,o.organization_id,cheSys.id as check_in_system_id,3 as status
4 from outworker o
5 join enterprise e on o.enterprise_id=e.id
6 join attendance_system_new attSys on o.enterprise_id=attSys.enterprise_Id and o.att_special_flag=attSys.Special_Flag
7 join att_sys_times attTime on attSys.id=attTime.attendance_System_Id
8 join check_in_system cheSys on attTime.id=cheSys.Att_Sys_Times_Id
9 where o.status=1 and o.activation_status=1 and e.check_in_index=1
10 and attSys.Check_In_Flag=1 and to_number(bitand(attSys.weekday,1))=1;
已选择32070行。
执行计划
----------------------------------------------------------
Plan hash value: 1821981487
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 324 | 44 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 324 | 44 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 32 | 8064 | 44 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 203 | 20 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 176 | 19 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | ATTENDANCE_SYSTEM_NEW | 1 | 72 | 9 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| OUTWORKER | 1 | 104 | 10 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | I_OUTWORKER_ENTERPRISE | 33 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_ENTERPRISE | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | ENTERPRISE | 1 | 27 | 1 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 5265 | 251K| 43 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | CHECK_IN_SYSTEM | 5265 | 251K| 24 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_ATT_SYS_TIMES | 1 | | 0 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | ATT_SYS_TIMES | 1 | 72 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("ATTSYS"."CHECK_IN_FLAG"=1 AND TO_NUMBER(TO_CHAR(BITAND("ATTSYS"."WEEKDAY",1)))=1)
8 - filter("O"."ACTIVATION_STATUS"=1 AND "O"."STATUS"=1 AND
"O"."ATT_SPECIAL_FLAG"="ATTSYS"."SPECIAL_FLAG")
9 - access("O"."ENTERPRISE_ID"="ATTSYS"."ENTERPRISE_ID")
10 - access("O"."ENTERPRISE_ID"="E"."ID")
11 - filter("E"."CHECK_IN_INDEX"=1)
14 - access("ATTTIME"."ID"="CHESYS"."ATT_SYS_TIMES_ID")
15 - filter("ATTSYS"."ID"="ATTTIME"."ATTENDANCE_SYSTEM_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
7 recursive calls
2 db block gets
162489309 consistent gets
456 physical reads
80 redo size
5530153 bytes sent via SQL*Net to client
24031 bytes received via SQL*Net from client
2139 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
32070 rows processed
SQL>
SQL>