|
Sort
|
In this issue, I will analyze the sort process based on the following example.
Even though each client performs the same process in some system, a certain client requires
longer execution time (more than double). The problem may be an SQL statement performing
a sort process.
I will check the sort configuration in details.
(Test environment)
OS: Red Hat Linux release7.1
Oracle: 9.2.0.1
1. Check the initialization parameter
SQL> select name,value from v$parameter
where name in ('workarea_size_policy','pga_aggregate_target')
or name like 'sort%';
NAME VALUE
------------------------- -------------------------
sort_area_size 65536
sort_area_retained_size 65536
pga_aggregate_target 4294967296000
workarea_size_policy AUTO
|
Automatic PGA Memory Management is set to active.
The size of PGA target memory is sufficient.
I actually perform sort in a client with slow performance.
2. Compare sort performance among sessions
Session A:
SQL> select * from code where rownum<2000000 order by 1;
Elapsed: 00:03:210.22
Session B:
SQL> select * from code where rownum<2000000 order by 1;
Elapsed: 00:01:42.88
|
Session A is a result of sort process performed in a client with slow performance.
Even though the same SQL statements are executed in both session A and B, session A
requires more than twice of the execution time.
I will check the session information.
3. Check the session information
*I capture important data only.
SQL> select sid,serial#,server from v$session
where username = 'XPRT';
SID SERIAL# SERVER
---------- ---------- ------------------
15 640 SHARED <= Session A
12 706 DEDICATED <= Session B
|
Take a look at SERVER column above. SERVER of Session A is SHARED which indicates
shared server. SERVER of Session B, on the other hand, is DEDICATED which indicates
the dedicated server.
In the shared server mode, SGA is allocated instead of PGA to perform sort.
In the dedicated server mode, a server process is committed to a single session only, which means
that the dedicated server mode requires large amount of system resources.
If your system environment does not have sufficient system resources such as CPU and memory,
the shared server enables many clients to connect to the same server process.
In the shared server mode, sort is performed by allocating SGA memory such as shared pool and large pool
instead of allocating PGA memory.
In other words, in the shared server mode, memory is allocated based not on the value of
pga_aggregate_target but on the value of sort_area_size.
This can be confirmed by referring to v$sql_workarea.
4. View v$sql_workarea
SQL> select
sql_text,
operation_type,
policy,
last_memory_used/1024,
last_execution,
last_tempseg_size
from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text like 'select * from code where rownum<2000000 order by 1%';
SQL_TEXT OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<2000000 order by 1 SORT
select * from code where rownum<2000000 order by 1 SORT
POLICY LAST_MEMORY_USED/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
-------- ----------------------- ---------------- -------------------
MANUAL 73 462 PASSES 70254592 <= Session A
AUTO 73608 OPTIMAL <= Session B
|
Take a look at POLICY column. POLICY of session A indicates MANUAL, which means that
the value of pga_aggregate_target is not used. Instead, the value of sort_area_size
is used. As the sort_area_size is set to 65536, multipath occurs and consequently, disk sort
is performed.
In this example, there are sufficient system resources. Thus, I change the initialization
parameter to use the dedicated server mode and to make the value of the pga_aggregate_target
be applied.
That's it for today.
Takuya Kishimoto
|
|
