Oracle Database Administration and Performance Tuning

The STATISTICS_LEVEL parameter controls statistics collection behavior. Setting it to BASIC disables AWR statistics collection and ADDM advisory services entirely. TYPICAL is the default setting and enables normal collection, while ALL enables additional statistics. OFF is not a valid value for this parameter - the question tests knowledge that BASIC disables these features.

When a SQL statement causes I/O issues, both SQL Tuning Advisor and SQL Access Advisor are relevant. SQL Tuning Advisor optimizes the SQL statement itself (indexes, statistics, SQL structure), while SQL Access Advisor focuses on access paths and materialized views. Using both provides comprehensive troubleshooting for I/O problems caused by SQL statements.

When submitting advisor analysis jobs, the 'Limited' scope adds the least overhead because it performs minimal analysis. 'Restricted' is not a valid job scope option in this context. 'Comprehensive' and 'Thorough' (though 'Thorough' is not standard Oracle terminology) would involve deeper analysis with more system overhead. Limited scope is the lightweight option for minimal system impact.

DBA_ADVISOR_RATIONALE is the view that contains the reasoning and justification behind ADDM recommendations. It explains WHY specific recommendations were made. DBA_ADVISOR_FINDINGS shows what problems were discovered, DBA_ADVISOR_OBJECTS lists affected objects, and DBA_ADVISOR_RECOMMENDATIONS contains the suggested solutions - but RATIONALE provides the explanatory reasoning.

The Memory Advisor in Oracle determines if allocated memory for Shared Pool, Large Pool, and Buffer Cache is adequate. It analyzes memory usage patterns and provides recommendations for SGA component sizing. SQL Tuning Advisor focuses on SQL optimization, not memory allocation. SGA Tuning Advisor and Pool Advisor are not standard Oracle advisor names.

The MTTR (Mean Time To Recover) Advisor evaluates if the estimated instance recovery duration meets service-level agreement requirements. It analyzes Redo log file sizing and configuration to optimize recovery time. Undo Management Advisor focuses on undo tablespace management, while SQL Access and Tuning Advisors deal with SQL optimization - not recovery time estimation.

The SQL Tuning Advisor is the appropriate first tool for analyzing and improving performance of a specific SQL statement showing high disk I/O. The SQL Access Advisor is for overall schema design (indexes/materialized views), not individual statement tuning. The main screen and alert log are for monitoring, not targeted SQL optimization.

The Performance screen in EM Database Control is the primary location to investigate sudden performance degradation, as it provides detailed metrics on wait events, throughput, and resource utilization. The Administration and Maintenance screens are for configuration and maintenance tasks, not performance diagnostics. The main screen only shows high-level alerts.

By default, AWR (Automatic Workload Repository) retains database statistics for 7 days. This is the standard retention period for automatic workload repository snapshots. 30 days is not the default, and 7 hours is too short. Statistics are not retained indefinitely by default.

The three primary sources of performance metrics in the EM Performance screen are Host (operating system metrics), Session (user session activity), and Instance (database instance statistics). Network metrics are not one of the three primary sources - they may be available but are not fundamental to the Performance screen's three-tier view.

Throughput is defined as the amount of work a system can perform in a given amount of time (transactions per second, queries per minute, etc.). Response time measures latency, uptime is availability, and runtime is execution duration - none of these define 'work per unit time'.

Only databases created using the Database Configuration Assistant (DBCA) automatically have the statistics collection job configured. When you create databases manually with scripts, the automatic statistics collection procedure is not set up by default. This is a documented behavior, not a parameter or login issue.

EM Database Control provides a recompile option for invalid objects directly in its interface. Additionally, you can use ALTER FUNCTION ... COMPILE and ALTER VIEW ... COMPILE commands to manually recompile specific PL/SQL objects. Shutting down the database won't fix invalid objects, and export/import is unnecessarily complex for simple recompilation.

Oracle Automatic Workload Repository (AWR) allows multiple baselines to compare performance metrics across different operational periods, such as comparing high-activity daytime workloads against low-activity off-hours or batch-processing windows.

Oracle Database Control supports both warning and critical thresholds for alert configuration. When monitoring tablespace free space, you can set a warning threshold to alert when free space drops to a concerning level, and a critical threshold for more urgent alerts when space becomes critically low.

When no email address is configured, EM Database Control displays alert information on its main console screen. The alerts remain visible in the EM interface for administrators to review. Alerts are not stored in data dictionary views like DBA_ALERTS or dynamic performance views like V$ALERTS when no email is specified.

In SQL, the correct syntax for renaming a table is 'ALTER TABLE product RENAME TO pro' (or 'RENAME TO PRO' in some databases). The simple 'RENAME product TO PRO' statement is not valid SQL syntax. Therefore, statement (a) is false and statement (b) is true.

This is valid Oracle SQL syntax for INSERT ALL statement, which allows multiple insert operations in a single statement. The statement would insert two rows into mytab table. However, the statement shown is incomplete (missing SELECT or semicolon in some contexts), but the core syntax shown is valid Oracle multi-table insert.