Home » SQL & PL/SQL » SQL & PL/SQL » No numbers on Imbalance and Variance... (11.2.0.3.x and Solaris 10)
| No numbers on Imbalance and Variance... [message #643082] |
Mon, 28 September 2015 15:52  |
 |
James Bennett
Messages: 2
Registered: September 2015
Location: Rancho Cordova
|
Junior Member |
|
|
I'm trying to create an ASM Health Check report as follows..:
SQL> select t4.instance_name "INSTANCE",
t3.group_number "GROUP",
2 3 t3.name "NAME",
t2.name "NAME",
4 5 t2.path "PATH",
t2.total_mb/1024 "Total GB",
6 7 t2.free_mb/1024 "Free GB",
8 100*(max((t2.total_mb-t2.free_mb)/t2.total_mb)-min((t2.total_mb-t2.free_mb)
9 100*(max(t2.total_mb)-min(t2.total_mb))/max(t2.total_mb) "Variance",
100*(min(t2.free_mb/t2.total_mb)) "MinFree",
100*(max(t2.free_mb/t2.total_mb)) "MaxFree",
10 11 12 t2.reads,
t2.read_time,
13 14 round(t2.read_time/t2.reads*1000,3) rd_rspd_ms,
15 t2.writes,round(t2.write_time/t2.writes*1000,3) wr_rspd_ms,
round((t2.read_time+t2.write_time)/(t2.reads+t2.writes)*1000,3) dsk_rspd_ms,
16 17 round((t2.bytes_read+t2.bytes_written)/1024/1024/(t2.read_time+t2.writ
18 from V$asm_disk t2, v$asm_diskgroup t3, v$asm_client t4
where
19 20 t3.group_number=t2.group_number
21 and
22 t2.group_number=t4.group_number
order by 2; 23
select t4.instance_name "INSTANCE",
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL>
But getting the above error. What is recommended to include into a GROUP BY line..?
James Bennett
|
|
|
|
|
|
|
|
| No numbers on Imbalance and Variance... [message #643090 is a reply to message #643082] |
Mon, 28 September 2015 18:10  |
|
James Bennett
Messages: 2
Registered: September 2015
Location: Rancho Cordova
|
Junior Member |
|
|
SELECT t4.instance_name "INSTANCE",
t3.group_number "GROUP",
t3.NAME "NAME",
t2.NAME "NAME",
t2.path "PATH",
t2.total_mb / 1024 "Total GB",
t2.free_mb / 1024 "Free GB",
--t2.reads,
--t2.read_time,
Round(t2.read_time / t2.reads * 1000, 3) rd_rspd_ms,
--t2.writes,
Round(t2.write_time / t2.writes * 1000, 3) wr_rspd_ms,
Round(( t2.read_time + t2.write_time ) / ( t2.reads + t2.writes ) * 1000, 3) dsk_rspd_ms,
Round(( t2.bytes_read + t2.bytes_written ) / 1024 / 1024 / (t2.read_time + t2.write_time )) MB_per_sec,
100 * ( Max(( t2.total_mb - t2.free_mb ) / t2.total_mb) - Min(( t2.total_mb - t2.free_mb ) / t2.total_mb) ) / Max((t2.total_mb - t2.free_mb ) / t2.total_mb) "Imbalance",
100 * ( Max(t2.total_mb) - Min(t2.total_mb) ) / Max(t2.total_mb) "Variance"
FROM v$asm_disk t2,
v$asm_diskgroup t3,
v$asm_client t4
WHERE t3.group_number = t2.group_number
AND t3.group_number = t4.group_number
GROUP BY t4.instance_name,
t3.group_number,
t3.NAME,
t2.NAME,
t2.path,
t2.total_mb,
t2.free_mb,
t2.read_time,
t2.reads,
t2.write_time,
t2.writes,
t2.bytes_read,
t2.bytes_written
ORDER BY 2;
*BlackSwan added {code} tags. Please do so yourself in the future.
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
[Updated on: Mon, 28 September 2015 18:38] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: No numbers on Imbalance and Variance... [message #643092 is a reply to message #643091] |
Mon, 28 September 2015 19:07 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT t4.instance_name
"INSTANCE",
t3.group_number "GROUP",
t3.name "NAME3",
t2.name "NAME2",
t2.path "PATH",
t2.total_mb / 1024
"Total GB",
t2.free_mb / 1024
"Free GB",
--t2.reads,
--t2.read_time,
Round(t2.read_time / t2.reads * 1000, 3)
rd_rspd_ms,
--t2.writes,
Round(t2.write_time / t2.writes * 1000, 3)
wr_rspd_ms,
Round(( t2.read_time + t2.write_time ) / ( t2.reads + t2.writes ) * 1000,
3)
dsk_rspd_ms,
Round(( t2.bytes_read + t2.bytes_written ) / 1024 / 1024 / (
t2.read_time + t2.write_time ))
MB_per_sec,
100 * ( Max(( t2.total_mb - t2.free_mb ) / t2.total_mb) - Min(
( t2.total_mb - t2.free_mb ) / t2.total_mb) ) / Max((
t2.total_mb - t2.free_mb ) / t2.total_mb)
"Imbalance",
100 * ( Max(t2.total_mb) - Min(t2.total_mb) ) / Max(t2.total_mb)
"Variance"
FROM v$asm_disk t2,
v$asm_diskgroup t3,
v$asm_client t4
WHERE t3.group_number = t2.group_number
AND t3.group_number = t4.group_number
GROUP BY t4.instance_name,
t3.group_number,
t3.name,
t2.name,
t2.path,
t2.total_mb / 1024,
t2.free_mb / 1024,
Round(t2.read_time / t2.reads * 1000, 3),
Round(t2.write_time / t2.writes * 1000, 3),
Round(( t2.read_time + t2.write_time ) / ( t2.reads + t2.writes ) *
1000, 3),
Round(( t2.bytes_read + t2.bytes_written ) / 1024 / 1024 / (
t2.read_time + t2.write_time ))
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 02:35:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
