|
| Hit ratio and sorts | 6 April 1999 |
| I have a poor cache hit ratio, and so I looked in V$BH and found that 80% of the blocks belong to the temporary tablespace. Why are the temporary segment blocks not aging out? How can I improve the hit ratio in this scenario? | ||
 | It would be nice to eliminate the disk sorts if possible, and if not, to use sort direct I/O. Look at the total writes against the temporary tablespace datafiles in V$FILESTAT, and divide by the number of disk sorts in V$SYSSTAT to get an average sort size. If you can set your sort_area_size to that, it will help a lot. All other things being default, sort_area_size needs to be 640K or more to get sort direct I/O. |
|
| Temp space for sorting | 18 May 1999 |
|
If my table occupies 500M of disk space, and I have a 1M sort_area_size,
how much temporary tablespace disk space do I need to perform the following sorts?
1. select col1, col2, col3 from tablename order by col1, col2, col3; | ||
|
The sort space requirements are dependent on the size of the row source being sorted, rather than the size of the key.
Your two sorts would require the same amount of disk space.
However, if you were joining to another table and including one of its columns in the select list,
then the size of the row source to be sorted could be larger than the largest base table.
It is also possible for the disk space requirements to be up to 1.9 times the size of the row source if the sort area size is too small. If for example you need to sort 1,001,000 rows and your sort area size can only accommodate 1000 rows at a time. Then the first phase of the sort will be to read the row source, and write 1001 sort runs of 1000 rows each to the temporary segment. The next phase is to read sort_multiblock_read_count blocks from each of the first N sort runs and merge them into a single large intermediate sort run. If sort_multiblock_read_count is 1 and each row takes an entire block then the merge width will be 900 (because no more than 90% of the sort memory can be used for read slots during a merge - the rest is reserved for write buffers). Towards the end of this merge, you need space for all the original 1001 sort runs and the big sort run at the same time - which is nearly 1.9 times the row source size. The third phase of this sort would be a second merge pass to merge to large sort run with the remaining 101 sort runs. |
|
| TS enqueue locks | 9 June 1999 |
| I trace my process and I see that there are too many waits on the TS lock. But I also know that there is no sort on disk. This means that my process does not use a temporary segment. Why is it waiting for a TS lock? | ||
| The TS (id2=0) lock is held for each sort run or hash partition write to a temp segment. You may be doing a hash join, if you are not doing a disk sort. Do not trust the disk sorts statistic, it is known to miss some disk sorts. Rather check for I/O against your temporary tablespace in V$FILESTAT, or look for an UNMAP operation in your raw trace file. That said, while you may see lots of gets on this lock, waits should be relatively rare, and their duration brief. You can check out X$KSQST for the ratio of gets to waits. The wait times have to come from the raw trace file. |
|
| Sort memory | 21 September 1999 |
| When is the sort_area_size chunk of memory allocated? Is it allocated as soon as a user process connects to Oracle, or is it when a user process first performs a sort? | ||
| The sort_area_size parameter is the maximum size to which a sort area can grow, but it is not allocated all at once. It grows in chunks of just a few K each, as the sort proceeds. The memory is reduced to the retained size when the sort is finished and while the sorted rows are being fetched. Thereafter it is freed entirely. Unless you use parallel query heavily, it is unlikely that your large sort area is a significant cause of memory pressure. But given that each session can use two sort areas concurrently, your worst case is worse than you might imagine. |
|
| Sort performance | 7 February 2000 |
| I did some tests on sort area size and got some results that I'm not sure how to explain. Increasing sort area size improves performance rapidly as you move through very low values, then levels off and does not improve again until you avoid the disk sort altogether. Any ideas? | ||
|
The big determinant of sort performance is the number of merge phases.
If the data set to be sorted fits into sort_area_size, there is no merge phase; it is just a memory sort.
Otherwise, sort runs are written to disk, and then merged.
If sort_area_size can hold at least one sort multiblock read from each sort run, then only one merge phase is required.
Otherwise sets of sort runs are merged into larger sort runs, and those larger sort runs are merged in a second merge phase.
In general, the number of merge phases required is the ceiling of log0.9 * rows-in-sort-area(rows-in-data-set).
Each merge phase (except the last) writes and reads the entire data set. The amount of I/O done depends entirely on the number of merge phases. The CPU cost of sorting the sort runs and merging them is almost insignificant by comparison with the I/O cost of merging. A larger sort area size reduces the merge CPU by more than it increase the sort CPU cost, but it is relatively trivial. |
|
| Can I set the sort_multiblock_read_count to the same size as db_file_multiblock_read_count to speed up the merge phase, if the CPU is not maxed out. |
| Yes, but you need to be careful because raising sort_multiblock_read_count lowers the threshold at which a second merge phase will be required. It does not however cost any more CPU, in fact it saves some. |
| Copyright © Ixora Pty Ltd |
|