Question : Open cusor

**cedrich** · 17/04/2012, 16h22

Bonjour à tous,

J'ai récemment eu une erreur "ora-01000 maximum open cursors exceeded", comme proposé dans les actions, j'ai augmenté le nombre "open cursor" afin de ne pas être bloqué. La version d'Oracle est une Expression Edition 10g.

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
 
-- Depuis une session SYS
SQL> show parameters open
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_cursors                         integer     300
 
-- Depuis l'utilisateur
SQL> delete int_utilisateur where id = 43;
delete int_utilisateur where id = 43
       *
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
 
 
-- Depuis une session SYS
SQL> alter system set open_cursors=500 scope=both;
 
System altered.
 
SQL> show parameters open
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_cursors                         integer     500
 
-- Depuis l'utilisateur (nouv. session)
SQL> delete int_utilisateur where id = 43;
 
1 row deleted.

Par curiosité, j'ai essayé d'identifier approximativement combien d"open cursor" était ouver part Oracle pour la suppression d'une seule ligne. J'en ai eu env. 333.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
-- Requête utilisé durant la suppression pour estimer le nombre de curseurs
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;

Ma suppression n'étant lié qu'à une ligne je ne m'attends pas à voir autant de curseur ouvert. Il y a bien des tables (35 en tout) lié avec la règle "DELETE ON CASCADE" et aucunes de ces tables ne contiennent de valeur pour l'ID en question.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-- Requete pour identifier les tables liés
SELECT 'SELECT COUNT(*) FROM '  || CONS.TABLE_NAME || ' WHERE ' || CONS_COL.COLUMN_NAME || ' = 43;'
FROM USER_CONSTRAINTS CONS,
     USER_CONSTRAINTS PAR,
     USER_CONS_COLUMNS CONS_COL
WHERE CONS.R_CONSTRAINT_NAME = PAR.CONSTRAINT_NAME AND
      CONS.CONSTRAINT_NAME = CONS_COL.CONSTRAINT_NAME AND
      CONS.OWNER = 'ID_FILSEK_DEMO' AND
      CONS.OWNER = PAR.OWNER AND
      CONS.OWNER = CONS_COL.OWNER AND
      PAR.TABLE_NAME = 'INT_UTILISATEUR' AND
      CONS.DELETE_RULE = 'CASCADE'
ORDER BY CONS.TABLE_NAME;

Ma question est comment Oracle gére les curseurs? Y a-t-il des informations à ce sujet ou doit-on simplement appliquer la régle d'augmenter la limite des "open cusors"?

D'avance merci de vos réponses avisées.

Meilleures salutations,

Cédric

**mnitu** · 17/04/2012, 16h40

On delete cascade ce n’est pas vraiment recommandée.
Avez-vous vérifié la présence des triggers sur cette table et autres tables liées ? Avez-vous aussi regardé si les éventuelles autre tables liées aux tables qui sont impactées via l’option delete cascade ne sont pas eux aussi référencées par des autres clé étangère avec la clause on delete cascade ou des triggers ?
Faite une trace sql pour avoir une idée de tout ce qui se passe derrière la suppression de votre enregistrement
Plus des curseur vaut plus de mémoire pouvant être nécessaire à la session.

**cedrich** · 17/04/2012, 17h35

Je n'ai pas fait le test explicitement et effectivement je m'attends à trouver des objets qui sont eux-mêmes en "DELETE ON CASCADE". Pour ce qui est des triggers, la trace en-dessous n'en mentionne pas.

Ce qui m'intéresse est de savoir comment et quand Oracle ouvre un curseur lors d'une suppression? Est-ce qu'il ouvre un curseur pour chaque contrainte d'intégrité lié à ma table de départ => La chaîne de "DELETE ON CASCADE" peut s'avérer être un problème.

Les développeurs utilisent "DELETE ON CASCADE" afin de simplifier les traitements de suppression. Ils n'utiliseront pas, à ma connaissance, ce mécanisme si la suppression en chaîne n'est pas possible. Elle se fera manuellement et explicitement.

Quels sont les désavantages de ce mécanisme Oracle? Oracle ne fait-il pas mieux et plus rapide que du code manuel?

La suppression explicite d'un enregistrement lié entre différentes tables me paraît plus complexe à mettre en place que le mécanisme Oracle.

Pouvez-vous préciser les désavantages/avantages de votre remarque? Avez-vous un article à me conseiller?

Voici le contenu partiel de la trace :

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
 
 
TKPROF: Release 10.2.0.1.0 - Production on Tue Apr 17 16:58:51 2012
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Trace file: xe_ora_3196.trc
Sort options: default
 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) 
from
 objauth$ where obj#=:1 and col# is not null group by privilege#, col#, 
  grantee# order by col#, grantee#
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      132      0.00       0.00          0          0          0           0
Execute    448      0.00       0.03          0          0          0           0
Fetch      448      0.01       0.00          0        896          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1028      0.01       0.05          0        896          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT GROUP BY (cr=2 pr=0 pw=0 time=81 us)
      0   TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=44 us)
      0    INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=33 us)(object id 103)
 
********************************************************************************
 
select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale,
  c.precision#,c.charsetid,c.charsetform 
from
 triggercol$ tc,col$ c ,trigger$ tr where tc.obj#=:1 and c.obj#=:2 and 
  tc.intcol#=c.intcol# and tr.obj# = tc.obj# and (bitand(tr.property,32) != 
  32 or bitand(tc.type#,20) = 20) union select type#,intcol#,position#,69,0,0,
  0,0,0 from triggercol$ where obj#=:3 and intcol#=1001 union select tc.type#,
  tc.intcol#,tc.position#,121,0,0,0,0,0 from triggercol$ tc,trigger$ tr where 
  tr.obj# = tc.obj# and bitand(tr.property,32) = 32 and tc.obj# = :4 and 
  bitand(tc.type#,20) != 20
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       17      0.00       0.00          0          0          0           0
Execute     17      0.00       0.00          0          0          0           0
Fetch      261      0.03       0.09          9        238          0         244
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      295      0.03       0.10          9        238          0         244
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     24  SORT UNIQUE (cr=15 pr=1 pw=0 time=7724 us)
     24   UNION-ALL  (cr=15 pr=1 pw=0 time=3167 us)
     24    HASH JOIN  (cr=10 pr=1 pw=0 time=2992 us)
     35     NESTED LOOPS  (cr=7 pr=0 pw=0 time=306 us)
      1      TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=3 pr=0 pw=0 time=40 us)
      1       INDEX UNIQUE SCAN I_TRIGGER2 (cr=2 pr=0 pw=0 time=20 us)(object id 126)
     35      TABLE ACCESS CLUSTER COL$ (cr=4 pr=0 pw=0 time=127 us)
      1       INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=13 us)(object id 3)
     24     INDEX RANGE SCAN I_TRIGGERCOL2 (cr=3 pr=1 pw=0 time=89 us)(object id 128)
      0    INDEX RANGE SCAN I_TRIGGERCOL2 (cr=2 pr=0 pw=0 time=14 us)(object id 128)
      0    NESTED LOOPS  (cr=3 pr=0 pw=0 time=36 us)
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=3 pr=0 pw=0 time=28 us)
      1      INDEX UNIQUE SCAN I_TRIGGER2 (cr=2 pr=0 pw=0 time=12 us)(object id 126)
      0     INDEX RANGE SCAN I_TRIGGERCOL2 (cr=0 pr=0 pw=0 time=0 us)(object id 128)
 
********************************************************************************
 
delete int_utilisateur 
where
 id = 43
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.21       2.44          0          0          0           0
Execute      1      0.01       0.20         17          1        159           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.23       2.65         17          1        159           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  (ID_FILSEK_DEMO)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  DELETE  INT_UTILISATEUR (cr=12095 pr=840 pw=0 time=5962489 us)
      1   INDEX UNIQUE SCAN INT_UTIL_PK (cr=1 pr=0 pw=0 time=18 us)(object id 77030)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  DELETE STATEMENT   MODE: ALL_ROWS
      1   DELETE OF 'INT_UTILISATEUR'
      1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'INT_UTIL_PK' (INDEX 
               (UNIQUE))
 
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."AS_LD_INVENTAIRE_DETAIL" where "INT_UTILISATEUR_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."GED_FILTRE_DC_MULTI" 
where
 "INT_UTILISATEUR_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          1          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          1          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."GED_FILTRE" where "GEDDOC_CHECKOUT_UTIL_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."GED_FILTRE" 
where
 "UTILISATEUR_ID" = :1
 
 
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          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SD_CAS" where "INT_IDENT_DEMANDEUR_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_ACTEUR_MODEL" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.02          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.02          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_TESTAMENT" where "INT_IDENT_DOSSIER" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."PR_PROD_CONTROLE_ENT" where "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."DO_DOCUM_VENTE" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."TP_VALIDATION_ABS" 
where
 "INT_IDENT_TIERS_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.03          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.03          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."AS_ARTU_ETAT_PER" where "INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."DB_ELEMENT" where "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.03          6          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.03          6          7          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DM_MAP_IDENTITE_METADATA" 
where
 "IDENTITE_PAR_DEFAUT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DM_MAP_IDENTITE" 
where
 "IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."DM_MAP_EMPLOYE" where "IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DM_MAP_COMPTETIERS2" 
where
 "IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DO_CONTRAT_FACT_IDENTITES" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          1          1          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          1          1          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DO_LOT_BON_FACT_IDENTITE_LIEN" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.02          1          1          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.02          1          1          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."HA_VOTANT_DEPUIS_ETRANGER" 
where
 "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.02          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.02          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."HA_PROPRIETAIRE_CHIEN" 
where
 "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."INT_IDENT_COMM_INTERNE" 
where
 "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."PR_ORDRE_FABRICATION" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."PR_OF_DET_TPS_MVTS" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."CO_TIERS" 
where
 "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          1          1         56           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.02          1          1         56           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_CONTRAT_ENTETE_PROV" where "INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.01          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.02          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_CAS_MVT_DET" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.02          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.02          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_TYPE_CAS" where "INT_IDENT_TIERS_DEFAUT_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."CO_DOMICILE_PMT" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.01          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
update "ID_FILSEK_DEMO"."INT_AN_ADRESSE" set "INT_IDENT_ID" = null 
where
 "INT_IDENT_ID" = :1
 
 
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          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."GED_DOCUMENT" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."HA_LIEN_IDENTITE_RELATION" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
update "ID_FILSEK_DEMO"."SE_PARAMETRE" set "TIERS_DOS_INTERNE_ID" = null 
where
 "TIERS_DOS_INTERNE_ID" = :1
 
 
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          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."TP_CAS_ABS" where "AUTRE_INTERVENANT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          2           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."AI_GARNITURE_LIVR_ENTETE" where "INT_IDENT_CONTACT_DEM_ID" 
  = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          2           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_EVT_TRIGGER_USR" where "TIERS_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          3          2           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_EVT_TRIGGER_MODEL_USR" where "TIERS_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          2           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
update "ID_FILSEK_DEMO"."INT_PHONE_CALL" set "INT_IDENT_ID" = null 
where
 "INT_IDENT_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.00       0.00          0          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          2           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_EVENEMENT" where "INT_IDENT_TROISIEME_TIERS_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          3          2           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_EVENEMENT" where "INT_IDENT_RESP_SUIVI_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_ACTEUR" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DO_LD_DOCVTE_MULTI_SEL_LIEN" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_CAS" where "INT_IDENT_TIERS_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "RESP_CAS_INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "TIERS_CAS_INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "RESP_EVT_INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "TIERS_EVT_INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "INT_EVT_CONTACT_UTIL_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."SE_LD_CAS_LIEN" where "INT_CAS_CONTACT_UTIL_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."AS_LD_MULTI_SELECTION_LIEN" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."INT_UTILISATEUR" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          5          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          5          7          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."DB_EXTENSIONS_LIEN" 
where
 "INT_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."HA_MUT_EVENT_RELATION" where "INT_IDENTITE_ID" = :1
 
 
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        1      0.00       0.00          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
delete from "ID_FILSEK_DEMO"."INT_LD_EMPLOYE_LIEN" 
where
 "INT_EMP_IDENTITE_ID" = :1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1          3          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1          3          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."DO_DOCUM_ACHAT" where "INT_IDENT_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "ID_FILSEK_DEMO"."HA_VOTDET_VOTANTS" where "INT_IDENT_ETR_ID" = :1
 
 
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        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 2)
 
....
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.21       2.44          0          0          0           0
Execute      1      0.01       0.20         17          1        159           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.23       2.65         17          1        159           1
 
Misses in library cache during parse: 1
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2987      0.82       1.06          0          0         12           0
Execute  19850      1.31       2.86        274        401       3548           7
Fetch    62006      1.62       6.04        883      96233          0       50957
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    84843      3.76       9.97       1157      96634       3560       50964
 
Misses in library cache during parse: 375
Misses in library cache during execute: 374
 
    1  user  SQL statements in session.
19850  internal SQL statements in session.
19851  SQL statements in session.
    1  statement EXPLAINed in this session.
********************************************************************************
Trace file: xe_ora_3196.trc
Trace file compatibility: 10.01.00
Sort options: default
 
       1  session in tracefile.
       1  user  SQL statements in trace file.
   19850  internal SQL statements in trace file.
   19851  SQL statements in trace file.
     376  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           ID_FILSEK_DEMO.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
  176954  lines in trace file.
      12  elapsed seconds in trace file.

Salutations,

Cédric

**Scriuiw** · 17/04/2012, 18h27

Quels sont les désavantages de ce mécanisme Oracle?

La lenteur principalement.
Essayes de supprimer 100 000 enregistrements avec + de 100 tables dépendantes de celle "en haut de la pyramide" et tu comprendras ta douleur

Après il est vrai que c'est une solution de facilité car il n'y a pas grand chose à programmer

La solution n'est pas nécessairement évidente mais une bonne consiste à avoir un script capable de remonter les contraintes de la BDD et faire de la purge en masse table par table et non enregistrement par enregistrement.

**mnitu** · 18/04/2012, 15h18

Je fais partie des gens qui n’aiment pas trouver 376 curseurs ouverts (regardez la synthèse du votre fichier trace) quand ils font une pauvre delete dans une table.
En gros les reproches sont
- Trop de choses qui se passent derrière la scène par automagie
- Risque augmenté de suppression des enregistrements en trop
- L’impact réel d’une requête anodine de type delete from table where cle_unique = valeur n’est pas visible au développeur/DBA/analyste
Cella ne signifie pas du tout de bannir l’ON DELETE CASCADE mais d’en user avec modération.

**cedrich** · 18/04/2012, 15h19

Merci Scriuiw pour l'explication.

J'étais un peu enfermé dans le mode de fonctionnement de nos besoins où les suppressions via l'applicatif ne sont pas faites en masse... D'où l'utilisation de ce mécanisme.

Et après réflexion et quelques recherches, nous utilisons également quelques scripts de nettoyage avec désactivation des contraintes pour gagner en rapidité. Et ce sont plus des scripts (très) occasionnels que systématique.

Je me permets de relancer ma question principale qui est de savoir quand Oracle ouvre un curseur? Ou est-ce une boîte noire et on applique "bêtement" l'augmentation systématique des "OPEN_CURSORS" lors de l'erreur?

D'avance merci,

Cédric

**mnitu** · 18/04/2012, 17h19

Envoyé par cedrich

...Je me permets de relancer ma question principale qui est de savoir quand Oracle ouvre un curseur? Ou est-ce une boîte noire et on applique "bêtement" l'augmentation systématique des "OPEN_CURSORS" lors de l'erreur?
...

Oracle Concepts

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information.

En gros chaque parsing d'une requête SQL implique création ou ouverture d'un curseur.

Question : Open cusor

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager