Workaround for ORA-600/ORA-7445 with SQL Repair Advisor

By | July 26th, 2011 | Category: 11gR2, Oracle Database | No Comments »

From time to time we hit Errors like ORA-600 or ORA-7445, which are triggered by specific SQL statements. For these cases, Oracle developed the SQL Repair Advisor. This is a short demonstration of utilizing the SQL Repair Advisor to avoid ORA-7445 caused by a SQL statement.

SQL and Error:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2;
 
ERROR at line 6:
ORA-03113: end-of-file ON communication channel
Process ID: 7746
SESSION ID: 737 Serial NUMBER: 9823
 
Tue Jul 26 15:19:41 2011
Exception [TYPE: SIGSEGV, Address NOT mapped TO object] [ADDR:0xA] [PC:0x888A3C9, xtyqbcb()+413] ...
ORA-07445: exception encountered: core dump [xtyqbcb()+413] [SIGSEGV] [ADDR:0xA] [PC:0x888A3C9]...

Execution of SQL Repair Advisor:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

DECLARE
  rep_out CLOB;
  t_id VARCHAR2(50);
BEGIN
  T_ID := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK( 
  sql_text => q'#SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2#', 
  task_name => 'ORA7445-xtyqbcb', 
  problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR
  );
  DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);
  rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);
  DBMS_OUTPUT.PUT_LINE ('Report : ' || rep_out);
END;
/
 
<strong>Output:</strong>
 
Report : GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ORA7445-xtyqbcb
Tuning Task Owner  : SYS
Workload TYPE      : Single SQL Statement
Scope              : COMPREHENSIVE
TIME LIMIT(seconds):
1800
Completion STATUS  : COMPLETED
Started at         : 07/26/2011 15:46:38
Completed at       : 07/26/2011 15:46:38
 
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 39ac71hjcn0rt
...
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1
finding)
-------------------------------------------------------------------------------
 
1- SQL Patch Finding (see EXPLAIN plans SECTION below)
------------------------------------------------------
  A potentially better execution plan was found FOR this statement.
 
  Recommendation
 
 
--------------
  - Consider accepting the recommended SQL patch.
    EXECUTE dbms_sqldiag.accept_sql_patch(task_name =>
            'ORA7445-xtyqbcb', task_owner => 'SYS', REPLACE => TRUE);
 
  Rationale
  ---------
    Recommended plan WITH hash VALUE 1503213169 has NUMBER OF ROWS 7, CHECK
 
SUM 13925076401, execution TIME 264 AND 1972 buffer gets
 
-------------------------------------------------------------------------------
 
 
 
PL/SQL PROCEDURE successfully completed.

Activation:

1
2
3
4
5
6

 BEGIN dbms_sqldiag.accept_sql_patch(
      task_name =>'ORA7445-xtyqbcb', 
      task_owner => 'SYS', 
      REPLACE => TRUE);
END;
/

Test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2;
 
      JAHR      MONAT ARBEITSTAGE CUST_FLAG
---------- ---------- ----------- ----------------
      2011          1          20                0
 
 
7 ROWS selected.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));
 
PLAN_TABLE_OUTPUT
---------------------
SQL_ID  5wdztx81x0r8f, child NUMBER 0
-------------------------------------
 
 
Plan hash VALUE: 1503213169
 
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |       |       |       |   591 (100)|          |
|*  1 |  TABLE ACCESS FULL | MD1              |     1 |    12 |       |     3   (0)| 00:00:01 |
|   2 |  SORT GROUP BY     |                  | 27402 |   294K|  2776K|   591  (12)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| TAB1             |   117K|  1260K|       |   369  (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
 
...
 
 
Note
-----
   - SQL patch "SYS_SQLPTCH_013166b4fb2a0001" used FOR this statement
 
 
66 ROWS selected.

Removal of SQL Patch:

1

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH ('SYS_SQLPTCH_013166b4fb2a0001');

Deletion of SQL Diag Task:

1

 EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ('ORA7445-xtyqbcb');

Leave Comment