Contention sur les redos [Résolu]

[big1]

Bonjour,
comment combiner ces trois requettes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT name, gets, misses,
immediate_gets, immediate_misses, sleeps
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy')
/
 
SET head off
 
SELECT 'Ratio of MISSES to GETS: '||
round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%'
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy')
/
 
SELECT 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
round((sum(immediate_misses)/
(sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy')

D'avance merci.

[elharet]

bonjour
ceci peut faire l'affaire

Code :

1
2
3
4
5
6
7
8
9
 
SELECT name, gets, misses,
immediate_gets, immediate_misses, sleeps, 'Ratio of MISSES to GETS: '||
round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%', 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
round((sum(immediate_misses)/
(sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy')
/

[big1]

Merci mais j'ai l'erreur suivante :

Code :

1
2
 
Query cannot be parsed within the Builder

[big1]

Et sous SQLPLUS :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> SELECT name, gets, misses,
  2  immediate_gets, immediate_misses, sleeps, 'Ratio of MISSES to GETS: '||
  3  round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%', 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
  4  round((sum(immediate_misses)/
  5  (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'
  6  FROM v$latch
  7  WHERE name IN ('redo allocation', 'redo copy')
  8  ;
SELECT name, gets, misses,
       *
ERROR at line 1:
ORA-00937: NOT a single-GROUP GROUP FUNCTION

[orafrance]

UNION ALL entre chaque requête et basta

[NGasparotto]

Envoyé par big1

Et sous SQLPLUS :

Code :

Tu as des fonctions d'aggregation (SUM) dans ta requête, ce qui implique d'avoir une clause de regroupement (GROUP BY), ajoutes à la fin de ta requête :

Code :

GROUP BY name, gets, misses,immediate_gets, immediate_misses, sleeps

Nicolas.

[big1]

Merci.
sous sqlplus OK.

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
 
SQL> SELECT name, gets, misses,
  2  immediate_gets, immediate_misses, sleeps, 'Ratio of MISSES to GETS: '||
  3  round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%', 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '||
  4  round((sum(immediate_misses)/
  5  (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'
  6  FROM v$latch
  7  WHERE name IN ('redo allocation', 'redo copy')
  8  GROUP BY name, gets, misses,immediate_gets, immediate_misses, sleeps;
 
NAME                                                                   GETS
---------------------------------------------------------------- ----------
    MISSES IMMEDIATE_GETS IMMEDIATE_MISSES     SLEEPS
---------- -------------- ---------------- ----------
'RATIOOFMISSESTOGETS:'||ROUND((SUM(MISSES)/(SUM(GETS)+0.0000000000
------------------------------------------------------------------
'RATIOOFIMMEDIATE_MISSESTOIMMEDIATE_GETS:'||ROUND((SUM(IMMEDIATE_MISSES)/(SUM(IM
--------------------------------------------------------------------------------
redo copy                                                              3232
         0          87739               66          0
Ratio of MISSES TO GETS: 0%
Ratio of IMMEDIATE_MISSES TO IMMEDIATE_GETS: .08%
 
 
NAME                                                                   GETS
---------------------------------------------------------------- ----------
    MISSES IMMEDIATE_GETS IMMEDIATE_MISSES     SLEEPS
---------- -------------- ---------------- ----------
'RATIOOFMISSESTOGETS:'||ROUND((SUM(MISSES)/(SUM(GETS)+0.0000000000
------------------------------------------------------------------
'RATIOOFIMMEDIATE_MISSESTOIMMEDIATE_GETS:'||ROUND((SUM(IMMEDIATE_MISSES)/(SUM(IM
--------------------------------------------------------------------------------
redo allocation                                                       93273
         7              0                0          0
Ratio of MISSES TO GETS: .01%
Ratio of IMMEDIATE_MISSES TO IMMEDIATE_GETS: 0%

Mais dans Query builder :

Code :

1
2
 
query COLUMN #7 ('RATIOOFMISSESTOGETS:'||ROUND((SUM(MISSES)/(SUM(GETS)+0.00000000001)*100),2)||'%') is invalid, use column alias

Merci d'avance.

[big1]

comme ça fonctionne :

Code :

1
2
3
4
5
6
7
 
SELECT name, gets, misses,
immediate_gets, immediate_misses, sleeps, round((sum(misses)/(sum(gets)+0.00000000001) * 100),2) "Ratio of MISSES to GETS:",
round((sum(immediate_misses)/(sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)"IMMED_MISSEStoIMMED_GETS"
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy')
GROUP BY name, gets, misses,immediate_gets, immediate_misses, sleeps

Merci.