Bonjour,

une de nos applications qui tourne sur une base 10G a ramé durant cette nuit.

En cherchant un peu j'ai détécté la requête qui pose problème: c'est un simple select avec des predicats. cette requête est exécutée plusieurs fois via une boucle. En générale cette boucle s'execute en 10 minutes et là elle a mis 3 heures. Normalement le plan d'execution de la requête se base sur un index local mais durant cette nuit elle a fait un Full scan de la partition.

Si j'execute la requête depuis sqlplus j'obtiens le bon plan d'execution.
Pourquoi cette fois ci le CBO a t'il choisit un FTS plutot qu'un accès par l"index ?

En regardant les curseurs partagés dans v$sql j'obtiens le résultat suivant:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
 
SQL_TEXT
----------------------------------------------------------------------------------------------------
EXECUTIONS SQL_ID        CHILD_NUMBER
---------- ------------- ------------
SELECT QTINI-QTCLO,NTFIC FROM SIMPOR S WHERE NUBIX=:B7 AND NUFDP=:B6 AND COMAR=:B5 AND NTFIC NOT IN
(:B4 ,:B3 ,:B2 ,:B1 ) FOR UPDATE OF QTINI,QTCLO,MTRES
       374 bm4kv56556s3n            0
 
SELECT QTINI-QTCLO,NTFIC FROM SIMPOR S WHERE NUBIX=:B7 AND NUFDP=:B6 AND COMAR=:B5 AND NTFIC NOT IN
(:B4 ,:B3 ,:B2 ,:B1 ) FOR UPDATE OF QTINI,QTCLO,MTRES
     25010 bm4kv56556s3n            2
Le plan d'execution du child_number 2 correspond au plan d'execution avec le Full Scan et la requête a été exécuté 25 010 fois.

Le plan d'execution du child_number 0 correspond au passage par l'indexe et a été exécuté seulement 374 fois.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
 
SQL> select * from table(dbms_xplan.display_cursor('bm4kv56556s3n',0,NULL));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  bm4kv56556s3n, child number 0
-------------------------------------
SELECT QTINI-QTCLO,NTFIC FROM SIMPOR S WHERE NUBIX=:B7 AND NUFDP=:B6 AND COMAR=:B5 AND NTFIC NOT IN
(:B4 ,:B3 ,:B2 ,:B1 ) FOR UPDATE OF QTINI,QTCLO,MTRES
 
Plan hash value: 3914215856
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |       |       |     1 (100)|          |       |       |
|   1 |  FOR UPDATE                         |         |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |         |     1 |    82 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SIMPOR  |     1 |    82 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | SIMPOR1 |    11 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("NUFDP"=:B6 AND "COMAR"=:B5 AND "NTFIC"<>:B4 AND "NTFIC"<>:B3 AND "NTFIC"<>:B2 AND
              "NTFIC"<>:B1))
   4 - access("NUBIX"=:B7)
 
Note
-----
   - dynamic sampling used for this statement
 
 
28 rows selected.
 
Elapsed: 00:00:00.06
SQL> select * from table(dbms_xplan.display_cursor('bm4kv56556s3n',2,NULL));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  bm4kv56556s3n, child number 2
-------------------------------------
SELECT QTINI-QTCLO,NTFIC FROM SIMPOR S WHERE NUBIX=:B7 AND NUFDP=:B6 AND COMAR=:B5 AND
NTFIC NOT IN (:B4 ,:B3 ,:B2 ,:B1 ) FOR UPDATE OF QTINI,QTCLO,MTRES
 
Plan hash value: 2277332810
 
--------------------------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |       |       |     3 (100)|          |       |       |
|   1 |  FOR UPDATE             |        |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|        |     1 |    82 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | SIMPOR |     1 |    82 |     3   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("NUBIX"=:B7 AND "NUFDP"=:B6 AND "COMAR"=:B5 AND "NTFIC"<>:B4 AND
              "NTFIC"<>:B3 AND "NTFIC"<>:B2 AND "NTFIC"<>:B1))
 
Note
-----
   - dynamic sampling used for this statement
Alors pourquoi à un moment donné Oracle n'a pas réutilisé le child cursor 0 ?

En regardant dans v$sql_shared_cursor j'obtiens le résultat suivant:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
select sql_id, child_number, 
SQL_TYPE_MISMATCH, OPTIMIZER_MISMATCH, OUTLINE_MISMATCH, STATS_ROW_MISMATCH, LITERAL_MISMATCH, 
SEC_DEPTH_MISMATCH, BUFFERED_DML_MISMATCH, PDML_ENV_MISMATCH, INST_DRTLD_MISMATCH, SLAVE_QC_MISMATCH, 
TYPECHECK_MISMATCH, AUTH_CHECK_MISMATCH, BIND_MISMATCH, DESCRIBE_MISMATCH, LANGUAGE_MISMATCH, 
TRANSLATION_MISMATCH, ROW_LEVEL_SEC_MISMATCH, REMOTE_TRANS_MISMATCH, LOGMINER_SESSION_MISMATCH, INCOMP_LTRL_MISMATCH, 
OVERLAP_TIME_MISMATCH, SQL_REDIRECT_MISMATCH, MV_QUERY_GEN_MISMATCH, USER_BIND_PEEK_MISMATCH, TYPCHK_DEP_MISMATCH, 
NO_TRIGGER_MISMATCH, CURSOR_PARTS_MISMATCH, STB_OBJECT_MISMATCH, ROW_SHIP_MISMATCH, PQ_SLAVE_MISMATCH, 
TOP_LEVEL_DDL_MISMATCH, MULTI_PX_MISMATCH, BIND_PEEKED_PQ_MISMATCH, MV_REWRITE_MISMATCH, ROLL_INVALID_MISMATCH, 
OPTIMIZER_MODE_MISMATCH, PX_MISMATCH, MV_STALEOBJ_MISMATCH, FLASHBACK_TABLE_MISMATCH,
LITREP_COMP_MISMATCH
from v$sql_shared_cursor where sql_id ='bm4kv56556s3n';
 
SQL_ID        CHILD_NUMBER S O O S L S B P I S T A B D L T R R L I O S M U T N C S R P T M B M R O P M F L
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
bm4kv56556s3n            0 N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N N N N N N N N N N N N
bm4kv56556s3n            2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
le mismatch correspond à correspond à AUTH_CHECK_MISMATCH et TRANSLATION_MISMATCH.

D'après la doc Oracle:
- AUTH_CHECK_MISMATCH: Authorization/translation check failed for the existing child cursor
- TRANSLATION_MISMATCH: The base objects of the existing child cursor do not match

qu'est ce que ça veut dire exactement?

Informations sur la table:
- pas de stats (dynamic sampling = 2)
- table partitionnée
- cursor_sharing = similar

merci de votre aide