Oracle Performance Tuning Trivia Quiz!

19 Questions | Total Attempts: 444

SettingsSettingsSettings
Oracle Performance Tuning Trivia Quiz! - Quiz

.


Questions and Answers
  • 1. 
    What best describes the relationship between indexes and SQL performance?
    • A. 

      Indexes are only used in special cases

    • B. 

      Indexes are used to make table storage more efficient

    • C. 

      Indexes rarely make a difference in SQL performance

    • D. 

      Indexes exist solely to improve query speed.

  • 2. 
    Which of the following is not an important Oracle tuning metric?
    • A. 

      Buffer hit ratio

    • B. 

      Library cache hit ratio

    • C. 

      Full-table scans

    • D. 

      Buffer busy waits

  • 3. 
     Why is searching for large-table full-table scans critical to SQL tuning?
    • A. 

      They indicate an optimized execution plan.

    • B. 

      They may be able to be tuned to use an index

    • C. 

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

    • D. 

      A full-table scan is always sub-optimal.

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

      Add blocks to the SGA buffer pool

    • B. 

      Created a clustered table

    • C. 

      Create tables with redundant columns from other tables

    • D. 

      Run parallel query against the table

  • 5. 
     Which of the following database design features is most important to SQL performance? 
    • A. 

      Removal of data redundancy

    • B. 

      The introduction of data redundancy

    • C. 

      The introduction of non-first normal form relations

    • D. 

      The introduction of SQL*Plus

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

      De-normalization of the logical tables

    • B. 

      Eliminating full-table scans from SQL queries

    • C. 

      Moving Oracle data files from hot disks onto cool disks

    • D. 

      Adding additional storage to the SGA shared pool

  • 7. 
     Which of the following is an important consideration when tuning an SQL statement?
    • A. 

      The number of CPUs on the server

    • B. 

      The degree of parallelism on the tables

    • C. 

      The quality of the SQL optimization

    • D. 

      The use of bitmap indexes

  • 8. 
    What areas of Oracle tuning have the most impact on system performance?
    • A. 

      Logical design tuning

    • B. 

      Physical design tuning

    • C. 

      SGA tuning

    • D. 

      SQL tuning

  • 9. 
    Which of the following is an important feature of relational databases and SQL?
    • A. 

      Independence of table relationships

    • B. 

      High speed of SQL

    • C. 

      Powerful GUI front-end

    • D. 

      Easy to install and use

  • 10. 
    Identify the correct statement about the Active Session History (ASH) data.
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

      None of the above

  • 11. 
    In which case is the database buffer cache NOT used?
    • A. 

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

    • B. 

      When the database writer process writes to the SYSTEM tablespace

    • C. 

      When the server process writes sort data to the temporary tablespace

    • D. 

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

  • 12. 
    Which statements are true about performance analysis by SQL Performance Analyzer?
    • A. 

      It detects changes in SQL execution plans.

    • B. 

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

    • C. 

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

    • D. 

      All of the above

  • 13. 
    What indicates that the rate of writing data blocks to the disk is very slow?
    • A. 

      Very high cache-hit ratio

    • B. 

      Increasing number of free buffer waits

    • C. 

      increasing number of buffer busy waits

    • D. 

      Waits on latch: cache buffers chains event

  • 14. 
    In which  scenarios would you recommend using RAID level 5 as an option for database storage?
    • A. 

      For a database with lower recovery (MTTR) overhead

    • B. 

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

    • C. 

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

    • D. 

      None of the above

  • 15. 
    Identify the reason why chained or migrated rows in a table cause performance degradation when you access them.
    • A. 

      Because the ROWID changes for these rows

    • B. 

      Because these rows make existing indexes invalid

    • C. 

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

    • D. 

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

  • 16. 
    While configuring the snapshot data capture for your database, you have specified the snapshot level setting as 6.Which statistics would be captured by the Statspack report?
    • A. 

      Resource limit

    • B. 

      memory statistics

    • C. 

      Optimizer execution plans

    • D. 

      All of the above

  • 17. 
    During a proactive database performance monitoring routine, on examining the AWR report you find that log file sync appears among the top 5 wait events.What does this event indicate?
    • A. 

      Frequent logfile switches are occurring.

    • B. 

      Redo is generated faster than LGWR can write it out.

    • C. 

      Frequent commits or rollbacks are taking place in the application.

    • D. 

      Frequent incremental checkpoints are taking place in the database.

  • 18. 
    You analyze an Automatic Database Diagnostic Monitor (ADDM) report to identify the component that contributes most to the database time. Which is the correct description of database time?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 19. 
    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.

Back to Top Back to top