Bonjour,
J'ai une requete qui tourne assez souvent sur toutes mes bases afin de consulter les infos sur les tablespaces.
Cette requete fonctionne correctement sur l'ensemble de mes bases, mais couine sur 1 base (temps d'execution assez long).
Cette derniere n'est pas saturée (bien au contraire), mais je comprend pas pourquoi elle prend autant de temps pour afficher les informations des tablespaces.
Voici ma requete:
Code sql : 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 SELECT TABLESPACE_NAME, FREE, TOTALE, GREATEST(TOTALE,TOTALEXT) MAXTOTALE, ROUND((GREATEST(TOTALE, TOTALEXT)-TOTALE+FREE)*100 / (GREATEST(TOTALE, TOTALEXT))) POURC, ROUND(FREE / 1024 / 1024,2) FREEMO, ROUND(TOTALE / 1024 / 1024,2) TOTALMO, ROUND(GREATEST(TOTALE, TOTALEXT) / 1024 / 1024,2) MAXTOTALMO FROM ( select T.TABLESPACE_NAME, F.Free, SUM(T.bytes) Totale, SUM(T.maxbytes) TotalExt FROM DBA_DATA_FILES T, (SELECT tablespace_name, SUM (bytes) Free FROM sys.dba_free_space GROUP BY tablespace_name ORDER BY tablespace_name) F WHERE T.TABLESPACE_NAME = F.tablespace_name GROUP BY T.TABLESPACE_NAME, F.Free UNION SELECT d.tablespace_name, a.BYTES-t.BYTES Free, SUM(a.BYTES) Totale, SUM(a.MAXBYTES) TotalExt FROM SYS.dba_tablespaces d, (SELECT tablespace_name, SUM (BYTES) BYTES, SUM (MAXBYTES) MAXBYTES FROM dba_temp_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM (bytes_used) BYTES FROM v$temp_extent_pool GROUP BY tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY' GROUP BY d.tablespace_name, a.BYTES-t.BYTES ) ORDER BY POURC;
et voici le plan d'execution:
Dans le doute, j'ai raffraichis les stats du schéma SYS.
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 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | B ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 63 (100)| 8 |00:01:49.43 | 22586 | 930 | 1 | SORT ORDER BY | | 1 | 3 | 168 | 63 (48)| 8 |00:01:49.43 | | 2 | VIEW | | 1 | 3 | 168 | 62 (47)| 8 |00:01:49.43 | 22586 | | 3 | SORT UNIQUE | | 1 | 3 | 192 | 62 (47)| 8 |00:01:49.43 | | 4 | UNION-ALL | | 1 | | | | 8 |00:01:49.35 | 22586 | 9304 | | 5 | HASH GROUP BY | | 1 | 2 | 110 | 53 (51)| 7 |00:01:49.35 | |* 6 | HASH JOIN | | 1 | 2 | 110 | 51 (50)| 7 |00:01:49.34 | | 7 | VIEW | DBA_DATA_FILES | 1 | 2 | 68 | 4 (0)| 7 |00:00:0 | 8 | UNION-ALL | | 1 | | | | 7 |00:00:00.06 | 40 | 0 | | | 9 | NESTED LOOPS | | 1 | 1 | 361 | 2 (0)| 0 |00:00:00.01 | 1 | 10 | NESTED LOOPS | | 1 | 1 | 346 | 1 (0)| 0 |00:00:00.01 | | 11 | NESTED LOOPS | | 1 | 1 | 333 | 1 (0)| 0 |00:00:00.01 | |* 12 | FIXED TABLE FULL | X$KCCFN | 1 | 1 | 310 | 0 (0)| 7 |00:0 |* 13 | TABLE ACCESS BY INDEX ROWID | FILE$ | 7 | 1 | 23 | 1 (0)| |* 14 | INDEX UNIQUE SCAN | I_FILE1 | 7 | 1 | | 0 (0)| 7 |00:00:00 |* 15 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 0 | 14 | 182 | 0 (0)| | 16 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 15 | 1 (0)| 0 |00:00 |* 17 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | | 0 (0)| 0 |00:00:00.01 | 18 | NESTED LOOPS | | 1 | 1 | 412 | 2 (0)| 7 |00:00:00.05 | 2 | 19 | NESTED LOOPS | | 1 | 1 | 399 | 2 (0)| 7 |00:00:00.01 | | 20 | NESTED LOOPS | | 1 | 1 | 390 | 1 (0)| 7 |00:00:00.01 | | 21 | NESTED LOOPS | | 1 | 1 | 375 | 0 (0)| 7 |00:00:00.01 | |* 22 | FIXED TABLE FULL | X$KCCFN | 1 | 1 | 310 | 0 (0)| 7 |00: |* 23 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 7 | 1 | 65 | 0 (0)| | 24 | TABLE ACCESS CLUSTER | TS$ | 7 | 1 | 15 | 1 (0)| 7 |00:0 |* 25 | INDEX UNIQUE SCAN | I_TS# | 7 | 1 | | 0 (0)| 7 |00:00:00.0 |* 26 | TABLE ACCESS BY INDEX ROWID | FILE$ | 7 | 1 | 9 | 1 (0)| |* 27 | INDEX UNIQUE SCAN | I_FILE1 | 7 | 1 | | 0 (0)| 7 |00:00:00. |* 28 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 7 | 14 | 182 | 0 (0)| | 29 | VIEW | | 1 | 7 | 147 | 47 (54)| 7 |00:01:49.30 | 225 | 30 | HASH GROUP BY | | 1 | 7 | 147 | 47 (54)| 7 |00:01:49.30 | 2 | 31 | VIEW | DBA_FREE_SPACE | 1 | 3432 | 72072 | 46 (53)| 163 |00:00:01.42 | 32 | UNION-ALL | | 1 | | | | 163 |00:00:01.42 | 22523 | 9304 | | 33 | NESTED LOOPS | | 1 | 1 | 64 | 1 (0)| 0 |00:00:00.01 | | 34 | NESTED LOOPS | | 1 | 1 | 45 | 1 (0)| 0 |00:00:00.01 | | 35 | INDEX FULL SCAN | I_FILE2 | 1 | 7 | 42 | 1 (0)| 7 |00:0 |* 36 | TABLE ACCESS CLUSTER | FET$ | 7 | 1 | 39 | 0 (0)| 0 |00 |* 37 | INDEX UNIQUE SCAN | I_TS# | 7 | 1 | | 0 (0)| 7 |00:00:00. |* 38 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 19 | 0 (0)| 0 |00:0 |* 39 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | | 0 (0)| 0 |00:00:00.0 | 40 | NESTED LOOPS | | 1 | 82 | 5740 | 5 (0)| 31 |00:00:00.01 | | 41 | NESTED LOOPS | | 1 | 82 | 5248 | 5 (0)| 31 |00:00:00.01 | |* 42 | TABLE ACCESS FULL | TS$ | 1 | 7 | 175 | 5 (0)| 7 |00:00 |* 43 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 7 | 13 | 507 | 0 (0)| |* 44 | INDEX UNIQUE SCAN | I_FILE2 | 31 | 1 | 6 | 0 (0)| 31 |00: |* 45 | HASH JOIN | | 1 | 3348 | 349K| 34 (71)| 132 |00:01:41.64 | 22 |* 46 | HASH JOIN | | 1 | 59 | 2478 | 11 (10)| 63 |00:00:00.01 | 2 | 47 | MERGE JOIN | | 1 | 59 | 2124 | 10 (10)| 63 |00:00:00.01 | | 48 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 63 | 693 | 4 (0 | 49 | INDEX FULL SCAN | RECYCLEBIN$_TS | 1 | 63 | | 3 (0)| 63 |00: |* 50 | SORT JOIN | | 63 | 7 | 175 | 6 (17)| 63 |00:00:00.01 | |* 51 | TABLE ACCESS FULL | TS$ | 1 | 7 | 175 | 5 (0)| 7 |00:0 | 52 | INDEX FULL SCAN | I_FILE2 | 1 | 7 | 42 | 1 (0)| 7 |00:0 | 53 | FIXED TABLE FULL | X$KTFBUE | 1 | 100K| 6347K| 22 (100)| 13956 |00: | 54 | NESTED LOOPS | | 1 | 1 | 88 | 6 (0)| 0 |00:00:00.01 | | 55 | NESTED LOOPS | | 1 | 1 | 82 | 6 (0)| 0 |00:00:00.01 | | 56 | NESTED LOOPS | | 1 | 1 | 63 | 6 (0)| 0 |00:00:00.01 | | 57 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 63 | 693 | 6 (0)| 6 | 58 | TABLE ACCESS CLUSTER | UET$ | 63 | 1 | 52 | 0 (0)| 0 |0 |* 59 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 63 | 1 | | 0 (0)| 63 |00 |* 60 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 19 | 0 (0)| 0 |00: |* 61 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | | 0 (0)| 0 |00:00:00. |* 62 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 6 | 0 (0)| 0 |00: | 63 | HASH GROUP BY | | 1 | 1 | 82 | 9 (23)| 1 |00:00:00.08 | | 64 | NESTED LOOPS OUTER | | 1 | 1 | 82 | 7 (0)| 1 |00:00:00 | 65 | NESTED LOOPS OUTER | | 1 | 1 | 54 | 6 (0)| 1 |00:00:0 | 66 | NESTED LOOPS | | 1 | 1 | 39 | 5 (0)| 1 |00:00:00.07 | 18 |* 67 | TABLE ACCESS FULL | TS$ | 1 | 1 | 26 | 5 (0)| 1 |00:00:00. |* 68 | FIXED TABLE FIXED INDEX | X$KCFISTSA (ind:1) | 1 | 13 | 169 | 0 (0)| | 69 | VIEW PUSHED PREDICATE | | 1 | 1 | 15 | 1 (0)| 1 |00:00:00.0 | 70 | SORT GROUP BY | | 1 | 1 | 64 | 1 (0)| 1 |00:00:00.01 | | 71 | NESTED LOOPS | | 1 | 1 | 64 | 1 (0)| 1 |00:00:00.01 | |* 72 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 1 | 25 | 1 (0)| 1 |* 73 | INDEX UNIQUE SCAN | I_TS1 | 1 | 1 | | 0 (0)| 1 |00:00:00.01 |* 74 | FIXED TABLE FIXED INDEX | X$KTSTFC (ind:1) | 1 | 1 | 39 | 0 (0)| | 75 | VIEW PUSHED PREDICATE | | 1 | 1 | 28 | 1 (0)| 1 |00:0 | 76 | SORT GROUP BY | | 1 | 1 | 429 | 1 (0)| 1 |00:00:00.01 | | 77 | NESTED LOOPS | | 1 | 1 | 429 | 1 (0)| 1 |00:00:00.01 | | 78 | NESTED LOOPS | | 1 | 1 | 377 | 1 (0)| 1 |00:00:00.01 | | 79 | NESTED LOOPS | | 1 | 1 | 362 | 0 (0)| 1 |00:00:00.01 | |* 80 | FIXED TABLE FULL | X$KCCFN | 1 | 1 | 297 | 0 (0)| 1 |00:0 |* 81 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:1) | 1 | 1 | 65 | 0 (0)| |* 82 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 1 | 15 | 1 (0)| 1 |* 83 | INDEX UNIQUE SCAN | I_TS1 | 1 | 1 | | 0 (0)| 1 |00:00:00.01 |* 84 | FIXED TABLE FIXED INDEX | X$KCCTF (ind:1) | 1 | 1 | 52 | 0 (0)| ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("T"."TABLESPACE_NAME"="F"."TABLESPACE_NAME") 12 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG 13 - filter("F"."SPARE1" IS NULL) 14 - access("FNFNO"="F"."FILE#") 15 - filter("FE"."FENUM"="F"."FILE#") 17 - access("F"."TS#"="TS"."TS#") 22 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG 23 - filter("FNFNO"="HC"."KTFBHCAFNO") 25 - access("HC"."KTFBHCTSN"="TS"."TS#") 26 - filter("F"."SPARE1" IS NOT NULL) 27 - access("FNFNO"="F"."FILE#") 28 - filter("FE"."FENUM"="F"."FILE#") 36 - filter("F"."FILE#"="FI"."RELFILE#") 37 - access("F"."TS#"="FI"."TS#") 38 - filter("TS"."BITMAPPED"=0) 39 - access("TS"."TS#"="F"."TS#") 42 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$"))) 43 - filter("TS"."TS#"="F"."KTFBFETSN") 44 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#") 45 - access("U"."KTFBUEFNO"="FI"."RELFILE#" AND "U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFN 46 - access("RB"."TS#"="FI"."TS#") 50 - access("TS"."TS#"="RB"."TS#") filter("TS"."TS#"="RB"."TS#") 51 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$"))) 59 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#" 60 - filter("TS"."BITMAPPED"=0) 61 - access("TS"."TS#"="U"."TS#") 62 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 67 - filter((DECODE("TS"."BITMAPPED",0,'DICTIONARY','LOCAL')='LOCAL' AND "TS"."ONLINE$"<>3 AND DECODE("TS"."CONTENTS$",0,DECODE(BITAND("TS"."FLAGS",16),16,'UNDO','PERMANENT'),1,'TEMPORARY' 68 - filter("TS"."TS#"="TSATTR"."TSID") 72 - filter(("TS"."CONTENTS$"=1 AND "TS"."BITMAPPED"<>0 AND "TS"."ONLINE$"=1)) 73 - access("TS"."NAME"="TS"."NAME") 74 - filter(("FC"."INST_ID"=USERENV('INSTANCE') AND "TS"."TS#"="FC"."KTSTFCTSN")) 80 - filter(("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7)) 81 - filter("V"."FNFNO"="HC"."KTFTHCTFNO") 82 - filter("HC"."KTFTHCTSN"="TS"."TS#") 83 - access("TS"."NAME"="TS"."NAME") 84 - filter(("TF"."TFDUP"<>0 AND BITAND("TF"."TFSTA",32)<>32 AND "V"."FNFNO"="TF"."TFNUM" AND "TF" 153 ligne(s) sélectionnée(s).
Auriez vous une idée ?
Partager