Tuesday, December 04, 2007

Sort operation is counted same regardless of the table size

Sort operation is counted same regardless of whether the table is big or small

In the following example, one sort is in memory against a small table, one sort is in disk against a big table, but they both count 1 sort operation.



**********************  Case 1  ***************************

scott@ORCL> select * from emp order by 5;

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 301026995

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 15 540 4 (25) 00:00:01
1 SORT ORDER BY 15 540 4 (25) 00:00:01
* 2 FILTER
3 TABLE ACCESS FULL EMP 15 540 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(CASE WHEN "SAL">=2000 THEN
SYS_AUDIT('SCOTT','EMP','EMP_SEL',3) ELSE NULL END IS NULL)


Statistics
----------------------------------------------------------
4 recursive calls
15 db block gets
9 consistent gets
0 physical reads
696 redo size
1356 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed

********************** Case 2 ***************************

scott@ORCL> select * from big_table order by 5;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1472477105

----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 998K 98M 24421 (1) 00:12:03
1 SORT ORDER BY 998K 98M 260M 24421 (1) 00:12:03
2 TABLE ACCESS FULL BIG_TABLE 998K 98M 3383 (2) 00:01:41
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
117 recursive calls
43 db block gets
14457 consistent gets
30123 physical reads
520 redo size
21149727 bytes sent via SQL*Net to client
733707 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1000000 rows processed

No comments: