|
SORT
|
Hello everyone. In this issue, I will complete the analysis of sort,
and move onto the next topic next week.
In this issue, I talk about two dynamic performance tables for sort. You can refer to it on
following versions.
V$SORT_SEGMENT --> Oracle7.3 or higher
V$SORT_USAGE --> Oracle8 or higher
1. V$SORT_SEGMENT
This view contains information about every sort segment in a given instance. Note that
information only covers a temporary segment that is created on a tablespace of type
TEMPORARY.
|
SQL> DESC V$SORT_SEGMENT
NAME NULL? DATATYPE
---------------------------- ----------- ------------
TABLESPACE_NAME VARCHAR2(31)
SEGMENT_FILE NUMBER
SEGMENT_BLOCK NUMBER
EXTENT_SIZE NUMBER
CURRENT_USERS NUMBER
TOTAL_EXTENTS NUMBER
TOTAL_BLOCKS NUMBER
USED_EXTENTS NUMBER
USED_BLOCKS NUMBER
FREE_EXTENTS NUMBER
FREE_BLOCKS NUMBER
ADDED_EXTENTS NUMBER
EXTENT_HITS NUMBER
FREED_EXTENTS NUMBER
FREE_REQUESTS NUMBER
MAX_SIZE NUMBER
MAX_BLOCKS NUMBER
MAX_USED_SIZE NUMBER
MAX_USED_BLOCKS NUMBER
MAX_SORT_SIZE NUMBER
MAX_SORT_BLOCKS NUMBER
RELATIVE_FNO NUMBER
|
..._EXTENTS and ..._BLOCKS such as TOTAL_EXTENTS, TOTAL BLOCKS,
USED_EXTENTS, USED_BLOCKS, FREE_EXTENTS, and FREE_BLOCKS indicate the number of extents
and the number of blocks.
I have illustrated the relationship as follows:
Ref40
According to my analysis, TOTAL_BLOCKS, MAX_BLOCKS, and MAX_USED_BLOCKS
indicate the same value. What we have to do here is to simply check a value of TOTAL_BLOCKS.
MAX_SORT_BLOCKS row shows the largest sort block size in sort on disk.
You can't check information about a temporary tablespace (PERMANENT) on V$SORT_SEGMENT because this
tablespace is dropped after each sort.
2. V$SORT_USAGE
This view contains further detailed status of sort at session level. V$SORT_USAGE operates effectively
when sort is performed frequently because the information appears only at an instance.
Information about both tablespace of type TEMPORARY and temporary tablespace (PERMANENT) is available on
V$SORT_SEGMENT.
|
|
SQL> DESC V$SORT_USAGE
NAME NULL? DATATYPE
------------------------------------ ---------- ----------------
USER VARCHAR2(30)
SESSIONS_ADDR RAW(4)
SESSION_NUM NUMBER
SQLADDR RAW(4)
SQLHASH NUMBER
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER
|
Take a look at CONTENTS and BLOCKS in V$SORT_USAGE.
CONTENTS indicates whether tablespace is TEMPORARY/PERMANENT.
EXTENTS indicates the number of extents allocated to the sort, and BLOCKS indicates
the number of extents in blocks allocated to the sort.
V$SORT_USAGE is obtained during sort operation. By using V$SORT_USAGE in conjunction
with V$SESSION and V$SQL, you can get a variety of information such as temporary
tablespace, username, program, SQL statement, and the number of blocks.
|
|
SQL> SELECT SORT. TABLESPACE, SORT, CONTENTS, SES. USERNAME, SES. PROGRAM
SQL. SQL_TEXT, SORT. BLOCKS FROM V$SESSION SES,
V$SORT_USAGE SORT, V$SQL SQL
WHERE SES. SADDR=SORT. SESSION_ADDR AND SES. SQL_ADDRESS=SQL. ADDRESS;
SQL> COL TABLESPACE FORMAT A8
SQL> COL CONTENTS FORMAT A10
SQL> COL USERNAME FORMAT A8
SQL> COL PROGRAM FORMAT A10
SQL> COL SQL_TEXT FORMAT A30
SQL> COL BLOCKS FORMAT 999
TABLESPACE CONTENTS USERNAME PROGRAM SQL_TEST BLOCKS
---------- --------- -------- ---------- ------------------------- ------
TEMP1 TEMPORARY CHAMU1 popsql.exe SELECT EMPNO, ENAME, 100
JOB, MGR HIREDATE, SAL,
COMM, DEPTNO FROM T1MAN_1
ORDER BY SAL, COMM DEPTNO
|
BLOCKS increases during sort operation, which will help you
identify SQL with heavy sort.
V$SORT_SEGMENT indicates accumulated value; V$SORT_USAGE, on the other hand,
stores session information of a second.
That's it for today. See you next week with a new topic.
|
|
Ora! Ora! Oracle
