Optimize Sort Operations
Introduction
As a DBA, you
are responsible to optimize sort operations
of your organization�s database in case of
performance problems. Your job�s
responsibilities dictate that you should be
aware of the following basic fundamental
subjects:
Monitoring a
sort statement operation
Optimizing a
sort statement operation
Using the
V$SYSSTAT view
SORTS (DISK)
SORTS (MEMORY)
Calculating
the sort Ratio
SORT_AREA_SIZE
PGA_AGGREGATE_TARGET
Increasing the
SORT_AREA_SIZE parameter
Hands-on
In this
exercise, you will learn how to monitor and
optimize sort operations.
Now, connect to SQLPlus as the
SYSTEM/MANAGER user.
SQL> CONNECT
system/manager AS SYSDBA
Sorting
process
The
Oracle Server Processes will sort as much as
they can in the memory sort area before
using any disk sort space.
Sort performed
destination (Memory vs. Disk)
Now, query the V$SYSSTAT view to track the
number of in-memory and to-disk sorts, as
well as the total number of rows sorted.
SQL> SELECT name,
value
FROM v$sysstat
WHERE name like 'sorts%'
/
Notice that the sorts (disk)
number must be very low, and the sorts
(memory) number can be as high as possible.
The �sorts
(memory)� value is a number of times that
Oracle tables were sorted in the memory. The
�sorts (disk)� value is a number of times
that Oracle tables were sorted on the disk
using the TEMPORARY tablespace.
Sort HIT Ratio
(Memory vs. Disk)
Now, determine
the sort ratio of the in-memory vs. to-disk
sorts.
SQL> SELECT 100*(a.value-b.value)/(a.value)
AS "Sort Ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (memory)'
AND b.name ='sorts (disk)'
/
Notice that the sort ratio should
be greater than 95%. If you are not using
the automatic PGA memory and the number is
less than 95 percent, you should greatly
consider increasing the value of the
SORT_AREA_SIZE parameter. If you are using
the automatic PGA memory and the number is
less than 95 percent, you should greatly
consider increasing the value of the
PGA_AGGREGATE_TARGET parameter.
Increase sort area in memory
Try to
increase the SORT_AREA_SIZE parameter to
819200 bytes.
SQL> ALTER SYSTEM
SET sort_area_size=819200 SCOPE=spfile
/
In order for the change to take
effect, the database needs to be restarted.
Shutdown and startup the database in order
to see the changes.
SQL> SHUTDOWN
IMMEDIATE;
SQL> CONNECT system/manager AS SYSDBA
SQL> STARTUP
Questions:
Q: How do you
optimize a sort operation in the Oracle SGA
memory?
Q: How do you
monitor a sort operation?
Q: How do you
use the V$SYSSTAT view to check a sort usage
in the SGA memory?
Q: Describe
the �sorts (disk)� value in the V$SYSSTAT
view.
Q: Describe
the �sorts (memory)� value in the V$SYSSTAT
view.
Q: How do you
calculate the sort ratio value in the SGA
sort area?
Q: How do you
optimize the SORT_AREA_SIZE memory?
Q: what does
the following SQL statement?
SQL> SELECT 100*(a.value-b.value)/(a.value)
AS "Sort Ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (memory)'
AND b.name ='sorts (disk)'
/
Q: What is an
acceptable range for the sort ratio in the
SGA sort area?
Q: When should
you consider increasing your SORT_AREA_SIZE
parameter?
Q: When should
you consider increasing your
PGA_AGGREGATE_TARGET parameter? |