Transaction executing slow and database undo log increasing soon.

[Former Member]

Dears,

I developed a transaction which query database many times in a repeater loop and finally generate a SAP MII XML Output Document which I want to display in a html hyper link in MII Navigation. (Using XacuteQuery and iGrid)

I found that

1. if I execute in SAP MII Workbench, the transaction executing very slow, and also the database undo log in D:\oracle\TMI\sapdata2\undo_1\UNDO.DATA1 increasing soon.

2. If I use MII Schedule Edit to run the transaction, it executing fast.

Does any one know why?

Is there any setting can make it executing fast in a MII Workbench?

Many thanks!

Ivan

[Former Member · Accepted solution]

Hi,

Is there any difference between the values you are using for the transaction input parameters while using the scheduler compared to when executing from the WB?

Thanks

Udayan

[Former Member · Accepted solution]

Hi Udayan,

I used exactly the same input parameters to compare.

Thanks!

[Former Member · Accepted solution]

Hmmm... strange. There shouldn't really be any difference as both the calls are pointing to the same transaction on the server.

So how did you compare this I mean is it just a visual difference that you are observing or you have something quantitative in both the cases?

What version of MII are you on?

[Former Member · Accepted solution]

I can see the executing duration time in Transaction Detail to compare both case.

The MII version is 12.1.7.50.

Besides transaction executing time, I found that I can also see the log in MII->System Management->Transaction Manager->Transaction Detail if executing in MII Workbench.

But I can not see the log if executing in Scheduler.

Because my transaction calling another transaction in a repeater, so it writing many log, I think it takes lots of time if writing many log.

Is there any setting to disable the transaction log? Maybe it can shorten the executing time.

Thanks!

[agentry_src · Accepted solution]

There is additional overhead processing when running in the Workbench (just think of the Execution window that comes up and the Tracers function). It can be significant depending upon your transaction structure.

Regards,

Mike

[Former Member · Accepted solution]

Hi Mike,

Thanks for your information.

But I've removed all Tracer actions in transaction.

The executing time should not be a big difference (2.5 minutes and 14 minutes)

The database archive log in C:\oracle\TMI\oraarch is also big difference. (under 44M and 6G)

So it really did more lots of jobs if executing in Wrokbench.

Thanks!

[Former Member · Accepted solution]

Hi Ivan,

I think Problem with oracle query or oracle query joining tables.

chek your oracle query out side transaction and debug your transaction logic.

Thanks,

Ramesh

[Former Member · Accepted solution]

Hi Ramesh,

Thank you for your information.

But if it's logic or sql problem. Why it's running fast and successfully in MII Scheduler?

Thanks!

[Former Member · Accepted solution]

Hi Ivan,

It depends on your SQL joining and BLS logic.Can you tell me about your SQL query what your getting data from query and modification from BLS side.

Thanks,

Ramesh

[Former Member · Accepted solution]

Hi,

Can you explain why it's different by using MII Workbench and Scheduler depending on sql joining and logic?

My transaction logical is basically as below,

1. query qualified sfc in SAPME tables

SELECT *

FROM (SELECT s.site, s.sfc, ss.operation_bo, ss.qty_in_queue,

ss.qty_in_work, s.priority, s.item_bo, s.shop_order_bo,

s.status_bo, ss.sfc_router_bo, ss.step_id, ss.step_sequence,

st.status_description, cf.ATTRIBUTE, cf.VALUE

FROM sfc_step ss,

sfc s,

sfc_router sr,

sfc_routing srt,

status st,

custom_fields cf

WHERE sr.handle = ss.sfc_router_bo

AND srt.handle = sr.sfc_routing_bo

AND s.handle = srt.sfc_bo

AND st.handle = s.status_bo

AND SUBSTR (s.status_bo, -3) IN ('402', '403', '404')

AND sr.handle = ss.sfc_router_bo

AND sr.in_use = 'true'

AND ((ss.qty_in_queue > 0) OR (ss.qty_in_work > 0))

AND s.site = '[Param.1]'

AND cf.handle(+) = s.handle

AND cf.ATTRIBUTE(+) = 'QTIMECONTROL'

[Param.2]

ORDER BY s.priority DESC, s.sfc)

WHERE (ATTRIBUTE = 'QTIMECONTROL' AND VALUE != 'N') OR VALUE IS NULL

2. use Repeater to query sfc's activity_log table

SELECT al.site, al.sfc, al.operation, al.operation_revision, op.description,

al.step_id,

TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'),

'YYYY-MM-DD'

)

|| 'T'

|| TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'), 'HH24:MI:SS')

AS date_time,

TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'),

'YYYY/MM/DD HH24:MI:SS'

) AS complete_date,

cf.VALUE AS qtime, action_code, ss.operation_bo AS current_op,

ss.step_id AS current_step_id,

ss.step_sequence AS current_setp_sequence,

al.item || ',' || al.item_revision AS item,

TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS check_time,

(sysdate-NEW_TIME (date_time, 'PST', 'GMT'))2460 as difference

FROM activity_log al,

custom_fields cf,

sfc s,

sfc_routing srg,

sfc_router sr,

sfc_step ss,

operation op

WHERE al.site = '[Param.1]'

AND (action_code IN( 'COMPLETE' , 'START' , 'SIGNOFF'))

AND al.sfc = '[Param.2]'

AND cf.handle(+) =

'OperationBO:'

|| al.site

|| ','

|| al.operation

|| ','

|| al.operation_revision

AND cf.ATTRIBUTE(+) = 'QTIME'

AND s.handle = srg.sfc_bo

AND srg.handle = sr.sfc_routing_bo

AND 'true' = sr.in_use

AND sr.handle = ss.sfc_router_bo

AND 0 < ss.qty_in_queue + ss.qty_in_work

AND s.sfc = al.sfc

AND al.operation = op.operation

AND al.operation_revision = op.revision

AND op.site= '[Param.1]'

AND al.operation NOT LIKE '%-W'

ORDER BY date_time DESC

3. call another transaction to parse input data and get output data

4. parse get back data to form a MII xml output Document.

Thanks!

[agentry_src · Accepted solution]

Hi,

I can't urge this strongly enough!

Build a couple of custom views in the ME database (or check for ones that may already exist). Your complex queries will run so much faster than having them execute in MII as written.

Yes, MII can pass it over to the relational database, but take advantage of the power and tools of the relational database.

Regards,

Mike

[Former Member · Accepted solution]

Hi,

Even the SQL is a little bit complex, but actually it reply in very short time. The transaction takes long time should not because of SQL query time, it could be because of taking time to calling another transaction.

But because it run fast in MII Scheduler, I'm trying find a way to run it in MII Workbench as fast as in MII Scheduler. (such as how to disable the log or trace of transaction to database, it takes long time and generate huge archive log).

Thanks!