comparing sql plans Options

[BeefStu]

I am in the process of upgrading my DB from 11107 -> 11203. I already upgraded my test
machine to 11203 and my prod machine is still running 11107.

I took the v$sql from both machines and created tables

create table test_sql as select * from v$sql
create table prod_sql as select * from v$sql

I am running this query to see the differences

CODE

select a.sql_id, a.plan_hash_value
from c0harpa.prod_sql a, c0harpa.test_sql b
where
a.sql_id = b.sql_id and
a.plan_hash_value <> b.plan_hash_value
group by a.sql_id, a.plan_hash_value;

What column/(s) can I use in the v$sql table to show if plan has gotten better or worse?
Would COST column work?

If somebody can provide an example it would be greatly appreciated.

BTW, We don't have RAT, which I know is much easier so that is why I am going the round about
way.

Thanks to all who answer

[burleson]

Hi Stu,

>> I am running this query to see the differences

So, is there any difference?

set autotrace on

http://www.dba-oracle.com/t_OracleAutotrace.htm

If the plans are the same, the SQL will run the same . . . .

*******************************************

>> What column/(s) can I use in the v$sql table to show if plan has gotten better or worse?

Not the COST, it means NOTHING!

Please read:

http://www.dba-oracle.com/t_sql_execution_...cost_column.htm

You dont; say what you goal is:

1 - maximize response time (set timing on)

or

2 - minimize computing resources (compares rows returned to consustent gets)

[BeefStu]

Donald,

My goal is to make sure the queries running under 11203 run at the same
speed or better than the queries under 11107.

Instead of running all these queries again (over 400) with autotrace on I am looking at the
v$sql.excution_time column for both DB's and trying to see if that is an accurate
indicator if the query is the speed is the same, better or worse.

Below is some of my data, do I have enoiugh information here to tell me
what I need? If not, can you make some suggestions?

CODE

    
                              PROD               PROD                TEST           TEST
SQL_ID                   PLAN_HASH      ELAPSED TIME   PLAN_HASH ELAPSED TIME  

241j3hd4q011g        4288829587    156649118        3938967893   23253638
241j3hd4q011g        2386805598       3009903       3938967893     23253638
241j3hd4q011g        3428590031       8877333       3938967893     23253638
241j3hd4q011g        4288829587    156649118      3938967893      26175084
241j3hd4q011g        2386805598        3009903      3938967893      26175084
241j3hd4q011g        3428590031        8877333      3938967893      26175084

Thanks in advance to all who answer

[burleson]

Hi Stu,

>> My goal is to make sure the queries running under 11203 run at the same
speed or better than the queries under 11107. can you make some suggestions?

OK, grab a workload from STATSPACK or AWR:

http://www.dba-oracle.com/capture_workload_11g.htm

Then, time it on both releases . . . .

Easy . . . .