SQL Tuning Tools

What areas of Oracle tuning have the most impact on system performance?

• Logical design tuning

• Physical design tuning

• SGA tuning

• SQL tuning

Answer explanation

This is because a good logical design will greatly minimize disk I/O, since less work is required to retrieve the desired information. What areas of Oracle tuning have the most impact on system performance? B is incorrect because physical tuning will only optimize tables that have already been designed. C is incorrect because SGA tuning has far less impact than design. D is incorrect because no amount of SQL tuning can compensate for a bad design.

You enabled tracing for a session by executing the following command: SQL>SET AUTOTRACE TRACEONLY; You issued a SQL query in the same session. Which two statements are true?

• a) Trace for the statement would be created without displaying the query output.

• b) It generates a report on the execution path used by the SQL optimizer and the statement execution statistics.

• c) Above a and b

• d) It generates only the statement execution statistics without executing the query.

In which case is the database buffer cache NOT used?

• When physical I/Os are performed to read from data files.

• When the database writer process writes to the SYSTEM tablespace

• When the server process writes sort data to the temporary tablespace

• When the database writer process writes undo data to the undo tablespace

What indicates that the rate of writing data blocks to the disk is very slow?

• Very high cache-hit ratio

• Increasing number of free buffer waits

• Increasing number of buffer busy waits

• Waits on latch: cache buffers chains event

What best describes the relationship between indexes and SQL performance?

• Indexes are only used in special cases

• Indexes are used to make table storage more efficient

• Indexes rarely make a difference in SQL performance

• Indexes exist solely to improve query speed

Answer explanation

The sole purpose of indexes in any relational database management system is to reduce the access time for SQL statements. The SQL optimizer can detect when and indexes present, and use sophisticated algorithms in order to determine the fastest way of accessing the data. If indexes did not exist in a relational database the Oracle database SQL optimizer would have no choice except to read every single row in the table.

Which of the following is not an important Oracle tuning metric?

• Buffer hit ratio

• Library cache hit ratio

• Full-table scans

• Buffer busy waits

Answer explanation

This is because some applications read most of a table, in which case a full-table scan is the fastest way to access the table. Which of the following is not an important Oracle tuning metric? A, B, and C are all important indicators of how well optimized the performance is for the database.

Why is searching for large-table full-table scans critical to SQL tuning?

• They indicate an optimized execution plan.

• They may be able to be tuned to use an index

• The full-table scan should be normalized from the database design

• A full-table scan is always sub-optimal.

Which of the following tuning techniques will NOT reduce the disk I/O for an Oracle query?

• Add blocks to the SGA buffer pool

• Created a clustered table

• Create tables with redundant columns from other tables

• Run parallel query against the table

Answer explanation

This is because a parallel query will not decrease the amount of disk I/O, but performance will improve because there are multiple processes reading the database blocks. Which of the following tuning techniques will NOT reduce the disk I/O for an Oracle query? A, B, and C are incorrect because their purpose is to reduce disk I/O for a query.

All else being equal, which of the following tuning tasks is least likely to provide an improvement in performance?

• De-normalization of the logical tables

• Eliminating full-table scans from SQL queries

• Moving Oracle data files from hot disks onto cool disks

• Adding additional storage to the SGA shared pool

Answer explanation

The correct answer is D, adding additional storage to the shared pool. This is because the shared pool size will only help to speed-up loads of PL/SQL packages and SQL statements. Which of the following tuning tasks is least likely to provide an improvement in performance? A is incorrect because the de-normalization of logical tables can be a huge performance booster. B is incorrect because unnecessary full-table scans are often the cause of poor performance. C is incorrect because disk load balancing can make a huge positive impact on performance.

Which of the following is an important consideration when tuning an SQL statement?

• The number of CPUs on the server

• The degree of parallelism on the tables

• The quality of the SQL optimization

• The use of bitmap indexes