Question:
Hi BW gurus
We're runnig a 2.1C BW on a 8.1.7.3 Oracle DB.
In every Earlywatch session SAP recommends us to use locally managed tablespace for PSAPTEMP. They claim, it will increase the performance.
I made now several tests with our development system and changed the PSAPTEMP to a locally manged tablespace PSAPTEMP and PSAPTEMP2 according to several OSS notes.
The system was afterwards much slower (even after some uptime and runnig the same query several times).
Does anyone use loccaly managed tablespace with a 2.X BW?
Thanks for your answer
siam
Answer:
This is correct that you should use locally managed tablespaces and I automatically set all ts to locally managed for BW systems as the resource to maintain is actioned by the system, not a dba.
For performace, you should create many small datafiles for PSAPTEMP and place the datafiles on as many devices (discs) as possible. Then set the Parallell Query settting on the tablespace to 2x the number of CPU in your db server. This will vastly improve performance on the db read/write operations into the temp area.
Kevin
_________________
Its a data-warehouse
Performance is found in the database!!!
Answer:
Hello Kevin
Thank you for your answer.
I've created several datafiles with 2GB size each (all together 34GB).
As the filesystem is on a SAN, I can not take influence on which disk the files are stored. But I think this is managed very well by the SAN itself.
The parallel Query is activated already 2x the CPU of the server, which is 8 in our case.
I've created the tablespace as follow:
create temporary tablespace PSAPTEMP2 tempfile '<path to tempfile>' size 2000M extent management local uniform size 20M;
Could it be, that I've to take an other value for uniform size?
Thank you
siam
Answer:
PQ works on the datafile level as the slaves are each assigned to a data file, so ideally if you have 16 slaves, then you need sixteen datafiles.
The only other area if pq is active is the reads against the source table as this may be a cause for I/O contention. I would use filemon under unix/aix to analyse the I/O waits on the hdisks for the san.
Regards,
Kevin Pepper
_________________
Its a data-warehouse
Performance is found in the database!!!
Answer:
hi Kevin and guest
Can you send me tha procedure. I have same problem in my quries that running slowley do you guys thinks is this is going to help. As I am assuming that we can analye reads and writes thru technical stats in BW and then strip ( Infocubes) across thru various disks and suppose to use separate controlcards. do we need to change sql statement to add parallel in order to have advantage of "pq" Mode ?
Please advise
Thanks in Advance
KumarVn
Answer:
Please beware that if the system is not correctly built on the disc sub-system and that the datafiles and blocks with the files are not set correctly then Oracle PQ can cause serious performance loss.
I had a support call on one client site who switched on PQ and their Master Data Re-Alignment run went for 1.5 hours to 3 days. This was the same for queries.
Only experienced Oracle DBA's should contemplate the correct use of PQ as it is not a BW function. There are thousands of hints and performance measures available on the DB level that cannot be accessed by BW. I have used compsite partitioning on Oracle and hash on DB2 EEE. These two options provided some much needed performance increase for certain problems but are not supported by SAP.
Please tread carefully as the system can be trashed if not managed correctly.
Regards Kevin Pepper
IBM Senior SAP BW Architect
_________________
Its a data-warehouse
Performance is found in the database!!!
Answer:
HI kEVIN
i A GOING THRu various option and I have a doubt do we need to insert a word "parallel" in sql ? with out inserting in parallel the explain plan will show correct results ?
Kumarvn
Answer:
Oracle PQ is set in the init'SID'.ora file. This sets the min and max slaves for the instanceand then you set the degree of pq per each table with an alter table statement. Then the oracle stats must be rebuilt for the tables that you have changed the degree on.
These steps will inform the optimizer that pq is allowed for this table (and don't for the indexes).
Please do not blame me if you experience a performance decrease as I do not know the logical build of the oracle database for your system
Regards
Kevin Pepper
_________________
Its a data-warehouse
Performance is found in the database!!!
Answer:
hi Kevin
with 1000's table and 45 Infocubes I can not efford to do on production but I would like do on test box if management permits. Thanks a lot.
do you suggest any other way to improve query with out chnageing data design or modeling .
1.we are not useing any navigational attributes as too much use will decrease q performence.
2.we have already agrregates. and aggregares are optimized.
3.we have lineitems already.
4.already did e fact table is partitioned by time range and f table is partioned by request
5.we have sap bw technical stats runing every day.
the problem is we have too any hierachyies and hier nodes. The queries used to select thru 14 nodes to 19 note ( when you see from RSRT )
still our query is not working fast we are invistigated various other areas like network trafic,hardware enhancement and sizing etc
do you have advises ?
Kumarvn
Answer:
If you have large master data tables then I would suggest moving those tables and indexes to seperate table spaces as this would help reduce I/O.
BW is extremely I/O bound even if you use an ESS (All of my clients use either EMC or the IBM Shark) and yes the ESS does try to keep contention to a minimum but you must place all objects carefully on the hdisks that make up the vpath otherwise you will create I/O issues.
Also, I prefer to create and manage statistics on the oracle level and not by any SAP programs. You should always estimate for the tables but compute for the indexes.
I would need much more detail to provide a performance review and I doubt that this could be done over the forum. If you have any specific questions I will try and help.
Regards
Kevin Pepper
_________________
Its a data-warehouse
Performance is found in the database!!!
Answer:
Hi Kevin
SAP are recommending using the SAP programs to create/update the DB statistics. Especially the histogram stats. If you are doing the stats update from database level do you do it via the brconnect command line or do you just use the Oracle tools please clarify.
Thanks
Jeff