Oracle Interview Questions - 6

Describe the package DBMS_STATS.

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.

Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS to gather statistics in parallel

Describe the package DBMS_TRACE.

The DBMS_TRACE package contains the interface to trace PL/SQL functions, procedures, and exceptions.

DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.

What is the DCL language?

DCL is Data Control Language statements (GRANT, COMMIT)

What is the DDL language?

DDL (Data Definition Language) statements are used to define the database structure or schema (CREATE, ALTER, TRUNCATE)

What is a dedicated server?

A database server configuration in which a server process handles requests for a single user process.

What are dispatcher processes in Oracle?

Optional background processes, present only when a shared server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

What is distributed transaction?

A transaction that updates data on two or more networked computer systems

distributed transaction

What is the DML language?

DML (Data Manipulation Language) statements are used for managing data within schema objects. (INSERT, MERGE, EXPLAIN PLAN)

Describe the embedded SQL and Pro*C environment.

SQL statements embedded within a program and prepared before the program is executed.

It is a method of combining the computing power of a high-level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program. Oracle's embedded SQL environment is called Pro*C.

A Pro*C program is compiled in two steps. First, the Pro*C precompiler recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact. Then, a regular C/C++ compiler is used to compile the code and produces the executable.

All SQL statements need to start with EXEC SQL and end with a semicolon

What is ERP System?

Enterprise Resource Planning, a system that is used to manage all aspects of a company's operations.

ERP is a way to integrate the data and processes of an organization into one single system

Describe ETL process.

Extract, Transform, and Load (ETL) is a process in data warehousing that involves extracting data from outside sources, transforming it to fit business needs (which can include quality levels), and loading into the end target, i.e. the data warehouse.

ETL can in fact refer to a process that loads any database

ETL can also be used for the integration with legacy systems

What is EXPLANE PLAN? What information can you get using this statement?

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

-An ordering of the tables referenced by the statement

-An access method for each table mentioned in the statement

-A join method for tables affected by join operations in the statement

-Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

-Optimization, such as the cost and cardinality of each operation

-Partitioning, such as the set of accessed partitions

-Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

You can use the V$SQL_PLAN to display the execution plan of a SQL statement

Describe an external tables.

External tables feature is a complement to existing SQL*Loader functionality. It allows you to access data in external sources as if it were in a table in the database.

As of Oracle Database 10g, external tables can also be written to.

To unload data, you use the ORACLE_DATAPUMP access driver. The data stream that is unloaded is in a proprietary format and contains all the column data for every row being unloaded.

An unload operation also creates a metadata stream that describes the contents of the data stream. The information in the metadata stream is required for loading the data stream. Therefore, the metadata stream is written to the datafile and placed before the data stream.

What is a function-based indexes?

Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data.

For the optimizer to use function based indexes, the following session or system variables must be set:

QUERY_REWRITE_ENABLED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

Function based indexes are only visible to the Cost Based Optimizer

What is a global temporary table?

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicate if the data in the table is transaction-specific (the default) or session-specific.