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

Home -> Community -> Usenet -> c.d.o.server -> Re: sql statements executing

Re: sql statements executing

From: Ben Stafford <bstafford_at_sqlinsight.com>
Date: Thu, 16 Jan 2003 02:15:05 GMT
Message-ID: <JAoV9.208$rD.33223773@newssvr30.news.prodigy.com>


The easiest way is checking the v$sqlarea... the following query will retrieve the information you need. Note, however, that the v$sqlarea only holds a limited cache, older statements will drop off. This will work fine if you're dealing with a data warehouse or such...

select

  sa.first_load_time,
  au.username,
  sa.sql_text,
  sa.sharable_mem,
  sa.persistent_mem,
  sa.runtime_mem,
  sa.sorts,
  sa.version_count,
  sa.executions,
  sa.loads,
  sa.parse_calls,
  sa.disk_reads,
  sa.buffer_gets,
  sa.rows_processed,
  sa.users_opening,
  sa.optimizer_mode,

  rawtohex(sa.address) address,
  sa.hash_value
from
  v$sqlarea sa,
  all_users au
where
  sa.executions >= 1
and sa.parsing_user_id <> 0
and sa.parsing_user_id = au.user_id(+)
order by
  sa.first_load_time desc

I'd also suggest using a tool such as SQL Insight, which has an SGA Browser, that can provide you with even more information and tuning abilities. www.sqlinsight.com

Ben Stafford

"Amit Kaushal" <amit_at_billdesk.com> wrote in message news:4c63c8fc.0301150248.442532a_at_posting.google.com...
> HI,
>
> i want to find out that over a period of time (say about a couple of
> days) which sql staements are being executed regularly so that i can
> work on them and optimise them.
>
> I am using oracle 8.0.6 on solaris 2.6
>
> If possible pls reply to this email id also : amit_at_billdesk.com
>
> Thanks in Advance
> amit kaushal
> www.billdesk.com
Received on Wed Jan 15 2003 - 20:15:05 CST

Original text of this message

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