|
SORT
|
Hello everyone. Last time, I talked about SORT_DIRECT_WRITES=FALSE and
SORT_DIRECT_WRITES=TRUE. In this issue, I will talk about
SORT_DIRECT_WRITES=AUTO.
SORT_DIRECT_WRITES=AUTO
SORT_DIRECT_WRITES is set to AUTO as a default. You can find the following description
in a manual.
When SORT_DIRECT_WRITES is set to the default of AUTO, and the value of the sort
area size is greater than ten times the block size, memory is allocated from the sort area to
do this.
Hmm... I don't exactly get a point because block size in this description is not so called DB_BLOCK_SIZE.
I make the description clearly understandable with my own words.
When SORT_DIRECT_WRITES is set to AUTO and SORT_AREA_SIZE is 655360 (640K) or greater,
memory is allocated from the sort area to directly write to disk. If SORT_AREA_SIZE is 655359 or less,
memory is not allocated but the sort data bypasses the buffer cache to write sort results to disk.
SORT_DIRECT_WRITES=655360 is a threshold of determining whether memory is allocated from the sort area.
Such definition is applicable to most of platforms in Oracle8, and Oracle7.3. (As far as I know, it is only
possible to set SORT_DIRECT_WRITES to TRUE in Oracle7.2.)
When SORT_DIRECT_WRITES is set to TRUE, some parameters become active.
SORT_WRITE_BUFFER_SIZE: buffer size for direct write (32KB is set to the default.)
SORT_WRITE_BUFFERS: number of buffers for direct write (2 is set to the default.)
Thus, a buffer size is determine by SORT_WRITE_BUFFER_SIZE*SORT_WRITE_BUFFERS
Refer to Ref.38 for details of buffer.
I use the SQL statement below to prove that default value above is correct. (If ISDEFAULT is set to TRUE,
the value will always be default.)
|
SQL> SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER
WHERE NAME IN ('sort_direct_writes', 'sort_write_buffers'
'sort_write_buffer_size');
NAME VALUE ISDEFAULT
-------------------------- ------------------- ---------------------
sort_direct_writes AUTO TRUE
sort_write_buffers 2 TRUE
sort_write_buffer_size 32768 TRUE
|
Some reference manual defines that the default of SORT_WRITE_BUFFERS is set to 1.
However, the SQL statement above proves that the default of SORT_WRITE_BUFFERS is
set to 2.
Sort area size is ten times the block size
Such definition can be interpreted that the threshold is 640K that is ten times of 2*32KB=64KB.
I mentioned previously that such definition is applicable to most of platforms; however, I have never
seen any environment in which threshold is any value other than 640KB.
If the threshold is any value other than 640K, the SORT_WRITE_BUFFERS*SORT_WRITE_BUFFER_SIZE
selected in V$PARAMETER will be any value other than 64KB.
Now, I examine if the threshold is really SORT_AREA_SIZE=655360 at SORT_DIRECT_WRITES=AUTO.
|
Memory is allocated from the sort area
|
 |
SQL> ALTER SESSION SET SORT_AREA_SITE=655360;
SQL> ALTER SESSION SET SORT_DIRECT_WRITES=AUTO;
SQL> SELECT * FROM T10MAN_ORG ORDER BY SAL;
|
|
Memory is NOT allocated from the sort area
|
|
SQL> ALTER SESSION SET SORT_AREA_SITE=655359;
SQL> ALTER SESSION SET SORT_DIRECT_WRITES=AUTO;
SQL> SELECT * FROM T10MAN_ORG ORDER BY SAL;
|
Following SQL statement is to verify database buffer for sort. I execute this SQL
statement per second to check the performance.
|
|
SQL> SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;
|
I use POPSQL that is an interpreter language we developed.
---------------------------POPSQL begins---------------------------
REM CONNECT as SYS to scan X$BH
CONNECT sys/manager
SET FETCH 1
LOOP (;;)
REM count(*) value is CNT as local variable (Local array when
REM two or more rows are scanned)
SAMPLE SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;
REM output to window. Enclose with ¥ to deploy variable
MESSAGE |¥CNT¥|
REM sleep for a second
SLEEP 1
ENDLOOP
---------------------------POPSQL ends---------------------------
|
X$BH when SORT_AREA_SIZE=655360 and SORT_DIRECT_WRITES=AUTO
This is just an abstract of how the number of buffers per second has shifted.
You can see that only one block is allocated on database buffer. Memory is thus
allocated from the sort area.
|1|
|1|
|1|
|1|
|1|
|1|
X$BH when SORT_AREA_SIZE=655359 and SORT_DIRECT_WRITES=AUTO
This is an abstract of how the number of buffers per second has shifted. You can see that
200 database buffers are all occupied by sort segment (DB_BLOCK_BUFFERS=200)
|192|
|198|
|196|
|200|
|200|
|200|
That's it for today. See you next week.
|
|
Ora! Ora! Oracle
