Contention sur les redos

Version imprimable

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.

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')
/

20/02/2007, 14h55
big1
Merci mais j'ai l'erreur suivante :
Code:

1 2 Query cannot be parsed within the Builder

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

20/02/2007, 15h00
orafrance

UNION ALL entre chaque requête et basta ;)

Citation:
Envoyé par 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
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.

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.

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.