sql performance tuning examination questions

Last updated on December 31st, 2015 at 06:03 pm

Here are some of sql performance tuning examination questions

1) following an ANALYZE command, which of the following data dictionary views will contain statistics on the number of rows in a table

a dbms_rows
b dba_table_rows
c dbs_tables
d dbs_statistics

Solution (c)
2) histograms are useful because they allow the optimizer to have a better idea of how the data in an indexed columns is
a sorted
b named
c entered
d distributed

Solution (d)
3) the optimizer mode can be set at which of the following levels
a instance
b session
c statement
d all of the above

Solution (d)

4) the ability to store predefined execution plans so that the optimizer knows in advance which execution plan is best is called
a stored outline
b materialized views
c hash cluster
d index organized tables

Solution (a)

5) which of the following types of indexes stores the indexed columns values associated row id as binary string
a b-tree index
b reverse key index
c bitmap index
d bitwise index

Solution (c)

6) which of the following factors would not make a column a good candidate for b-tree index
a the data in the column has low cardinality
b the column frequently used in sql statement where clases
c most queries on the table return only small portion of all row
d none of the above

Solution (a)
7) the process of preparing a statement for execution is called
a caching
b hashing
c parsing
d none of the above

Solution (c)

8) finding a statement already cached in the library cache is referred as
a cache hit
b cache miss
c cache match
d cache parse

Solution (a)

9) to determine if a new statement is a match for an existing statement already in the shared pool, oracle compares each statement
a result set
b security
c execution plan
d hashed value

Solution (d)

10) in order for two statements to result in a cache hit, which of the following must be true
a the statements must use the same case – either upper , lower , or mixed
b the statements must be issued against the same table
c the statements must be on the same number of lines
d all of the above must be true

Solution (d)
11) which dynamic data dictionary view contains information about the library cache hit ratio
a v$rowcache
b v$librarycache
c v$dictionarycache
d all of the above

Solution (b)
12) according to oracle, what should the data dictionary hit ratio be for a well tuned oltp system
a more than 85 percent
b less than 85 percent
c between 50 and 60 percent
d non of the above

Solution (a)

Leave a Reply