Shared Pool Tuning - Quiz Explanation

The correct answers are indicated below, along with text that explains the correct answers.
 
1. What is one way you can minimize the reparsing of SQL statements?
Please select the best answer.
  A. Make all SQL use literal variables
  B. Write SQL statements to execute correlated subqueries
  C. Try to write all SQL so that it can be shared by multiple tasks.
  D. Try to make all SQL execute through ODBC
  The correct answer is C:Try to write all SQL so that it can be shared by multiple tasks. A is incorrect because literal variables increase reparsing. B is incorrect because correlated subqueries are unrelated to reparsing. D is incorrect because ODBC is not related to SQL reparsing.

2. Preventing SQL reloads is a tuning strategy you would use for which area of the SGA?
Please select the best answer.
  A. Session memory
  B. The library cache
  C. The redo log buffer
  D. The data dictionary cache
  The correct answer is B:the library cache.
A is incorrect because session memory is not effected by SQL. C is incorrect because the redo log buffer is not effected by SQL reloads. D incorrect because the data dictionary cache is only used for dictionary objects and not end-user SQL.

3. A low pin hit ratio indicates that you should:
Please select the best answer.
  A. Increase the db_block_size parameter
  B. Flush the library cache of non-reusable SQL statements
  C. Increase the number of packages pinned in the shared pool
  D. Increase the shared_pool_size init.ora parameter
  The correct answer is D:Increase the shared_pool_size init.ora parameter. A is incorrect because the db_block_size will only help data cache hits. B is incorrect because flushing the library cache will not immediately help with pinning issues. C is incorrect because increased pinning of packages may lower the pin hit ratio as other packages are flushed.

4. Cached cursors on the server can result in:
Please select the best answer.
  A. More memory usage in the PGA
  B. More memory usage in the UGA
  C. Memory being freed up in the SGA
  D. Memory being freed up in the UGA
  The correct answer is B Cached cursors on the server can result in more memory usage in the UGA. A is incorrect because caching will always consume more memory, not less. C is incorrect because cached cursors are not related to freed memory. D is incorrect because UGA memory will not be freed as a result of using cached cursors.