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
Messages: 5
Registered: March 2000
Junior Member

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 ?



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
Messages: 1089
Registered: May 2002
Senior Member
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.

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

Current Time: Sat Feb 24 20:48:25 CST 2024