Re: what does "sorts(memory) statcs mean?"

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Jan 2017 08:26:23 +0000
Message-ID: <MMXP123MB09112C3CEFC072D9DC923A49A5630_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>

That bit of SQL looks familiar ;)

Don't forget that Oracle will have had to do some work to optimize the statement, and that would probably have resulted in some calls to populate the dictionary cache from the data dictionary tables. Those statements are probably the source of extra little bits of sorting. Your could enable the 10046 trace before the 10032 trace and check the sql and execution plans for that recursive SQL if you wanted to cross-check.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Áõ Qinliu <Ivyliu_99_at_hotmail.com> Sent: 06 January 2017 08:10:37
To: 'ORACLE-L'
Subject: what does "sorts(memory) statcs mean?"

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 -----------------------

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Fri Jan 06 2017 - 09:26:23 CET