Database Management Systems

Explain Plan and Trace are the correct first steps for SQL tuning. Explain Plan shows the execution plan and access paths. Trace provides detailed execution statistics. Parse and Bind are execution phases, not tuning tools - they describe how SQL is processed, not how to analyze or improve it.

Bind variables (parameters) should always be used in SQL statements instead of concatenating values. This prevents SQL injection attacks, improves query performance through cursor sharing, and enhances security by separating code from data.

To join N tables in SQL without causing a Cartesian product, you need a minimum of N-1 join conditions. For 3 tables, N-1 equals 2 join conditions. Fewer conditions result in an unwanted cross join, while more conditions are unnecessary for a basic join structure.

Modern Oracle optimizers are cost-based and generally choose the most efficient join methods based on statistics. Explicitly controlling join types (using hints) is rarely necessary and can degrade performance if not done carefully. The optimizer should be trusted unless there's a proven performance issue.

Updating only changed columns is better practice than updating all columns. It reduces undo/redo log generation, minimizes lock contention, improves performance, and makes auditing easier. Updating all columns unnecessarily consumes resources and can trigger cascading effects.

Always fetching all columns is bad practice. It increases network traffic, memory usage, and I/O. Use SELECT with specific columns needed for the query. This is called 'projection' and is fundamental to SQL performance optimization.

DB2 can be accessed from all three environments listed: TSO (Time Sharing Option) provides interactive access, IMS (Information Management System) allows transaction processing with DB2, and BATCH jobs can submit SQL queries to DB2. DB2 was specifically designed to support multiple access interfaces in mainframe environments.

ROLLBACK establishes a syncpoint (rollback point) when a transaction ends unsuccessfully, undoing all changes since the last commit. COMMIT establishes a syncpoint for successful transactions. ALLOCATE and DEALLOCATE are resource management commands for memory or cursors, unrelated to transaction syncpoints.

Stored procedures are precompiled collections of SQL statements and optional control-of-flow statements that are stored on the server. They provide a way to encapsulate business logic and database operations that execute server-side, reducing network traffic and improving security.

The statement is false because stored procedures ARE stored in system tables of the current database after compilation. When first executed, a stored procedure is compiled and its execution plan is cached in system tables (like syscomments in some systems) for reuse. This caching improves performance on subsequent executions.

When a tablespace has erroneous or corrupted datafiles, the TEMPORARY offline option is appropriate because it brings the tablespace offline without performing a checkpoint on the datafiles. NORMAL requires a checkpoint and clean datafiles, IMMEDIATE is used for emergency situations when NORMAL fails, and FOR RECOVER is used when the tablespace needs media recovery before being brought online.

Oracle provides several predefined roles to simplify privilege management. DBA grants all system privileges, RESOURCE allows users to create objects, CONNECT allows basic database connection, EXP_FULL_DATABASE grants full export privileges, and IMP_FULL_DATABASE grants full import privileges. All of these are valid predefined roles in Oracle.

In Oracle, the AUTOALLOCATE clause is only valid with EXTENT MANAGEMENT LOCAL. Specifying EXTENT MANAGEMENT DICTIONARY along with AUTOALLOCATE will result in a syntax error, so the statement fails and produces an error.

ADABAS allows multiple DDMs (Data Definition Modules) to be mapped to the same physical database, providing different logical views of the data. This is a standard feature for accessing the same database with different field selections and naming conventions. The statement is true.