Home » SQL & PL/SQL » SQL & PL/SQL » Different explain plan for same sql
Different explain plan for same sql [message #197699] Thu, 12 October 2006 06:29 Go to next message
dzeljko
Messages: 19
Registered: April 2005
Junior Member
I executed the same sql on two different servers with the same environment and schema definition (developing and production server). The execution plans for the sql are different and of course the performance executed sql also. Where might be the possible reason for differences?

Execution Plan (DEV)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=140)
1 0 SORT (UNIQUE) (Cost=8 Card=1 Bytes=140)
2 1 FILTER
3 2 NESTED LOOPS (Cost=7 Card=1 Bytes=140)
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=116)
5 4 NESTED LOOPS (Cost=4 Card=1 Bytes=86)
6 5 MERGE JOIN (CARTESIAN) (Cost=3 Card=1 Bytes=78)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SRCH_CONTENT' (Cost=2 Card=1 Bytes=70)
8 7 DOMAIN INDEX OF 'IM_SRCH_CONTENT' (Cost=0)
9 6 BUFFER (SORT) (Cost=1 Card=221 Bytes=1768)
10 9 INLIST ITERATOR
11 10 INDEX (RANGE SCAN) OF 'PK_WMGACCCONT' (UNIQUE) (Cost=1 Card=221 Bytes=1768)
12 5 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT' (Cost=1 Card=1 Bytes=8)
13 12 INDEX (UNIQUE SCAN) OF 'PK_WMCONTENT' (UNIQUE)
14 4 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT_LANGUAGE_VERSION' (Cost=2 Card=1 Bytes=30)
15 14 INDEX (RANGE SCAN) OF 'PK_WMCONTLANGVERS' (UNIQUE) (Cost=1 Card=1)
16 15 SORT (AGGREGATE)
17 16 FILTER
18 17 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT_LANGUAGE_VERSION' (Cost=3 Card=1 Bytes=19)
19 18 INDEX (RANGE SCAN) OF 'PK_WMCONTLANGVERS' (UNIQUE) (Cost=2 Card=3)
20 17 SORT (AGGREGATE)
21 20 NESTED LOOPS (Cost=2 Card=1 Bytes=22)
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE' (Cost=1 Card=1 Bytes=7)
23 22 INDEX (UNIQUE SCAN) OF 'PK_WMSITE' (UNIQUE)
24 21 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE_ATTRIBUTE' (Cost=1 Card=1 Bytes=15)
25 24 INDEX (UNIQUE SCAN) OF 'PK_WMSITEATTR' (UNIQUE)
26 25 SORT (AGGREGATE)
27 26 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE_ATTRIBUTE' (Cost=2 Card=1 Bytes=16)
28 27 INDEX (RANGE SCAN) OF 'PK_WMSITEATTR' (UNIQUE) (Cost=1 Card=2)
29 3 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT_LANGUAGE' (Cost=1 Card=1 Bytes=24)
30 29 INDEX (UNIQUE SCAN) OF 'PK_WMCONTENTLANG' (UNIQUE)
31 2 NESTED LOOPS (Cost=5 Card=10 Bytes=150)
32 31 INDEX (UNIQUE SCAN) OF 'PK_WMSITEALLOWSHARECONT' (UNIQUE)
33 31 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT' (Cost=4 Card=10 Bytes=70)
34 33 INDEX (RANGE SCAN) OF 'I_FK_WMCONTENT_SITE' (NON-UNIQUE) (Cost=1 Card=21)

Execution Plan (ACC)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=162)
1 0 SORT (UNIQUE) (Cost=8 Card=1 Bytes=162)
2 1 FILTER
3 2 NESTED LOOPS (Cost=7 Card=1 Bytes=162)
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=121)
5 4 NESTED LOOPS (Cost=5 Card=1 Bytes=114)
6 5 NESTED LOOPS (Cost=4 Card=1 Bytes=107)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SRCH_CONTENT' (Cost=2 Card=1 Bytes=69)
8 7 DOMAIN INDEX OF 'IM_SRCH_CONTENT' (Cost=1)
9 6 TABLE ACCESS (FULL) OF 'WM_CONTENT_LANGUAGE_VERSION' (Cost=2 Card=69 Bytes=2622)
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT' (Cost=1 Card=1 Bytes=7)
11 10 INDEX (UNIQUE SCAN) OF 'PK_WMCONTENT' (UNIQUE)
12 4 INLIST ITERATOR
13 12 INDEX (RANGE SCAN) OF 'PK_WMGACCCONT' (UNIQUE) (Cost=1 Card=1 Bytes=7)
14 3 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT_LANGUAGE' (Cost=1 Card=1 Bytes=41)
15 14 INDEX (UNIQUE SCAN) OF 'PK_WMCONTENTLANG' (UNIQUE)
16 2 NESTED LOOPS (Cost=3 Card=1 Bytes=42)
17 16 INDEX (UNIQUE SCAN) OF 'PK_WMSITEALLOWSHARECONT' (UNIQUE)
18 16 TABLE ACCESS (BY INDEX ROWID) OF 'WM_CONTENT' (Cost=2 Card=1 Bytes=16)
19 18 INDEX (RANGE SCAN) OF 'U_WMCONTENT_NAME' (UNIQUE) (Cost=2 Card=1)
20 2 SORT (AGGREGATE)
21 20 FILTER
22 21 TABLE ACCESS (FULL) OF 'WM_CONTENT_LANGUAGE_VERSION' (Cost=2 Card=1 Bytes=22)
23 21 SORT (AGGREGATE)
24 23 NESTED LOOPS (Cost=2 Card=1 Bytes=40)
25 24 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE' (Cost=1 Card=1 Bytes=16)
26 25 INDEX (UNIQUE SCAN) OF 'PK_WMSITE' (UNIQUE)
27 24 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE_ATTRIBUTE' (Cost=1 Card=1 Bytes=24)
28 27 INDEX (UNIQUE SCAN) OF 'PK_WMSITEATTR' (UNIQUE)
29 28 SORT (AGGREGATE)
30 29 TABLE ACCESS (BY INDEX ROWID) OF 'WM_SITE_ATTRIBUTE' (Cost=2 Card=1 Bytes=18)
31 30 INDEX (RANGE SCAN) OF 'PK_WMSITEATTR' (UNIQUE) (Cost=1 Card=3)

[Updated on: Thu, 12 October 2006 06:32]

Report message to a moderator

Re: Different explain plan for same sql [message #197704 is a reply to message #197699] Thu, 12 October 2006 06:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is the data same?
Is the filesystem same?
Above all, when was the last time you refreshed the statistics on both nodes?
Re: Different explain plan for same sql [message #197713 is a reply to message #197704] Thu, 12 October 2006 07:01 Go to previous messageGo to next message
dzeljko
Messages: 19
Registered: April 2005
Junior Member
No data isn't same due to one server is for developing and second is for production.

The filesystem is same on both servers.

I didn't refresh statistics at all on both nodes?

Can I improve performance of sql query on production server with refresh statistic? Please I you can give me more information about solving bad performance on second server.
Re: Different explain plan for same sql [message #197716 is a reply to message #197713] Thu, 12 October 2006 07:05 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Can I improve performance of sql query on production server with refresh statistic?
May be or may not be.
But without correct statistics, CBO will not do the right job.
CBO will make use these statistics to estimate the path.
It derives it based on distribution,bucket size and many many other factors.

First collect statistics on the "other" server.
Use dbms_stats. COllect stats on tables and indexes. Try again.
Previous Topic: sql "where" conditions (array)
Next Topic: Basic Question
Goto Forum:
  


Current Time: Sat Dec 10 10:41:40 CST 2016

Total time taken to generate the page: 0.04445 seconds