sql performance tuning examination questions

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)