Home » RDBMS Server » Performance Tuning » Query monitoring/intercepting (10g)
Query monitoring/intercepting [message #438679] Mon, 11 January 2010 14:09 Go to next message
newerakb
Messages: 3
Registered: January 2010
Junior Member
Wasn't sure exactly where to put this, sorry if I'm in the wrong place.

I was brainstorming for an Oracle->Teradata Data Warehouse ETL process, and was wondering if it's possible to run an application on the Oracle side that constantly monitors incoming queries, and triggers a separate action on certain conditions.

Example: If a user or batch runs a DELETE statement, when the DELETE query is sent to the parser (or wherever Oracle sends queries, I'm a Teradata guy), the application will see the query and write it to a separate transaction table.

Essentially we don't want to have to update hundreds of separate applications to put new logging transactions in every time we delete. It would be better if a single application ran at a lower level that monitored every transaction by itself. This would allow our ETL process to just pick up the transaction list.

Thanks for any suggestions.

-Kevin
Re: Query monitoring/intercepting [message #438680 is a reply to message #438679] Mon, 11 January 2010 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
I must admit you have a vivid imagination, unconstrained by reality

Nothing is impossible for the person who does not have to do it.

Instead of burdening Oracle with the SQL capture, I suggest you modify the application doing the SQL to add it to your transaction list.

Re: Query monitoring/intercepting [message #438681 is a reply to message #438680] Mon, 11 January 2010 14:32 Go to previous messageGo to next message
newerakb
Messages: 3
Registered: January 2010
Junior Member
BlackSwan wrote on Mon, 11 January 2010 14:20
I suggest you modify the application doing the SQL to add it to your transaction list.


What would 'the application' be in this instance? We have hundreds of PL/SQL batches that would have to be updated with improved transaction logging to allow for any sort of accurate ETL process. Either way the work has to be done on Oracle.

I'm trying to think of a way to make life EASIER on the Oracle DEVs, not harder.
Re: Query monitoring/intercepting [message #438683 is a reply to message #438681] Mon, 11 January 2010 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Either way the work has to be done on Oracle.
The requirements are not clear to this reader so I am not sure the following will benefit you.

research Oracle's CHANGE DATA CAPTURE capability.

Good Luck!
Re: Query monitoring/intercepting [message #438687 is a reply to message #438683] Mon, 11 January 2010 15:20 Go to previous message
newerakb
Messages: 3
Registered: January 2010
Junior Member
That looks exactly like what I'm looking for. Thanks a lot!
Previous Topic: how to reduce the response time of the sql query
Next Topic: 11g tuning
Goto Forum:
  


Current Time: Mon Sep 26 19:37:42 CDT 2016

Total time taken to generate the page: 0.08789 seconds