on 06-29-2009 10:28 AM
Hello,
We are experiencing troubles with online reorganization of tables on our BI7 with oracle 10.2.0.4 with brspace since the size of our tables after the reorganization is bigger than before.
Our SAP system is at the following level:
Software component Release level
SAP_ABA 700 14
SAP_BASIS 700 14
ST-PI 2005_1_700 6
PI_BASIS 2005_1_700 14
SAP_BW 700 16
FINBASIS 600 7
BI_CONT 703 8
SEM-BW 600 7
ST-A/PI 01K_BCO700 0
The system is running under HP-UX B.11 at the release B.11.23 and with a kernel at the level 150.
The oracle patch 10.2.0.4 has been set thereu2019s one month ago.
We did not experienced that problem before the setup of the new oracle patch.
These are the parameters for one of our table, we reorganized and for which the table increased after the operation:
Options for reorganization of tables: SAPR3./BIC/EZDAYSLS1 (degree 1)
1 ~ New destination tablespace (newts) ........ [PSAPUSEREORD]
2 ~ Separate index tablespace (indts) ......... [PSAPUSEREORI]
3 - Parallel threads (parallel) ............... [1]
4 ~ Table/index parallel degree (degree) ...... []
5 - Create DDL statements (ddl) ............... [yes]
6 ~ Category of initial extent size (initial) . []
7 ~ Sort by fields of index (sortind) ......... []
8 - Table reorganization mode (mode) .......... [online]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
As a result, the table increased from 8 413 184 KB , 1 051 648 blocks and 287 extents, with 10% of free in used blocks to 10 071 616 KB, 1 258 952 blocks and 285 extents with 0% of free in used blocks.
As we have to get some place into our database in order to prepare future increases of tablespaces in our production system, do you know what we do wrong or what need to be changed?
Thanks
It is normal that E fact tables are larger after an online reorganization because they have no primary index. As a consequence the ROWID based reorganization is used and a (finally hidden) ROWID column is added to the target table. This can result in a space increase of up to 30 %. Therefore I would use other reorg approaches like MOVE ("-m offline" option of BRSPACE) for F and E fact tables whenever possible.
Kind regards
Martin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
please update the message with the output of the statement
select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name in ('PSAPUSEREORD', 'PSAPUSEREORI')
so that we can check whether this could be related to the used extent management.
Best Regards,
Michael
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
before doing this activity run Database Statics and rebuild indexs as well. u can run online tabel reorgnization as well.
before doing such type of activity do not forget to take any type of backup sa well.
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
There's nothing into the log about the drop table step. Nevertheless, as the reorganization has been performed with a move into another tablespace, the drop of the table into the old one does impact the size of the table into the new tablespace.
Christophe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
two questions here:
1. what's the type of your tablespaces(ASSM etc) for old and new?
2. how you caculate the size and free space from the table? By dbms_space?
--->As a result, the table increased from 8 413 184 KB , 1 051 648 blocks and 287 extents, with 10% of free in used blocks to 10 071 616 KB, 1 258 952 blocks and 285 extents with 0% of free in used blocks.
Regards,
Mike
Hello,
The SAP note 48180 is related to an oracle release 7 to 9. Currently we have oracle 10.2.0.4 and compared to the note we perform the reorganization with brtools (brspace) and not with sapdba.
Our table in exemple at the beginning of this thread has been enlarged of quite 2GB.
Christophe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
I am not sure but is BRTOOLS skipping drop table step after moving it to new tablespace? Can you please verify with the reorganization log?
You can manually do reorganization from command prompt. Procedure is described in OSS 646681 - Reorganizing tables with BRSPACE
Check section IV,
Thanks and Regards
Anindya
Hi,
check SAP Note 48180 - SAPDBA: Enlarge Used Space in reorganization
Thanks
Sunny
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.