Analyzing the ADDM report we have found below:
Wait class "Commit" was consuming significant database time
It was a DB where an application continuously loads data using sql loader (using default parameters). As a result, huge amount of commits are always going on.
Now we want to control the number of rows between commits.
We changed the sqlldr script by appending ROWS=1000 and found below on sql loader log:
Space allocated for bind array: 495360 bytes(40 rows)
So, commit is occurring after insertion of each 40 rows which uses BIND size of 495360 bytes.
Now we have to calculate the appropriate size for BIND. We calculated as [(495360/40)*1000] bytes.
So, we have to use a new parameter BINDSIZE and append below in the old sqlldr command:
BINDSIZE =12384000 rows=1000
But we have to control BINDSIZE so that it doesn't exceed the max permitted value for READSIZE (read buffer size) for the platform (for 9i database it is 20MB or 20971520 bytes)[http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm#1004855]
Now our sql loader logfile show as below:
Space allocated for bind array: 12384000 bytes(1000 rows)
Read buffer bytes:12384000
So, we are getting exactly 1000 rows interval for commits.
[Thanks to ssunda6: http://www.orafaq.com/forum/t/84903/2/]
On our exadata half rack machine, two of our asm diskgroups were DATA2 with 3.5 TB and DATA1 with 11 TB.
We were planning to reduce DATA2 to 2 TB and DATA1 to 12.5 TB.
I followed below steps to accomplish the task:
[As we are using normal redundency about 50% of the size specified here will be usable]
step 1> resize from ASM. Resize DATA2 to 2 TB[4 TB raw] (form 88GB to 50 GB per GRIDDISK, in grididsk we should consider raw size)
SQL> ALTER DISKGROUP DATA2 RESIZE ALL SIZE 50 G REBALANCE POWER 11;
step 2> resize GRIDDISKs of DATA2_* on each cell for each griddisk run below command
CellCLI> ALTER GRIDDISK griddisk_name size=50G;
[
--create scripts using below query:
--as in exadata each cell is a failgroup use
--failgroup to generate script for a cell
select 'ALTER GRIDDISK ' || name || ' size=50G;'
from v$asm_disk
where name like 'DATA2_%' and failgroup='FAILGROUP';
]
step 3> check griddisk detail for DATA2 [size:]
$ cellcli -e "list griddisk where name like 'DATA2_.*' detail"|grep size:
step 4> check size of a DATA1 griddisk [size:]
$ cellcli -e "list griddisk where name like 'DATA1_.*' detail"|grep size:
-- let the size = CURR_GD [270G]
step 5> check free space on CELLDISKs [freeSpace:]
$ cellcli -e "list celldisk where name like 'CD_.*' detail"|grep freeSpace:
-- let free space = FREE_CD [38G]
-- we want to increase the size by FREE_CD
-- (here, FREE_CD has been freed by shirnking
-- each DATA2 gridisks to 25 GB)
-- so, new size of each griddisk of DATA1,
-- NEW_SIZE = CURR_GD+FREE_CD (in GB, raw size)
-- [308 G=270+38]
step 6> resize GRIDDISKs of DATA1_* on each cell for each griddisk run below command
CellCLI> ALTER GRIDDISK griddisk_name size=NEW_SIZE G;
[
--create scripts using below query:
--as in exadata each cell is a failgroup use
--failgroup to generate script for a cell
select 'ALTER GRIDDISK ' || name || ' size=NEW_SIZE G;'
from v$asm_disk
where name like 'DATA1_%' and failgroup='FAILGROUP';
]
step 7> resize from ASM
SQL> ALTER DISKGROUP DATA1 RESIZE ALL REBALANCE POWER 11; --no size specified to allocate maximum availabe space on related griddisks
step 8> check status of all asmdisks using "v$asm_disk"
step 9> check DB alert logs
step 10> check cell alert history on each cell
CellCLI>list ALERTHISTORY
