J’avoue… La partie la plus difficile consiste à trouver la requête SQL qui « explose » avec un coredump et une ORA-07445 sur une base 11.1.0.7. Mais vous y arriverez; il est d’ailleurs probable que la même requête ne fonctionne sur la version 10.2.0.4 !
Ce post présente comment vous pouvez « développer », vous-même, un patch SQL pour ce genre de situations avec Oracle 11g. Avant de tomber en émoi, il ne s’agit en fait « que » de changer le plan de la requête. Mais, quand même, c’est un petit exploit réalisé par le SQL Repair Advisor puisqu’il est semble-t-il capable de détecter qu’une requête SQL échoue avec un coredump, sans, lui-même faillir à son devoir. Mais passons à l’exemple!
1. Une requête; une ORA-07445
ORA-07445 est, comme vous le savez, une erreur générique qui signifie qu’un process génère un core dump. Il s’agit de trouver le bug/la requête qui permet d’arriver à ce résultat; voilà un exemple pour 11.1.0.7 qui s’appuie sur le bug : « 7328856 – Dump (evaopn2) from INLIST on virtual expression used in indexes » et nécessite que vous ayez 2 indices fonctions utilisant les mêmes colonnes et une série de clause OR ou IN; Voici comment arriver au résultat voulu :
alter session set "_replace_virtual_columns" = false;
create table foo (id number,
d1 nvarchar(10),
d2 nvarchar(10));
insert into foo values (1,
translate('a' using char_cs),
translate('b' using char_cs));
commit;
create index foo_i1 ON foo (
nlssort("D1",'nls_sort=''BINARY_CI'''),
nlssort("D2",'nls_sort=''BINARY_CI'''));
create index foo_i2 ON foo (
nlssort("D2",'nls_sort=''BINARY_CI'''));
alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
SELECT ID
FROM foo
WHERE (D2 = 'b')
AND (D2 = 'x' OR D2 = 'y' OR D1 = 'z');
SELECT ID
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8728
Session ID: 143 Serial number: 47
Si vous regardez dans le répertoire de traces de votre instance, vous trouverez un fichier de trace et un coredump; Voici ce qui apparait dans le fichier de trace:
*** 2008-12-21 01:25:02.762
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0xE916F4F, evaopn3.()+147]
Incident 169391 created, dump file: /u01/app/oracle/diag/rdbms/black/BLACK/incident/incdir_169391/BLACK_ora_8728_i169391.trc
ORA-07445: exception encountered: core dump [evaopn3.()+147] [SIGSEGV] [ADDR:0x0] [PC:0xE916F4F] [Address not mapped to object] []
ssexhd: crashing the process...
Shadow_Core_Dump = PARTIAL
2. Utiliser SQL Repair Advisor
L’utilisation de SQL Repair Advisor n’a rien d’exceptionnelle; il suffit de suivre la documentation de DBMS_SQLDIAG:
connect / as sysdba
alter session set "_replace_virtual_columns" = false;
alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
set pages 1000
set lines 180
set longchunksize 200
set serveroutput on buffer 300000
DECLARE
rep_out CLOB;
t_id VARCHAR2(50);
BEGIN
t_id :=
DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text => 'SELECT ID FROM foo WHERE (D2 = ''b'')'||
' AND (D2 = ''x'' OR D2 = ''y'' OR D1 = ''z'')',
task_name => 'error_task',
problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_EXECUTION_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;
/
Le résultat mérite un intérêt particulier:
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : gu0m88f5d6vat
SQL Text : SELECT ID FROM foo WHERE (D2 = 'b') AND (D2 = 'x' OR D2 = 'y' OR
D1 = 'z')
-------------------------------------------------------------------------------
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 => 'error_task',
task_owner => 'SYS', replace => TRUE);
Rationale
---------
Recommended plan with hash value 1245013993 has number of rows 1, check
sum 2342552567, execution time 0 and 3 buffer gets
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Strategy execution Original plan failed with error code 602 and error
message
- Recommended plan with hash value 1245013993 has number of rows 1, check sum
2342552567, execution time 0 and 3 buffer gets
- Alternative plan with hash value 1245013993 has number of rows 1, check sum
2342552567, execution time 0 and 3 buffer gets
- Plan for strategy outline based feature toggle with plan directive (hint)
NO_INDEX(@"SEL$1" "FOO"@"SEL$1" "FOO_I2") has cost 2 with plan hash value
1245013993
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_XMLINDEX_REWRITE_IN_SELECT has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_XMLINDEX_REWRITE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_XML_DML_REWRITE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_XML_QUERY_REWRITE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NOPARALLEL has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_UNNEST has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NOMERGE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NOREWRITE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
STAR_TRANSFORMATION TEMP_DISABLE has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_STAR_TRANSFORMATION has cost 1 with plan hash value 1799930839
- Plan for strategy avoid umbrella features with plan directive (hint)
NO_QUERY_TRANSFORMATION has cost 1 with plan hash value 1799930839
- Plan for strategy rule optimizer has cost 0 with plan hash value 1245013993
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.0.0') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.0.4') has cost 1 with plan hash value
1799930 839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.0.5') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.0.6') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.1.0') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled oggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.1.5') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.1.6') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('8.1.7') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('9.0.0') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('9.2.0') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.1.0') has cost 1 with plan hash value
1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.1.0.3') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.1.0.4') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.1.0.5') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.2.0.1') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.2.0.2') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.2.0.3') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('10.2.0.5') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('11.1.0.6') has cost 1 with plan hash
value 1799930839
- Plan for strategy optimizer features enabled toggle with plan directive
(hint) OPTIMIZER_FEATURES_ENABLE('11.1.0.7') has cost 1 with plan hash
value 1799930839
- Plan for strategy validation history has cost 1 with plan hash value
1799930839
- Validation [rule: predicate selectivity lies between 0.0 and 1.0, status:
pass]
- Validation [rule: all the columns in predicates are in frocol chain,
status: pass]
- Validation [rule: all the columns in frocol chain are referenced, status:
pass]
- Validation [rule: all the expression in the frocol chain are columns,
status: pass]
- Validation [rule: integrity of a qbcdef, status: pass]
- Validation [rule: integrity of the statement, status: pass]
- Strategy execution compilation history failed with error code 602 and error
message ORA-00602: internal programming exception
Process ID:
Session ID: 0 Serial number: 0
- Feature 'Fix-#6748058' was accepted because of bug-fix control
- Feature 'Fix-#6503543' was accepted because of bug-fix control
- Feature 'Fix-#6468287' was accepted because of bug-fix control
- Feature 'Fix-#6365442' was accepted because of bug-fix control
- Feature 'Fix-#6007259' was accepted because of bug-fix control
- Feature 'Fix-#6377505' was accepted because of bug-fix control
- Feature 'Fix-#3426050' was accepted because of bug-fix control
- Feature 'Fix-#6239909' was accepted because of bug-fix control
- Feature 'Fix-#4168080' was accepted because of bug-fix control
- Feature 'Fix-#5844495' was accepted because of bug-fix control
- Feature 'Fix-#5741044' was accepted because of bug-fix control
- Feature 'Fix-#5371452' was accepted because of bug-fix control
- Feature 'Fix-#2492766' was accepted because of bug-fix control
- Feature 'Fix-#5882954' was accepted because of bug-fix control
- Feature 'Fix-#5259048' was accepted because of bug-fix control
- Feature 'Fix-#5391505' was accepted because of bug-fix control
- Feature 'Fix-#4279274' was accepted because of bug-fix control
- Feature 'Fix-#5547895' was accepted because of bug-fix control
- Feature 'Fix-#5112460' was accepted because of bug-fix control
- Feature 'Fix-#5396162' was accepted because of bug-fix control
- Feature 'Fix-#5383891' was accepted because of bug-fix control
- Feature 'Fix-#5384335' was accepted because of bug-fix control
- Feature 'Fix-#5302124' was accepted because of bug-fix control
- Feature 'Fix-#5240607' was accepted because of bug-fix control
- Feature 'Fix-#4717546' was accepted because of bug-fix control
- Feature 'Fix-#4600710' was accepted because of bug-fix control
- Feature 'Fix-#4615392' was accepted because of bug-fix control
- Feature 'Fix-#4722900' was accepted because of bug-fix control
- Feature 'Fix-#4904838' was accepted because of bug-fix control
- Feature 'Fix-#4273361' was accepted because of bug-fix control
- Feature 'Fix-#4545802' was accepted because of bug-fix control
- Feature 'Fix-#4887636' was accepted because of bug-fix control
- Feature 'Fix-#4386734' was accepted because of bug-fix control
- Feature 'Fix-#4708389' was accepted because of bug-fix control
- Feature 'Fix-#4728348' was accepted because of bug-fix control
- Feature 'Fix-#4556762' was accepted because of bug-fix control
- Feature 'Fix-#4663698' was accepted because of bug-fix control
- Feature 'Fix-#4584065' was accepted because of bug-fix control
- Feature 'Fix-#4602374' was accepted because of bug-fix control
- Feature 'Fix-#4554846' was accepted because of bug-fix control
- Feature 'Fix-#4488689' was accepted because of bug-fix control
- Feature 'Dynamic sampling' was accepted because of execution plan
- Feature 'All rows (optimizer mode)' was accepted because of execution plan
- Feature 'Index range scan' was accepted because of execution plan
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1799930839
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FOO_I2 | 1 | | 1 (0)| 00:00:01 |
------------------------------ --------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / FOO@SEL$1
2 - SEL$1 / FOO@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("D2",'nls_sort=''BINARY_CI''')=HEXTORAW('006200') OR
NLSSORT("D1",'nls_sort=''BINARY_CI''')=HEXTORAW('007A00') )
2 - access(NLSSORT("D2",'nls_sort=''BINARY_CI''')=HEXTORAW('006200') )
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
2 - "FOO".ROWID[ROWID,10], NLSSORT("D2",'nls_sort=''BINARY_CI''')[RAW,170]
Note
-----
- dynamic sampling used for this statement
2- Original With Adjusted Cost
------------------------------
Plan hash value: 1245013993
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FOO | 1 | 37 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / FOO@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("D2",'nls_sort=''BINARY_CI''')=HEXTORAW('006200')
AND (NLSSORT("D2",'nls_sort=''BINARY_CI''')=HEXTORAW('006200') OR
NLSSORT("D1",'nls_sort=''BINARY_CI''')=HEXTORAW('007A00') ))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement
-------------------------------------------------------------------------------
3. Implémenter le patch
Il suffit pour cela, de suivre les instructions:
exec dbms_sqldiag.accept_sql_patch(task_name => 'error_task',-
task_owner => 'SYS', replace => TRUE);
Et la requête SQL fonctionne désormais sans erreur:
connect / as sysdba
alter session set "_replace_virtual_columns" = false;
alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
SELECT ID FROM foo WHERE (D2 = 'b') AND (D2 = 'x' OR D2 = 'y' OR D1 = 'z');
ID
----------
1
4. Autres remarques
Vous noterez que dans le cas d’un SQL Patch comme pour un SQL Profile et une SQL Plan Management Baseline (cf les posts 1, 2 et 3 sur le sujet), les changements sur le plan sont stockés dans sqlobj$data:
col signature format 99999999999999999999
set long 10000
set longchunksize 100
select so.signature,
so.plan_id,
decode(so.obj_type, 1,'SQL_PROFILE',
2,'SQL_PLAN_BASELINE',
3,'SQL_PATCH',
null) obj_type,
sod.comp_data
from sys.sqlobj$ so,
sys.sqlobj$data sod
where so.signature=sod.signature
and so.plan_id=sod.plan_id
and so.obj_type=sod.obj_type
and so.obj_type=3;
SIGNATURE PLAN_ID OBJ_TYPE
--------------------- ---------- -----------------
COMP_DATA
--------------------------------------------------------------------------------
12752553474938959214 0 SQL_PATCH
<outline_data>
<hint><![CDATA[NO_INDEX(@"SEL$1" "FOO"@"SEL$1" "FOO_I2")]]></hint>
</outline_data>
Enfin, vous pouvez, comme pour le reste, supprimer advisors et patchs :
exec DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ('error_task');
select name from DBA_SQL_PATCHES;
NAME
------------------------------
SYS_SQLPTCH_011e5701beed0000
exec DBMS_SQLDIAG.DROP_SQL_PATCH('SYS_SQLPTCH_011e5701beed0000');
