Performance Tuning Presales

What type of data would benefit from the use of a reverse Index? For index values which increment by 1 . For any character-based index value. For any index value with repeating sections.

For indexes with low cardinality. Correct, a reverse Index would work well for tables where the Index values Increment by 1. 2. When should you resort to using an hint In an SQL statement? You should always use hints. You should use a hint to force the optimizer into recognizing an newly added index. You should use a hint once you understand exactly why the execution plan for the tenement was used.

*) You should never, ever use hints. Correct, you should only use a hint In an SQL statement once you understand exactly why the execution plan for the statement was used. 3. How can the use of bind variables improve performance? By improving the speed of selection.

By eliminating the need to parse the query. (*) By reducing the memory requirements for a query. By using data In data buffers. Correct, bind variables can improve performance by eliminating the need to parse 4. How does partitioning improve the performance of queries? By optimizing Joins By sorting data in advance of queriesBy partition pruning to eliminate 1/0 (*) By using less network bandwidth Correct, partitioning improves performance by partition pruning to eliminate 1/0.

5. Which of the following performance-enhancing features of the Oracle Database require specific initialization parameter settings to gain their benefits? (Choose all correct answers) Bit-mapped indexes Materialized views (*) Parallelism (*) Partitioning None of these Correct, you must have specific initialization parameter settings to take advantage of parallelism and materialized views. 6.A query run in parallel completes sooner than the same query run serially. Always Sometimes (*) Never If the SQL uses a predicate Correct, a query run in parallel will not always complete faster than the same query run serially. True (*) False Correct, SAM automatically implements disk striping.

8. True or false - you can perform maintenance operations on a single partition without affecting the other partitions in the table. Correct, you can perform maintenance operations on a single partition without affecting the other partitions in the table. . What were some of the weaknesses of the rule-based optimizer? A limited set of rules to decide optimal retrieval paths (*) Difficulty in using with standard SQL statements Was not updated with latest features in the Oracle Database (*) Required extensive maintenance Correct, the rule-based optimizer could only use a limited set of rules to decide optimal retrieval paths and was not updated with the latest features of the Oracle Database. 10.

For an ALTO system, which of the following types of data should be placed on your least busy device?Indexes Redo logs (*) Archive logs Data files 11. What is an execution plan? A plan for installing the Oracle Database. A plan for execution of all appropriate maintenance tasks for an Oracle Database. A plan that lists the individual steps used to execute a query. (*) A plan for development of the Oracle Database over upcoming releases. Correct, an execution plan is a plan that lists the individual steps used to execute a query.

12. How can invisible indexes improve performance? It doesn't - it Just makes it easier to test the effect of the index. *) By improving the speed of index access. By improving the use of the index by the optimizer.

By reducing the storage needed for the index. Correct, an invisible index does not improve performance, it Just makes it easier to test the effect of the index. 3. True or false - parallelism in an Oracle database can only be based on an existing partition. True False (*) Correct, parallelism in an Oracle Database can be assigned dynamically.

14. Which of the following types of partitioning automatically allocate new partitions when required by new data?Hash Composite Virtual column-based Interval (*) Correct, interval partitioning automatically allocate new partitions when required by new data. 15. How can bit-mapped indexes improve performance? Increase the cardinality of the index Reduce storage space required for index (*) Reduce the cardinality of the index Allow bitwise operations on the index (*) Correct, a bit-mapped index reduces the storage space required for the index and allows bitwise operations the index.

16. You can use statistics on what set of entities for a detailed look at the performance of the overall database server?Oracle Events Oracle Universal Tasks Oracle Wait Events (*) Oracle Task Events Performance Phase Events Correct, you can use statistics on Oracle Wait Events for a detailed look at the performance of the overall database server. 17. How does the Oracle Database use statistics? To redefine SQL queries.

To properly allocate memory. To calculate storage requirements. Correct, the Oracle Database uses statistics to figure the cost of different access options. 18.

What is the common prefix for Oracle's dynamic performance views? Correct, Oracle's dynamic performance views share the prefix of VS.. 9. True or false - You can only create SQL plan baselines from a production database. Correct, you can create SQL plan baselines from a SQL Tuning Set (SST) as well as a 20.

Which of the following performance features can improve the performance of queries running against a standard data warehouse schema? Parallelism Materialized views Star transformations (*) Correct, star transformations can improve the performance of queries running 21 . Which parameter is used to determine the number of parallel servers created when the database starts up?A local partitioned index has which of the following characteristics? A local partitioned index is always unique. A local partitioned index is always a hash index A local partitioned index is defined with the LOCAL keyword. (*) A local partitioned index consumes less space than a global partitioned index.

A local partitioned index is queue-partitioned with its associated table. (*) Correct, a local partitioned index is defined with the LOCAL keyword and is queue- partitioned with its associated table. 7. What methods can the Oracle Database use to implement parallel operations?Table scan FIFO Block range (*) Correct, the Oracle Database can implement parallelism based on block range or partitions. 28. Which of the following performance features store index values in a special way? Bit-mapped indexes (*) Star transformations Correct, bit-mapped indexes store index values in a special way.

29. How can Result Set Caching improve performance? By storing result sets that can be used to fulfill some of the requirements of other queries. (*) By storing complete result sets. (*) By reducing memory usage for queries.

Correct, intra-operation parallelism can have the number of parallel servers represented by the degree of parallelism for the statement 31 . When using parallelism with ARC, which of the following statements are true? Parallel servers can only be on the owner node of the cluster Parallel servers must be spread across all the nodes of the cluster. The cluster tries to allocate parallel servers on the owner node first (*) Parallel servers can be spread across all nodes of the cluster (*) Correct, the cluster tries to allocate all the parallel servers on the owner node first, ND only then looks for parallel servers on other nodes. 2. What is the purpose of adaptive multiuse parallelism? To automatically set the proper default degree of parallelism when defining new objects To throttle back the degree of parallelism on busy systems (*) To manage parallelism differently for ALTO systems and data warehouse systems To set the degree of parallelism for large objects Correct, adaptive multiuse parallelism is used to throttle back the degree of parallelism on busy systems. 33.

What technique can be used to reduce the memory required by the shared pool in most scenarios?