یکی از روش های بهبود پرفورمنس در دیتابیس اوراکل استفاده از قابلیت REBUILD کردن ایندکس ها است. REBUILD کردن ایندکس هایی که با روشی مناسب انتخاب می شوند می تواند پرفورمنس دیتابیس را به طور قابل ملاحظه ای بهبود دهد. البته عملیات آنالیز و REBUILD ایندکس زمانبر بوده و سربار ایجاد می کند. در این متن REBUILD ایندکس ها و روش استفاده مناسب از آن توضیح داده می شود.
REBUILD کردن ایندکس های دیتابیس هایی که APPLICATIONهای آنها شرایط زیر را داشته باشند به لحاظ پرفورمنسی به صرفه خواهد بود:
-عملیات SQL در دیتابیس شامل تعداد زیادی از دستورات DML است که سبب DELETE و INSERT شدن نودهای LEAF در ایندکس ها می شوند.
-در انتخاب مسیر برای QUERYهای برنامه، تعداد بالایی از SCANهای ایندکس از نوع RANGE SCAN ،FAST FULL SCAN و FULL SCAN انجام می شود.
پیشنهاد مطالعه:
نکته: در این متن REBUILD ایندکس های NORMAL یا B-TREE جهت بهبود پرفورمنس بررسی می شود ولی این عملیات برای ایندکس های BITMAP نیز کاربرد دارند.
پیشنهاد مطالعه:
نکته: صرف نظر از بحث پرفورمنس، به منظور USABLE یا VALID کردن ایندکس ها و پارتیشن آنها از عمل REBUILD استفاده می شود.
پیشنهاد مطالعه:
ویژگی PARTIAL INDEXING برای جداول پارتیشن شده
نکته: REBUILD کردن ایندکس ها در سیستم های DATAWARE HOUSE از لحاظ پرفورمنس به صرفه نیست.
چه ایندکس هایی کاندیدای مناسب برای REBUILD هستند؟
زمانی که مقادیر یا سطرهای جدولی که دارای ایندکس است DELETE می شوند اوراکل نود های LEAF ایندکس را به صورت فیزیکی حذف نمی کند بلکه به صورت منطقی آنها را غیرفعال در نظر می گیرد بنابراین آن نود را در ساختمان داده ایندکس نگه می دارد. در زمان فعالیت دیتابیس نودهای جدید در انتهای ساختمان داده ایندکس اضافه می شوند و احتمال استفاده مجدد از نودهای غیرفعال بسیار پایین است.
اوراکل به منظور افزایش سرعت عملیات DELETE و INSERT در دستورات SQL این رویه را پیش می گیرد ولی با گذشت زمان فضای حافظه هدر می رود و سرعت SCAN ایندکس کاهش می یابد.
عمل REBUILD کردن یا ساخت مجدد ایندکس برای ایندکس هایی مناسب است که نود های LEAF حذف شده زیادی دارند و برای هر بار دسترسی نیاز به خواندن تعداد زیادی بلاک از ایندکس می باشد زیرا ارتفاع این ایندکس ها به مرور زمان افزایش یافته است. به این ایندکس ها “به هم ریخته” یا FRAGMENTED می گویند.
تعیین ایندکس هایی که می توانند از از عمل REBUILD نفع ببرند به نحوه کاربرد ایندکس نیز بستگی دارد. برای مثال ایندکسی که همیشه به صورت UNIQUE SCAN دسترسی می شود نیاز به REBUILD کردن ندارد. ولی ایندکس هایی که به صورت FAST FULL SCAN، FULL SCAN یا RANGE SCAN دسترسی می شوند از عمل REBUILD نفع می برند.
استفاده از JOB یا برنامه ای که تمام ایندکس های یک اسکیما را به صورت دوره ای و اتوماتیک REBUILD کند روشی مناسب نیست زیرا عملیات REBUILD سربار زیادی دارد و زمانبر است. همچنین با توجه به نکات ذکر شده REBULID کردن بسیاری از ایندکس ها پرفورمنس را بهبود نمی دهد.
بنابراین می بایست با روشی مناسب فقط ایندکس هایی که REBUILD کردن آنها سبب بهبود در پرفورمنس می شود را شناسایی کرد. در ادامه روش شناسایی این دسته از ایندکس ها ارائه می شود.
شناسایی ایندکس های مناسب برای REBUILD
پیشنهاد می گردد ایندکس هایی که تعداد بالایی از نودهای LEAF حذف شده دارند یا ارتفاع ساختمان داده آنها 4 یا بیشتر از 4 می باشد برای REBUILD انتخاب گردند.
برای ایندکس های NORMAL میلیون ها نود می توانند در سه سطح اول درخت درج شوند بنابراین زمانی که یک ایندکس به ارتفاع 4 یا بیشتر می رسد به احتمال زیاد عملیات DELETE منطقی نودها و INSERT شدن نودهای جدید در انتهای ساختنمان داده سبب افزایش ارتفاع آن شده است و می تواند از عمل REBUILD بهره ببرد.
برای دستیابی به آمارهای ساختاری ایندکس ها (تعداد LEAFهای حذف شده، ارتفاع ساختمان داده و …) و قرار دادن آنها در جداول دیتادیکشنری دیتابیس می بایست از دستورات ANALYZE استفاده گردد:
SQL> select count(*) from index_stats;
COUNT(*)
———-
0
SQL> ANALYZE INDEX milad.ind1 COMPUTE STATISTICS;
Index analyzed
SQL> ANALYZE INDEX milad.ind1 VALIDATE STRUCTURE;
Index analyzed
SQL>
SQL> select v.name, v.del_lf_rows “DELETED LEAF BLOCKS” , v.height from index_stats v;
کارشناسان معتقدند ایندکس هایی که شروط زیر را دارند کاندیدای مناسب برای REBUILD هستند:
-ایندکس هایی که 20 درصد یا بیشتر از نودهای LEAF آنها تاکنون حذف شده باشند.
-ایندکس هایی که ارتفاع ساختمان داده آنها 4 یا بیشتر از 4 باشد.
SQL> ANALYZE INDEX milad.IND2 COMPUTE STATISTICS;
Index analyzed
SQL> ANALYZE INDEX milad.IND2 VALIDATE STRUCTURE;
Index analyzed
SQL>
SQL> select name, height, lf_rows, del_lf_rows, round((del_lf_rows/lf_rows)*100,2) “ratio” from index_stats where (lf_rows > 100 and del_lf_rows> 0) and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);
SQL> ALTER INDEX ind2 REBUILD ONLINE;
Index altered
نکته: در صورت LOCK بودن آنالیز جداول، امکان استخراج آمار ایندکس ها وجود نخواهد داشت:
SQL> exec DBMS_STATS.LOCK_TABLE_STATS (‘MILAD’,’SAVABEGH’);
PL/SQL procedure successfully completed
SQL> select table_name, stattype_locked from dba_tab_statistics where owner=’MILAD’ and stattype_locked is not null;
SQL> ANALYZE INDEX milad.ind1 COMPUTE STATISTICS;
ANALYZE INDEX milad.ind1 COMPUTE STATISTICS
ORA-38029: object statistics are locked
SQL>
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS (‘MILAD’,’KARBARAN’);
PL/SQL procedure successfully completed
SQL> select table_name, stattype_locked from dba_tab_statistics where owner=’MILAD’ and stattype_locked is not null;
SQL> ANALYZE INDEX milad.ind1 COMPUTE STATISTICS;
Index analyzed
SQL>
با SCRIPT زیر می توان تمام ایندکس های اسکیمایی که به دیتابیس متصل است را بررسی نمود تا کاندیدای مناسب برای REBUILD شناسایی شوند. البته اجرای این SCRIPT در سیستم های عملیاتی ممکن است سربار زیادی ایجاد کند:
فایل :rebuild.sql
set echo off
set termout off
set verify off
set trimspool on
set feedback off
set heading off
set lines 300
set pages 0
set serverout on
spool analyze_User1_indexes.tmp
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| user ||”’,”’|| table_name ||”’);’ from user_tables order by table_name asc;
begin
for x in ( select index_name from user_indexes where index_type = ‘NORMAL’)
loop
dbms_output.put_line(‘ANALYZE INDEX ‘ || x.index_name || ‘ COMPUTE STATISTICS;’);
dbms_output.put_line(‘ANALYZE INDEX ‘ || x.index_name || ‘ VALIDATE STRUCTURE;’);
dbms_output.put_line(‘select name, height, lf_rows, del_lf_rows, round((del_lf_rows/lf_rows)*100,2) as ratio from index_stats where (lf_rows > 100 and del_lf_rows
> 0)
and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);’);
end loop;
end;
/
select ‘exec DBMS_STATS.LOCK_TABLE_STATS (”’|| user ||”’,”’|| table_name ||”’);’ from user_tables order by table_name asc;
spool off
spool /home/oracle/analyze_User1_index_report.txt
PROMPT NAME | HEIGHT | LF_ROWS | DEL_LF_ROWS | RATIO (del_lf_rows/lf_rows) %
@@/home/oracle/analyze_User1_indexes.tmp
spool off
اجرای rebuild.sql:
[oracle@db ~]$ sqlplus “milad/password”
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 15 12:44:20 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/home/oracle/rebuild.sql
SQL> exit
فایل خروجی کاندیدای مناسب:
بنابراین ایندکس های MILAD.IND2 و MILAD.QRQ4RQ34 که دارای 32 و 67 درصد نودهای حذف شده هستند می توانند از REBUILD بهره ببرند.
نکته: ایندکس هایی که سایز آنها در مقایسه با سایز دیتای جدول بسیار زیاد شده است کاندیدای مناسب برای REBUILD کردن هستند.
نکته: زمانی که عمل REBUILD ایندکس ها انجام می شود با توجه به حذف فیزیکی نودهای غیرفعال، فضای مصرفی ایندکس ها(تعداد بلاک ها) در دیتابیس کاهش می یابد:
SQL> select f.blocks from dba_segments f where f.segment_type=’INDEX’ and f.owner=’MILAD’ and f.segment_name=’IND1′;
BLOCKS
———-
2304
SQL> alter index milad.ind1 rebuild;
Index altered
SQL> select f.blocks from dba_segments f where f.segment_type=’INDEX’ and f.owner=’MILAD’ and f.segment_name=’IND1′;
BLOCKS
———-
8
SQL>
دستورات REBULID کردن ایندکسها
برای REBUILD کردن ایندکس می توان از دستور زیر استفاده نمود:
SQL> ALTER INDEX milad.ind1 REBUILD TABLESPACE users;
نکته: استفاده از کلمه کلیدی TABLESPACE اختیاری می باشد ولی پیشنهاد می گردد TABLESPACE مورد استفاده مشخص شود تا اوراکل به صورت پیش فرض از TABLESPACE دیگر استفاده نکند.
نکته: TABLESPACE که عمل REBUILD در آن انجام می شود می بایست سایز مورد نیاز برای سگمنت های موقت را نیز داشته باشد. بنابراین در بازه زمانی عمل REBUILD به فضای خالی دو برابر سایز ایندکس نیاز خواهیم داشت.
نکته: REBUILD کردن ایندکس ها در یک TABLESPACE جدید با اندازه بلاک بزرگتر می تواند سبب بهبود پرفورمنس شود.
زمانی که از دستور بالا برای REBUILD کردن ایندکس استفاده می شود، اوراکل تمام DMLهایی که در حین اجرای REBUILD برای داده های ایندکس شده آن جدول ارسال می شود را تا زمان اتمام REBUILD بلاک می کند. ولی اگر در دستور REBUILD از کلمه کلیدی ONLINE استفاده گردد این دستورات DML نیز می توانند همزمان انجام شوند البته همچنان عمل بلاک برای زمان بسیار کوتاهی انجام می گردد.
SQL> ALTER INDEX milad.ind1 REBUILD ONLINE;
در اوراکل 11g و بالاتر، دیتابیس ابتدا منتظر پایان یافتن دستورات در حال اجرای DML مرتبط با ایندکس می شود و سپس اجرای دستور REBUILD را شروع می کند. در زمان اجرا شدن دستور REBUILD نیز هیچ دستور DML بلاک نخواهد شد. اوراکل این کار را با ساخت یک SNAPSHOT LOG برای نگه داری DMLها انجام می دهد و پس از اتمام عملیات REBUILD، این DMLها بر روی ایندکس اعمال می شوند.
ایندکسها را می توان به صورت پارالل REBUILD کرد. ایندکسی که REBUILD کردن آن زمان زیادی می برد را می توان در یک سرور با CPU_COUNT بالا و درجه موازی سازی مناسب با سرعت زیاد انجام داد.
SQL> ALTER INDEX milad.ind1 REBUILD PARALLEL 63;
نکته: بعد از REBUILD ایندکس به صورت پارالل باید درجه موازی سازی به حالت اولیه تنظیم شود.
SQL> ALTER INDEX milad.ind1 NOPARALLEL;
Index altered
نکته: می توان از حالت NOLOGGING در REBUILD کردن ایندکس استفاده نمود که سبب می شود عملیات تا 30 درصد سریعتر انجام گردد.
SQL> alter index milad.ind1 rebuild online parallel 63 nologging tablespace users;
Index altered
SQL> select a.degree,a.logging from dba_indexes a where a.index_name=’IND1′ and a.owner=’MILAD’;
SQL> alter index milad.ind1 noparallel;
Index altered
SQL> alter index milad.ind1 logging;
Index altered
SQL>
نکته: در مقایسه با عمل REBUILD، حذف و ساخت مجدد یک ایندکس زمانبر خواهد بود و سربار بیشتری ایجاد می کند:
زمانی که ایندکس را DROP کرده و دوباره می سازیم:
1.جدول FULL TABLE SCAN می شود وکلیدها و ROWIDها استخراج می شوند.
2.کلیدها و ROWIDها SORT می شوند.
3.با توجه به مرحله 1 و 2 ساختمان داده ایندکس تشکیل می شود.
زمانی که ایندکس REBUILD می شود:
1.ایندکس موجود برای دستیابی به کلیدها پیمایش می شود.
2.داده های ایندکس و ساختار جدید آن در سگمنت موقت قرار می گیرد.
3.ایندکس قدیمی حذف می شود و نام سگمنت موقت برابر با ایندکس قدیمی می شود.
اگر مرحله 2 در REBUILD ایندکس به صورت کامل انجام نشود اوراکل ایندکس قدیمی را حذف نمی کند بنابراین در عملیات REBULID هیچ ایندکسی از بین نمی رود.
