Home » RDBMS Server » Performance Tuning » number of stmts executed against an oracle instance
number of stmts executed against an oracle instance [message #64818] Thu, 12 February 2004 09:59 Go to next message
Mo
Messages: 5
Registered: March 2000
Junior Member
Hi,

I am trying to get the total number of selects that are executed against an oracle instance since it was up. I can get info from v$sql table, but the data gets aged out after a while. Is there a better way to get this stats ?

TIA

MO

  
Re: number of stmts executed against an oracle instance [message #64820 is a reply to message #64818] Sat, 14 February 2004 03:52 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
you are right about V$sql ,v$sqlarea etc not reflecting the true picture since sql statements age out from the library cache based on LRU algorithm.
I've never had the need for this and so never attempted.

If you are keen on gettng ALL the select statements since the instance startup, you could turn on instance level sql tracing and scan the trace files for Select statements ,together with their 'execute' count ! , which is a very tedious procedure.

Also you could turn on statspack data collection when the instance startsup and query sum(executions) from STATS$SQL_SUMMARY where sql_text like 'SELECT%';
Again I havent confirmed this,but its worth a try.

The other option you can try out is audit select table for all tables in all schemas by access. Too much auditing ,isnt it?

there are statistics like user_calls etc,but they include all types of sql statements,not just selects.

well,that should give you some options.

-Thiru
Previous Topic: 10 % Hit Ratio
Next Topic: Performance Problem / Query Problem
Goto Forum:
  


Current Time: Fri Mar 29 05:08:21 CDT 2024