Re: Save query and statistics automatic

On Feb 13, 6:12 am, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> On Feb 11, 5:16 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
>
>
>
>
> > On Feb 10, 10:59 pm, bdurrettc..._at_yahoo.com wrote:
>
> > > On Feb 10, 9:26 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
> > > > Hi All,
> > > > Is it possible to store, query with username, who is executing it,
> > > > with osuser, with time taken by that query plus number rows processes.
> > > > Actually I want to store each query with right literals not bind
> > > > variable, which are store in v$sqlarea(including query executes twice
> > > > with two different stats), which is executed by user between 8 PM to 9
> > > > PM in a table. Is it possible to store it with details.
> > > > Please help.
>
> > > You could turn on 10046 level 12 tracing on all of the sessions that
> > > you need to monitor and then dig through the trace files afterward.
> > > That will store the values of your bind variables.
>
> > > - Bobby
>
> > Thank you for your reply Bobby.
> > But I want to store these queries in table with values I mentioned
> > above. Is it possible.
>
> I don't understand why the suggestion wouldn't work. You just have to
> parse the raw trace file for what you want, and then insert the data
> in which you are interested into your table.
>
> Regards,
>
> Steve- Hide quoted text -
>
> - Show quoted text -

I try to make you understand by giving one example. I have a query, whould you please tell why this query taking huge buffers and disk read without knowing information like how many records were fetch by this query, what was the time taken to execute it etc.

SELECT sc_user.tril_gid FROM sc_person, sc_user WHERE (((UPPER(sc_person.QuickLookID) = :1 AND (sc_user.IsMarkDeleted=:2 OR sc_user.IsMarkDeleted IS NULL)

         AND sc_user.Status = :3) AND (sc_user.WOTTeamWeb=:4 OR sc_user.WOTTeamWeb=:5))
AND sc_user.Person = sc_person.tril_gid ORDER BY sc_user.tril_gid

Disk Read    Execution       Buffer Gets         Sort
5347734       2959               478778563          0

I can see the exact query from software because it is third party software. I have to find exact query from database only. Received on Tue Feb 13 2007 - 04:30:37 CST