OraFAQ Forum: Performance Tuning » comparing SQL plans between 2 DB's

Home » RDBMS Server » Performance Tuning » comparing SQL plans between 2 DB's (Oracle 11203 Solaris 2.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comparing SQL plans between 2 DB's [message #542016]

Fri, 03 February 2012 14:32

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I just upgraded one of the DB's from 11107->11203 and I want
to compare SQL plans from my PROD (11107) to my dev (11203). I have the same code running on both..

Can somebody suggest a way I can go about this. An example,
would be greatly appreciated.

Thanks to all who answer

Report message to a moderator

Re: comparing SQL plans between 2 DB's [message #542042 is a reply to message #542016]

Sat, 04 February 2012 02:17

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

With EM, if you bought the option, you can capture the execution of a workload in both environments and compare them.

Regards
Michel

Report message to a moderator

Re: comparing SQL plans between 2 DB's [message #542048 is a reply to message #542042]

Sat, 04 February 2012 02:44

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Have you bought (or can you motivate the purchase of) the Real Application Testing option? The SQL Prrformance Analyzer http://docs.oracle.com/cd/E11882_01/server.112/e16540/rat_intro.htm#CHDDDHBF is what you need.

Report message to a moderator

comparing SQL plans between 2 DB's [message #542115 is a reply to message #542048]

Sat, 04 February 2012 09:15

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Thanks for your expertise, unforetuneatly we dont have
either option but I just read up on RAT and it looks perfect
for what I am trying to do..

Just wondering how others take care of this task if they don't
have this software available. Are there queries that can be
run to do the same thing?

Thanks to all who anwser

Report message to a moderator

comparing SQL plans between 2 DB's [message #542306 is a reply to message #542115]

Mon, 06 February 2012 09:05

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I beleive I found a partial solution. Does anybody see
any issues with this?


/*
** Run ON TEST MACHINE
*/

create table tempusr.test_sql(
    SQL_ID                                 VARCHAR2(13),
    PLAN_HASH_VALUE                        NUMBER,
    PARSING_SCHEMA_NAME                    VARCHAR2(30),
    CAPTURE_DATE                           DATE,
    SQL_FULLTEXT                           CLOB);

CREATE INDEX tempusr.test_sql_I1 ON tempusr.test_sql
(SQL_ID)
LOGGING
TABLESPACE USERS;

create table tempusr.prod_sql(
    SQL_ID                                VARCHAR2(13),
    PLAN_HASH_VALUE                       NUMBER,
    PARSING_SCHEMA_NAME                   VARCHAR2(30),
    CAPTURE_DATE                          DATE,
    SQL_FULLTEXT                          CLOB);

CREATE INDEX tempusr.prod_sql_I1 ON tempusr.prod_sql
(SQL_ID)
LOGGING
TABLESPACE USERS;

insert into  tempusr.test_sql
 (SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, 
  CAPTURE_DATE, SQL_FULLTEXT)
  select SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, 
          SYSDATE, SQL_FULLTEXT
 from v$sql
where parsing_schema_name in ('?????')

/*
** Run on PROD Machine
*/

create table testusr.prod_sql(
    SQL_ID                                  VARCHAR2(13),
    PLAN_HASH_VALUE                         NUMBER,
    PARSING_SCHEMA_NAME                     VARCHAR2(30),
    CAPTURE_DATE                            DATE,       SQL_FULLTEXT                                CLOB);

insert into  testusr.prod_sql
 (SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, 
   CAPTURE_DATE, SQL_FULLTEXT)
  select SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, 
         SYSDATE, SQL_FULLTEXT
from v$sql
where parsing_schema_name in ('??????');


export testusr.prod_sql from PROD machine 
drop table testusr.prod_sql


/*
** Run on TEST machine
*/

Import into testusr.prod_sql machine

select a.sql_id, a.plan_hash_value 
from testusr.prod_sql a, testusr.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;

SQL_ID          PLAN_HASH_VALUE
=============   ===============
ccuq8jrv699qg	572023905
9403xt20g571c	2188307123
6q5u806kr94z8	2186098964
0xjbf1cgra1f3	2520981771
bxzjv24bhf713	1615918160
19wgcndw7v9cv	4138431570
692nmqcvr3h44	2374404815
bgs6svxkvbqhc	906480417
51hxfv2khhah4	588003193
37ukwbt3u61sr	2407394300
9m8u0u53jjx2u	75919616
2r8n3n9339qzw	3323753043
7rtb5htrxa6qc	1002969367
6svq0d7xkvd4x	1389007332
57wpamw1rbdc7	3006220833
1m61xqabhvfgr	1403238724
bs24nt96bvkja	2805285694
9dg0n017f45c9	2953310350
0wpmc2wsdw2b0	2719532159
cxrc4brkm8vq7	2005044541
0853nmk37dvc8	3035914035
6u4g0ychu623f	2691321969
ad6z6fcz4mcuw	2434987098
80s30ut4gvxm0	3747523052
fhb101hhcp2jk	2327885766
062ad7x41hwvb	1254923593
11upn2c745041	3539118591
1w8gv67cxpxrt	1550324933
2x7sag57v361g	3288702570
cf3ut3cb2s8p2	90902579
716yn9s47hbbf	2327885766
5phd03h4k024s	3344626755
dv1p350zrd6bn	2975774263
2s4k3zb6gtdk0	1405051678
77r447zjy52xq	1643543764
cmrs482ymnry3	2260866041
86gdsx2j4krx5	572570105
6p8p6fh9bqm3b	2075244440
36rnurn1xy7uw	3386538710
bzmc9ywz3z7mx	2347623570
c4y9zy1kpvhhw	4138431570
g418fqx9tvxmw	3323753043
6mz5qsq8f4g2t	1161221985
828hd01sqcp7c	2121893972
8jzrd3xm9wqdm	2075244440
9s170jr4pf15h	3629081960
3cjk2n0c3dj33	1254923593
d62dxrjpndt15	3323753043
1r4whuj6ptjur	1708640057
cda203b7rdr7s	98149117
9h11s9ry3ygmr	1389007332
81xkn9vn1fr06	1647738288
d4n93974cazu4	2600611880
fh3kgg0j2ca6d	2956402617
2qx0a1mhq14gu	2792750793
fqqhy26u1j5aa	4138431570
34mxagzarfd1w	1653242321
0wf5qvw6pfaqd	2767603614
fc4qg1pryub2y	3323753043
59dzvaknwmbak	387026774
9nwrzzsvv6xzk	1311692127
6ffts1m07qus7	2792750793
241j3hd4q011g	926650615
4r13cbht7ws6c	1614567794
68d87uxqjcv3h	1359916879
chkw35s1751k2	1316075729
6w2zrdnprhrmx	2691321969
43rtmx4xv5zj7	2933381923
4n4rr6tcckm7m	608989327
da2adn1kc6nkf	973072234
71hfyddd8v83d	3443728220
60pjbnhnybgpk	2362683993

[Updated on: Mon, 06 February 2012 13:33]

Report message to a moderator

Previous Topic:	What info should be gathered while doing sql tuning?
Next Topic:	Query Rewrite

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 10:50:55 CST 2025