Oracle Query processing 的程式

Oracle Query processing 的程式

當一個查詢statement送出時，他的執行程式為：(from Jonathan Lewis web site)

1. Perform syntax check
2. Perform semantic check
3. Perform hash function
4. Perform library cache lookup
5. If hash value found then
6. .....If command is identical to existing one in cache then
7. ..........If the objects referenced in the cached command are the same as the ones in the new command then
8. ...............This is a soft parse, go to step 11
9. This is a hard parse, build parse tree --&gt (parse 程式)
10. Build execution plan
11. Execute plan.

(parse 程式) Meta link 46234.1

[1] Syntactic - checks the syntax of the query

[2] Semantic - checks that all objects exist and are accessible

[3] View Merging - rewrites query as join on base tables as

opposed to using views

[4] Statement Transformation - rewrites query transforming some complex

constructs into simpler ones where

appropriate (e.g. subquery unnesting, in/or

transformation)

[5] Optimization - determines the optimal access path for the

query to take. With the Rule Based

Optimizer (RBO) it uses a set of heuristics

to determine access path. With the Cost

Based Optimizer (CBO) we use statistics

to analyze the relative costs of accessing

objects.

[6] Query Evaluation Plan Generation

重複的進行hard parse是非常消耗資源的，我們知道一個statement會先被算出一個hash值，然後去share pool找尋是否有相同的hash value statement來重複利用已解析過statement，並利用其執行計畫，statement如不好好使用bind variable加上share pool不足，儲存的statement終會被LRU演演算法給踢除，這就會造成hard parse的增加了。

如果這個問題非常嚴重，在不能改寫程式的情況下，調整cursor sharing也許是不得已的方法了，不過要謹慎考量他的副作用。