Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to capture SQL

Re: How to capture SQL

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 20 Aug 2004 01:56:08 GMT
Message-ID: <YScVc.4866$QJ3.2354@newssvr21.news.prodigy.com>


Joel Garry wrote:

> "Randy Harris" <randy_at_SpamFree.com> wrote in message news:<TrxUc.2712$ZC7.249_at_newssvr19.news.prodigy.com>...
>

>>Is there a simple means of capturing an SQL statement sent from an
>>application to an Oracle 9i server (on the server end)?  I've been looking
>>through the documentation, not sure if this should be done with trace, or
>>auditing or if there is some way to enable SQL logging, or dump the redo log
>>or something else.

>
>
> If it is something that runs for a while: go into OEM 9.2 top
> sessions and select the session you want. The logon time is probably
> the best way to relate what session you want if your app doesn't show
> usernames unambiguously. Drill down to the explain plan. Click on
> the report icon. Show management what a brilliant performance
> analysis and documentation expert you are.
>
> jg
> --
> @home.com is bogus.
> "It means 'friend'" - Cheech
> http://www.guardian.co.uk/online/news/0,12597,1286066,00.html

Better yet, under the "Tools...Diagnostics Pack" menu, right next to the choice Joel mentions, is another one: "Top SQL". This queries the v$sql* views (apparently) to list whatever is still in memory, whether it took a while to run or not, so if you have a relatively small set of queries that have run since startup, you can see quite a bit of history here (history in the sense of what has been executed, but not when or who). There are lots of filtering options too.

(I've occasionally wondered if clicking on this OEM menu on my workstation means I must be licensed for Diagnostics Pack...)

If the OP will indicate what the goal is (tuning, auditing, etc) some choices would stand out as better than others.

--Mark Bole Received on Thu Aug 19 2004 - 20:56:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US