Oracle Query processing 的程序

Oracle Query processing 的程序 當一個查詢statement送出時，他的執行程序為：(from Jonathan Lewis web site) Perform syntax check Perform semantic check Perform hash function Perform library cache lookup If hash value found then .....If command is identical to existing one in cache then ..........If the objects referenced in the cached command are the same as the ones in the new command then ...............This is a soft parse, go to step 11 This is a hard parse, build parse tree --&gt (parse 程序) Build execution plan 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也許是不得已的方法了，不過要謹慎考量他的副作用。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/779728/viewspace-926727/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/779728/viewspace-926727/