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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of 刘 Qinliu <Ivyliu_99@xxxxxxxxxxx>
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�^
