Commands/Script to Compare Oracle Performance

[Alzhy]

DBA's.. can you give me a simple sequence of commands or script to compare performance of Oracle?

I have 1 TB instance that's on 2 environments -- one is stored as RAW and the other Filesystems.

Nothing fancy.. probably simple querys to do some decent comparison.

[Steven E. Protter]

This may be useful:
http://www.oracle.com/technology/pub/notes/technote_dms.html

A possibly helpful white paper.
http://oracle.ittoolbox.com/nav/t.asp?t=379&p=379&h1=379

Your dba should be able to write some scripts that test performance of the database alone.

There are some third party products that you have to purchase for the same purpose.

SEP

[TwoProc]

Nelson, Statspack will definitely tell you about what kind of I/O delays you're facing. You can set it to snapshot in 1 hour increments, 1/2 hours, 15 min, etc...

Are the loads the same on both servers such that a statspack compare would be useful? If loads are not the same - then you've got a different problem.

Since you're looking at concurrency for raw i/o vs filesystem - then I think you're mainly going to be faced with testing with something that generates a fair amount of writes, and concurrency.

I know you've asked for something simple, but once statspack is setup, is actually quite simple to run reports from. You asked for simple queries, and I'm not sure that's going to get you any where...

But, lets say you do a
create table junk as select a.a,a.b,a.c, b.a,b.b,b.c from employees a, users b
where a.empid = b.empid;

I'm supposing that you can get two large tables you can join for this... in my example I'm just

Now, run them concurrently as a parallel query...

create table junk as select /*+PARALLEL(inventory_parts 12) */ a.a,a.b,a.c, b.a,b.b,b.c from inventory_parts a, inventory_kits b
where a.invid = b.kitid;

Run again at level 32 or something insane...

Now, run them all on the other server.

Check your timings, see what you've got.

You may not need to join two tables to create your new one, you could also check it out just pulling from a single table...

[TwoProc]

If the parallel part(s) doesn't give you what you need - you could just script it up and run 12 at once - creating junk1,junk2,junk3, etc...
at the same time.

[TwoProc]

Nelson,
When you're done - I'd be interested in hearing how much different the two systems are. It'd be great if you could post a summary of whatever you used to determine the differences and how it panned out. Thanks.

[Yogeeraj_1]

hi,

as john mentioned above, a 15mins (not more) snap at different time intervals will definitely help in doing this.

Another method would require you to intervene at the database level and write down plsql code and analyze using SQL_TRACE.

Operations that you may want to compare would be:
1. Bulk insert
2. Single-row inserts
3. Bulk update
4. Single-row updates
5. Bulk delete
6. Single-row deletes

This benchmark will conclusively show how well each system is doing. No guessing involved!

hope this helps!

regards
yogeeraj

[TwoProc]

Nelson, points