Oracle 21c DBA_INDEX_USAGEを使ってインデックスが利用されているのか調べる方法

環境
Windows 11 Pro 21H2 64bit
Oracle Database 21c Express Edition Release 21.0.0.0.0
A5:SQL Mk-2

説明
DBA_INDEX_USAGEについて
DBA_INDEX_USAGEは、各索引の累積統計を示します。
NAME 索引名
OWNER 索引の所有者

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
TOTAL_ACCESS_COUNT 索引にアクセスされた合計回数
TOTAL_ROWS_RETURNED 索引によって戻された合計行。
TOTAL_EXEC_COUNT 索引が関与した実行の合計
TOTAL_ACCESS_COUNT 索引にアクセスされた合計回数 TOTAL_ROWS_RETURNED 索引によって戻された合計行。 TOTAL_EXEC_COUNT 索引が関与した実行の合計
TOTAL_ACCESS_COUNT 索引にアクセスされた合計回数
TOTAL_ROWS_RETURNED 索引によって戻された合計行。
TOTAL_EXEC_COUNT 索引が関与した実行の合計

SQL構文

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
select
diu.OBJECT_ID
, diu.NAME
, diu.OWNER
, diu.TOTAL_ACCESS_COUNT
, diu.TOTAL_EXEC_COUNT
, diu.TOTAL_ROWS_RETURNED
, diu.LAST_USED
, cft.TABLE_OWNER
, cft.TABLE_NAME
, do.CREATED
, do.LAST_DDL_TIME
from
DBA_INDEX_USAGE diu
inner join dba_users du
on diu.OWNER = du.USERNAME
and du.COMMON = 'NO'
inner join dba_objects do
on diu.OWNER = do.OWNER
and diu.NAME = do.OBJECT_NAME
and do.OBJECT_TYPE = 'INDEX'
inner join dba_indexes cft
on diu.OWNER = cft.OWNER
and diu.NAME = cft.INDEX_NAME
order by
diu.LAST_USED desc;
select diu.OBJECT_ID , diu.NAME , diu.OWNER , diu.TOTAL_ACCESS_COUNT , diu.TOTAL_EXEC_COUNT , diu.TOTAL_ROWS_RETURNED , diu.LAST_USED , cft.TABLE_OWNER , cft.TABLE_NAME , do.CREATED , do.LAST_DDL_TIME from DBA_INDEX_USAGE diu inner join dba_users du on diu.OWNER = du.USERNAME and du.COMMON = 'NO' inner join dba_objects do on diu.OWNER = do.OWNER and diu.NAME = do.OBJECT_NAME and do.OBJECT_TYPE = 'INDEX' inner join dba_indexes cft on diu.OWNER = cft.OWNER and diu.NAME = cft.INDEX_NAME order by diu.LAST_USED desc;
select
  diu.OBJECT_ID
  , diu.NAME
  , diu.OWNER
  , diu.TOTAL_ACCESS_COUNT
  , diu.TOTAL_EXEC_COUNT
  , diu.TOTAL_ROWS_RETURNED
  , diu.LAST_USED
  , cft.TABLE_OWNER
  , cft.TABLE_NAME
  , do.CREATED
  , do.LAST_DDL_TIME 
from
  DBA_INDEX_USAGE diu 
  inner join dba_users du 
    on diu.OWNER = du.USERNAME 
    and du.COMMON = 'NO' 
  inner join dba_objects do 
    on diu.OWNER = do.OWNER 
    and diu.NAME = do.OBJECT_NAME 
    and do.OBJECT_TYPE = 'INDEX' 
  inner join dba_indexes cft 
    on diu.OWNER = cft.OWNER 
    and diu.NAME = cft.INDEX_NAME 
order by
  diu.LAST_USED desc;

 

Oracle 21c

Posted by arkgame