Reorganization online BSIS takes a long time

[former_member209959]

Hi,

We've started the reorganization of the implicated tables in the archived. Until now we didn't have any issues but the BSIS, which is the bigest with 140 Gb (index included), is taking a long time in the phase:

/* MV_REFRESH (MRG) */ MERGE INTO "SAPR3"."BSIS#$ --> this is the head of the execution plan

We run the reorganization 24 hours ago, and it started this phase 18 hours ago, and it still continues without apparently doing anything.

Is it normal this behaviour?

Regards.

[Former Member]

Hi,

Check for the table name/size of SAPR3.BSIS#$

It shows you the size that reorg process has done.

Online reorg doesn't impact any of your live operations, just monitor the process and wait for the process to complete.

Regards,

Nick Loy

[stefan_koehler]

Hi Maximino,

> Is it normal this behaviour?

DBMS_REDEFINITION is based on materialized views "under the hood". So that SQL makes sense in your context.

> We run the reorganization 24 hours ago, and it started this phase 18 hours ago, and it still continues without apparently doing anything.

What's the (main) wait event for that MERGE statement? I can imagine that this particular recursive SQL (never traced it in detail) is executed by PL/SQL procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE, which needs a lock in consequence.

However it may also be possible that BR*Tools are using PL/SQL procedure DBMS_REDEFINITION.SYNC_INTERIM_TABLE to keep the overhead as tiny as possible at the end of the reorganization for large tables.

As you can see we need more information from your side to answer your question

Regards

Stefan

[former_member209959]

Hi Darryl and Stefan and thanks for your answers,

Maybe I didn't explain too well. I wanted to say if it's normal that the reorganization was taking  so time (more than 20 hours) doing the merge.

It doesn't matter, because finally it was canceled because a "snapshot too old".

Now we decide to shinrk the table in instead of the reorganization.

Regards.

[former_member193518]

Hi Maximino,

Sorry to hear it failed.

You could also try rebuilding the indexes on the table before your try a table reorg.

This may help speed up the selection of the data if the indexes are highly fragmented.

Easiest check for significant fragmentation, is if the index sizes are larger than the table size.

Be careful with the DBMS_SPACE shrink.  Make sure you read the SAP notes containing the warnings first!  It's not particularly reliable at certain Oracle patch-set levels.

Good luck.

Darryl

[stefan_koehler]

Hi Maximino,

> I wanted to say if it's normal that the reorganization was taking  so time (more than 20 hours) doing the merge.

That is impossible to answer without the context information. It is the same like asking "I traveled 20 km today - is this normal"? By what by train, by foot, by car, by plain?

It depends on so many factors like I/O sub system, amount of changed data while building up of interim table, amount of indexes, parallelism, long running parallel DMLs on source, final merge or partial merge, etc.. That is the reason why i have requested more context information.

> This may help speed up the selection of the data if the indexes are highly fragmented.

@ Darryl: Can you please explain this in case of an reorg by DBMS_REDEFINITION? I don't get your conclusion in case of MVs and newly created interim table and corresponding indexes by DBMS_REDEFINITION.

Regards

Stefan

[former_member193518]

Hi Stefan,

BSIS is a big table.
If the indexes are shot to pieces, it's possible that Oracle is deciding to full tablescan.

I was thinking of what you said about locking.  Reducing I/O on the table is going to help reduce locks (during INSERTs/UPDATEs etc), reduce I/O and hopefully speed up the entire process as the DB could then have time to concentrate on doing other stuff (like build the interim table).

It's a long shot, but if it's a large table, making improvements in little chunks might be easier...

[stefan_koehler]

Hi Darryl,

thank you. I think you should re-check how DBMS_REDEFINITION works.

In case of creating the interim table a FTS (with direct path reads on larger segments) is usually used on the source table (disregarding special cases) with an APPEND HINT by building up the interim table. The fragmentation of the indexes (of the source table) is totally insignificant here. After that table data copy has finished new indexes are created on the interim table.

My comment about the lock was the exclusive lock that is needed by PL/SQL procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE (MV merge and switch). This lock can be delayed forever in bad cases, if there are contiguous ongoing (uncommitted) DMLs on the source table. Starting with Oracle 12c there is an parameter option "called dml_lock_timeout" in PL/SQL procedure DBMS_REDEFINITION.FINISH_REDEF_TABLE, but the default is wait mode furthermore.

Regards

Stefan

[former_member193518]

Hi Stefan,

I thought you would say that.

I'm not thinking about the way that redefinition works.  I'm thinking about the way that any reduction in I/O on that table is going to reduce Oracle's workload specifically because it is building the (large) interim table with a full table scan.

If you did understand me on that, then what you're saying is:

When Oracle is busy performing a full table scan on a large table, other requests that also do a full tablescan on the same table will not be impacted, and it would be fruitless to rebuild any highly fragmented indexes that could potentially help reduce I/O.

I stand by my thinking that trying (there's no guarantee) to reduce I/O on that table would improve the overall performance of the redefinition, because the overall DB workload would have reduced.
You can see it takes a looong time to redefine at the moment, so why not check the current indexes.

You're right, it won't change the way the redefinition thinks/works, but it might just help it along by giving it more DB resources.

Other than that, my reasoning is to do it offline.

Regards,

Darryl

[former_member209959]

Hi Darryl,

Now we are rebuilding the indexes and then we will try to make the shrink.

Regards.

[former_member193518]

What do you mean it's not doing anything?

See the Oracle docs for the MERGE statement:  MERGE

It's inserting into the SAPR3.BSIS#$ table.

Once it has finished building this new table, it can start to re-build it all back in the original table name.

BSIS is a large table and it may take a long time depending on your system performance.

I hope you have enough archive log space for this work.

I would have recommended an offline reorg on this table, but it depends on your SLAs I suppose.

Regards,

Darryl

[Former Member]

Hi Darryl,

Offline reorg is old fashion/process that I do not recommend, because online reorg works pretty fine with any size and it doesn't impact live operations . If you have enough resources on system, there won't be any difference.

Regards,

Nick Loy