|
| Freeing the temporary segment in a TEMPORARY tablespace | 6 April 1999 |
| We have a 1G TEMPORARY tablespace, but suspect that sort segment extents are not being properly reused. We don't know how to free the sort segment, other than at instance startup. We would like to know if there is a way to force this to happen without restarting the instance. | ||
 | Doing an ALTER TABLESPACE to set PCTINCREASE to what it already is (hopefully 0) has the effect of cleaning up the existing sort segment in the tablespace under normal circumstances. |
|
| Round robin extent allocation | 17 May 1999 |
| Does Oracle uses a round robin extent allocation technique when there are multiple datafiles in a tablespace? | ||
| Yes, for direct loads and parallel CREATE TABLE AS SELECT. For dynamic extent allocation, Oracle prefers to take a correctly sized extent from any datafile, rather than to split a large extent from the "next" datafile. |
|
| 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 twice 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 one row at a time from each of the first 1000 sort runs and merge them into a single large sort run. Towards the end of this phase, you need space for all the original 1001 sort runs and the big sort run at the same time - which is nearly twice 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 original 1000 row sort run. To avoid this, and process a sort in a single merge pass, the sort area size must hold at least as many rows as the square root of the number of rows in the row source. |
|
| Minimum extent size | 23 June 1999 |
| How can specifying a MINIMUM EXTENT on each tablespace prevent fragmentation? | ||
| The trick is that it is not just a minimum. All extent allocations are rounded up to a multiple of this size, so all fragments (except possibly the last in the datafile) will be multiples of the minimum extent size, hence no fragmentation problems. In my opinion, this is one of the most undervalued features of Oracle8. |
|
| Reclaiming space | 23 June 1999 |
| Our data load failed and rolled back, but the table and index space was not reclaimed. Why? | ||
| Oracle always does space management in recursive transactions. Recursive transactions commit immediately, independent of the parent transaction. Otherwise, a lock (the ST enqueue) would be retained until the end of the user transaction, and that would suspend all other space management operations. So what you see here is good and normal. |
|
| Truncate table slow | 5 October 1999 |
| We support several 8.0.x databases. On some of them, the truncate table is very fast. On others it is very slow. Truncate with REUSE STORAGE is always fast, but not always appropriate. I have had situations where I will do a truncate on a 500MB table and it takes 15-20 minutes when no one else is on the system. I am assuming it is caused by the enqueue on the system tables while it "frees" the extents. Is there an init.ora parameter that may help or something else I can look for? | ||
|
This sounds like the space management clusters in the data dictionary are in poor condition.
Use
execute dbms_utility.analyze_schema('SYS', 'COMPUTE'); and then check the AVG_BLOCKS_PER_KEY column in DBA_CLUSTERS. If it is more than 2 or 3, then you should consider rebuilding the database. When you have finished, remove the statistics again with execute dbms_utility.analyze_schema('SYS', 'DELETE'); |
|
| Temporary tablespace usage | 9 December 1999 |
| How can one see how much space each user is using in the temporary tablespace? | ||
| Under Oracle8, you can just query V$SORT_USAGE. Under Oracle7, there is some PL/SQL code available under the "Tales from the Scrypt" section of MetaLink that can do the same, but it is far from easy to get it working. Good luck. |
|
| Non-sort uses of temporary tablespace | 19 December 1999 |
| What operations, over and above sorting, use the temporary tablespace? | ||
| View/subquery materialization (from 7.0), hash joins (from 7.3) and global temporary tables and indexes (8.1) are other non-sort uses of temporary space. |
|
| Truncate | 24 March 2000 |
| Then I traced a truncate, and found that it updated OBJ$ and TAB$, but not FET$ and UET$. Why? | ||
| Changes to FET$ and UET$ are not necessarily required for a truncate. If the segment has not grown beyond MINEXTENTS, then there is no space to free and no space management is required. Your segment may also have been in a locally managed tablespace. |
|
| Locally managed SYSTEM tablespace? | 3 April 2000 |
| I can't seem to find the syntax for creating a database with a locally managed SYSTEM tablespace. | ||
| I tried it under 8.1.5 by editing sql.bsq. The UNIFORM policy did not work. I got an error saying that only AUTOALLOCATE was allowed for SYSTEM. But when I tried AUTOALLOCATE, database creation failed with an internal error because the initial extent sizes were too small. For now Oracle say that it is a documentation bug, and that there is no local extent management for the SYSTEM tablespace. However, I'm fairly confident that with careful sizing of the data dictionary segments in sql.bsq that it could be made to work, but I have not tried again, because I don't think it is a good idea anyway. |
| Copyright © Ixora Pty Ltd |
|