sqloracle-databaseindexingplsqloracle11g

Oracle Database: Find Missing Indexes for Performance tuning


We are currently doing database performance tuning. Does Oracle database have any DMV Select Queries to find missing indexes directly?

Microsoft SqlServer has this below: https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

-- Missing Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Solution

  • This is one good resource: https://www.dba-scripts.com/scripts/diagnostic-and-tuning/troubleshooting/find-missing-index/

    If anyone has more answers, feel free to post

    ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:'
    
    select * from (
      select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
      from sys.col_usage$ u,
           sys.obj$ o,
           sys.col$ c,
           sys.user$ us
      where u.obj# = o.obj#
      and   u.obj# = c.obj#
      and   us.user# = o.owner#
      and   u.intcol# = c.col#
      and   us.name='&SCHEMA_NAME'
      and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
      and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
      order by u.equality_preds+u.equijoin_preds desc)
    WHERE rownum <11;