جلوگیری از SORTهای ناخواسته در دستور SQL

ممکن است گاهی متوجه عملیات مرتب سازی (SORT) در دستورات خود نشوید.SORT معمولا در موارد زیر استفاده می شود:

•       CREATE INDEX
•       GROUP BY
•       ORDER BY
•       INTERSECT
•       MINUS
•       UNION
•       DISTINCT
•       Unindexed table joins
•       Some correlated subqueries

 

ههدف شما می بایست همیشه کاهش یا حذف SORT در دستور SQL  باشد.استفاده از SORT در لایه application اتفاق می افتدبنابراین برنامه نویس باید به گونه ای دستورSQL  خود را بنویسد که از SORTهای ناخواسته جلوگیری شود.نکاتی در ذیل مطرح می شود که باید در کد ملاحظه کرد:

1-  از distinct اجتناب کنید مگر اینکه واقعا نیاز باشد.

2-  از UNION ALL به جای UNION استفاده شود مگر اینکه نیاز باشد رکوردهای مشابه حذف گردند.

3- سعی کنید از HASH JOIN بجای SORT_MERGE_JOIN استفاده شود.ضمنا استفاده از HINTها باعث می شود OPTIMIZER از INDEX HINT های مناسب استفاده کند تا از SORTها جلوگیری شود.مثلا cost based optimizer  از عملیات SORT اجتناب می کند وقتیکه FIRST_ROWS hint استفاده شود.

4-  از مناسبترین ایندکسها در کد خود استفاده کنید.

چنانچه در مانیتورینگ با مشکلاتی ناشی از SORT مواجه شدید افزایش مقدار پارامتر SORT_AREA_SIZE متناظر با میزان کل فضای حافظه قابل ملاحظه است.

اجرای Sql Tuning به صورت دستی

گاهی ( مثلا در نبود Oracle Enterprise Manager ) نیاز به اجرای Sql tuning به صورت دستی می باشد؛ مراحل زیر عملیات Sql tuning را در خط فرمان نشان می دهد :

 1- ابتدا باید  sql_id کوئری مورد نظر را بیابید.

select sq.sql_id,sq.sql_text from v$sql sq where sq.sql_text like '%Your Query%';

یا

select sql_id from v$session where sid = x

 

2- در گام بعدی برای کوئری یک tuning task ایجاد می کنیم :

SET SERVEROUTPUT ON

declare

stmt_task VARCHAR2(40);

begin

tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ' 5fktw7d0p8mut');

DBMS_OUTPUT.put_line('task_id: ' || tune_task);

end;

/

task_id: TASK_1042

تبدیل select تودرتو به join برای بهبود سرعت اجرا

گاهی نیاز است برای بهبود سرعت پرس و جو ، بجای  select تو در تو از join استفاده کنیم.مثال زیر نمونه ای از این تبدیل می باشد:

 select *
  FROM emp A
  WHERE A.sal between 800 and 2000
  AND A.deptno NOT IN
 (SELECT B.deptno
  FROM dept B
  WHERE B.deptno = A.deptno
  AND B.dname = 'sales');
  //-----------------------------------
  SELECT *  
  FROM emp A
  left join dept B on B.deptno = A.deptno AND B.dname='sales'
  WHERE   A.sal between 800 and 2000
  and B.dname is null

مشاهده مقادیر Bind Variable در دستورات SQL

یکی از بهترین روشها برای مشاهده مقادیر Bind Variable ها استفاده از ابزار TOAD میباشد.
بدین منظور مراحل زیر را طی نمایید :

DATABASE --> MONITOR --> SGA Trace/Optimization

پس از ورود به این ابزار و مشاهده لیست کوئری های اجرا شده در SGA میتوان با انتخاب گزینه Substitute values for bind variables if possible مقادیر مذکور را مشاهده نمود. این گزینه از نسخه 10 به Toad اضافه شده است. لازم به توضیح است این روش برای کوئری های ORM جواب نمیدهد و برای آنها باید از روش زیر استفاده کنید.

چنانچه نرم افزار Toad در دسترس نباشد و یا کوئری ها از نوع ORM باشند میتوانید از کوئری زیر استفاده کنید:

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
 
SELECT 
  sql_id,
  t.sql_text sql_text,  
  b.name bind_name,
  b.value_string bind_value 
FROM
  v$sql t 
JOIN
  v$sql_bind_capture b  using (sql_id)
WHERE
  b.value_string is not null  
AND
  sql_id='&sqlid'
/

Selectivity و Cardinality در محاسبه Query Cost

مثال 1 : فرض کنید 1000000 کارمند وجود دارند و از بین آنها میخواهیم فردی را انتخاب کنیم که دارای حقوق 20 میلیون تومان میباشد . در صورتی که تنها یک نفر این حقوق را دریافت کند ،

Selectivity ما برابر با 1  تقسیم بر 1000000 میباشد که بسیار نزدیک به صفر است .

Cardinality برابر است با تعداد کل رکورد ها ضربدر Selectivity .

در این مثال Cardinality=1 میباشد.

;Select * from Employee where salary=20000000

چند نکته مهم در کار با ایندکس ها

1- اگر در محیط عملیاتی اقدام به ساخت ایندکس میکنید حتما از حالت Online استفاده کنید تا کاربران دچار Lock Conflict نشوند . همچنین جهت تسریع در ساخت ایندکس میتوان درجه Parallel را تنظیم نمود(معمولاً بیش از 8 توصیه نمیشود).دقت شود که پس از ساخت ایندکس حتما Degree آن برداشته شود .

2- سعی کنید TBS محل ذخیره ایندکس ها جدا از TBS دیتاها باشد.
3- در صورت امکان دیتافایلهای محل ذخیره ایندکس ها را بر روی هارد های SSD قرار دهید . تا کمترین Latency را در I/O داشته باشید.