what does "sorts(memory) statcs mean?"

From: Áõ Qinliu <Ivyliu_99_at_hotmail.com>
Date: Fri, 6 Jan 2017 08:10:37 +0000
Message-ID: <PS1PR01MB0683ECCD93F3D62D46E1EA9386630_at_PS1PR01MB0683.apcprd01.prod.exchangelabs.com>

I googled the "sorts(memory)", and got the following results:

sorts(memory) £ºThe sorts memory Oracle metric indicates if the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented.

In order to test.

create table t1
nologging -- adjust as necessary
as
with generator as (
 select --+ materialize
  rownum id,
 substr(dbms_random.string('U',4),1,4) sortcode  from all_objects
 where rownum <= 5000
)
select
 /*+ ordered use_nl(v2) */

 substr(v2.sortcode,1,4) || substr(v1.sortcode,1,2) sortcode,
 substr(v1.sortcode,2,2)     v2,
 substr(v2.sortcode,2,3)     v3

from
 generator v1,
 generator v2
where
-- rownum <= 12000
 rownum <= 1048576
;

oracle db 11.2.0.4

run the following sql:

alter session set sort_area_size = 31457280;

alter session set workarea_size_policy = auto;

alter session set events '10032 trace name context forever';

select
 sortcode
from
 t1
order by
 sortcode;

alter session set events '10032 trace name context off';

I compared the staitstics of sorts(memory) .

why there is 24 times of sorts(memory) while just only one order by clause.

and another thing, there is no match between sorts (rows) and Input records of 10032 trace

sorts (memory)                                                              24
sorts (rows)                                                         1,048,831

10032 trace :

 69 *** 2017-01-06 16:02:24.683

     70 ---- Sort Parameters ------------------------------
     71 sort_area_size                    13107200
     72 sort_area_retained_size           13107200
     73 sort_multiblock_read_count        1
     74 max intermediate merge width      799
     75
     76 *** 2017-01-06 16:04:52.669
     77 ---- Sort Statistics ------------------------------
     78 Input records                             1048576
     79 Output records                            1048576
     80 Total number of comparisons performed     11382755
     81   Comparisons performed by in-memory sort 11382755
     82 Total amount of memory used               23593984
     83 Uses version 2 sort
     84 ---- End of Sort Statistics -----------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 06 2017 - 09:10:37 CET