Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> comparing sql plans
BeefStu
post Feb 8 2012, 02:34 PM
Post #1


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



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
Go to the top of the page
 
+Quote Post
burleson
post Feb 8 2012, 04:01 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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)


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
BeefStu
post Feb 8 2012, 04:24 PM
Post #3


Advanced Member
***

Group: Members
Posts: 112
Joined: 6-July 10
Member No.: 43,418



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
Go to the top of the page
 
+Quote Post
burleson
post Feb 8 2012, 04:35 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



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 . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 12:30 AM