Database Tuning and Query Optimisation, Chapter 13
Quiz
The ____ is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.
sort cache
SQL cache
data cache
optimizer mode
The DBMS processes queries in three phases; the first is ____.
parsing
analyzing
execution
fetching
Generally, what will provide the best database client performance in terms of CPU speed?
single processor
multiple processors
the slowest processor
the fastest processor
Working with data in the data cache is many times faster than working with data in the data files because ____.
there is no network access involved
the DBMS doesn't have to wait for the hard disk to retrieve the data
the DBMS can optimize queries against the cache
the data cache contains only a few records from each table
When creating a database, in order to optimize performance, put high-usage tables in their own ____.
data caches
data spaces
table caches
table spaces
Database statistics are stored in the system catalog in specially designated ____.
indexes
rows
table spaces
tables
To optimize queries, use simple columns or literals as operands in a conditional expression and avoid the use of conditional expressions with ____ whenever possible.
the WHERE clause
functions
the GROUP BY clause
indexes
The architecture of a DBMS is represented by the ____ and structures used to manage a database.
tables
queries
processes
logs
The ____ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM.
table space
extends
data cache
SQL cache
Index ____ is a measure of how likely an index will be used in query processing.
statistics
connectivity
sparsity
selectivity
A(n) ____ is a logical grouping of several data files that store data with similar characteristics.
table space
extends
data cache
SQL cache
The majority of current database performance problems are related to ____.
not having enough memory on the database server
poorly written SQL code
improper use of table space
old statistics
To view the access plan for a given SQL statement, use the following command: SELECT * FROM TABLE(____).
DBMS_XPLAN.DISPLAY
PLAN_STAT
STATISTICS
ACCESS_PLAN
The ____ DBMS process schedules the concurrent execution of SQL requests.
scheduler
optimizer
listener
user
What is the goal of database performance?
to return accurate results
to prevent an inconsistent state
to execute queries as fast as possible
to replace other forms of data storage
In the SQL ____ phase of query processing, all I/O operations indicated in the access plan are executed.
parsing
execution
I/O
fetching
Database ____ refers to a set of activities and procedures designed to reduce the response time of the database system.
integrity checking
locking
query handling
performance tuning
A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as ____.
tables
extends
segments
logs
____ facilitate searching, sorting, and using aggregate functions and even join operations.
Indexes
Plans
Queries
Fetches
To see the access plan used by the DBMS to execute your query, use the ____ statement.