Version before 9i
For each session, the sort will first use sort_area_size if it is insufficient, and the temporary table space will be used. But what is the process in this? Here, I may have a useful place for everyone (if there is anything unclear or inappropriate, you will be able to explore the sort_area_size = 100k, it is exactly 100 records to sort, when the sort record is less than or equal to 100, OK, all Sort in memory, very fast, but if more than 100, the temporary table space (using disk is performed) We choose a critical value to explain that there is 10010 records that need to be sorted, this time we are sorted Divided into 101 groups, each read 100 group sort, then write to disk, only 10 of the 101st group, and write the disk after sorting. This is the second order, this order will be in the top 100 groups Any draw is selected. "According to my personal guess, it should be a ranked into a disk, and the group of the record is re-extracting the group is sorted out (so soon as it is in an orderly record group)." When this process is completed, the disk space you need is approximately twice the actual record storage space (this is also the reason why the sort space mentioned in most books is 2 times the recording space), and due to the rest of 10 Record, then this 10 records need to be sorted with 100,000 records in front, this cost is also quite big! Therefore, we usually recommend that if you need to sort records up to 1 million, Sort_area_size should be able to install 1000 items, otherwise, the extra 10, only 10 will bring huge consideration. ! If the setting is extremely unreasonable, the sorting record has reached the three-way squares that sort_area_size can accommodate. For example, the above example requires 1 million records, then the same, repeat this process, when each 10,000 records are sorted above Afterwards, then sorted from this 100 group (10,000 records per group) ... In this process, the cost of the disk consumption and time should have a sense of sensibility. So, we recommend that sprt_area_size can accommodate the number of records that are at least larger than the number of sort records.
From 9i, Oracle uses PGA_AGGREGATE_TARGET to replace some settings about the session level, that is, when Workarea_SIZE_POLICY = Auto, all * _Area_size is not set at the session level (only for the Dedicated mode, the SHARED mode), These session shares the PGA_AGGREGATE_TARGET set together with the database instance level
Show Parameters Area
Name Type Value --------------------------------- ------------------------------ bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 248576sort_area_retained_size integer 0sort_area_size integer 124288workarea_size_policy string AUTO and follow each session using a PGA_AGGREGATE_TARGET Rules, that is, each session use space cannot exceed PGA_AGGREGATE_TARGET * 5%, if it exceeds the temporary table space
