With cursorsharing similar cursors are shared for

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: With CURSOR_SHARING = SIMILAR, cursors are shared for safe literals only. What is meant by 'safe literals only'? Actualtests.com - The Power of Knowing 1Z0-030 A. No literal value is substituted for a shared cursor. B. Different execution plans are generated for substituted literal values. C. The substitution of a literal value will produce different execution plans. D. The substitution of any literal value will produce exactly the same execution plan. Answer: D Explanation: Oracle9i has enhanced cursor sharing mode. It can use additional value, SIMILAR, in addition to the EXACT and FORCE cursor sharing modes. When you specify SIMILAR, Oracle only uses the execution plan if is certain that the execution plan does not have any association with the specific literal value. You can enable similar statements to share the same SQL execution plan by setting CURSOR_SHARING to either FORCVE or SIMILAR. Incorrect Answers A: Literal value is substituted for a shared cursor. The substitution of any literal value will produce exactly the same execution plan. B: Exactly the same execution plans will be generated for substituted literal values. C: The substitution of any literal value will produce exactly the same execution plan. OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217 Chapter 4: Performance and Scalability Enhancements Oracle 9i New Features, Robert Freeman, p. 57-59 Chapter 2: Oracle9i Architecture Changes QUESTION 5: The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command. This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components. Which three statements are true for the Dynamic SGA feature? (Choose three.) A. The maximum granule size is 4 MB. B. The minimum SGA configuration is three granules. C. SGA memory is based on granules by SGA components. D. The size of the SGA components is set by the SGA_MAX_SIZE parameter. E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter. Answer: B, C, E Explanation: Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and Actualtests.com - The Power of Knowing 1Z0-030 running. Additionally, the unit of memory allocation for SGA is a granule in Oracle9i. At a minimum, one granule allocated for the shared pool, one for the buffer cache, and one for the redo log buffer. You can confirm a total amount of granules by checking the value of the column LO_SETID in the V$BUFFER_POOLS view. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE. If...
View Full Document

This document was uploaded on 03/08/2014.

Ask a homework question - tutors are online