Re: Activate SQL logging

Mark A wrote:
> > > > How can I capture incoming SQL statements to a text file?
> > >
> > > AFAIK, there is no (simple?) way to do this.
> > >
> > > Why in the world do you even want to consider doing this?
> > >
> > > The contents of the "text file" would be subject to
security/privacy
> > > compromises.
> >
> > Just trying to figure out what an application is doing to the
database. I
> > thought there would be some way to activate a log, that would
capture the
> > SQL.
> >
> This can easily be done in DB2, using either a statement monitor and
> snapshot (logs all SQL between when the monitor is turned on and then
turned
> off). Lots of information is provided such as number of executions,
average
> execution time, average CPU time, etc. This can be done globally, or
for a
> specific application.
>
> You can also use the Event Monitor to track specific SQL events and
notify
> you when they occur. If a more powerful tool is needed, the DB2 Query
> Patroller can be used.

Lovely, however of what actual use is this 'response' to the original poster, since he's using Oracle? Absolutely none.

The two valid options are:

1. Set sql_trace to TRUE at either the session level or for the entire instance. This will capture the SQL for either the affected sessions or for every session instance-wide. Using tkprof will massage this trace output into usable information, including execution plans provided the explain=uer/pass option is passed to tkprof.
2. Use logminer to 'peruse' your redo logs and, if you have them, archived logs. There are scripts provided in 8.1.7.4 to install logminer, and these must be run under the SYS account. Once installed it should be fairly simple to get running.

Why such a useless post was given in response to the original question, KNOWING this is an Oracle newsgroup, would normally baffle me. Noting who posted it makes it all the more obvious Oracle bashing is occurring, in spades, probably due to the failure of this poster to get any response close to 'wow, DB2 must be good' to his original TPC-C troll that pitted Oracle against DB2 under extremely different configurations, both in hardware and in schema design.

Such behaviour is childish and rude, and does no one any real service outside of the responder who possibly gleans satisfaction from knowing his useless information has disrupted an otherwise useful thread. This is reminicent of the playground bully disrupting activity for his own amusement. I hope you are amused, Mr. "Mark A", or whoever you *really* may be. We certainly are not. Of course, it is your right to post in any newsgroup you wish, posting whatever you wish, to whomever asks any question whatsoever. And, it is my right to killfile your useless antics to prevent any further disruption of threads you, Mr. "Mark A", may choose to inflict.

David Fitzjarrell Received on Sun Feb 13 2005 - 18:01:14 CST