There is a need to understand what will be the impact when resizing some 8000 plus datafiles consecutively. After some search did some test like enabled 10046 trace and captured what will happen when there is a datafile resize.
Used tkprof to format and provided the output to customer explaining the behavior.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='mytrace_10046';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m;
Database altered.
SQL> ALTER SESSION SET EVENTS='10046 trace name context off';
Session altered.
SQL> oradebug tracefile_name
D:\ORACLE_DB\diag\rdbms\testdb\testdb\trace\testdb_ora_556_mytrace_10046.trc
SQL>
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.01 1.96 0 0 3 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 1.99 0 0 3 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 31 0.03 0.10
db file sequential read 2 0.02 0.04
Disk file operations I/O 3 0.01 0.04
Data file init write 1 0.00 0.00
db file single write 1 0.00 0.00
control file parallel write 3 0.00 0.00
rdbms ipc reply 1 0.21 0.21
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 10.41 10.41
********************************************************************************
Used tkprof to format and provided the output to customer explaining the behavior.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='mytrace_10046';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m;
Database altered.
SQL> ALTER SESSION SET EVENTS='10046 trace name context off';
Session altered.
SQL> oradebug tracefile_name
D:\ORACLE_DB\diag\rdbms\testdb\testdb\trace\testdb_ora_556_mytrace_10046.trc
SQL>
TKPROF Output.
alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150mcall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.01 1.96 0 0 3 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 1.99 0 0 3 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 31 0.03 0.10
db file sequential read 2 0.02 0.04
Disk file operations I/O 3 0.01 0.04
Data file init write 1 0.00 0.00
db file single write 1 0.00 0.00
control file parallel write 3 0.00 0.00
rdbms ipc reply 1 0.21 0.21
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 10.41 10.41
********************************************************************************
Recommendation given ,
-- See the above test, when there is a resize it will go through all this events
-- This test is done on my local machine where there is a very minimal load.
-- As it took 1.99s for a single file there will be more time considering your server.
-- Considering other factors like OS/ storage performance, it will take more time
-- Do it in a small batches and in a sequential manner. So that it will not harm database performance.
-- Also it is best to test a small batch to estimate run time in non business hours and proceed with the activity.
Thanks for reading, post your suggestions / comments on this. Happy reading.