ORA-00001: unique constraint (SYS.WRI$_ADV_SQLT_PLANS_PK) violated

Last week it was my honor to dive into some performance problems in a data warehouse. The database was running 10.2.0.4 and should have been upgraded a long time ago. While analyzing heavy resource consuming SQL I prefer to use dbms_sqltune to do the analyzing part. In this case, the most costly statement ran for hours and hours, was quite complex and needed some serious attention.

This is the SQL that is playing a role. Not difficult, plain old SQL nothing special

CREATE table Z as SELECT * from all_objects where 1 = 2;

--/ 

--Create the error logging table.

BEGIN          

DBMS_ERRLOG.create_error_log (dml_table_name => 'z');

end;

/

insert /*+ append nologging */into z select * from all_objects LOG ERRORS INTO err$_z REJECT LIMIT UNLIMITED;

col sql_id new_value this_one noprint

select sql_id from v$sql where sql_text like 'insert /*+ append nologging */into z select * fr%';

--/

declare        

task varchar2(30);

begin        

task:= dbms_sqltune.CREATE_TUNING_TASK ( sql_id => '&this_one' );        

dbms_output.put_line (task);        

dbms_sqltune.execute_tuning_task(task);

end;

/

Excution in Oracle version 10.2.0.5:

an other interesting error message is when running explain plan using a jdbc client:

ORA-01436: CONNECT BY loop in user data

As is the case in the above example, the error disappears when you remove the LOG ERRORS clause.

execution in 11.2.0.3:

 

Both problems are gone in 11.2.0.3

An other reason to upgrade ASAP.

While searching for a solution, I found similar error reports for 11.2.0.1 and messages that the bug was solved in 11.2.0.2.

I hope this helps.