| Oracle uses it's own version of "quick-sort", which operates in memory but
overflows to disk. Oracle has what is known as "temporary segments", which
is where the disk-based sorting occurs.
The size of the in-memory sort buffer is defined by the SORT_AREA_SIZE
parameter in the INIT.ORA file. It's probably too small (and you're sorting to
disk) or it's too large (and you're paging to disk).
Rick
|
| Thanks for the response. I presume Oracle does not use bubble sort.
Does the SORT_AREA_SIZE goes together with the adjustment to the
SGA (System Global Area?) ? meaning that it has to synchronize. And if
it flushed to the disk then it should be placed in some common area.
I guessed I've to use the SQL*DBA to find out the size of the in-memory
sort buffer required.
Can the trace facility provided by Oracle trapped the activity of the
sorting ? I'm just curious to know how efficient does it do the
sorting itself.
In Rdb, you could possibly dump the data on a flat file and use the
system sort. On the other hand I'm not sure if Oracle allows us to do
this.
Rgds,
Zahir.
P.S. Sorry, I just had to use this conference for such discussion.
Don't know where else to post it.
|
| > Does the SORT_AREA_SIZE goes together with the adjustment to the
> SGA (System Global Area?) ? meaning that it has to synchronize. And if
> it flushed to the disk then it should be placed in some common area.
> I guessed I've to use the SQL*DBA to find out the size of the in-memory
> sort buffer required.
Now you're getting into areas I'm not that familiar with :-) The Oracle DBA
Guide and System Tuning guide provide *adequate* (read: not great, just barely
enough to try something) hints in this area.
> Can the trace facility provided by Oracle trapped the activity of the
> sorting ? I'm just curious to know how efficient does it do the
> sorting itself.
It may - I cannot find anything in the manuals that explicitly says that it does.
Any reasonable trace facility *should* provide this basic information.
> In Rdb, you could possibly dump the data on a flat file and use the
> system sort. On the other hand I'm not sure if Oracle allows us to do
> this.
Not easily.
Rick
|
|
I deliver VMS tuning for sites in my district, so my perspective is
probably a bit oversimplified, but this is what I've seen:
There is usually a "temp" tablespace on disk somewhere; if the amount of
data for the sort run exceeds the SORT_AREA_SIZE, the interim results
go into the temp tablespace. If the temp tablespace isn't defined,
ORACLE will store the data in a temporary segment of the SYSTEM
tablespace.
You can look at I/O to the temp tablespace via SQL*DBA:
SQLDBA> Monitor file
If the rates are high (based on the device writing) and sustained over
a "long" period of time, you probably will want to increase the memory
size for the sort area. Increases to the sort area will (according to
the O tuning guide) "reduce the number of runs (and the number of
merges) and reduce disk I/O to the temporary segment".
SORT_AREA_SIZE defines the sort area per user process. If you decide
to increase the INIT.ORA parameter, you may have to increase the user
process working set. Otherwise, VMS memory management will affect the
sort by paging of the working set, either to the modified page list or
the page file.
I have several customers who use external sorts, the VMS sort utility
and other third party packages. I don't know the specific mechanisms,
but it seems as if the results are written to a flat file and then the
sort is called. After the sort, the report writing programs are
called.
Kathy S.
|
| Hi,
re 4. I think you are quite correct about the SORT_AREA_SIZE
but we definitely need to look at a bunch of other stuff as
well.
The query that we have is joining several tables together and
sort it by the order of date. We can't make the date field as
an index for some reasons. Anyway, most of the
functionalities in the application are query in nature rather
than update intensive type of functions.
After looking at the tuning guide for Oracle, it states that
we could possibly trace the query using EXPLAIN PLAN
statement in conjunction with the trace facility. This will
show the execution for each SQL statement in the application.
Thus, we can identify whether the SQL statement did a full
scan of the table. And if it does, then in order for us to
improve the performance is by increasing value of the
DB_FILE_MULTIBLOCK_READ_COUNT in INIT.ORA file. May be we
should also try clustering the tables but this has to be
thought of properly otherwise it would be performance hit for
the updating processes.
We should also do some system tuning to decrease the swapping
and paging, may be increases the total memory.
In conjunction with that we have to ensure that the entire
SGA (System Global Area) fits into memory. The purpose of SGA
is to store data in memory for fast access.
We also need to control the parsing of the SQL statement if
the case that the query is utilize frequently enough. We can
determine that by using the TKPROF. This is the SQL trace
facility that shows fetch counts, CPU and elapsed times,
physical reads and logical reads and number of rows
processed. The processing of the SQL statement seems to be
done in the context area which is a workspace in the memory.
The CONTEXT_AREA parameter can be adjusted accordingly in
INIT.ORA.
We also have to look into the possibility of increasing the
DB_BLOCK_BUFFERS in INIT.ORA which is the parameter for
controlling buffer cache in the SGA that will holds copies of
the database blocks.
Phew!! so much stuff to look into for a query...
SGA, DB_FILE_MULTIBLOCK_READ_COUNT, CONTEXT_AREA, SORT_AREA,
DB_BLOCK_BUFFERS, all boils down to how much memory you have
in the system and how efficient you use it.
I was reading a chapter on Memory Structures in the ORACLE
RDBMS - Database Administrator's Guide and on the first page
of the chapter it printed two quotations :
"Own only what you can always carry with you; know language,
know countries, know people. Let your memory be your travel
bag. " - Alexander Solzhentisyn
"When I was youger I could remember anything, whether it had
happened or not." - Mark Twain.
Regards,
Zahir.
|