cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with background job--taking more time

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member603052
Contributor
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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