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