A few days ago we had a problem with the performance of several queries and we found that the problem is with queries which take different index than should be for some not bind variables.
So we decide set invisible parameter for this index. Before that I checked what queries use this index using v$sql_plan and I ran:
The list of queries that used this index there was one important query and after setting invisible for this index execution plan for this query start use different index and elapsed time increased almost 100 times.
So we decide set invisible parameter for this index. Before that I checked what queries use this index using v$sql_plan and I ran:
alter index index_owner.index_name INVISIBLE;
The list of queries that used this index there was one important query and after setting invisible for this index execution plan for this query start use different index and elapsed time increased almost 100 times.
There was no possibility to add hint USE_INVISIBLE_INDEXES to this query directly, so I hat to use sql profile:
DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr('USE_INVISIBLE_INDEXES'), name => 'PROFILE_&SQL_ID', force_match => TRUE );
END;
/
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr('USE_INVISIBLE_INDEXES'), name => 'PROFILE_&SQL_ID', force_match => TRUE );
END;
/
