Hallo Forum,
Allen ein gutes Neues Jahr!
Es ist öffter schon mal die Frage nach dem Optimizer_mode aufgekommen. Damit nicht jeder seinen local DBA consulten muß habe ich mal kurz was aus der Oracle Dokumentation Kopiert.
Ab hier dann aber nur noch in Englisch:
Optimization is the process of choosing the most efficient way to execute a SQL statement. This is an important step in the processing of any data manipulation language (DML) statement: SELECT, INSERT, UPDATE, or DELETE (a.s. Das was man so in INNTRALINK Tag ein Tag aus macht). Many different ways to execute a SQL statement often exist, for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect how quickly the statement executes.
A part of Oracle called the optimizer calculates the most efficient way to execute a SQL statement. The optimizer evaluates many factors to select among alternative access paths. It can use a cost-based or rule-based approach.
Note: The optimizer may not make the same decisions from one
version of Oracle to the next. In more recent versions, the optimizer
may make different decisions based on better, more sophisticated
information available to it.
You can influence the optimizer’s choices by setting the optimizer approach and goal and by gathering statistics for cost-based optimization. Sometimes the application designer, who has more information about a particular application’s data than is available to the optimizer, can choose a more effective way to execute a
SQL statement (a.s. leider hält sich PTC an dieser Stelle ja immer sehr bedekt, so das dieser weg ausfällt). The application designer can use hints in SQL statements to specify how the statement should be executed.
Costbased Optimizer
Using the cost-based approach, the optimizer determines which execution plan is most efficient by considering available access paths and factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The cost-based approach also considers hints, which are optimization
suggestions placed in a Comment in the statement.
Conceptually, the cost-based approach consists of these steps:
1. The optimizer generates a set of potential execution plans for the SQL statement based on its available access paths and hints.
2. The optimizer estimates the cost of each execution plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement. The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular execution plan. The optimizer calculates the cost of each possible access method and join order based on the
estimated computer resources, including (but not limited to) I/O, CPU time, and memory, that are required to execute the statement using the plan. Serial execution plans with greater costs take more time to execute than those with smaller costs. When using a parallel execution plan, however, resource use is not directly related to elapsed time.
3. The optimizer compares the costs of the execution plans and chooses the one with the smallest cost.
Rule- Based Optimizer
Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. You can use rule-based optimization to access both relational data and object types.
Oracle’s ranking of the access paths is heuristic. If there is more than one way to execute a SQL statement, the rule-based approach always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank.
For more information, see "Choosing an Access Path with the Rule-Based Approach" on page 23-54.
Und ab hier geht es dann richtig ans Oracle eingemachte.
An dieser Stellen muß man dann darüber diskutieren wie Oracle einzelne SQL- Statements abarbeitet.
Gruß
Andreas
Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP
Foren auf CAD.de
| 
