Symptom
Sql execution is too long
Cause
After automatic statistics collection on table, the execution plan changed. It is no more picking the index and doing full table scan.
solution
Step 1. Find the SQL ID that needs a profile,
in this example: 50ux45v27k6ab
Step 2. Find the hint that introduces a good plan
In this example: INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)
Step 3. Run following anonymous PLQSL block
DECLARE
cl_sql_text CLOB;
BEGIN
SELECT sql_text
INTO cl_sql_text
FROM gv$sqlarea where sql_id = '50ux45v27k6ab' and rownum = 1;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text,
profile => sqlprof_attr(‘INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)'),
name => 'USE_PK_FOR_UPDATE',
category => 'DEFAULT',
force_match => TRUE);
end;
/
Hope this helps! Rupam
