Oracle Performance Tuning Trivia Quiz!

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.

Buffer hit ratio

Library cache hit ratio

Full-table scans

Buffer busy waits

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.

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

Removal of data redundancy

The introduction of data redundancy

The introduction of non-first normal form relations

The introduction of SQL*Plus

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

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

Logical design tuning

Physical design tuning

SGA tuning

SQL tuning

Independence of table relationships

High speed of SQL

Powerful GUI front-end

Easy to install and use

A part of SGA memory is used to store ASH data as rolling buffer.

All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.

All ASH data in memory are flushed to disk by MMON in every 3 seconds.

None of the above

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

It detects changes in SQL execution plans.

It produces results that can be used to create the SQL plan baseline.

It generates recommendations to run SQL Tuning Advisor to tune regressed SQL statements.

All of the above

Very high cache-hit ratio

Increasing number of free buffer waits

increasing number of buffer busy waits

Waits on latch: cache buffers chains event

For a database with lower recovery (MTTR) overhead

For a database in which the applications primarily perform read operations and predominantly sequential I/O is performed

For a database in which performance is the primary goal of the database because applications are demanding in terms of I/O operations

None of the above

Because the ROWID changes for these rows

Because these rows make existing indexes invalid

Because it is not possible to use indexes to retrieve these rows

Because retrieving these rows requires more than one data block to be accessed

Resource limit

memory statistics

Optimizer execution plans

All of the above

Frequent logfile switches are occurring.

Redo is generated faster than LGWR can write it out.

Frequent commits or rollbacks are taking place in the application.

Frequent incremental checkpoints are taking place in the database.

It is the sum of the CPU time of all nonidle sessions.

It is the sum of wait time and the CPU time of all nonidle sessions.

It is the time spent in processing the input/output (I/O) request by users.

It is the difference between the wait time and the CPU time of all nonidle sessions.

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

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

Above A and B

It generates only the statement execution statistics without executing the query.