Re: Save query and statistics automatic

On Feb 13, 5:30 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> 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.

I'm sorry, I just noticed this post. You're correct, it would be difficult to tell why it was slow with what you have above...but not if you parsed the trace file as Bobby suggested. Read up on 10046 trace (googling will return hundreds of results), as it stores everything you want.

HTH, Steve Received on Tue Feb 20 2007 - 13:26:02 CST