Archive for the ‘OCP DBA 9i’ category

Materialized views can be created either through the CREATE MATERIALIZED VIEW statement or the Oracle Enterprise Manager (OEM). The CREATE MATERIALIZED VIEW statement can also be used for populating the materialized views. A materialized view can be derived from an existing user-defined materialized view. In this case, the ON PREBUILT TABLE clause of the CREATE MATERIALIZED VIEW is used. However, if a user-defined materialized view does not exist, a materialized view can be populated by using either the BUILD IMMEDIATE or BUILD DEFERRED clause of the CREATE MATERIALIZED VIEW statement, depending upon whether the materialized view has to be populated immediately or at some later time, respectively. However, the BUILD DEFERRED materialized view cannot be used until a refresh is performed on it. As soon as the first refresh is performed on the BUILD DEFERRED materialized view, it is enabled, provided the ENABLE QUERY REWRITE clause is specified.

Latches are data structures used for protecting memory structures that are needed for a short period of time, e.g., the least recently used (LRU) list. Request for latches are not queued for execution. Following are the two modes of latches:

• Willing-to-wait mode
• Immediate mode

The objective of this test is to test the analytical skills of the user as a Database Administrator, using Oracle9i as the back-end tool. The test requires a thorough grasp of database concepts, familiarity with the GUI tools, scripts, views, and packages available in Oracle9i. This includes being able to identify the potential tuning areas with the help of the available tools and packages. The user should be able to analyze which tool or package should be used in which case. This requires a lot of practice with the tools and packages. The user should also be able to use PL/SQL-based queries for identifying potential bottlenecks in the performance of the system.

ONLINE is an option of the ALTER INDEX REBUILD statement, which is used to minimize locking contentions that may arise at the time of rebuilding an index. Locking contentions generally arise when several operations are being simultaneously performed with the index rebuilding operation, on a table corresponding to the index.

An active session pool is a feature that allows a user to control the maximum number of active sessions permissible within a resource consumer group. As more active sessions are created within a resource consumer group, the resource consumption increases accordingly. This may lead to a situation where a single resource consumer group is engaging the entire available resources, whereas other groups are starving. To avoid this situation, the active session pool queues the active sessions that are created beyond the permissible limit, without actually executing them. A queued active session is executed only after an existing active session ends. The active session pool follows the first-in-first-out queuing principle. There is only one active session pool for each resource consumer group.

Direct path loading is a method of data loading where the direct path application program interface (API) passes the data to be loaded to the load engine on the server. The load engine builds a column array from the data. It then uses the array structure to format data blocks and build index keys. The data blocks so formatted are written directly to the database. In direct path loading, data conversion occurs on the client side rather than on the server side. Direct path loading is generally faster than conventional loading because of the following reasons:

• Contention with other users is minimized because the processes perform their own I/O instead of using the buffer cache.
• Direct path loading employs multiblock asynchronous I/O for writing to the datafiles.
• Partial blocks are not employed by direct path loading. Hence, there is no overhead of finding them.

Direct path loading is generally used when the data to be loaded is quite large or the data is to be loaded in parallel for enhanced performance. Integrity constraints are employed in direct path loading, e.g., the NOT NULL constraint. Integrity constraints depending upon other rows or tables, e.g., the referential constraint, are disabled before the direct path loading and are re-enabled afterwards.