1Z0-117 Oracle Oracle Database 11g Release 2- SQL Tuning
OracleDatabase Data Warehousing Guide,Star Transformation with a Bitmap Index QUESTION: 118 Which two statements are true about index full scans? A. An index fast full scan multi block I/O to read the index structure in its entirely. B. Index nodes are not retrieved in the index order, and there fore the nodes are not in sequence. C. An index fast full scan reads the index block by block. D. An index fast full scan reads the whole index from the lowest value to the higher value. Answer: A, B Explanation: A:To speed table and index block access, Oracle uses the db_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full-index scan data blocks into the data buffer cache as fast as possible. B:The index nodes are not retrieved in index order, the rows will not be sequenced. Note: there are some requirements for Oracle to invoke the fast full-index scan. Reference: index fast full scan tips QUESTION: 119 An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created. Which two tasks would you perform to achieve this? A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE. B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans. 137
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans. E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans. Answer: A, D Explanation: A:OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost. D:EVOLVE_SQL_PLAN_BASELINE Function This function evolves SQL plan baselines associated with one or more SQL statements. A SQL plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted plan or plans. If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').
You've reached the end of your free preview.
Want to read all 12 pages?
- Fall '20
- Bloom filter, bit array