|
SORT
|
Hello everyone. In this issue, I will analyze if following guide is correct.
<Guide>
you may find the following guide in regard to SORT_AREA_SIZE, INITIAL, NEXT, and
PCTINCREASE of default storage for temporary tablespace.
Set as follows: SORT_AREA_SIZE=INITIAL=NEXT PCTINCREASE=0
Before I start my analysis, I set DB_BLOCK_SIZE to 2K, and reboot Oracle before executing
SQL statement to sort.
<SORT_AREA_SIZE threshold>
|
SQL> SELECT /*+ FULL(T10) */ EMPNO, JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
|
NOTE: T10MAN_ORG contains 100,000 rows that have been expanded from EMP table.
SORT_AREA_SIZE threshold for the SQL statement above is 3241984 and 3241985. Here is
AUTOTRACE of that moment.
<Memory sort>
|
|
SQL> ALTER SESSION SET SORT_AREA_SIZE=3241985;
SQL> SET AUTOTRACE TRACE
SQL> SELECT /*+ FULL(T10) */ EMPNO, JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
Execution plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Bytes=1100000)
1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000)
2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=100000 Bytes=1100000)
Statistics
------------------------------------------------------------------
0 recursive calls
15 db block gets
2499 consistent gets
1433 physical reads
0 redo size
5706824 bytes sent via SQL*Net from client
740350 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
1 sorts (memory) <--proof of which sort is performed on memory
0 sorts (disk) <--proof of which sort is performed on disk
100000 rows processed
|
<Disk sort threshold>
ALTER SESSION SET SORT_AREA_SIZE = 3241984;
[Tablespace information where sort is performed]
You can see that TEMPORARY_TABLESPACE for user SCOTT is TEMP5.
|
|
SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM USER_USERS;
USERNAME TEMPORARY_TABLESPACE
---------------------------------
SCOTT TEMP5
|
Here, you can see INITIAL=4K NEXT=2K PCTINCREASE=0 is set in default storage
clause for TEMP5, and it is a tablespace of type TEMPORARY.
Note: As initial extent contains one block of segment header, INITIAL of default storage is
4K even though it is set to 2K.
|
|
SQL> SELCT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE,
CONTENTS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TEMP5' ;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE CONTENTS
----------------------------------------------------------------------------
TEMP5 4096 2048 0 TEMPORARY
|
OK, I perform sort based on the status of tablespace of type TEMPORARY above.
|
|
SQL> ALTER SESSION SET SORT_AREA_SIZE = 3241984;
SQL> SET AUTOTRACE TRACE
SQL> SELECT /*+ FULL (T10) */ EMPNO, JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
Execution plan
---------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Bytes=1100000)
1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000)
2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=100000 Bytes=1100000)
Statistics
---------------------------------------------------------------------
89896 recursive calls <--many internal calls
6479 db block gets <--physical read exceeds memory sort
71788 consistent gets <--physical read exceeds memory sort
3572 physical reads <--physical read exceeds memory sort
1342444 redo size <--REDO is created even though SELECT is used
5706824 bytes sent via SQL*Net from client
740350 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory) <--proof of which sort is not performed on memory
1 sorts (disk) <--proof of which sort is performed on disk
100000 row processed
|
Now, execute the following SQL statement to examine if tablespace
of type TEMPORARY has been expanded.
|
|
SQL> SELECT TABLESPACE_NAME, SEGMENT_NAME, EXTENTS, BLOCKS FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME='TEMP5' ;
TABLESPACE_NAME SEGMENT_NAME EXTENTS BLOCKS
---------------------------------------------------
TEMP5 13.450 1016 1016
|
The result above indicates that 1,016 extents and 1,016 blocks are allocated.
Now, I summarize my analysis.
SORT_AREA_SIZE=3241984=3241984/1024=3166K=3166/2=1583 blocks
Also, disk sort threshold is 3241985, which should be rounded to DB_BLOCK_SIZE internally.
To be more specific, it is 1584.
<Summary>
Number of blocks on disk = 1016 blocks
SORT_AREA_SIZE threshold = 1583 blocks
This analysis proves that about two-third the size of SORT_AREA_SIZE threshold (1583) is written
in tablespace of type TEMPORARY. Thus, SORT_AREA_SIZE=INITIAL=NEXT is not correct at all.
Worst process? It is to perform sort on memory to smaller extents such as INITIAL=NEXT=2K.
The guide is appropriate in some point because one extent of sort segment can handle the process. However,
I think it goes into detail too much.
Consistent gets and physical reads exceed sort on memory in number because it is necessary to
write sorted data into temporary tablespace and read it again to merge.
That's it for today.
|
|
Ora! Ora! Oracle