Link to home
Start Free TrialLog in
Avatar of Billy Ma
Billy MaFlag for Hong Kong

asked on

Oracle SQL Repair Advisor

I am doing the practice of Oracle course.
However, I do not understand why the simple delete statement shown below can cause the system crash?

delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

It only said because the original plan will need to do full table scan....but if you see the execution plan after applying the SQL Patch recommended by the SQL Repair Advisor, the execution plan still have TABLE ACCESS FULL

i cannot see any differences.
I hope someone can explain.
grant connect, resource, dba, query rewrite, unlimited tablespace to scott identified by tiger;
alter user scott account unlock;

drop table t;

create table t(a varchar(40), b number, c varchar(240), d varchar(240));

insert into t values('a', 1, 'b', 'c');
insert into t values('a', 1, 'x', 'c');
insert into t values('e', 2, 'f', 'g');

alter session set "_fix_control"="5868490:OFF";

explain plan for delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

select plan_table_output from table(dbms_xplan.display('plan_table',null));

delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

-------------------------------------------------------------

exec dbms_sqldiag.drop_diagnosis_task('sqldiag_bug_5869490');

variable rep_out clob;

declare
  t_id            varchar2(50);
begin
  t_id := dbms_sqldiag.create_diagnosis_task(
    sql_text => 'delete from t t1 where t1.a = ''a'' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d)',
    task_name => 'sqldiag_bug_5869490',
    problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);

  dbms_sqltune.set_tuning_task_parameter(
    t_id,
    '_SQLDIAG_FINDING_MODE',
    dbms_sqldiag.SQLDIAG_FINDINGS_FILTER_PLANS);

  dbms_output.put_line ('t_id = ' ||  t_id);

  dbms_sqldiag.execute_diagnosis_task (t_id);

  dbms_output.put_line ('executed t_id = ' ||  t_id);

  :rep_out := dbms_sqldiag.report_diagnosis_task (t_id, DBMS_SQLDIAG.TYPE_TEXT);

end;
/

print rep_out

execute dbms_sqldiag.accept_sql_patch(task_name => 'sqldiag_bug_5869490', task_owner => 'SCOTT', replace => TRUE);

explain plan for delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

select plan_table_output from table(dbms_xplan.display('plan_table',null,'advanced'));

delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Billy Ma

ASKER

sorry, I cannot give u Grade A as this SQL Statement does cause the Oracle database crash, that's why they have implemented a fix for this.
I have got no idea why this statement crash the database, this is the main question.