BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle tuning: Oracle and Expert Systems Technology

Oracle Tips by Burleson Consulting

 

SQL Optimizer Mode Defaults Quiz

Question:

What is a way to ensure that your SQL will not change access paths?

  1. Always use optimizer_mode=choose

  2. Place an ALTER SESSION command in all production SQL

  3. Use optimizer_mode=all_rows

  4. Place a hint after the SELECT statement

Correct Answer:

D

Explanation:

The correct answer is D, place a hint after the SELECT statement.  This is because a hint for always force the use of the optimizer of your choosing.  Question: What is the best way to ensure that your SQL will not change access paths?  A is incorrect because the “choose” mode is the most unreliable optimizer mode.  B is incorrect because it is not practical to add ALTER SESSION statements to production code.  C is incorrect because the all_rows hint may change the access path as the statistics for the tale and index change.

Question:

Which of the following were listed as ways to override the default optimizer mode? (Choose all that apply)

  1. Change the optimizer_mode in the init.ora file

  2. Use SQL*Plus for your SQL queries

  3. ALTER SESSION SET OPTIMIZER_MODE

  4. Add a hint to the SQL query

Correct Answer:

C, D

Explanation:

The correct answers are C, ALTER SESSION SET OPTIMIZER_MODE, and D, add a hint to the SQL query.  These are the only ways to change the default optimizer.  Question: Which of the following were listed as ways to override the default optimizer mode? (Choose all that apply)  A is incorrect because optimizer will change the default, not override it.  B is incorrect because SQL*Plus has no effect on the optimizer mode.

 

Question:

What hint would most likely be used by a cost-based OLTP query?

  1. All_rows

  2. First_rows_n

  3. choose

  4. rule

Correct Answer:

B

Explanation:

The correct answer is B, the first_rows_n hint.  This is because first_rows_n will return table rows data faster than the all_rows hint.  Question: What hint would most likely be used by a cost-based OLTP query?  A, C and D are incorrect because they are not the most appropriate for cost-base OLTP queries.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.