تغییر Execution Plan یک کوئری می تواند به دلایل ساده ای مثل حذف و اضافه کردن ایندکس، پارتیشن بندی جدول، پارتیشن بندی ایندکس اتفاق بیفتد اما شناسایی علت تغییر رفتار Optimizer همیشه ساده نیست چرا که در بعضی از موارد تغییر در Optimizer Environment منجر به ایجاد Execution Plan جدید می شود.

برای مثال در sessionای پارامتر OPTIMIZER_INDEX_COST_ADJ که میزان گرایش Optimizer به استفاده از ایندکس را تعیین می کند، به عدد 1 و در session دیگر این پارامتر به مقدار 1000! تنظیم شده است بدون تردید این تفاوت ها در Optimizer Environment، می تواند Execution Plan بعضی از کوئری ها را تغییر دهد.

موضوع این مستند در مورد آن است که چگونه می توانیم تشخیص دهیم تغییر Execution Plan یک کوئری به دلیل تغییر در Optimizer Environment است؟ و به طور دقیق تر، کدام پارامترها و عوامل محیطی منجر به ایجاد Execution Plan جدید شده اند. این کار را با قابلیت جدیدی که اوراکل در نسخه 23c ارائه کرده است، انجام خواهیم داد.

اوراکل در نسخه های قبل از 23c، در ویوهای V$SQL، V$SQLAREA و DBA_HIST_SQLSTAT در کنار Plan Hash Value مقداری را برای Optimizer-Environment Hash Value نگه می داشت ولی جزییات بیشتری را در مورد این ستون ارائه نمی کرد. اما در نسخه 23c، ویوی DBA_HIST_OPTIMIZER_ENV_DETAILS را ارائه شده است که در این زمینه بسیار راهگشا خواهد بود.

نکته!با استفاده از ویوی v$sys_optimizer_env و v$sql_optimizer_env می توانیم Optimizer Environmentها را ببینیم.

در ادامه قصد داریم از پارامتر OPTIMIZER_INDEX_COST_ADJ برای آشنایی بیشتر با ویوی DBA_HIST_OPTIMIZER_ENV_DETAILS استفاده کنیم.

در ابتدا برای پیش بردن سناریو، جدول و ایندکسی را ایجاد می کنیم:

SQL> create table usef.tbl1 as select * from dba_source;
Table created
SQL> create index usef.indTYPE on usef.tbl1(type);
Index created

قرار است کوئری زیر را اجرا کنیم:

SQL> select * from usef.tbl1 where type='PACKAGE BODY';

مقدار پیش فرض پارامتر optimizer_index_cost_adj برابر با 100 است با این مقدار، برای کوئری فوق، Execution Plan زیر را خواهیم داشت:

SQL> alter system set optimizer_index_cost_adj=100;
System altered.
SQL> select * from usef.tbl1 where type='PACKAGE BODY';

با افزایش مقدار پارامتر optimizer_index_cost_adj، هزینه استفاده از ایندکس را بالا می بریم:

SQL> alter system set optimizer_index_cost_adj=1000;
System altered.

Sql_id کوئری فوق برابر با  3ubvmambcg5fzاست و برای این sql id دو plan hash value وجود دارد:

SQL> select sql_text,sql_id,plan_hash_value, OPTIMIZER_ENV_HASH_VALUE from v$sql where sql_text like '% usef.tbl1 where%' and sql_text not like '%like%';

شناسایی این تغییر برای کسی که در جریان این تغییر نبوده است، چگونه ممکن است؟ویوی DBA_HIST_OPTIMIZER_ENV_DETAILS می تواند در این زمینه به ما کمک کند.

این ویو به AWR snapshot متکی است بنابرین در ابتدا باید این sql_id را به snapshot بعدی اضافه کنیم این کار با دستور زیر قابل انجام است:

SQL> exec dbms_workload_repository.add_colored_sql('3ubvmambcg5fz');
PL/SQL procedure successfully completed

بعد از آن، AWR snapshot را به صورت دستی ایجاد می کنیم:

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed

در گام پایانی، با استفاده از ویوی DBA_HIST_OPTIMIZER_ENV_DETAILS پارامترهای مربوط به هر کدام از این دو plan hash value را مشخص می کنیم:

SQL> SELECT distinct optimizer_env_hash_value,name, value FROM dba_hist_optimizer_env_details WHERE optimizer_env_hash_value in ( 3841567604,4128052869) and name='optimizer_index_cost_adj';
OPTIMIZER_ENV_HASH_VALUE NAME                           VALUE
------------------------ ------------------------------ -----
              3841567604 optimizer_index_cost_adj       100
              4128052869 optimizer_index_cost_adj       1000

البته در مثال فوق ما از پارامتری که تغییر کرده بود مطلع بودیم، در صورتی که در محیط واقعی معمولا دلیل تغییر Execution Plan بر ما پوشیده است بنابرین باید به شکل دیگری از این ویو کوئری بگیریم:

SQL> select distinct optimizer_env_hash_value, name, value
  from dba_hist_optimizer_env_details
 where optimizer_env_hash_value in (3841567604, 4128052869)
   and name in (with a as
                (SELECT distinct name, value
                   FROM dba_hist_optimizer_env_details
                  WHERE optimizer_env_hash_value in (3841567604, 4128052869))
                 select name from a group by name having count(*) > 1
                );

کوئری زیر که در منابع اوراکل موجود است، دو ویوی dba_hist_sqlstat  و  dba_hist_optimizer_env_details را با هم join زده و جستجو را بر اساس sql_id انجام می دهد:

WITH /*+ MATERIALIZE */
common_opts AS
 (SELECT e2.name, e2.value, count(DISTINCT e2.optimizer_env_hash_value)
    FROM dba_hist_sqlstat s2, dba_hist_optimizer_env_details e2
   WHERE s2.con_id = e2.con_id
     AND s2.con_id = sys_context('USERENV', 'CON_ID')
     AND s2.optimizer_env_hash_value = e2.optimizer_env_hash_value
     AND s2.sql_id = '3ubvmambcg5fz'
   GROUP BY e2.name, e2.value
  HAVING(count(DISTINCT e2.optimizer_env_hash_value) = (SELECT count(DISTINCT
                                                                    s3.optimizer_env_hash_value)
                                                         FROM dba_hist_sqlstat s3
                                                        WHERE s3.sql_id =
                                                              '3ubvmambcg5fz') AND count(DISTINCT e2.optimizer_env_hash_value) > 1))
SELECT DISTINCT e.optimizer_env_hash_value OPT_ENV,
                e.name                     OPT_PARAM,
                e.value
  FROM dba_hist_sqlstat s, dba_hist_optimizer_env_details e
 WHERE s.con_id = e.con_id
   AND s.con_id = sys_context('USERENV', 'CON_ID')
   AND s.optimizer_env_hash_value = e.optimizer_env_hash_value
   AND s.sql_id = '3ubvmambcg5fz'
   AND (e.name, e.value) NOT IN (SELECT c.name, c.value FROM common_opts c)
 ORDER BY 1, 2;