Self Test #4: Performance Tuning Workshop (Chapters 16-20)

Welcome to the Interactive Self-Study Module. This page contains all of the review questions from the end of each chapter. Read the question, then click on the appropriate answer. A correct answer will link you to the next question.

If you need further review of the topic, click on the Review Button.jpg (3900 bytes) icon to link to the appropriate section in the electronic book.

 

 

Chapter 16 Self Test

 
1. The DBA is about to begin performance tuning. Which utility script can be run by the DBA in order to begin tracking performance statistics on the database instance?
Review Button.jpg (3900 bytes)
A. UTLSTAT  
B. UTLBSTAT  
C. UTLMONTR  
D. UTLLOCKT  
2. Which of the following is not part of REPORT.TXT?
Review Button.jpg (3900 bytes)
A. Redo log and rollback segment entries  
B. Database instance initialization parameters  
C. Dirty buffer write queue statistics  
D. Statistics collection start and stop times  
3. What area of the database should the DBA tune before tuning memory structures?
Review Button.jpg (3900 bytes)
A. Disk I/O  
B. Contention  
C. SQL statements  
D. Latches and Locks  
E. Dispatchers and shared servers  
4. Output for the EXPLAIN PLAN command is stored in
Review Button.jpg (3900 bytes)
A. PLAN_TABLE  
B. report.txt  
C. TRACE files  
D. init.ora  
E. Nowhere  
5. The DBA is preparing to analyze database performance statistics using UTLBSTAT and UTLESTAT. In order to increase the likelihood that UTLBSTAT will capture meaningful statistics,
Review Button.jpg (3900 bytes)
A. The instance name should be fewer than eight characters.  
B. The instance should be running for several hours before starting UTLBSTAT.  
C. The shared pool should be flushed.  
D. The SYSTEM tablespace should be reorganized to reduce fragmentation.  
6. The most efficient and effective way to find out the database instance parameters is to
Review Button.jpg (3900 bytes)
A. Run UTLBSTAT/UTLESTAT  
B. Read the init.ora file  
C. Execute the show parameter command  
D. Read report.txt  
7. Which of the following is NOT a tool used for diagnosing tuning problems on the Oracle instance?
Review Button.jpg (3900 bytes)
A. Server Manager  
B. V$ performance views  
C. SQL*Loader  
D. TKPROF  
E. DBMS_APPLICATION_INFO  
8. Which two views are used to track latch performance statistics?
Review Button.jpg (3900 bytes)
A. V$LATCH  
B. V$LATCHWAIT  
C. V$LATCHNAME  
D. V$LATCHHOLDER  
E. V$LATCHLOG  
9. SQL operations listed as output from EXPLAIN PLAN
Review Button.jpg (3900 bytes)
A. Are executed from top to bottom, from outside in  
B. Are executed from bottom to top, from outside in  
C. Are executed from top to bottom, from inside out  
D. Are executed from bottom to top, from inside out  
10. Dynamic performance views in the Oracle instance are owned by
Review Button.jpg (3900 bytes)
A. SYSTEM  
B. SYSDBA  
C. OSDBA  
D. SYS  

 

Chapter 17 Self Test

 
1. Table EMP has one index, on EMPID. Which SQL statement will yield the worst performance?
Review Button.jpg (3900 bytes)
A. select EMPID from EMP where EMPID = '604';  
B. select * from EMP where nvl(EMPID,0) = '604'  
C. select EMPID from EMP WHERE ROWID = '021D1D1A.001.0002';  
D. select * from EMP where EMPID = '604';  
2. Modules of code registered with the Oracle database have their performance statistics tracked in
Review Button.jpg (3900 bytes)
A. V$SYSSTAT  
B. V$SESSION  
C. V$PROCESS  
D. V$PACKAGE  
3. What area of the database should the DBA tune before tuning memory structures?
Review Button.jpg (3900 bytes)
A. Disk I/O  
B. Contention  
C. SQL statements  
D. Latches and locks  
E. Dispatchers and shared servers  
4. Two of the primary performance goals in OLTP systems are
Review Button.jpg (3900 bytes)
A. Fast report execution  
B. Fast update capability  
C. Fast insert capability  
D. Fast ad hoc queries  
E. Fast online access to data  
5. In order to improve performance on SQL statements, what operation should be avoided?
Review Button.jpg (3900 bytes)
A. Index range scan  
B. Full table scan  
C. Index unique scan  
D. Table access by ROWID  
6. One reason for poor performance on a SELECT statement using an index may be
Review Button.jpg (3900 bytes)
A. The query causes a TABLE ACCESS FULL to be performed  
B. The query uses an INDEX SCAN  
C. The index has only one extent  
D. The index has low cardinality  
7. Which of the following features are generally NOT found in decision support systems?
Review Button.jpg (3900 bytes)
A. Frequent updates made by users  
B. Frequent reports generated by users  
C. Use of indexes  
D. Use of clusters  
E. Low data volatility  
8. Which of the following options makes the task of reconfiguring Oracle easier?
Review Button.jpg (3900 bytes)
A. Many change processes done manually  
B. Use of multiple init.ora files  
C. The parallel query option  
D. Usage of SQL Trace and TKPROF to track statistics.  
E. Multiple tablespaces to store index data.  
9. TKPROF accepts as input
Review Button.jpg (3900 bytes)
A. The output from UTLESTAT  
B. The output from SELECT * FROM DBA_IND_COLUMNS;  
C. The output from SQL Trace  
D. The output from EXPORT  
10. Usage of an index on a heavily updated table in an OLTP system
Review Button.jpg (3900 bytes)
A. Decreases performance for reports and therefore shouldn't be used  
B. Decreases performance of online viewing and therefore shouldn't be used  
C. Decreases performance of ad hoc queries and therefore shouldn't be used  
D. Decreases performance of database updates and therefore shouldn't be used  
11. The process in an Oracle client/server environment that accepts user connections to the database and routes them to a dispatcher is a
Review Button.jpg (3900 bytes)
A. Router  
B. Listener  
C. Receiver  
D. Shared server  
E. Connector  
12. Which procedure is not part of the DBMS_APPLICATION_INFO package?
Review Button.jpg (3900 bytes)
A. read_action( )  
B. set_client_info( )  
C. read_client_info( )  
D. set_module( )  
13. The DBA runs SQL Trace, but the output file contains only about half of the information from the session. What should the DBA do to correct the problem?
Review Button.jpg (3900 bytes)
A. Increase the value of TIMED_STATISTICS  
B. Increase the value of MAX_DUMP_FILE_SIZE  
C. Run UTLBSTAT/UTLESTAT again  
D. Flush the shared pool  

 

Chapter 18 Self Test

 
1. The database is experiencing problems with performance. Decreasing the value set for the LOG_SMALL_ENTRY_MAX_SIZE
Review Button.jpg (3900 bytes)
A. Slows performance for writing rollback segment log entries  
B. Slows performance for writing redo log entries  
C. Speeds performance for writing rollback segment log entries  
D. Speeds performance for writing redo log entries  
2. The maximum number of redo allocation latches for the Oracle instance is
Review Button.jpg (3900 bytes)
A. 0  
B. 1  
C. 10  
D. Twice the number of CPUs on the machine hosting the Oracle instance  
3. The DBA is about to begin performance tuning. Which of the following items is not part of the Oracle SGA?
Review Button.jpg (3900 bytes)
A. ROWID cache  
B. Library cache  
C. Row cache  
D. Buffer cache  
4. The shared pool contains which of the following structures? (Choose two)
Review Button.jpg (3900 bytes)
A. Dictionary cache  
B. Shared SQL areas  
C. Dirty buffers  
D. Redo entries  
5. Library cache hit information is calculated from statistics found in which view?
Review Button.jpg (3900 bytes)
A. V$LIBRARY  
B. V$ROWCACHE  
C. V$SYSSTAT  
D. V$LIBRARYCACHE  
6. What is the name of the procedure used to pin objects in the shared pool?
Review Button.jpg (3900 bytes)
A. DBMS_PACKAGE.keep( )  
B. DBMS_SHARED_POOL.keep( )  
C. DBMS_PACKAGE.pin( )  
D. DBMS_SHARED_POOL.pin( )  
7. The dynamic performance view X$KCBCBH is commonly used for
Review Button.jpg (3900 bytes)
A. Decreasing the number of hits on the redo log buffer  
B. Increasing the number of hits on the buffer cache  
C. Decreasing the number of hits on the library cache  
D. Decreasing the number of hits on the buffer cache  
8. To decrease wait time processes experience when writing redo log entries, the DBA should increase
Review Button.jpg (3900 bytes)
A. The LOG_SMALL_ENTRY_MAX_SIZE parameter  
B. The SORT_WRITE_BUFFERS parameter  
C. The LOG_BUFFERS parameter  
D. The number of redo allocation latches  
E. The DB_BLOCK_SIZE parameter  
9. What is the name of the dynamic performance view that tracks dictionary cache performance?
Review Button.jpg (3900 bytes)
A. V$ROWCACHE  
B. V$DICTCACHE  
C. V$DICTIONARY  
D. V$DATACACHE  
10. To increase the time Oracle keeps data blocks in the buffer cache from FULL TABLE SCANS on the EMP table,
Review Button.jpg (3900 bytes)
A. Run full table scans before running other queries  
B. Join data from tables accessed via full table scan with data from indexed tables  
C. Increase the DB_BLOCK_BUFFERS parameter  
D. Execute the statement alter table EMP cache;  
11. Which of the following steps will not reduce the number of checkpoints on the system?
Review Button.jpg (3900 bytes)
A. Set LOG_CHECKPOINT_INTERVAL to value larger than redo log file  
B. Set LOG_CHECKPOINT_TIMEOUT to 0  
C. Set LOG_MAX_CHECKPOINTS to the number of CPUs available to Oracle  
D. Increase the size of redo log files  
12. What initialization parameter must be set in order to use the X$KCBCBH dynamic performance view?
Review Button.jpg (3900 bytes)
A. ENABLE_KCBCBH  
B. DB_BLOCK_LRU_STATISTICS  
C. DB_BLOCK_BUFFERS  
D. LOG_BUFFER  
13. DBWR write dirty buffers to disk when
Review Button.jpg (3900 bytes)
A. Told to do so by ARCH  
B. When a time-out occurs  
C. When LGWR writes redo entries to disk  
D. Never, dirty buffers are written to disk by the server process  
14. Which of the following describe events that occur as the result of a checkpoint? (Choose two)
Review Button.jpg (3900 bytes)
A. Redo information is written to disk.  
B. Dirty buffers are written to disk.  
C. Redo log sequence numbers are written to the control file.  
D. The server process reads data into the buffer cache.  

 

Chapter 19 Self Test

 
1. A high PCTFREE
Review Button.jpg (3900 bytes)
A. Keeps the data blocks filled to capacity with table or index data  
B. Works well for both OLTP and decision support systems  
C. Maximizes performance on the database buffer cache  
D. Reduces the possibility of row chaining and data migration  
2. What is the maximum number of rollback segments recommended for an instance?
Review Button.jpg (3900 bytes)
A. 200  
B. 100  
C. 75  
D. 50  
3. Freelist contention can be reduced by
Review Button.jpg (3900 bytes)
A. Increasing the size of the buffer cache  
B. Increasing the size of the redo log  
C. Increasing the value the FREELIST initialization parameter  
D. Dropping and re-creating the table with more freelists  
4. Which of the following methods can be used to detect row migration?
Review Button.jpg (3900 bytes)
A. UTLBSTAT/UTLESTAT  
B. analyze list chained rows  
C. select count(ROWID) from V$CHAIN;  
D. show parameter block  
5. Which of the following combinations is inappropriate for distributing disk I/O?
Review Button.jpg (3900 bytes)
A. Redo logs and control files  
B. DATA tablespaces and control files  
C. INDEX tablespaces and DATA tablespaces  
D. RBS tablespaces and the SYSTEM tablespace  
6. What should not be done to resolve the error "snapshot too old (rollback segment too small)"?
Review Button.jpg (3900 bytes)
A. Increase the size of the TEMP tablespace  
B. Optimize the SQL statements in the transaction  
C. Increase the rollback segment size  
D. Run the transaction that caused the error during low database activity levels  
E. Assign the transaction to a larger rollback segment  
7. Which of the following lines in this SQL statement will produce an error?
Review Button.jpg (3900 bytes)
A. create rollback segment RBS01  
B. storage (initial 50M next 50M  
C. minextents 5 maxextents 50  
D. pctincrease 20  
E. optimal 250M);  
F. There are no errors in this statement  
8. Distributing extents of a table across multiple disk drives is called
Review Button.jpg (3900 bytes)
A. Load optimization  
B. Striping  
C. Segmentation  
D. Condensation  
9. All rollback segments
Review Button.jpg (3900 bytes)
A. Are placed in the RBS tablespace  
B. Are placed in the TEMP tablespace  
C. Are placed in the DATA tablespace, except those in the SYSTEM tablespace  
D. Are placed in the RBS tablespace, except those in the SYSTEM tablespace  
10. The value of DB_BLOCK_SIZE can be changed by
Review Button.jpg (3900 bytes)
A. Resetting the DB_BLOCK_SIZE parameter  
B. Re-creating the database  
C. Resetting the value in the next storage option in the table  
D. Resizing the value in the pctincrease storage clause for the tablespace  
11. The number of times a rollback segment resizes itself according to the OPTIMAL clause is collected in which performance view?
Review Button.jpg (3900 bytes)
A. V$ROLLSTAT  
B. V$WAITSTAT  
C. VV$SYSSTAT  
D. V$SESSTAT  
12. The DBA creates a database and issues a CREATE TABLESPACE data_01 statement. The minimum number of rollback segments Oracle must allocate in order for the instance to start is
Review Button.jpg (3900 bytes)
A. 1  
B. 2  
C. 3  
D. 4  
E. 5  

 

Chapter 20 Self Test

 
1. The V$LATCH performance view is used for determining performance on
Review Button.jpg (3900 bytes)
A. Latches  
B. Dispatchers  
C. Shared servers  
D. Online redo logs  
2. The name of the file containing parameters for the SQL*Net Listener is
Review Button.jpg (3900 bytes)
A. config.ora  
B. listener.ora  
C. init.ora  
D. sqlnet.ora  
E. tnsnames.ora  
3. Before using sort direct writes, the DBA should first ensure that
Review Button.jpg (3900 bytes)
A. SORT_AREA_SIZE is larger than DB_BLOCK_BUFFERS  
B. SORT_DIRECT_WRITES is greater than 10  
C. SORT_TEMP_TABLESPACES contains the name of the tablespaces holding temporary segments  
D. All temporary segments are placed in the SYSTEM tablespace  
E. There is enough real memory to store a number of bytes equal to or greater than SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE  
4. Increasing SORT_AREA_SIZE
Review Button.jpg (3900 bytes)
A. Has the potential to size the SGA beyond real memory capacity  
B. Improves the performance of sort direct writes  
C. Increases the size of redo log entries  
D. Alters the location of the alert log  
5. To kill a user session requires which two pieces of information about the session?
Review Button.jpg (3900 bytes)
A. Username and session ID  
B. Username and SQL operation  
C. Session ID and SERIALl#  
D. SERIAL# and process address  
E. Username and process address  
6. What is the name of the row in V$SYSSTAT that identifies the number of sorts occurring in memory?
Review Button.jpg (3900 bytes)
A. Memory(sorts)  
B. Memory, sorts  
C. Sorts(memory)  
D. Sorts, memory  
7. The type of lock obtained by an UPDATE process is the
Review Button.jpg (3900 bytes)
A. Shared lock  
B. Exclusive lock  
C. Shared row exclusive lock  
D. Row shared lock  
E. Row exclusive lock  
8. Which of the following SQL operations does not use sorts?
Review Button.jpg (3900 bytes)
A. group by  
B. select * from EMP;  
C. order by  
D. select count(*)  
E. create index  
9. What two recommendations can DBAs make to application developers to avoid deadlocks?
Review Button.jpg (3900 bytes)
A. Use exclusive locks for all data change operations.  
B. Give different priorities to different background processes.  
C. Set all processes to acquire locks in the same order.  
D. Assign long-running transactions to large rollback segments.  
E. Set all processes to use the lowest level of locking necessary.  
10. A listing of processes holding locks and processes waiting for those locks can be found in output of which utility?
Review Button.jpg (3900 bytes)
A. UTLBSTAT/UTLESTAT  
B. EXPORT  
C. DBMS_APPLICATION_INFO  
D. UTLLOCKT