Stored Procedures

[ajoybasu]

Hi,

I am using Stored Procedures to perform certain calculations and set the statuses based on the rules. But we found that any updates being my made the Stored procedure does not reflect immediatley on the OTM screens. The changes become visible only after a certain amount of time.

Can you let me know why this is so . Is there some Cache settings that needs to be updated or changed.

Are there are any disadvantages of using Stored Procedures when compared to Agents with DirectSQL?

Regards
Ajoy

[chrisplough]

Re: Stored Procedures

Ajoy,

You've hit against the main disadvantage of stored procedures within OTM. The App server utilizes many large caches in order to maintain performance levels. Disabling these caches isn't an option within the application -- and if it was, would degrade performance significantly.

The only supported way to ensure that these changes are immediately reflected are to utilize Agents with DirectSQL, so that the application becomes aware of the execution and then updates the appropriate caches. The disadvantage of this approach is that Agents are significantly more expensive (in terms of performance) than stored procedures.

Thanks!
--Chris

[runinord]

Re: Stored Procedures

Ajoy,

You've hit against the main disadvantage of stored procedures within OTM. The App server utilizes many large caches in order to maintain performance levels. Disabling these caches isn't an option within the application -- and if it was, would degrade performance significantly.

The only supported way to ensure that these changes are immediately reflected are to utilize Agents with DirectSQL, so that the application becomes aware of the execution and then updates the appropriate caches. The disadvantage of this approach is that Agents are significantly more expensive (in terms of performance) than stored procedures.

Thanks!
--Chris

I actually use stored procedures in the Agent action section, by setting a direct sql update and in the direct sql update specify that it is a stored procedure and then do the call to the stored procedure (call glogowner.procedure_name(argument)) and you then have the option to refresh the cache and values are available immediatly.

[chrisplough]

Re: Stored Procedures

Very good point - that will ensure the caches refresh. The only thing I would add is to then do an analysis and ensure that the performance gained by the stored procedure (and subsequent cache refresh) is as expected.

--Chris

[bluesdude]

Re: Stored Procedures

Very good point - that will ensure the caches refresh. The only thing I would add is to then do an analysis and ensure that the performance gained by the stored procedure (and subsequent cache refresh) is as expected.

--Chris

Hi Chris,
Interesting post. I normally use Pl/sql code when the overall logic is too complicated to be implemented within standard agent variables and saved queries.

Also agents have a limit of 99 lines in its body section and using Pl sql addresses this limitation.

My main gripe with agents is transaction control, or the lack thereof, in a multi sql statement transaction.

Cheers,
Simon.

[jlgottlieb]

Re: Stored Procedures

There is a bug in version 5.5, where the cache is not refreshed even if you check the refresh cache box on your direct sql update in your agent.

I don't know when this bug was introduced, but I had many problems when I went from 5.5 CU3 to 5.5 CU5 RU4. I reported problems several times and finally was told that there is a bug 9049403 - fixed in 5.5. CU6-RU1 (I think OTM had a cache issue with deletes before this with a lazy delete as I had occasional problems in CU3 with deletes from an Agent with both a sql statement and stored procedure).

To get around this, I do a DIRECT SQL UPDATE (setting it as a STORED PROCEDURE - do not check refresh cache), then I do a 2nd DIRECT SQL UPDATE (as a SQL STATEMENT - and check refresh cache.

The 2nd DIRECT SQL UPDATE is a dummy update. Example for a Shipment Agent:

update shipment
set shipment_name = 'DUMMY_SQL_UPDATE'
where shipment_gid = $gid
and 1=2

This statement will never update any row because of the 1=2 in the where clause, but since the refresh cache is checked it will refresh the cache.

Its not the most elegant of solutions, but it works. My stored procedures have too much logic in it to be replaced by simple sql statements.

If your making multiple DIRECT SQL UPDATES in an Agent, you only have to call this dummy update once, after all your other updates.

I actually have a SHIPMENT_REFRESH_CACHE agent, and an ORDER_RELEASE_REFRESH_CACHE agent that simply does this type of dummy update and refresh. I have had occaision to make some updates directly to the tables via CSV or sql, then I have a data_query which I update that selects all the objects I want to refresh, and raises a custom event which then triggers the REFRESH_CACHE Agent for each object in the query.

Hope this helps.

Janice