Databases Reference
In-Depth Information
In the examples provided in this recipe, we are not demonstrating indexes, but even if some
useful indexes were used, the execution plans (
SORT
operations) wouldn't change. The only
difference would be that the FTS operation is replaced by another data access operation
using the indexes.
Many developers use
UNION
instead of
UNION
ALL
without
understanding the difference in execution time. An additional
condition to the
WHERE
clauses can often avoid the use of the
costly
UNION
operation.
See also
F
In
Chapter
4
,
Optimizing SQL Code the Using
Subqueries
and
Optimizing
joins
recipes
Troubleshooting temporary tablespaces
At the beginning of this chapter, we saw that sorts may occur in memory or on disk, and that
in-memory sorts are faster than on-disk ones because fewer I/O operations are involved.
However, system memory is finite and cannot expand the sort area above the limits of the
physical memory available. If sorts exceed the sort area, it's better to use on-disk sort than
over allocating memory—ending in very slow pagination (swap to disk managed by the host
Operating System).
On-disk sort operations require space to save sort runs, which cannot be stored in memory.
Oracle uses sort segments to store this type of information on disk.
In this recipe, we will see how to configure temporary tablespaces to speed up on-disk sort
operations and some diagnostic queries to be used when we want to retrieve information
about them.
How to do it...
The following steps will demonstrate how to configure temporary tablespaces:
1.
Connect as
SYSDBA
to the database:
CONNECT / AS SYSDBA
2.
Examine the number of sorts in the system (from instance startup):
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%sorts%';
