Oracle基础体结构及性能优化.docVIP

Oracle基础体系结构及性能优化 1、基础体系结构介绍 Oracle 数据库基础对象 Tablespaces and Datafile Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship. Figure 3-1 Datafiles and Tablespaces Temporary Tablespaces and tempfiles What are Temporary Tablespaces: Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc. What are TEMPFILES? Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file. TEMPFILEs are not recorded in the databases control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment. One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example: SQL ALTER DATABASE TEMPFILE /oradata/temp02.dbf DROP INCLUDING DATAFILES; If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace: SQL ALTER TABLESPACE temp ADD TEMPFILE /oradata/temp03.dbf SIZE 100M; Except for adding a tempf