Re: view resources intensive SQL query

chuaby_at_hotmail.com wrote:
> Hi
>
> May i know are there any tables in Oracle 8i where i can check what are
> the SQL queries submitted (not necessarily current active session but
> queries run for the last weeks too ) by users but are very resource
> intensive ? this will help to highlight to users by identifying the
> query, space used by that query etc and to look into tuning this query.
>
> for some advice please
> Thank you
> Boon Yiang
>

The only view will show you those SQL statements that are currently in the shared pool. So you may lose historical information going back a week. I recommend using Statspack for this activity. The following URL shows you how to set it up:

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#34837

While the instructions are for Oracle 9i, they are the same for 8i.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown