RE: Best way to calc transactions for the month

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_at_xxxxxxxxxxxxxxx> To: <sbecker6925_at_xxxxxxxxx>, <oracle-l_at_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_at_xxxxxxxxxxxxx
[mailto:oracle-l-bounce_at_xxxxxxxxxxxxx] On Behalf Of Sandra Becker Sent: Wednesday, April 11, 2007 12:24 PM To: oracle-l_at_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
--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 11 2007 - 15:26:10 CDT