Oracle Database - Part Two

Database schema
Most Oracle database installations traditionally came with a default schema called SCOTT. After the installation process has set up the sample tables, the user can log into the database with the username scott and the password tiger. The name of the SCOTT schema originated with Bruce Scott, one of the first employees at Oracle (then Software Development Laboratories), who had a cat named Tiger.

Oracle Corporation has de-emphasized the use of the SCOTT schema, as it uses few of the features of the more recent releases of Oracle. Most recent[4] examples supplied by Oracle Corporation reference the default HR or OE schemas.

Other default schemas include:
• SYS (essential core database structures and utilities)
• SYSTEM (additional core database structures and utilities, and privileged account)
• OUTLN (utilized to store metadata for stored outlines for stable query-optimizer execution plans.)
• BI, IX, HR, OE, PM, and SH (expanded sample schemas containing more data and structures than the olderSCOTT schema).

System Global Area
Each Oracle instance uses a System Global Area or SGA—a shared-memory area—to store its data and control-information. Each Oracle instance allocates itself an SGA when it starts and de-allocates it at shut-down time. The information in the SGA consists of the following elements, each of which has a fixed size, established at instance startup:

Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the following characteristics:

• One or more datafiles form a logical unit of database storage called a tablespace.
• A datafile can be associated with only one tablespace.

• Datafiles can be defined to extend automatically when they are full. Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory. Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once

• the redo log buffer: this stores redo entries—a log of changes made to the database. The instance writes redo log buffers to the redo log as quickly and efficiently as possible. The redo log aids in instance recovery in the event of a system failure.

• the shared pool: this area of the SGA stores shared-memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. An insufficient amount of memory allocated to the shared pool can cause performance degradation.

• the Large pool Optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes

• Database buffer cache: Caches blocks of data retrieved from the database • KEEP buffer pool: A specialized type of database buffer cache that is tuned to retain blocks of data in memory for long periods of time

• RECYCLE buffer pool: A specialized type of database buffer cache that is tuned to recycle or remove block from memory quickly
• nK buffer cache: One of several specialized database buffer caches designed to hold block sizes different from the default database block size

• Java pool:Used for all session-specific Java code and data in the Java Virtual Machine (JVM)

• Streams pool: Used by Oracle Streams to store information required by capture and apply

When you start the instance by using Enterprise Manager or SQL*Plus, the amount of memory allocated for the SGA is displayed.

Library cache
The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

Data dictionary cache
The data dictionary comprises a set of tables and views that map the structure of the database. Oracle databases store information here about the logical and physical structure of the database. The data dictionary contains information such as:

• user information, such as user privileges
• integrity constraints defined for tables in the database
• names and datatypes of all columns in database tables
• information on space allocated and used for schema objects

The Oracle instance frequently accesses the data dictionary to parse SQL statements. Oracle operation depends on ready access to the data dictionary—performance bottlenecks in the data dictionary affect all Oracle users. Because of this, database administrators must make sure that the data dictionary cache has sufficient capacity to cache this data. Without enough memory for the data-dictionary cache, users see a severe performance degradation. Allocating sufficient memory to the shared pool where the data dictionary cache resides precludes these particular performance problem.

Program Global Area
The Program Global Area or PGA memory-area of an Oracle instance contains data and control-information for Oracle's server-processes. The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:

• stack-space: the memory that holds the session's variables, arrays, and so on
• session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)
• private SQL-area: an area that holds information such as bind-variables and runtime-buffers
• sorting area: an area in the PGA that holds information on sorts, hash-joins, etc. DBAs can monitor PGA usage via the V$SESSTAT system view.

Dynamic performance views
The dynamic performance views (also known as "fixed views") within an Oracle database present information from virtual tables (X$ tables) built on the basis of database memory. Database users can access the V$ views (named after the prefix of their synonyms) to obtain information on database structures and performance.

Creative Commons Attribution-Share Alike 3.0 //