what does "sorts(memory) statcs mean?"
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-lReceived on Fri Jan 06 2017 - 09:10:37 CET