|
SORT
|
Hello everyone. In this issue, I will talk about SORT_DIRECT_WRITES.
SORT_DIRECT_WRITES is an algorithm when sort is performed on disk.
SORT_DIRECT_WRITES
When you don't use SORT_DIRECT_WRITES at sorting on disk, data is read to database buffer
and DBWR writes data to temporary tablespace.
When you use SORT_DIRECT_WRITES, on the other hand, you can reduce load on DBWR because a buffer is used
to write data to temporary tablespace, and server process itself writes data. SORT_DIRECT_WRITES
is also effective to reduce contention against database buffer and increase the hit ratio.
Refer to Ref.38 for details.
It is obvious that SORT_DIRECT_WRITES is a useful parameter. In Oracle8 or earlier,
SORT_DIRECT_WRITES parameter is set to any one of FALSE, TRUE, AUTO
(AUTO is set to the default) to determine whether sort data will bypass the buffer cache to write
intermediate sort result to disk.
SORT_DIRECT_WRITES parameter is obsolete in Oracle8i or higher.
When sort requires a write to temporary tablespace now, a buffer to write from sort area to temporary
tablespace is automatically allocated. It is thus no longer necessary bypass the buffer cache.
Now, I will analyze if there is any difference in performance on database buffer when I use
SORT_DIRECT_WRITES and when I don't. I use Oracle8 for my analysis because SORT_DIRECT_WRITES
is obsolete on Oracle8i or later and I can't determine the difference.
I use X$BH to check performance on database buffer. BH represents Buffer Header.
The number of data selected from X$BH is equal to the value of the initialization parameter
DB_BLOCK_BUFFERS
|
SVRMGR> SHOW PARAMETER DB_BLOCK_BUFFERS
NAME TYPE VALUE
------------------------- ------------ ----------
db_block_buffers integer 200 !match!
SVRMGR> SELECT COUNT(*) FROM X$BH;
COUNT(*)
--------
200 !match!
|
X$BH contains FILE# row that agrees with FILE# of V$DATAFILE. This FILE# identifies
which datafile data exists on database buffer.
This time, datafile number in a tablespace is 2. Thus, I execute the following SQL statement to
determine the number of data blocks in sort segment.
|
|
SQL> SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;
|
OK, I execute one SQL statement that uses SORT_DIRECT_WRITES and the other SQL statement
that does't use SORT_DIRECT_WRITES.
|
SQL statement with SORT_DIRECT_WRITES=FALSE
|
 |
SQL> ALTER SESSION SET SORT_AREA_SIZE=65536;
SQL> ALTER SESSION SET SORT_DIRECT_WRITES=FALSE;
SQL> SELECT * FROM T10MAN_ORG ORDER BY SAL;
|
|
SQL statement with SORT_DIRECT_WRITES=TRUE
|
|
SQL> ALTER SESSION SET SORT_AREA_SITE=65536;
SQL> ALTER SESSION SET SORT_DIRECT_WRITES=TRUE;
SQL> SELECT * FROM T10MAN_ORG ORDER BY SAL;
|
Following SQL statement is to verify database buffer used 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.
(Script)
--------------POPSQL begins----------------
REM in order to scan X$BH, use SYS to connect
CONNECT sys/manager
SET FETCH 1
LOOP (;;)
REM CNT is a local variable (local array if two or more rows are to be scanned)
Count (*) value is stored as REM
SAMPLE SELECT COUNT (*) CNT FROM X$BH WHERE FILE#=2;
Output to REM screen. Embrace with ¥ to deploy variables
MESSAGE |¥CNT¥|
REM sleep a second
SLEEP 1
ENDLOOP
--------------POPSQL ends----------------
Result of the analysis
<Performance without SORT_DIRECT_WRITES>
Following shows a transition of the number of buffers per second (abstract.) All 200 database buffers
are occupied with sort segment. This causes the lower hit ratio and heavy load on DBWR.
|192|
|198|
|196|
|200|
|200|
|200|
<Performance with SORT_DIRECT_WRITES>
Following shows a transition of the number of buffers (abstract.) There is only one block
on database buffer. The difference is quite obvious.
|1|
|1|
|1|
|1|
|1|
|1|
That's it for today. See you next week.
|
|
Ora! Ora! Oracle
