The "ORA-600 [qksanGetTextStr:1]" in 12.1.0.2 and workaround

One of the situations we had after we upgraded to 12c - we had been receiving ORA 600 [qksanGetTextStr:1] errors. After looking at MOS, found that this was a bug and the patch for that is 19141838. This is due to some glitch in SQL Plan Baselines. One workaround is disabling that completely (ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false).  However,  it made no sense for us, we could not do that, we have been relying heavily on SQL Plan Baselines since 11gR2.

After we applied the patch (19141838), the problem gone for one database but had been hitting errors from the other database which we also upgraded to 12c. This was mostly happened for two SQL fortunately out of thousands. While support trying to understand why patch did not work for the other database, my workaround for this (other than completely disabling SQL Plan Baselines) was below -

1. Find the error in alert* file (Oracle alert log file, which should be in $ORACLE_BASE/diag/rdbms/.... )

2. Locate the Incident details trace file from alert log

3. Check the trace file and find the Current SQL for the session (the problem SQL)

4. Find the SQL Handle for the problem SQL - have to match by text most of the time (be careful about picking the right SQL) - if you don't find the SQL by SQL ID. For example -

SELECT signature, sql_handle, plan_name, sql_text
FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id = '&sql_id' );

OR, (if no output for the above)

SELECT signature, sql_handle, plan_name, sql_text
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT rec.id, rec.mar_id, rec.client, rec.client_display_name%'
ORDER BY signature, sql_handle;


5. Drop all plans (using SQL Handle) from the baselines associated with the SQL -


SET SERVEROUTPUT ON;

DECLARE
l_plans_dropped  PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => '&sql_handle',
plan_name  => NULL);
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/


***