Oracle Database 11g Performance Tuning Presales

Topics: SQL, Database management system, Partition Pages: 21 (2701 words) Published: February 23, 2011
Oracle Database 11g Performance Tuning PreSales

1. 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 statement 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 the query.

4. How does partitioning improve the performance of queries?

By optimizing joins

By sorting data in advance of queries

By partition pruning to eliminate I/O (*)

By using less network bandwidth

Correct, partitioning improves performance by partition pruning to eliminate I/O.

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.

7. True or false - ASM automatically implements disk striping.

True (*)

False

Correct, ASM 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.

True (*)

False

Correct, you can perform maintenance operations on a single partition without affecting the other partitions in the table.

9. What were some of the weaknesses of the rule-based optimizer?

(Choose all correct answers)

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 OLTP system, which of the following types of data should be placed on your least busy device?

Indexes

Redo logs (*)

Archive logs

Data files

Correct, for an OLTP system, redo logs should be placed on your least busy device

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...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Oracle Database 11 G Essay
  • Oracle Database: SQL Fundamentals Essay
  • Database Essay
  • Oracle Database Backup and Recovery Essay
  • Essay about Saving File in Oracle Database
  • HFM Performance Tuning Guide Research Paper
  • Oracle Essbase Presales Specialist Assessment Essay
  • Database Essay

Become a StudyMode Member

Sign Up - It's Free