Home » SQL & PL/SQL » SQL & PL/SQL » difference between explain plans (Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
difference between explain plans [message #397672] Mon, 13 April 2009 06:07 Go to next message
jimit_shaili
Messages: 231
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #397706 is a reply to message #397704] Mon, 13 April 2009 08:04 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
"One index may be fragmented or poorly clustered "

Sir I want to know what does it mean. You meant to say that index are note build in the same way in the two environment?

Thanks
Re: difference between explain plans [message #397709 is a reply to message #397672] Mon, 13 April 2009 08:27 Go to previous messageGo to next message
jimit_shaili
Messages: 231
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: difference between explain plans [message #397796 is a reply to message #397672] Mon, 13 April 2009 23:39 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Sir

Above plan is for my both the servers.Its absolutely same.

Jimit

Re: difference between explain plans [message #397871 is a reply to message #397796] Tue, 14 April 2009 06:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do a trace and post the TKPROF output for both

Ross Leishman
Previous Topic: Modifications required in existing Procedure
Next Topic: Can Foriegn Key Constarint Deferred?
Goto Forum:
  


Current Time: Sat Dec 03 06:06:49 CST 2016

Total time taken to generate the page: 0.12810 seconds