Re: Best way to calc transactions for the month

Moving to 10g is on the schedule for late this year. However, if I don't get some time to plan and test some time soon, it may have to wait until next year. I'm so busy right now that they are definitely planning to hire a junior DBA some time this year to assist me. Plus the president of the company has now decided that I need to be moved into more of a management/planning role. Not sure what I did to deserve THAT punishment! And this from a company that didn't think they needed a full-time DBA just a year ago. My how things change in a year.

Thanks everyone for all your suggestions.

Sandy

On 4/11/07, Wayne Adams <work_at_wayneadams.com> wrote:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l