Oracle Certification
61 practice questions with correct answers and detailed explanations. Use this guide to review concepts before taking the practice exam.
The Oracle Performance Tuning (1Z0-149) certification validates professional expertise in Oracle technologies. This study guide covers all 61 practice questions from our 1Z0-149 practice test, complete with correct answers and explanations to help you understand each concept thoroughly.
Review each question and explanation below, then test yourself with the full interactive practice exam to measure your readiness.
Which Oracle initialization parameter controls the number of database writer processes?
DB_WRITER_PROCESSES directly controls how many database writer (DBWn) processes are created, which improves write performance on multi-CPU systems.
What is the primary purpose of the Automatic Workload Repository (AWR)?
AWR captures and stores historical performance metrics at regular intervals, enabling DBAs to analyze performance trends and create performance baselines for comparison.
When examining an execution plan, which operation typically indicates a performance bottleneck if executed with high row counts?
A FULL TABLE SCAN accessing millions of rows with high cost suggests the query is not using indexes effectively and may benefit from index creation or query rewriting.
Which wait event indicates that sessions are waiting for log buffer space to be flushed to disk?
The 'log buffer space' wait event occurs when the log buffer is full and sessions must wait for LGWR to write buffer contents to the redo log file.
What is the effect of increasing the LOG_BUFFER parameter on a system with high 'log buffer space' waits?
Increasing LOG_BUFFER provides more space for redo entries before LGWR must flush to disk, reducing 'log buffer space' waits on systems with intensive logging.
In the context of SQL performance tuning, what does the term 'cardinality' refer to?
Cardinality is the row count estimate used by the optimizer to make execution plan decisions; poor cardinality estimates lead to suboptimal plans.
Which dynamic performance view displays current wait events for active sessions?
V$SESSION_WAIT shows the current wait event for each active session, allowing real-time identification of performance bottlenecks.
What is the primary advantage of using partitioning for large tables in Oracle?
Partitioning enables queries to scan only relevant partitions rather than the entire table, improving performance through partition pruning and enabling parallel operations.
Which parameter controls the maximum size of the System Global Area (SGA)?
SGA_MAX_SIZE sets the upper limit for the entire SGA; individual components can be sized up to this limit without restarting the database.
What does the 'db file sequential read' wait event indicate?
This wait event occurs when sessions perform index-based lookups that require reading specific blocks sequentially, typical of indexed access patterns.
How does the Cost-Based Optimizer (CBO) estimate query cost?
The CBO uses table and index statistics (including selectivity, cardinality, and I/O characteristics) to calculate the cost of different execution paths.
Which Oracle feature allows automatic tuning of memory parameters without manual intervention?
ASMM automatically distributes available SGA memory among different pools (buffer cache, shared pool, large pool, etc.) based on workload demands.
What is the purpose of analyzing a table with the DBMS_STATS package?
DBMS_STATS collects statistics on tables, indexes, and columns, providing the optimizer with accurate information about data distribution and characteristics.
Which initialization parameter determines how Oracle allocates memory when MEMORY_TARGET is set?
When MEMORY_TARGET is specified, Oracle's Automatic Memory Management distributes memory dynamically between SGA and PGA based on workload demands.
What does an execution plan with a high 'Cost' value in the Rows column typically suggest?
The 'Rows' column in an execution plan shows the optimizer's estimated row count for that operation; high values indicate substantial data processing.
Which wait event typically dominates on I/O-bound database systems?
These two wait events represent disk I/O operations and are commonly the top waits on systems where I/O is the limiting resource.
When using the SQL Tuning Advisor, what is the primary output generated?
The SQL Tuning Advisor analyzes problem SQL statements and provides actionable recommendations such as creating indexes or rewriting SQL for better performance.
What does the EXPLAIN PLAN command do, and what view must be queried to see results?
EXPLAIN PLAN parses and plans a query without execution, writing the plan to PLAN_TABLE which can then be queried to view the execution path.
Which type of index is most suitable for columns with many NULL values and low cardinality?
Bitmap indexes are highly efficient for low-cardinality columns including NULLs, using minimal space and providing fast retrieval.
What is the relationship between the buffer cache hit ratio and overall database performance?
A higher buffer cache hit ratio means more data is found in memory, reducing expensive disk I/O operations and improving response time.
Which dynamic performance view provides cumulative statistics for individual SQL statements since database startup?
V$SQLAREA aggregates statistics for each unique SQL statement, showing cumulative CPU time, I/O operations, and execution counts.
How does setting a high value for PROCESSES parameter impact system performance?
Setting PROCESSES too high wastes memory on unused process structures and increases context switching overhead, degrading rather than improving performance.
What is the primary benefit of implementing a materialized view in an Oracle database?
A materialized view stores the results of a complex query, allowing subsequent queries to retrieve pre-aggregated data without recalculating joins and aggregations.
Which initialization parameter controls the degree to which Oracle can parallelize query execution?
PARALLEL_DEGREE_POLICY determines whether parallel execution is enabled and how the degree of parallelism is calculated for statements.
In a NESTED LOOPS join, what performance issue may occur when the inner table is large and lacks appropriate indexes?
Without an index on the inner table's join column, each outer row requires a full table scan of the inner table, resulting in O(n*m) complexity and poor performance.
Which Oracle initialization parameter controls the size of the shared pool and directly affects library cache performance?
SHARED_POOL_SIZE directly controls the memory allocated for the shared pool, which contains the library cache, dictionary cache, and other structures critical for parsing and execution.
What is the primary purpose of the Automatic Workload Repository (AWR) in Oracle database performance tuning?
AWR captures performance data at regular intervals, allowing DBAs to analyze historical performance trends, identify bottlenecks, and generate tuning recommendations.
When analyzing wait events in Oracle, which wait event typically indicates contention on the redo log buffer or log file I/O issues?
The 'log file sync' wait event occurs when a foreground process waits for the background process (LGWR) to flush redo log entries to disk, indicating redo-related contention or I/O delays.
Which metric in the Dynamic Performance View v$session_wait is most useful for identifying which specific resource a session is waiting for?
The P1, P2, and P3 columns contain event-specific identifiers (like file number, block number for I/O waits) that pinpoint the exact resource being waited on.
In Oracle, what is the relationship between the PCTUSED and PCTFREE parameters in table storage, and how do they affect performance?
PCTFREE reserves space for row growth to reduce migration/chaining, while PCTUSED determines when a block becomes available for inserts again, directly affecting insert performance and space utilization.
Which type of index is most beneficial when a query needs to access data in a specific sorted order without performing a separate sort operation?
A B-tree index on the ORDER BY columns allows Oracle to retrieve data pre-sorted, eliminating the need for an explicit SORT operation and improving performance.
What does a high value in the metric 'parse time elapsed' in v$sql indicate about SQL execution performance?
High parse time elapsed indicates excessive time spent in syntax checking, semantic analysis, and optimization, often due to library cache pressure or missing bind variables.
When using the SQL Tuning Advisor, which statement best describes the tuning recommendations it can provide?
The SQL Tuning Advisor analyzes execution plans and can recommend statistics updates, SQL profiles, index creation/modification, and other structural improvements.
In the context of Oracle performance tuning, what is the significance of monitoring the 'cursor: pin S' wait event?
The 'cursor: pin S' wait event signals contention when multiple sessions need shared access to the same cursor, often caused by excessive hard parsing or library cache misses.
Which initialization parameter controls the maximum number of parallel execution processes available to the database, and how does it affect performance?
PARALLEL_MAX_SERVERS limits the total number of parallel execution processes available; setting it too high wastes resources while too low limits parallelism benefits.
When a query shows high 'cell single block physical read' wait events in Exadata environments, what does this typically indicate?
High single block physical reads on Exadata suggest queries are not leveraging intelligent storage capabilities (smart scans), indicating suboptimal predicate pushdown or filtering.
Which dynamic performance view is most appropriate for monitoring real-time CPU consumption and identifying sessions consuming excessive CPU resources?
v$sess_io provides session-level I/O statistics, and when joined with v$session, allows identification of CPU-consuming sessions and their CPU-consuming activities.
In Oracle, what is the optimal strategy for tuning a query with a join between a small dimension table and a large fact table?
Hash joins are optimal when joining small tables with large tables, as the smaller table is built into a hash table in memory, allowing efficient matching with the larger table.
What does the 'blocks cleaned out' metric in table statistics indicate, and how does it relate to performance?
High blocks cleaned out values suggest delayed block cleanup is occurring, potentially due to suboptimal PCTUSED/PCTFREE settings, which can increase I/O and reduce performance.
When using AWR reports to identify performance issues, which metric indicates potential problems with the data buffer cache?
A low Buffer Cache Hit Ratio (typically <99%) combined with high physical read wait times indicates the cache is not effectively storing frequently accessed blocks.
Which Oracle feature allows a query to be executed using multiple CPU cores and processes simultaneously, and what impact does it have on resource usage?
Parallel execution divides work across multiple processes/cores, significantly improving throughput for large scans and operations but requiring careful tuning to avoid resource oversubscription.
In the context of Oracle's cost-based optimizer, what does a high value in the COST column of execution plans suggest?
The COST metric represents the optimizer's estimate of resource consumption; high costs typically indicate inefficient access paths that should be tuned (indexes added, statistics updated, etc.).
Which strategy is most effective for reducing the impact of 'enqueue: TX - row lock contention' wait events in an OLTP environment?
Row lock contention is best addressed through application-level tuning: shorter transactions, faster processing, earlier commits, and proper isolation levels reduce lock hold times.
When analyzing v$sqlarea for SQL tuning opportunities, which metric is most indicative of a query that might benefit from optimization?
Queries with many executions but high disk reads indicate either missing indexes or poor selectivity; high PARSE_CALLS relative to EXECUTIONS suggests missing bind variables.
Which Oracle initialization parameter controls the automatic collection of optimizer statistics, and what are the implications of disabling it?
When automatic statistics collection is disabled, tables may retain outdated statistics, leading to poor optimizer decisions and degraded query performance.
In a data warehouse environment, what is the primary advantage of using bitmap indexes over B-tree indexes for low-cardinality columns?
Bitmap indexes use bit vectors for each distinct value, resulting in compact storage for low-cardinality columns and enabling fast boolean operations in analytical queries.
When using SQL Plan Management (SPM) to control query execution, which scenario represents the most appropriate use case?
SQL Plan Baselines are ideal for protecting proven efficient plans from optimizer regressions caused by environment changes, while still allowing the optimizer to evolve plans when beneficial.
Which dynamic performance view should be queried to identify tablespaces that are approaching storage capacity and could cause performance degradation?
Joining v$tablespace with dba_data_files provides space usage information, allowing proactive identification of tablespaces nearing capacity before they become full.
In tuning I/O performance, what does a high 'direct path read' wait event typically indicate about the query execution strategy?
Direct path reads bypass the buffer cache, which is optimal for very large scans that would flush useful data from cache; however, high frequencies suggest buffer cache underutilization.
Which approach is most effective for tuning a report that performs a large aggregation across a fact table with millions of rows?
Materialized views pre-compute aggregations and can be incrementally refreshed, providing orders of magnitude performance improvement for repetitive aggregation queries.
When monitoring the library cache using v$librarycache, what does a high RELOAD value relative to PINS indicate about cache efficiency?
High RELOAD/PINS ratios indicate objects are frequently evicted and reloaded from the library cache, suggesting either insufficient shared pool size or excessive invalidations due to DDL.
When using the DBMS_PROFILER package to analyze PL/SQL performance, what information does the PLSQL_PROFILER_DATA table primarily store?
DBMS_PROFILER records profiling data including execution counts and elapsed time for each line of PL/SQL code, allowing developers to identify performance bottlenecks at the line level.
Which initialization parameter controls the size of the buffer cache in an Oracle database, directly affecting how much data can be cached in memory?
DB_BUFFER_CACHE specifies the size of the buffer cache, which is critical for caching table and index blocks; a larger cache reduces disk I/O and improves performance.
You observe that a SQL query is using a full table scan instead of an index. After analyzing the explain plan, you notice the optimizer chose this path due to a high cardinality estimate. What should you investigate first to resolve this issue?
Stale or missing statistics cause the optimizer to make incorrect cardinality estimates; gathering fresh statistics with DBMS_STATS is the proper first step before considering hints or parameter adjustments.
In the context of cursor management, what is the primary benefit of using bind variables in SQL statements?
Bind variables enable cursor sharing because the same SQL statement with different parameter values reuses the same execution plan, significantly reducing soft parse overhead and improving performance.
Which wait event indicates that a session is waiting for a data block that is currently being read from disk?
The 'db file sequential read' wait event occurs when a session waits for a single block to be read from disk, typically during index range scans or full table scans.
A developer reports that after adding a new index on a frequently queried table, the overall database performance has degraded. What is the most likely cause?
Adding an index increases the overhead of INSERT, UPDATE, and DELETE operations because the index must be maintained; if the index is not used efficiently by queries, the maintenance cost outweighs the benefit.
When analyzing V$SQLAREA to identify performance issues, which metric best indicates that a SQL statement is causing excessive CPU consumption?
High CPU_TIME with high EXECUTIONS indicates a statement that consumes significant CPU resources; when combined, these metrics identify CPU-intensive queries that would benefit from optimization.
You need to reduce the response time of a query that performs multiple joins on large tables. The query currently has a cost of 50,000 according to the explain plan. Which approach is MOST likely to provide significant improvement?
Optimizing join order, gathering statistics, and creating strategic indexes on join and filter columns address the root causes of high query cost and provide the most sustainable performance improvements.
A batch job that runs nightly is experiencing increased runtime. The job consists of multiple SQL statements that process large volumes of data. Which tool would be most effective for identifying which specific statements are consuming the most time?
SQL Performance Analyzer allows you to baseline the performance of all statements in the batch job, compare execution plans and times, and identify which statements have degraded or are consuming the most resources.
In a system experiencing poor performance, you find that the redo log buffer (LOG_BUFFER) is small and LOG_BUFFER waits are high. However, after increasing LOG_BUFFER significantly, performance does not improve. What should you investigate next?
If increasing LOG_BUFFER does not improve performance, the bottleneck is the log writer's ability to flush redo to disk, not buffer capacity; disk I/O performance and LGWR process efficiency become the focus.
You've reviewed all 61 questions. Take the interactive practice exam to simulate the real test environment.
▶ Start Practice Exam — Free