difference between explain plans [message #397672] |
Mon, 13 April 2009 06:07  |
jimit_shaili
Messages: 237 Registered: June 2006 Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Friends
i have two identical severs with identical configuration and also the same db version.Data is also same on both the db.
//Live Server
exec dbms_stats.gather_table_stats('RMS','TRACELOT');
explain plan for
UPDATE rms.tracelot
SET wscht = (wscht + 1),
blcht = ((rccht + opcht) - (ischt + wscht + 1))
WHERE compcode = 3
AND sessionid = 'chintan.patel'
AND lotno = '09D198'
AND godowncode = 1
AND LOCATION = 1
/
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 132 | 1 |
| 1 | UPDATE | TRACELOT | | | |
|* 2 | INDEX RANGE SCAN | TRCLT | 1 | 132 | 1 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRACELOT"."COMPCODE"=3 AND "TRACELOT"."SESSIONID"=
'chintan.patel' AND "TRACELOT"."LOTNO"='09D198' AND
"TRACELOT"."GODOWNCODE"=1 AND "TRACELOT"."LOCATION
"=1)
Note: cpu costing is off
18 rows selected.
Elapsed: 00:00:00.00
//Dev Server
exec dbms_stats.gather_table_stats('RMS','TRACELOT');
explain plan for
UPDATE rms.tracelot
SET wscht = (wscht + 1),
blcht = ((rccht + opcht) - (ischt + wscht + 1))
WHERE compcode = 3
AND sessionid = 'chintan.patel'
AND lotno = '09D198'
AND godowncode = 1
AND LOCATION = 1
/
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 132 | 2 (50)|
| 1 | UPDATE | TRACELOT | | | |
|* 2 | INDEX RANGE SCAN | TRCLT | 1 | 132 | 2 (50)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRACELOT"."COMPCODE"=3 AND "TRACELOT"."SESSIONID"='chin
tan.patel' AND "TRACELOT"."LOTNO"='09D198' AND "TRACELOT
"."GODOWNCODE"=1 AND "TRACELOT"."LOCATION"=1
15 rows selected.
Elapsed: 00:00:00.00
my optimizer stats is set to 'choose' for both instance.
my main question is there is huge difference in performance.so can any one explain me what's difference between two execution plans.
Regards
Jimit
|
|
|
Re: difference between explain plans [message #397682 is a reply to message #397672] |
Mon, 13 April 2009 06:42   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
jimit_shaili wrote on Mon, 13 April 2009 13:07 | Dear Friends
i have two identical severs with identical configuration and also the same db version.Data is also same on both the db.
//Live Server
Note: cpu costing is off
|
The two are NOT the same
|
|
|
Re: difference between explain plans [message #397704 is a reply to message #397682] |
Mon, 13 April 2009 07:44   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Just because you have the same data, that doesn't mean it is ARRANGED the same way in the table.
One table may be fragmented, or have a high HWM. One index may be fragmented or poorly clustered.
Ross Leishman
|
|
|
|
Re: difference between explain plans [message #397709 is a reply to message #397672] |
Mon, 13 April 2009 08:27   |
jimit_shaili
Messages: 237 Registered: June 2006 Location: India, Ahmedabad
|
Senior Member |
|
|
I drop the table,delete all the indexes and recreate the table and indexes again on both live and dev server.This time i does not gather stats for any of server and still find huge performance diffrence on both server.
my new execution plan is same for both server.
SQL> explain plan for
2 UPDATE rms.tracelot
3 SET wscht = (wscht + 1),
4 blcht = ((rccht + opcht) - (ischt + wscht + 1))
5 WHERE compcode = 3
6 AND sessionid = 'chintan.patel'
7 AND lotno = '09D198'
8 AND godowncode = 1
9 AND LOCATION = 1
10 /
Explained.
Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | TRACELOT | | | |
|* 2 | INDEX RANGE SCAN | TRCLT | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRACELOT"."COMPCODE"=3 AND "TRACELOT"."SESSIONID"=
'chintan.patel' AND "TRACELOT"."LOTNO"='09D198' AND
"TRACELOT"."GODOWNCODE"=1 AND "TRACELOT"."LOCATION
"=1)
Note: rule based optimization
18 rows selected.
Elapsed: 00:00:00.01
please explain me if there are any other server level parameter which may effect this type of performance issue.
Regards
Jimit
|
|
|
Re: difference between explain plans [message #397741 is a reply to message #397709] |
Mon, 13 April 2009 12:50   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
jimit_shaili wrote on Mon, 13 April 2009 09:27 | I drop the table,delete all the indexes and recreate the table and indexes again on both live and dev server.This time i does not gather stats for any of server
|
Without any statistics, as you can see, the RBO is in effect and you cannot compare an RBO plan with a CBO plan or their running times.
|
|
|
|
|