Archive for July 2011

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');

Performance Degradation for Query on DBA_SEGMENTS.BYTES in 11gR2

By | July 18th, 2011 | Category: 11gR2, MetaLink, Oracle Database, Performance Tuning | 4 comments

I have been troubleshooting a performance issue in a DWH environment, which is quite interesting. It was a query on DBA_SEGMENTS in 11gR2 with lots of partitions and it was taking almost 10 minutes versus only a few seconds in 10gR2. The problem could be stripped downt to this SQL query:

select bytes from dba_segments

The dba_segment view can be seen in DBA_VIEWS:

SELECT ...
       decode(bitand(segment_flags, 131072), 131072, blocks,
           (decode(bitand(segment_flags,1),1,
            <strong>dbms_space_admin.segment_number_blocks</strong>(tablespace_id, relative_fno,
            header_block, segment_type_id, buffer_pool_id, segment_flags,
            segment_objd, blocks), blocks)))*blocksize,
      ...
FROM sys_dba_segs

The response time was dominated by Waits for “db file sequential reads” taking almost all of the response time.

11gR2:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    12563     23.17     546.74     157447     580414          1      188421
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    12565     23.17     546.74     157447     580414          1      188421

10gR2:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.06          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     5104      1.77       2.54          0      96688          0       76542
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5106      1.83       2.61          0      96688          0       76542

The 10046 Trace File contains more data regarding those waits:

WAIT #4: nam='db file sequential read' ela= 134 file#=21 block#=2313482 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 120 file#=15 block#=2128019 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 128 file#=21 block#=2313490 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 372 file#=21 block#=2313498 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 108 file#=21 block#=2313506 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 130 file#=21 block#=2313514 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 132 file#=21 block#=2313522 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 121 file#=21 block#=2313530 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 158 file#=15 block#=2128003 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 146 file#=21 block#=2313538 blocks=1 obj#=-1 
WAIT #4: nam='db file sequential read' ela= 116 file#=21 block#=2313546 blocks=1 obj#=-1 
(Output modified for formatting)

Strange thing that obj# is -1. I made some block dumps of those blocks and found out that all those blocks (file#/block#) have something in common: They were all segment header blocks. Another strange thing was that when the query was executed a second time, all those I/O requests were performed again – so no caching.

At that point, I opened a Support service request. After a couple of weeks, Support suggested running this for each tablespace of the user segments:

EXEC  dbms_space_admin.TABLESPACE_FIX_SEGMENT_EXTBLKS('<tablespace_name>');

Finally this fixed the problem. Now, we are trying to find out why this was necessary for a database, that was freshly created with dbca 11.2.0.2 from the seeded template and filled with data pump import.