DROP INDEX is sometimes quite slow}

[Former Member]

On a productive BI 7 system there are often batchjobs BI_PROCESS_DROPINDEX running.

The database is Oracle 10.2.0.4. Typically the DROP INDEX commands require only

a few seconds, but sometimes it takes several minutes to drop the first index of a

BW fact table, e.g.:

05.11.2009 15:15:33 SQL: 05.11.2009 15:15:33 ALEREMOTE 05.11.2009 15:15:33 DROP INDEX "/BIC/FOOS_PL_A~010" 05.11.2009 15:19:03 SQL-END: 05.11.2009 15:19:03 00:03:30 05.11.2009 15:19:03 SQL: 05.11.2009 15:19:03 ALEREMOTE 05.11.2009 15:19:03 DROP INDEX "/BIC/FOOS_PL_A~020" 05.11.2009 15:19:08 SQL-END: 05.11.2009 15:19:08 00:00:05 05.11.2009 15:19:08 SQL: 05.11.2009 15:19:08 ALEREMOTE 05.11.2009 15:19:08 DROP INDEX "/BIC/FOOS_PL_A~040" 05.11.2009 15:19:15 SQL-END: 05.11.2009 15:19:15 00:00:07 05.11.2009 15:19:15 SQL: 05.11.2009 15:19:15 ALEREMOTE 05.11.2009 15:19:15 DROP INDEX "/BIC/FOOS_PL_A~050" 05.11.2009 15:19:22 SQL-END: 05.11.2009 15:19:22 00:00:07 05.11.2009 15:19:22 SQL: 05.11.2009 15:19:22 ALEREMOTE

I ran an Oracle raw extended trace to find out what is causing such delays,

and this is what tkprof told me:

DROP INDEX "/BIC/FOOS_PL_A~010"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.34     204.46        162         71        914           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.34     204.46        162         71        914           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 23

There are two dominating wait events: 148 seconds for "enq: RO - fast object reuse"

and 55 seconds for "rdbms ipc reply". I already read note 845639 about the enqueue

waits, but here I get the suggestion to trace the 8 DBWR processes which would be

quite difficult to realize (I never know in advance whether a DROP INDEX will be

slow or not, so this requires lots of tracing.)

Has anyone else hit such an issue? Would the merge fix 8834933 from note 1333619 help?

Or do we have to activate dbfast_obj_truncate = FALSE ?

Regards,

Mark

[lbreddemann · Accepted solution]

Hello Mark,

I highly recommned to open a support message for that.

regards,

Lars

[Former Member]

Hello @,

just a short follow-up: The Oracle merge fix 8834933 really fixed this issue.

Regards,

Mark