Valid optimization methods - Oracle Transaction
Oracle (MCQ) questions and answers
Q. Which of the following are valid optimization methods?
- Published on 04 Aug 15
Both a and b
ANSWER: Both a and b
Oracle DBA (
Oracle Replication (
Oracle Architecture (
Oracle Transaction (
Administering and Managing an Oracle Database (
Managing Oracle Database Processes (
Oracle Control Files (
Oracle Redo Log (
Oracle Tablespaces (
Oracle Datafiles and Tempfiles (
Oracle Managed Files (
Oracle Schema (
Oracle Tables (
Oracle Indexes and Partitioned Tables (
Oracle Clusters (
Oracle Views, Sequences, and Synonyms (
Oracle Distributed Database concepts (
-Posted on 22 Sep 15
- Cost based optimizer
- Query optimization is the overall process of choosing the most efficient means of executing a SQL statement.
- SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.
- The database optimizes each SQL statement based on statistics collected about the accessed data.
- When generating execution plans, the optimizer considers different access paths and join methods. Factors considered by the optimizer include:
1. System resources, which includes I/O, CPU, and memory
2. Number of rows returned
3. Size of the initial data sets
- The cost is a number that represents the estimated resource usage for an execution plan.
- The optimizer assigns a cost to each possible plan, and then chooses the plan with the lowest cost.
- For this reason, the optimizer is sometimes called the cost-based optimizer (CBO) to contrast it with the legacy rule-based optimizer (RBO).
- Rule based optimizer
- The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g.
- The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle.
- It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer).
- The results of this osolescence are:
1. The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
2. The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
3. The CHOOSE and RULE optimizer hints still exist but are no longer supported.
4. Code requiring the RBO must be migrated to use the query optimizer.
Post your comment / Share knowledge
Enter the code shown above:
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)