51 lines
1.3 KiB
SQL
51 lines
1.3 KiB
SQL
|
|
|
|
/*
|
|
|
|
**********************************************************
|
|
********* Needs to be run on ASM instance ****************
|
|
**********************************************************
|
|
|
|
Max and min columns should be close to each other for balanced files.
|
|
|
|
file# max min
|
|
=== === ===
|
|
3013 57 941
|
|
3018 58 942
|
|
3021 59 942
|
|
3043 940 942
|
|
3053 938 941
|
|
|
|
** All the disks in this example are having the same total capacity.
|
|
|
|
From the output above, files with imbalanced extents are file# 3013, 3018 and 3021. For example, for file# 3013 there are 59 extents in one disk and 942 extents in another disk.
|
|
|
|
|
|
File# 3043 and 3053 have extents almost equally balanced. Small imbalances are expected.
|
|
|
|
*/
|
|
|
|
DEFINE diskgroup_number="&&1"
|
|
|
|
set pagesize 55
|
|
set linesize 90
|
|
|
|
SELECT group_kffxp Group#
|
|
, number_kffxp file#
|
|
, MAX(count1) MAX
|
|
, MIN(count1) MIN
|
|
FROM
|
|
(SELECT group_kffxp
|
|
, number_kffxp
|
|
, disk_kffxp
|
|
, COUNT(XNUM_KFFXP) count1
|
|
FROM sys.x$kffxp
|
|
WHERE group_kffxp = &diskgroup_number
|
|
ANd disk_kffxp != 65534
|
|
GROUP BY group_kffxp, number_kffxp, disk_kffxp
|
|
)
|
|
GROUP BY group_kffxp, number_kffxp;
|
|
ORDER BY group_kffxp
|
|
, number_kffxp
|
|
|
|
UNDEFINE diskgroup_number |