یه روز قشنگ نشستیم و داریم کارمون رو میکنیم که یهو یه sql تصمیم میگیره که یه جور دیگه اجرا بشه. sql ی که تا حالا داشته خوب اجرا میشده یهو تصمیم میگیره یه کله قند بندازه وسط صفحه enterprise manager. یا مثلا یکی زنگ میزنه میگه دستوری که اجرا می کرده، تا دیروز یه ساعت طول می کشیده و حالا شش ساعت طول میکشه و تهش snapshot too old میده. اینجور موقع ها باید چه کنیم؟
قبل از اینکه ادامه بدم باید از استاد عزیزم محسن بهادری و دوست عزیزم سعید مرادخانی بخاطر راهنمایی هاشون تو نوشتن این مقاله تشکر کنم.
SQL Plan Baseline یک مجموعه از پلن‌های اجرایی تایید شده است که Oracle برای اجرای کوئری‌ها از آن استفاده میکنه و این ویژگی به جلوگیری از تغییرات ناخواسته در پلن‌های اجرایی کمک میکنه.
بعد از اینکه sql_id این Sql یاغی رو پیدا کردیم، اولین کاری که باید بکنیم اینه که بگردیم و ببینیم واقعا قبلا بهتر اجرا میشده یا نه؟! برای اینکار از جستجوی زیر استفاده می کنیم:
select address , sql_id , first_load_time, plan_hash_value, elapsed_time, last_load_time, last_active_time,sql_plan_baseline, hash_value from v$sql where sql_id='70zd67a9zq6w1';
توضیح اینکه برای پیدا کردن Sql_id چندین راه وجود داره. یکی از تو enterprise manager هست که تو صفحه performance پیداش کنید. اما راهی که من دوست دارم اینه که از طریق V$session با مشخصاتی که از تعداد تکرار یا از کاربری که گزارش مشکل کرده، بدست میاد، اون رو پیدا کنیم.
از جستجوی بالا اگر بیش از یک خط با plan_hash_value های متفاوت و بخصوص قسمت elapsed_time متفاوت بهمون نشون داد، مشخص میشه که باید دست به کار بشیم و اون plan که زمان کمتری میبره رو ست کنیم. از این امر هم غافل نشید که گاهی به دلیل stale بودن statistics این موضوع پیش میاد که با gather کردن، میشه جلوش رو گرفت.
SELECT
S.SID,
S.SERIAL#,
S.SQL_ID,
M.sql_PLAN_HASH_VALUE
FROM
V$SESSION S
JOIN
V$SQL_MONITOR M ON S.SQL_ID = M.SQL_ID
WHERE
S.STATUS = 'ACTIVE'
AND M.STATUS = 'EXECUTING'
AND S.SQL_ID = '70zd67a9zq6w1';
حالا باید بریم سراغ اطلاعاتی که تو snapshot هایی که اوراکل می گیره. از روی زمانی که تو جسجتوی Sql استخراج کردیم و از روی زمانی که last_active_time میده بهمون باید از ویوی dba_hist_snapshot بتونیم snap_id هایی که میخوایم رو بدست بیاریم.
در مرحله بعد باید یه sqlset درست کنیم. برای اینکه با مثال توضیح بدم، sqlset رو مثل یک ظرف در نظر بگیرید که قرار هست با plan مدنظر ما پر بشه.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sqlset',
description => 'SQL Tuning Set for specific SQL_ID'
);
END;
حالا باید این ظرف رو پر کنیم . برای اینکار با اطلاعاتی که از جستجوهای قبل به دست آوردیم اقدام می کنیم.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => <BEGIN_SNAP_ID>,
end_snap => <END_SNAP_ID>,
basic_filter => 'sql_id = ''70zd67a9zq6w1'' and plan_hash_value = 3404959673'
)
) P;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sqlset',
populate_cursor => cur
);
END;
تو مرحله بعد sql set رو باید با dbms_spm که مخفف sql plan management هست بارگزاری کنیم.
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'my_sqlset'
);
END;
برای اینکه مطمئن بشیم که کار میکنه، از جستجوی زیر استفاده میکنیم:
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_ID = '70zd67a9zq6w1';
حالا یه سری خبر خوب هم بدم. روشی که انجام دادیم برای نسخه 11 بود که کلی از امکانات جدیدی که از 12 به بعد اومده رو نداشت. اگر نسخه شما از 12 به بعد هست می تونید از روش ساده تری استفاده کنید:
اول ببینید که جستجو شما تو Awr هست یا نه:
SELECT * FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID = 'exf6mxfttzn6r'
AND PLAN_HASH_VALUE = 3404959673;
حالا اگر بود، به راحتی از دستور زیر استفاده کنید:
DBMS_SPM.load_plans_from_awr
یا
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
/