Performance Tuning & Optimization Flashcards

Which of the following is a common method for tuning SQL queries in Oracle?

Answer: Indexing and optimizing joins

A common and highly effective method for tuning SQL queries in Oracle is to strategically use indexes on frequently queried columns, especially those in `WHERE` clauses or `JOIN` conditions. Additionally, optimizing join orders and types can significantly reduce the amount of data processed and improve query execution speed. These techniques help the optimizer find the most efficient data access paths.

What is the main purpose of Oracle's Automatic Workload Repository (AWR)?

Answer: To gather performance data for tuning

Oracle's Automatic Workload Repository (AWR) is a built-in repository that collects, processes, and maintains performance statistics for problem detection and self-tuning. It automatically gathers crucial performance data at regular intervals, providing a historical perspective for analyzing database performance, identifying bottlenecks, and making informed tuning decisions.

How can Oracle SQL Developer help with performance tuning?

Answer: By identifying and analyzing slow-running queries

Oracle SQL Developer provides various tools and features that assist with performance tuning, such as the SQL Worksheet, Explain Plan, and SQL Tuning Advisor integration. These features allow developers and DBAs to identify slow-running queries, analyze their execution plans, and receive recommendations for optimization. This helps pinpoint and resolve performance bottlenecks effectively.

What is the Oracle instance parameter 'OPTIMIZER_MODE'?

Answer: It influences the query optimization strategy

The Oracle instance parameter `OPTIMIZER_MODE` dictates the goal of the query optimizer when generating execution plans. Common modes include `ALL_ROWS` (for best throughput), `FIRST_ROWS_N` (for best response time for the first N rows), and `CHOOSE` (default, lets the optimizer decide). This parameter directly influences how queries are executed, impacting overall performance.

What is an Oracle database index used for?

Answer: To speed up query performance

An Oracle database index is a schema object that helps speed up data retrieval operations by providing quick access paths to rows in a table. Similar to an index in a book, it allows the database to locate specific data without scanning the entire table. This significantly improves query performance for `SELECT` statements, especially on large tables.

Which of the following is a way to improve Oracle database performance by reducing disk I/O?

Answer: Using large memory caches and indexes

Reducing disk I/O is crucial for improving database performance, and this can be effectively achieved by using large memory caches (like the buffer cache) to store frequently accessed data in RAM. Additionally, well-designed indexes allow the database to retrieve data more efficiently, often avoiding full table scans that require extensive disk I/O. These measures keep data in memory, minimizing slow disk access.

What is the effect of using parallel queries in Oracle?

Answer: It speeds up query processing by using multiple CPUs

Parallel queries in Oracle enable the database to divide a single query's workload among multiple processes or threads, which can then execute simultaneously on different CPUs or cores. This parallel execution significantly speeds up the processing of large, complex queries. It is particularly beneficial in data warehousing environments where large data sets are frequently analyzed.

What does the Oracle parameter 'SORT_AREA_SIZE' control?

Answer: The memory allocated for sorting operations

The Oracle parameter `SORT_AREA_SIZE` specifies the maximum amount of memory (in bytes) that can be used by a single user process for sorting operations. Allocating sufficient memory for sorting reduces the need for disk-based sorts, which are much slower. This directly improves the performance of queries involving `ORDER BY`, `GROUP BY`, or `DISTINCT` clauses by performing sorts in memory.

What does 'EXPLAIN PLAN' in Oracle do?

Answer: It shows the query's execution plan

'EXPLAIN PLAN' is a SQL command in Oracle (and similar databases) that provides insight into how the database optimizer intends to execute a given SQL statement. It details the steps, access methods, and join orders the database will use, which is crucial for identifying performance bottlenecks and optimizing query execution.