در بسیاری از محیطهای دیتابیسی مشاهده می‌شود که ایندکس‌هایی وجود دارند که سال‌هاست هیچ کوئری از آنها استفاده نمی‌کند، اما همچنان در سیستم باقی مانده‌اند. این ایندکسها نه تنها مفید نیستند، بلکه باعث کاهش سرعت درج و حذف داده‌ها، افزایش حجم دیتابیس و مصرف منابع در هنگام آمارگیری یا بکاپ‌گیری می‌شوند.
در این مقاله با استفاده از قابلیت ALTER INDEX … MONITORING USAGE در اوراکل، بررسی میکنیم که کدام ایندکسها واقعاً مورد استفاده قرار می‌گیرند و کدام‌ها فقط بار اضافی هستند.
هدف آزمایش
-
ساخت جدولی با بیش از 10 میلیون ردیف (حجم حدود 600 مگابایت).
-
ایجاد دو ایندکس: یکی واقعی و مفید، دیگری بی‌ارزش روی ستونی با مقدار ثابت.
-
بررسی رفتار Optimizer در انتخاب بین Index Scan و Full Table Scan.
مرحله 1 – ساخت جدول تستی بزرگ
CREATE TABLE test_index_usage AS
SELECT
ROWNUM AS id,
owner,
segment_name,
segment_type,
bytes,
blocks,
'CONSTANT' AS fixed_col
FROM dba_segments
;
مقدار ثابت برای تست ایندکس بی‌فایده ساخته شده است. برای افزایش حجم جدول دستور زیر را تا رسیدن تعداد به بالای 10 میلیون رکورد ادامه میدهیم.
INSERT INTO test_index_usage
SELECT * FROM test_index_usage;
commit;
مرحله 2 – ایجاد ایندکسها
CREATE INDEX idx_test_segname ON test_index_usage(segment_name);
CREATE INDEX idx_test_fixed ON test_index_usage(fixed_col);
مرحله 3 – فعال‌سازی مانیتورینگ استفاده از ایندکسها
ALTER INDEX idx_test_segname MONITORING USAGE;
ALTER INDEX idx_test_fixed MONITORING USAGE;
در این مرحله یک نگاه ابتدایی به استفاده یا عدم استفاده از ایندکسی که گفتیم مانیتور شود می اندازیم
select * from dba_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- -------------------- ------------------------------ --- --- ------------------- -------------------
SYS IDX_TEST_SEGNAME TEST_INDEX_USAGE YES NO 10/07/2025 05:15:04
SYS IDX_TEST_FIXED TEST_INDEX_USAGE YES NO 10/07/2025 05:15:05
همانطور که مشخص است، در این مرحله هنوز کسی از ایندکس استفاده نکرده است.
حالا جستجوهایی را اجرا می کنیم تا ببینیم وضعیت استفاده از ایندکسها به چه صورت تغییر پیدا میکند.
دستور SET AUTOTRACE ON EXPLAIN در محیط SQL*Plus باعث می‌شود اوراکل به‌صورت خودکار طرح اجرای کوئری (Execution Plan) را پس از اجرای هر دستور SQL نمایش دهد.
این دستور کوئری را دوبار اجرا نمی‌کند؛ بلکه تنها نشان می‌دهد که Optimizer چگونه تصمیم گرفته داده‌ها را بخواند (مثلاً با استفاده از ایندکس یا Full Table Scan).
این قابلیت در بررسی عملکرد و تست رفتار ایندکسها بسیار کاربردی است، چون به‌سرعت می‌توان دید که اوراکل در اجرای کوئری از ایندکس استفاده کرده یا خیر.
SELECT COUNT(*) FROM test_index_usage WHERE segment_name = 'SYS_LOB000000123456789$' and BYTES=4 and OWNER='HR';
COUNT(*)
----------
0
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 69626570
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 580 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 31 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_INDEX_USAGE | 3 | 93 | 580 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TEST_SEGNAME | 2204 | | 10 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BYTES"=4 AND "OWNER"='HR')
3 - access("SEGMENT_NAME"='SYS_LOB000000123456789$')
همانطور که انتظار داشتیم، به علت selectivity بالا، از ایندکس استفاده شد. با اینکه بجز ستون ایندکس شده، دو ستون دیگر نیز وجود داشت.
حالا جستجوی دیگر را اجرا می کنیم:
SELECT COUNT(*) FROM test_index_usage WHERE fixed_col = 'CONSTANT' and BYTES=4 and OWNER='HR';
COUNT(*)
----------
0
Elapsed: 00:00:00.90
Execution Plan
----------------------------------------------------------
Plan hash value: 3401396856
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 24663 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | TABLE ACCESS FULL| TEST_INDEX_USAGE | 13960 | 272K| 24663 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BYTES"=4 AND "OWNER"='HR' AND "FIXED_COL"='CONSTANT')
همانطور که مشخص است، با اینکه این ستون دارای ایندکس بود، ولی از آن استفاده نشد.
در محیط عملیاتی این فرصت و امکان وجود ندارد که در مورد تک تک جستجو ها این بررسی صورت پذیرد. پس از روش monitoring usage استفاده می شود.
حالا جستجو قبلی را دوباره اجرا می کنیم:
select * from dba_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- -------------------- ------------------------------ --- --- ------------------- -------------------
SYS IDX_TEST_SEGNAME TEST_INDEX_USAGE YES YES 10/07/2025 05:15:04
SYS IDX_TEST_FIXED TEST_INDEX_USAGE YES NO 10/07/2025 05:15:05
می بینیم که قسمت used این ایندکس فعال شده است.
در محیطهای عملیاتی ، باید یک دوره کامل business را در نظر بگیرید. مثلاً برخی از جستجوها فقط در پایان هفته یا ماه اچرا می شوند. بعد از این دوره در مورد استفاده یا عدم استفاده از ایندکس و بعد از آن پاک کردن آن ایندکس باید تصمیم گیری کنیم. در پایان کار با روش زیر ، ایندکسها را از حالت مانیتور خارج می کنیم:
ALTER INDEX idx_test_segname MONITORING USAGE;
ALTER INDEX idx_test_fixed MONITORING USAGE;
و ایندکسی که استفاده نمی شود را پاک میکنیم.