Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Commands/Script to Compare Oracle Performance

Alzhy
Honored Contributor

Commands/Script to Compare Oracle Performance

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.
Hakuna Matata.
6 REPLIES
Steven E. Protter
Exalted Contributor

Re: Commands/Script to Compare Oracle Performance

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
TwoProc
Honored Contributor

Re: Commands/Script to Compare Oracle Performance

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...
We are the people our parents warned us about --Jimmy Buffett
TwoProc
Honored Contributor

Re: Commands/Script to Compare Oracle Performance

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.
We are the people our parents warned us about --Jimmy Buffett
TwoProc
Honored Contributor

Re: Commands/Script to Compare Oracle Performance

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.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: Commands/Script to Compare Oracle Performance

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
TwoProc
Honored Contributor

Re: Commands/Script to Compare Oracle Performance

Nelson, points
We are the people our parents warned us about --Jimmy Buffett