STUDENTS 3K

  • Home
  • Students+
    • Engineering Students
    • Education Loan
    • Study Abroad Guide
    • Projects Download
  • Freshers
    • Aptitude Q & A
    • Placement Papers
    • Verbal Ability
    • Interview Questions
    • IT Company Details
    • Job Updates
  • Study Resources
    • Career Guidance
    • LAB Programs
      • C Programs
      • CPP Programs [C++]
      • Java Programs
    • Question Papers
    • Learn English
    • Notice Board
  • More –>>
    • Love calculator
You are here: Home / Interview / What is Cost Based Optimizer?

What is Cost Based Optimizer?

Query optimizer in RDBMS can be defined as a part of SQL program which is used to analyze the best way to run a SQL query. The query optimizers are used to improve the efficiency of the SQL statements.

  • Related: SQL & .NET Interview questions and Answers

What is Cost based Optimizer?

Cost based optimizer can be defined as the optimizer which finds the best possible scenario to find the efficiency of a particular query. The cost based optimizer will select the best available scenario to execute the query. The efficiency of a query is estimated by utilizing the database statistics.

The cost based optimizers in SQL are complicated component of Oracle. Estimating the value of SQL query is tough process and Cost Based Optimizers (CBO) are the best available option. The execution speed of every Oracle query can be determined by the CBO. The CBO of Oracle is influenced by the following parameters.

  • CBO parameters
  • CBO statistics

Cost based optimizers are dramatically improvised in Oracle Database 10g. The internal improvisation and easier mechanisms will enable SQL optimization. The following are the improved SQL optimizer technique in Oracle Database 10g.

  • Rule based Optimizer de – Support: The rule based optimizers (RBO) present in oracle database 10g are seldomly used because the sites which uses the first row optimizer mode will adjust the parameter to lesser number. The parameter value of rule based optimizer is lower than 25. The lower parameter value will stimulate the behaviour of cost based optimizer. The execution plans of oracle database 10g will ensure the stability of cost based optimizer.
  • User Initiated Buffer Cache Flushing: The buffer cache of oracle database 10g can be flushed manually between the runs of test queries. The buffer cache flushing will enable the diagnosing and run time execution of SQL statements. The response time of SQL statements will unique after the initialization of buffer cache flushing.
  • SQL Access Advisor: The SQL access advisor present inside the dbms_package is a system designed to solve the problems of SQl statements. The dbms_advisor package will analyze the library cache and recommends the indexes and materialized views.

Recommended: Final year Academic projects download

Cost Basis

Every Oracle optimizer will work on the basis of a cost; cost of optimizer can be defined as the efficiency of an optimizer. The cost of an optimizer can be determined by following these guidelines.

  • The detailed information about columns can be determined with the help of histograms. The value of index column might change the executional plan so histograms are used only when no other option is available.
  • Writing the correct SQL which can get the correct is not enough because fine tuning  the correct SQL according to the query is necessary for improved performance. The SQL developers should be trained in fine tuning their SQL statements.
  • Missing indexes and sub optimal table joins may affect the efficiency of SQL statements.
  • The change of executional plans in a query is highly recommended by Oracle. But they are only used as least preferable option.
  • The executional plan for SQL library cache can be seen inside v$sql_plan. The queries will also provide an overview about SQL execution internals.

Filed Under: Interview Tagged With: Interview, SQL, SQL Interview questions





Random Materials :

  • Data Mining and Data Warehousing
  • Difference Between Class and Primitive Types
  • Database Roles in SQL
  • CASE Expression in SQL
  • Concurrent Update Problem

Follow Us on Facebook

Advertisement

Company Profile

  • About Students3k
  • Contact Us
  • Earn Money Online
  • Website Archive Page
  • Privacy Policy
  • Disclaimer

Categories

  • Important Programs
  • Study Abroad
  • IT Companies
  • Career Guidance for Students
  • Teachers
  • Verbal Analogies

We Are Social

  • Facebook Fans
  • Twitter Follow
  • Google Plus
  • Pinterest Page

© Copyright 2012 - 2016 Students3k.com · All Rights Reserved · Designed By Benefits

Show