潛水珽的異想世界

今天在查SQL loading時,發現V$SQL的OPTIMIZER_MODE出現了MULTIPLE CHILDREN PRESENT,上網查了一下,網路上對這個mode的解釋如下:

===============================================================
What does 'MULTIPLE CHILDREN PRESENT' in column optimizer_mode
of the view v$sql mean?


Solution Description:
===============================================================
Finding this value in optimizer_mode column of v$sql means that the same query was executed at least twice with a slightly different executing environment which caused the optimizer to choose a different execution path.

Some examples:

- Two people selecting from different private tables named emp.

- Same query but are using different session optimizer_mode setting.

- Both queries are using cost based, but with a different session value for sort_area_size or other CBO related parameters
===============================================================

這個意思是,一個query中有兩個以上的SQL查詢(譬如SQL中有使用到子查詢),這時子查詢中的OPTIMIZER_MODE就會秀MULTIPLE CHILDREN PRESENT,這是我初步的理解.