Note that there is datafile overhead for tablespaces with uniform size extent allocation .
i.e, eight 8k blocks used for 8k block_size tablespace and four 32k blocks for 32k block size tablespace.
eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes.
-- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).
-- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.
Please take note when extend tablespaces.
SYS > show parameter block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
2 extent management local uniform size 1m;
Tablespace created.
SYS > select file#, bytes ,creation_time from v$datafile where file#=27;
FILE# BYTES CREATION_
---------- ---------- ---------
27 104857600 27-MAY-11
SYS > create user test identified by test;
User created.
SYS > grant dba to test;
Grant succeeded.
SYS > alter user test default tablespace test;
User altered.
SYS > connect test/test;
Connected.
TEST > desc dba_free_space
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 103809024 3168
27
TEST > select 103809024/1048576 from dual;
103809024/1048576
-----------------
99
TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
TEST > select * from dba_extents where file_id=27;
no rows selected
TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;
OVERHEAD_BYTES
--------------
262144
TEST > select 104857600+262144 from dual;
104857600+262144
----------------
105119744
TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;
Database altered.
TEST > select * from dba_free_space where file_id=27;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 27 5 104857600 3200
27
TEST > prompt 100MB available for allocation
100MB available for allocation
TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;
Tablespace altered.
TEST > select * from v$datafile where file#=28;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#
------------------ --------- --------------------- --------- ------------
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI BYTES BLOCKS
--------- --------------- -------------- --------- ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON
------------------- ---------
28 93782698 27-MAY-11 8 28 ONLINE READ WRITE
93782699 27-MAY-11 0
0 0 10485760 320
10485760 32768
/ods001/oradata/ODSDMS/test2.dbf
0 32768
NONE
0
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
TEST > select 9437184/1048576 from dual;
9437184/1048576
---------------
9
TEST > select 32*1024*2+10485760 from dual;
32*1024*2+10485760
------------------
10551296
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10551296;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- -- 9 x 1MB extents
TEST > select 32*1024*3+10485760 from dual;
32*1024*3+10485760
------------------
10584064
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10584064 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 9437184 288
28
-- still 9 x 1MB extents
TEST > select 32*1024*4+10485760 "extra4blocks" from dual;
extra4blocks
------------
10616832
TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize 10616832 ;
Database altered.
TEST > select * from dba_free_space where file_id=28;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST 28 5 10485760 320
-- 10 x 1MB extents now
Friday, May 27, 2011
Thursday, May 26, 2011
11gR2 AWR runs slowly due to unrealistic system statistics
After database upgraded from 10.2.0.2 to 11.2.0.2, found AWR runs slower than 10g, the more snapshots covered the longer it takes.
Struggled for days, finally notice below system statistics in read is nor realistic to seem.
SYSP> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
02-23-2011 09:05
SYSSTATS_INFO DSTOP
02-23-2011 09:31
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1400.84388
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 28231.75
SYSSTATS_MAIN MREADTIM 24989.114
SYSSTATS_MAIN CPUSPEED 1170
SYSSTATS_MAIN MBRC 6
SYSSTATS_MAIN MAXTHR 96043008
SYSSTATS_MAIN SLAVETHR 3421184
13 rows selected.
actions:
1.) backup system statistics and delete it. Run AWR report promptly .
SYS@ODSP> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-26-2011 14:39
SYSSTATS_INFO DSTOP
05-26-2011 14:39
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1151
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
2.) Gather NOWORKLOAD system statistics. Run report okay.
select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-26-2011 14:46
SYSSTATS_INFO DSTOP
05-26-2011 14:46
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1152
SYSSTATS_MAIN IOSEEKTIM 6
SYSSTATS_MAIN IOTFRSPEED 34397
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
Finally from http://structureddata.org/2008/01/02/what-are-your-system-statistics/ , I realized it is a bug. Bug 9842771 Wrong SREADTIM and MREADTIM statistics in AUX_STATS$
The workaround is to manually set SYSTEM statistics or NOWORKLOAD system statistics.
Lesson learned again : understand what exactly you are going to do in depth. BTW, since this is a known bug, I think the complete checklist of upgrade guide should be updated.
Here are more informations about system statistics.
1. When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMS_STATS.GATHER_SYSTEM_STATS procedure. Oracle Corporation highly recommends that you gather system statistics.
gathering_mode
Mode values are:
NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
-- In this case, I use START|STOP to sample my database for 30 minutes during normal workload .
3. specs and units
CPUSPEED Workload CPU speed in millions of cycles/second
CPUSPEEDNW Noworkload CPU speed in millions of cycles/second
IOSEEKTIM Seek time + latency time + operating system overhead time in milliseconds
IOTFRSPEED Rate of a single read request in bytes/millisecond
MAXTHR Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC Average multiblock read count sequentially in blocks
MREADTIM Average time for a multi-block read request in milliseconds
SLAVETHR Average parallel slave I/O throughput in bytes/second
SREADTIM Average time for a single-block read request in milliseconds
Struggled for days, finally notice below system statistics in read is nor realistic to seem.
SYSP> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
02-23-2011 09:05
SYSSTATS_INFO DSTOP
02-23-2011 09:31
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1400.84388
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 28231.75
SYSSTATS_MAIN MREADTIM 24989.114
SYSSTATS_MAIN CPUSPEED 1170
SYSSTATS_MAIN MBRC 6
SYSSTATS_MAIN MAXTHR 96043008
SYSSTATS_MAIN SLAVETHR 3421184
13 rows selected.
actions:
1.) backup system statistics and delete it. Run AWR report promptly .
SYS@ODSP> select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-26-2011 14:39
SYSSTATS_INFO DSTOP
05-26-2011 14:39
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1151
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
2.) Gather NOWORKLOAD system statistics. Run report okay.
select * from aux_stats$;
SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED
SYSSTATS_INFO DSTART
05-26-2011 14:46
SYSSTATS_INFO DSTOP
05-26-2011 14:46
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1152
SYSSTATS_MAIN IOSEEKTIM 6
SYSSTATS_MAIN IOTFRSPEED 34397
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
Finally from http://structureddata.org/2008/01/02/what-are-your-system-statistics/ , I realized it is a bug. Bug 9842771 Wrong SREADTIM and MREADTIM statistics in AUX_STATS$
The workaround is to manually set SYSTEM statistics or NOWORKLOAD system statistics.
Lesson learned again : understand what exactly you are going to do in depth. BTW, since this is a known bug, I think the complete checklist of upgrade guide should be updated.
Here are more informations about system statistics.
1. When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMS_STATS.GATHER_SYSTEM_STATS procedure. Oracle Corporation highly recommends that you gather system statistics.
gathering_mode
Mode values are:
NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both 'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
-- In this case, I use START|STOP to sample my database for 30 minutes during normal workload .
3. specs and units
CPUSPEED Workload CPU speed in millions of cycles/second
CPUSPEEDNW Noworkload CPU speed in millions of cycles/second
IOSEEKTIM Seek time + latency time + operating system overhead time in milliseconds
IOTFRSPEED Rate of a single read request in bytes/millisecond
MAXTHR Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC Average multiblock read count sequentially in blocks
MREADTIM Average time for a multi-block read request in milliseconds
SLAVETHR Average parallel slave I/O throughput in bytes/second
SREADTIM Average time for a single-block read request in milliseconds
Subscribe to:
Posts (Atom)
