Execution time agent

[new_otm]

Hi,
I have created an agent on order release, one of the actions is a direct sql with a stored procedure call. Users run the agent with an action.

My problem is the execution time. If I add the action on a domain D1, the agent took one minute to get the result, but if I create a new action and agent on other domain D2 that runs the same stored procedure it took 10 minutes or more.
Also I am not able to run the action on D2 from D1 but Domain D1 has ALL_TS grant on D2.

Does anyone know if there is any problem on direct sql with stored procedure , domains and subdomains.

Thanks you very much in advance.

[LourensGlog]

Re: Execution time agent

1 or 10 minute to get a result seems a long time for me. Do you have the same problem when you run a simple (non-DSU) agent with an action on both domains?

[new_otm]

Re: Execution time agent

Hi,
No i only have problems with this agent. others run successfully.

What i have noticed is that the first time i run the agent after create the action, screen set and so on it works correctly. The several times is when the agent takes 10 minutes.

Thanks.

[acuartero]

Re: Execution time agent

Hi,

It sounds suspiciously like a VPD performance issue. Is the agent set to run as object or run as user? What does your direct SQL do and what are the domain relationships between the objects it works on?

btw, Which version of OTM are you on?

-Alan

[new_otm]

Re: Execution time agent

Hi,

I have no vpd so may be that is not the problem.

The agent is on order_release actions menu and runs when the user wants.
The direct SQl call to a big stored procedure that it is on database.
Domain D1 can see all data from the second domain, and this one (D2) inherit master data such as location, servide providers, etc… from the first one.

We are using OTM 5.5 .

Thanks in advance

[acuartero]

Re: Execution time agent

Hi,

If you haven't done so you may want to turn on agent and exception logs to see exactly which agent action is taking 10 minutes to process.

If the problem does turn out to be the call to the stored procedure you may want to look further into a VPD related issue. You may not have a VPD profile configured but OTM is still using VPD internally to manage access across the domains. If for example you have the agent in D2 set to run as user and the user's context is D1 that may cause this type of performance issue. It's really hard to say without knowing more about the SQL and objects involved. A simple suggestion that you can try would be to change the context of the agent to run as object (assuming that the object exists in the same domain as the agent this should help). Beyond that you're probably talking about using SQL*Plus (or your favorite SQL utility i.e. TOAD) to generate an explain plan for the SQL with the VPD set to the correct context (via a call to stored procedure vpd.set_user()) in order to diagnose the problem.



-Alan

[new_otm]

Re: Execution time agent

Hi,

The stored procedure is the one that takes 10 minutes to finish.

What do you mean with running the agent as an object? including an event that makes the agent begins? I have tried this and the result is the same, it took 10 minutes to finish.

On agent ‘s direct sql I use ‘call glogowner.mystoredprocedurename (order_release_gid variable)’ . Where should I used the vpd.set_user?

Thanks.

[acuartero]

Re: Execution time agent

Hi,

On the bottom of the Agent manager screen (the first header screen you get when you choose to edit your agent) there is a drop down box that says "Run As". This controls the VPD context that all of the agent actions use when the execute. Setting this as OBJECT (in this case the Order_Release_Gid) as opposed to USER may help performance (it can also make it worse but it's worth a shot).

If you find that changing the agent VPD context does not help and you want to determine how the database is executing the SQL in the procedure then you should use the vpd.set_user() call before generating an explain plan for the SQL so you can see the performance impact of VPD. It can add quite a bit of overhead to even relatively simple SQL.

-Alan

[new_otm]

Re: Execution time agent

Hi,
I have tried to change the “run as” field but the execution time is the same ;-(.

What I have seen is, if I create master data on the domain in which I am running the agent (D2) the execution time is the one I expected. The problem is that I don’t want to duplicate data on database, I want to continue using grants and sharing data with domains.

Do you know if this could be the problem? Is any restriction on agent’s execution when domain have grants and master data is in other domain?


Thanks

[acuartero]

Re: Execution time agent

Hi,

Its possible that changing the context of the agent didn't impact performance because the user has the same context of the object. However what you're describing does sound like a VPD related performance issue. Especially since you showed that copying the objects to the domain D2 improves performance. While it is possible to selectively remove VPD from a table this does bypass a level of security in OTM. I think the best approach would be to fist try and optimize the SQL involved. I'm sure that if you were to post the SQL involved you would receive a number of suggestions that might help.

-Alan

[josephliang]

Re: Execution time agent

What I found, using "Run As" does not always help, unless you specify Role as DBA.ADMIN. You might need to change AGENT.RUN_AS_USER_ROLE_GID via TOAD or SQL Developer. This would allow the Agent to be executed without VPD. Please use this way with caution.

But I agreed with Alan's point. You should fine tune your SQL outside OTM first, then refer to the following thread to test SQL performance with VPD.
http://www.otmfaq.com/forums/f21/ins...rformance-513/

[new_otm]

Re: Execution time agent

Hi,



Thank you very much for your help, finally i have solved the problem. I have created the agent on public domain and the procedure execution only takes seconds to finish. The problem was the heredity of master data.

Thanks for your replays.

[new_otm]

Re: Execution time agent

Hi,
I have solved the execution time problem, creating the agent on public domain.

But know i get this error one minute after the agent begins exeception: {call glogowner.XX_UPDATE_OR(?)=null}; inParams=[FAG.09NAL_PRUEBA1003].

XX_UPDATE_OR is my procedure name and the param is the order relase selected.

Does anyone know how to solve this?

thanks.