on 03-11-2009 9:11 AM
Hi,
We have a custom program which runs as the background job. It runs every 2 hours.
Itu2019s taking more time than expected on ECC6 SR2 & SR3 on Oracle 10.2.0.4. We found that it taking more time while executing native SQL on DBA_EXTENTS. When we tried to fetch less number of records from DBA_EXTENTS, it works fine.
But we need the program to fetch all the records.
But it works fine on ECC5 on 10.2.0.2 & 10.2.0.4.
Here is the SQL statement:
EXEC SQL PERFORMING SAP_GET_EXT_PERF.
SELECT OWNER, SEGMENT_NAME, PARTITION_NAME,
SEGMENT_TYPE, TABLESPACE_NAME,
EXTENT_ID, FILE_ID, BLOCK_ID, BYTES
FROM SYS.DBA_EXTENTS
WHERE OWNER LIKE 'SAP%'
INTO
: EXTENTS_TBL-OWNER, :EXTENTS_TBL-SEGMENT_NAME,
:EXTENTS_TBL-PARTITION_NAME,
:EXTENTS_TBL-SEGMENT_TYPE , :EXTENTS_TBL-TABLESPACE_NAME,
:EXTENTS_TBL-EXTENT_ID, :EXTENTS_TBL-FILE_ID,
:EXTENTS_TBL-BLOCK_ID, :EXTENTS_TBL-BYTES
ENDEXEC.
Can somebody suggest what has to be done?
Has something changed in SAP7 (wrt background job etc) or do we need to fine tune the SQL statement?
Regards,
Vivdha
Hi Vivdha,
Check where the program is taking more time in ST03 (either at Database or WorkProcess or CPU..etc) and if its Database then you need to decide with the ABAPer about debugging the program.
Regards,
Kalyan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please update the DB statistics.
using ORASID execute following command on DB server.
brconnect -c -u / -f stats -t all u2013f collect -p 4
Also make sure you run this command during off-pick period. It might create a slight performance issue.
After successful collection of DB statistics, execute your backgound job and verify the timelines.
Regards,
Sachin Rane.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
there was an issue with LMT's but that was fixed in 10.2.0.4
besides missing system statistics:
But WHY do you collect every 2 hours this information? The dba_extents view is based on really heavy used system tables.
Normally , you would start queries of this type against dba_extents ie. to identify corrupt blocks and such:
SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &AFN
AND &BLOCKNO BETWEEN block_id AND block_id + blocks -1
Not sure what you want to achieve with it.
There are monitoring tools (OEM ?) around that may cover your needs.
Bye
yk
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Vivdha,
i am not sure about the differences between SAP ECC5 and ECC6, but have you collected dictionary statistics (for SYS/SYSTEM objects) on your Oracle 10g databases?
Check sapnote #838725 for more information about gathering dictionary/system statistics, etc.
Regards
Stefan
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 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.