Optimisation d'une requête DBA pour consulter les tablespaces

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:

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:

Code:

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).

Dans le doute, j'ai raffraichis les stats du schéma SYS.
Auriez vous une idée ?