These holy wars have been continuing since the first database was created as a separate software. What is the right or even what is the best place to implement business logic: on DB side or on Application side? I collected a few links to very interesting articles and videos.
Why use PL/SQL?
Article from Oracle. It describes thick database paradigm providing benefits of using PL/SQL as access layer to the database.
Fill the Glass Episode 3 with Cary Millsap & Toon Koppelaars
Great video delivered by well known experts.
“Thick Database” Approach to Web Development
Presentation made by Paul Dorsey, big proponent of Thick Database.
Don’t be thick – Use a “Thick Database” approach
One more article from Paul Dorsey.
There is one pretty common and widely used approach how to process events in Oracle applications. Publisher INSERTs one row per event into the EVENTS table and Subscriber reads this table, processes the events and DELETEs the corresponding rows. But what if from one hand Publisher inserts events very frequently and you are interested in only last event from another hand. Also there is one additional assumption: the number of different events is relatively small. In this case we can replace INSERT operation to UPDATE with timestamp or event version.
Here I’ll describe this method with examples.
Here I’m going to consider how to use Instrumentation Library for Oracle (ILO) in case your stored procedure returns SYS_REFCURSOR as out parameter.
This is skeleton for the future article.
To instrument application we use DBMS_APPLICATION_INFO package.
To find top modules and actions we query ASH view V$ACTIVE_SESSION_HISTORY manually or indirectly using OEM. This view contains snapshots of V$SESSION taken every second for each active session. And there is a problem here. Duration of database calls should be more than 1 second to guarantee acccurate ASH statistics. But typical OLTP calls are quite short. So, what if you need accurate statistic how much time do your modules and actions take? Does Oracle DB have a native tool to gather such accurate statistic? Something except SQL Trace(10046 event), TRCSESS and TKPROF.
Of course, there are a lot of frameworks which allow you to measure execution time manually. But I’m looking for the native Oracle tool.
Things to consider:
PL/SQL Hierarchical Profiler
Using the PL/SQL Hierarchical Profiler
Instrumentation Library for Oracle (ILO)