Oracle database management tracks its computers data storage with the help of information stored in the system or tablespace. The system or tablespace contains the data dictionary and often (by default) indexes and clusters. A data dictionary consists of a special collection of tables that contains information about all user-objects in the database.
The oracle RDBMS also supports “locally managed” tablespaces that store space management information in bitmaps in their own headers rather than in the tablespace or system (as happens with the default “dictionary-managed” tablespace). Oracle has also introduced the tablespace or system, which contains some of the tables formerly stored in the tablespace (system), along with objects for other tools such as OEM, which previously required its own tablespace.
Disk files primarily represent one of the following structures:
- Data and index files: These files provide the physical storage of data, which can consist of the data-dictionary data (associated to the tablespace system), user data or index data. These files can be managed manually or manage by Oracle itself (Oracle-managed files). Note that a data file has to belong to exactly on tablespace, whereas a tablespace can consist of multiple data files.
- Redo log files: this consists of all changes to the database, used to recover from an instance failure. Note that often a database will store these files multiple times, for extra security in case of disk failure. The identical redo log files are said to belong to the same group.
- Undo files: These are special data files, which can only contain undo information, aid in recovery, rollbacks and read-consistency.
- Archive log files: These files, copies of the redo log files, are usually stored at different locations. They are necessary (for example) when applying changes to a standby database, or when performing recovery after a media failure. It is possible to archive to multiple locations.
- Temp files: These special data files serve exclusively for temporary storage data (used for example for large sorts or for global temporary tables)
- Control file, necessary for database startup. “A binary file that records the physical structure of a database and contains the names and locations of redo log files, the time stamp of the database creation, the current log sequence number, checkpoint information and so on.
Monitoring of locks
Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either user objects, such as tables and rows, or system objects not visible to users, such as shared data structures in memory and data dictionary rows. Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.
Monitoring wait events
Wait events are statistics that are incremented by a server process to indicate that it had to wait for an event to complete before being able to continue processing. A session could wait for a variety of reasons, including waiting for more input, waiting for the operating system to complete a service such as a disk write, or it could wait for a lock or latch.
When a session is waiting for resources, it is not doing any useful work. A large number of waits is a source of concern. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.
Oracle provides several views that display wait event statistics.