July 2015 – Page 2

dbsrvr>echo $ORACLE_SID corpdb dbsrvr>export ORACLE_SID=corpdb_1 dbsrvr>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 28 00:18:05 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile=init_corpdb.ora ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2220200 bytes Variable Size 889196376 bytes Database Buffers 3372220416 bytes Redo Buffers 12144640 bytes Database mounted. Database opened.

It can be useful if you work frequently with OEM metrics…

# OEM’s Query

SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count)
min_disk_extents
, MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp)
extent_count
FROM x$kffxp
WHERE group_kffxp = 1
AND disk_kffxp != 65534
GROUP BY number_kffxp, disk_kffxp
ORDER BY number_kffxp, disk_kffxp)
GROUP BY file_num
HAVING MAX(extent_count) - MIN(extent_count) > 5
ORDER BY disk_extents_imbalance DESC;

# Matheus’ Query

select max(free_mb) biggest, min(free_mb) lowest, avg(free_mb) AVG,
trunc(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb))),2)||'%' as balanced,
trunc(100-(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb)))),2)||'%' as inbalanced
from v$asm_disk
where group_number in
(select group_number from v$asm_diskgroup where name = upper('&DG'));

I made my own query for two reasons:
1) I didn’t have the OEM query in the time i made it.
2) My query measures the imbalance with the avg of the disks (if every disk would balanced, how would be the difference), rather than the real/present difference between the disk with the maximum and the minimum usage…

So, you can chose the one you need… 🙂

Matheus.

Month: July 2015

ORA-29760: instance_number parameter not specified

ASM: Disk Size Imbalance Query