Tables reorg & index rebuilt - impact on batches

[Former Member]

Dear all,

Recently I performed online reorg and index rebuild for tables and indexes that were recommended by an Oracle Support.

Now I would like to see what was the impact of reorg and rebuild on my batches.

If I have the table name and  the index, how can I find which job is using it ?

I  would like to compare the execution times before and after the operation.

What would you suggest ?

Thanks in advance,

J.

[volker_borowski2 · Accepted solution]

Runtime in Seconds in SM37 as a starter ...

Volker

[Former Member · Accepted solution]

Hi Volker,

I know the table and indexes name, and based on that I want to know the names of the bacthes that use those tables and indexes. So how to find out which jobs were using the specific tables ? OR either way, if I have the table, how to find which programs are using it ?

Runtime in sm37 will not help, unless I know the names of the batches

Thanks in advance,

J.

[Former Member · Accepted solution]

Hi Jordan,

>> OR either way, if I have the table, how to find which programs are using it

You can identify the table used by programs on SE11 tcode, by using where used list

>> So how to find out which jobs were using the specific tables

After you identified the programs, you can find the scheduled jobs on SM37

As an additional info, you can check the numbers on ST04, if you need to compare statistical information, before and after reorg operation.

Best regards,

Orkun Gedik

[volker_borowski2 · Accepted solution]

Hi,

I was thinking of a more general approach.

Anything which is less than 5 minutes in BTC does not count at all.

So get todays list of long lasting nightly jobs, and sort the list by runtime.

Get the topmost 20-50 (whatever) and re select SM37 in a second window with only these jobs for 30 days back (one by one). Should take max. 20 secs per job so you could check the top 50 in ~ 15 minutes.

Any Jump in execution time related to the date of you reorg will be a good indication.

Volker

[Former Member · Accepted solution]

Thank you Orkun, Volker.

I will check it and get back to you.

Cheers!

J.