I just recently had to do something similar though for a different
reason. I was
trying to get a rough measure of how accurate a set of benchmark tests
that
Development wrote approximated the production workload of our OLTP
environment.
As an initial sanity check of the benchmarks, I decided to use the ratio
of
INSERTS vs SELECTS (turns out they were doing 2.5x the peak load of
SELECTS and
like 10x the peak load of INSERTS). Unfortunately, even as I was putting
together this solution for you, I looked back at your email and realized
that
you're on 9i. Doh! But maybe you plan on upgrading to 10G soon. If you
do, you
can do something similar to this.
select dhsnap.snap_id, to_char(begin_interval_time, 'DD-MON-RR HH24:MI')
snap_time, sum(dhss.executions_delta)
from dba_hist_sqlstat dhss, dba_hist_sqltext dhst, dba_hist_snapshot
dhsnap
where dhss.sql_id = dhst.sql_id
and dhss.parsing_schema_name = 'APPLICATION_SCHEMA_NAME'
and upper(dhst.sql_text) like '%INSERT%'
and dhsnap.snap_id = dhss.snap_id
and begin_interval_time > sysdate -10
group by dhsnap.snap_id, to_char(begin_interval_time, 'DD-MON-RR HH24:MI')
order by dhsnap.snap_id;
This query returns the number of INSERT statements that were executed by a
particular user (APPLICATION_SCHEMA_NAME) during each snapshot interval
over the
past 10 days. For SELECT statements, just modify the LIKE against the
sql_text.
This is easily modified to aggregate it over any time period. How far
back you
can go is dependent on your AWR retention period.
You may be able to approximate something similar to this in 9i by creating
your
own periodic job that scans thru v$sql and stores execution counts/deltas
of
different SQL types over periodic intervals and stores them in a table.
FYI: DBA_HIST views technically require a Diagnostics Pack license.
Wayne Adams
www.wayneadamsconsulting.com
-----------------------------------------------------
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <sbecker6925@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 11 Apr 2007 13:21:22 -0400
Well, if you look at Statspack snapshots, that will give you transaction
rate, but it won't include selects. If I recall correctly, it looks at
the user rollbacks and user commits statistics. In fact, it will count
transactions, which could be made up of many DML statements.
Hmm...I can't think of any good way to do this, short of turning on
trace or auditing, and summarizing data from there.
Any chance you can talk him out of asking for what he's asking for? ;-)
It's almost certainly not at all meaningful or useful, but convincing
him of that may be a political and diplomatic challenge...;-)
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest/CSA
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandra Becker
Sent: Wednesday, April 11, 2007 12:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Best way to calc transactions for the month
The president of the company would like to know how many transactions
per month are running through the database. He wants to include all
selects, inserts, updates, and deletes. What would be the best way to
gather this information. Is there a way I can easily break it down by
each type of transaction?
Oracle 9.2.0.8, RHEL 4.0
Sandy
--
//www.freelists.org/webpage/oracle-l
