Home » RDBMS Server » Performance Tuning » Different plan for same sql (oracle 10.2.0.3)
Different plan for same sql [message #414547] Wed, 22 July 2009 10:10 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
The sql (sorry for not being able to provide the sql and table name is translated into a different name) when ran in two different environment I am getting different explain plan for the same sql.
The stats are upto date .Indexes are same. But in the plan index being used is different for test9

Test 9 table have 100 million rows in env1 and in env2 it is around 74k.

Why table access is full in env2 for test9

env1:

	
1	 
2	The command executed successfully with no results returned.
3	
4	PLAN_TABLE_OUTPUT
5	Plan hash value: 301684089
6	 
7	---------------------------------------------------------------------------------------------------------------
8	| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
9	---------------------------------------------------------------------------------------------------------------
10	|   0 | SELECT STATEMENT                 |                            |     3 |   699 |    37   (6)| 00:00:01 |
11	|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST1               |     1 |   100 |     2   (0)| 00:00:01 |
12	|*  2 |   INDEX RANGE SCAN               | XPKTEST1            |     1 |       |     1   (0)| 00:00:01 |
13	|   3 |  TABLE ACCESS BY INDEX ROWID     | TEST2                   |     1 |    14 |     1   (0)| 00:00:01 |
14	|*  4 |   INDEX UNIQUE SCAN              | XPKTEST2                |     1 |       |     0   (0)| 00:00:01 |
15	|   5 |  TABLE ACCESS BY INDEX ROWID     | TEST6_CLARIFY                 |     1 |   100 |     4   (0)| 00:00:01 |
16	|*  6 |   INDEX RANGE SCAN               | AFXTEST6C01                   |     1 |       |     3   (0)| 00:00:01 |
17	|   7 |  TABLE ACCESS BY INDEX ROWID     | TEST3     |     1 |    24 |     5   (0)| 00:00:01 |
18	|*  8 |   INDEX RANGE SCAN               | XPKTEST3  |     1 |       |     4   (0)| 00:00:01 |
19	|   9 |  SORT AGGREGATE                  |                            |     1 |    40 |            |          |
20	|  10 |   NESTED LOOPS                   |                            |     1 |    40 |     5   (0)| 00:00:01 |
21	|* 11 |    TABLE ACCESS BY INDEX ROWID   | TEST4        |     1 |    19 |     3   (0)| 00:00:01 |
22	|* 12 |     INDEX UNIQUE SCAN            | XPKTEST4     |     1 |       |     2   (0)| 00:00:01 |
23	|  13 |    TABLE ACCESS BY INDEX ROWID   | TEST5     |    20M|   412M|     2   (0)| 00:00:01 |
24	|* 14 |     INDEX UNIQUE SCAN            | TEST5_PK |     1 |       |     1   (0)| 00:00:01 |
25	|  15 |  SORT ORDER BY                   |                            |     3 |   699 |    37   (6)| 00:00:01 |
26	|  16 |   NESTED LOOPS                   |                            |     3 |   699 |    36   (3)| 00:00:01 |
27	|  17 |    NESTED LOOPS OUTER            |                            |     3 |   576 |    30   (4)| 00:00:01 |
28	|  18 |     NESTED LOOPS OUTER           |                            |     3 |   534 |    24   (5)| 00:00:01 |
29	|  19 |      NESTED LOOPS                |                            |     3 |   483 |    18   (6)| 00:00:01 |
30	|  20 |       TABLE ACCESS BY INDEX ROWID| TEST6                         |     3 |   183 |     6   (0)| 00:00:01 |
31	|* 21 |        INDEX RANGE SCAN          | XPKTEST6_NBR                  |     3 |       |     3   (0)| 00:00:01 |
32	|  22 |       TABLE ACCESS BY INDEX ROWID| TEST7                   |     1 |   100 |     4   (0)| 00:00:01 |
33	|* 23 |        INDEX RANGE SCAN          | XF_TEST62                   |     1 |       |     2   (0)| 00:00:01 |
34	|* 24 |      INDEX RANGE SCAN            | XPKTEST8    |     1 |    17 |     2   (0)| 00:00:01 |
35	|  25 |     TABLE ACCESS BY INDEX ROWID  | TEST9                 |     1 |    14 |     2   (0)| 00:00:01 |
36	|* 26 |      INDEX UNIQUE SCAN           | XPKTEST9              |     1 |       |     1   (0)| 00:00:01 |
37	|* 27 |    TABLE ACCESS BY INDEX ROWID   | TEST10              |     1 |    41 |     2   (0)| 00:00:01 |
38	|* 28 |     INDEX UNIQUE SCAN            | XPKTEST10           |     1 |       |     1   (0)| 00:00:01 |
39	---------------------------------------------------------------------------------------------------------------
[code]
[/code]
env2:

 
The command executed successfully with no results returned.

PLAN_TABLE_OUTPUT
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |   213 | 54315 |   872   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST1               |     1 |    32 |     2   (0)|
|*  2 |   INDEX RANGE SCAN               | XPKTEST1            |     1 |       |     1   (0)|
|   3 |  TABLE ACCESS BY INDEX ROWID     | TEST2                   |     1 |    14 |     1   (0)|
|*  4 |   INDEX UNIQUE SCAN              | XPKTEST2                |     1 |       |     0   (0)|
|   5 |  TABLE ACCESS BY INDEX ROWID     | TEST6_CLARIFY                 |     1 |    28 |     2   (0)|
|*  6 |   INDEX RANGE SCAN               | AFXTEST6C01                   |     1 |       |     1   (0)|
|   7 |  TABLE ACCESS BY INDEX ROWID     | TEST3     |     1 |    25 |     4   (0)|
|*  8 |   INDEX RANGE SCAN               | XPKTEST3  |     1 |       |     3   (0)|
|   9 |  SORT AGGREGATE                  |                            |     1 |    40 |            |
|  10 |   NESTED LOOPS                   |                            |     1 |    40 |     5   (0)|
|* 11 |    TABLE ACCESS BY INDEX ROWID   | TEST4        |     1 |    20 |     3   (0)|
|* 12 |     INDEX UNIQUE SCAN            | XPKTEST4     |     1 |       |     2   (0)|
|  13 |    TABLE ACCESS BY INDEX ROWID   | TEST5     |   646K|    12M|     2   (0)|
|* 14 |     INDEX UNIQUE SCAN            | TEST5_PK |     1 |       |     1   (0)|
|  15 |  SORT ORDER BY                   |                            |   213 | 54315 |   872   (1)|
|* 16 |   HASH JOIN OUTER                |                            |   213 | 54315 |   871   (1)|
|* 17 |    HASH JOIN OUTER               |                            |   211 | 50851 |   670   (1)|
|  18 |     NESTED LOOPS                 |                            |   211 | 47053 |   571   (0)|
|  19 |      NESTED LOOPS                |                            |   211 | 36081 |   149   (0)|
|  20 |       TABLE ACCESS BY INDEX ROWID| TEST6                         |    46 |  1656 |    37   (0)|
|* 21 |        INDEX RANGE SCAN          | XPKTEST6_NBR                  |    46 |       |     1   (0)|
|  22 |       TABLE ACCESS BY INDEX ROWID| TEST7                   |     5 |   675 |     3   (0)|
|* 23 |        INDEX RANGE SCAN          | XF_TEST62                   |     5 |       |     2   (0)|
|* 24 |      TABLE ACCESS BY INDEX ROWID | TEST10              |     1 |    52 |     2   (0)|
|* 25 |       INDEX UNIQUE SCAN          | XPKTEST10           |     1 |       |     1   (0)|
|  26 |     INDEX FAST FULL SCAN         | XPKTEST8    | 83311 |  1464K|    97   (2)|
|  27 |    TABLE ACCESS FULL             | TEST9                 | 74014 |  1011K|   200   (1)|
----------------------------------------------------------------------------------------------------
 

[Updated on: Wed, 22 July 2009 10:21]

Report message to a moderator

Re: Different plan for same sql [message #414549 is a reply to message #414547] Wed, 22 July 2009 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Obviously it is not the same environment and/or the statistics are not up to date.

Regards
Michel
Re: Different plan for same sql [message #414550 is a reply to message #414549] Wed, 22 July 2009 10:34 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, In both the environment the same sql when ran provides different plan.

In both the env
1. stats are upto date
2. index are same
3. The table CIT have 74k rows in Env2 while in env1 its 100 million
4. In Both it returns 1 rows
5. env1 has more data compared to env2
Oracle version in both: 10.2.0.3

DIFFERENCE FOUND :
CIT TABLE IS USING DIFFERENT INDEX IN THE TWO PLANS.IN ENV2 FOR TABLE CIT IS ACCESS FULL AND GOES FOR FAST FULL SCAN INSTEAD OF INDEX BY ROWID AND AVOID TABLE SCAN.

ENV1:



	
1	 
2	The command executed successfully with no results returned.
3	
4	PLAN_TABLE_OUTPUT
5	Plan hash value: 301684089
6	 
7	---------------------------------------------------------------------------------------------------------------
8	| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
9	---------------------------------------------------------------------------------------------------------------
10	|   0 | SELECT STATEMENT                 |                            |     3 |   699 |    37   (6)| 00:00:01 |
11	|   1 |  TABLE ACCESS BY INDEX ROWID     | C_P               |     1 |   100 |     2   (0)| 00:00:01 |
12	|*  2 |   INDEX RANGE SCAN               | XPKC_P            |     1 |       |     1   (0)| 00:00:01 |
13	|   3 |  TABLE ACCESS BY INDEX ROWID     | LOC                   |     1 |    14 |     1   (0)| 00:00:01 |
14	|*  4 |   INDEX UNIQUE SCAN              | XPKLOC                |     1 |       |     0   (0)| 00:00:01 |
15	|   5 |  TABLE ACCESS BY INDEX ROWID     | R_C                |     1 |   100 |     4   (0)| 00:00:01 |
16	|*  6 |   INDEX RANGE SCAN               | AFXREC01                   |     1 |       |     3   (0)| 00:00:01 |
17	|   7 |  TABLE ACCESS BY INDEX ROWID     | NIO    |     1 |    24 |     5   (0)| 00:00:01 |
18	|*  8 |   INDEX RANGE SCAN               | XPKNIO |     1 |       |     4   (0)| 00:00:01 |
19	|   9 |  SORT AGGREGATE                  |                            |     1 |    40 |            |          |
20	|  10 |   NESTED LOOPS                   |                            |     1 |    40 |     5   (0)| 00:00:01 |
21	|* 11 |    TABLE ACCESS BY INDEX ROWID   | I_C         |     1 |    19 |     3   (0)| 00:00:01 |
22	|* 12 |     INDEX UNIQUE SCAN            | XPKI_C      |     1 |       |     2   (0)| 00:00:01 |
23	|  13 |    TABLE ACCESS BY INDEX ROWID   | FPT      |    20M|   412M|     2   (0)| 00:00:01 |
24	|* 14 |     INDEX UNIQUE SCAN            | FPT_PKEY |     1 |       |     1   (0)| 00:00:01 |
25	|  15 |  SORT ORDER BY                   |                            |     3 |   699 |    37   (6)| 00:00:01 |
26	|  16 |   NESTED LOOPS                   |                            |     3 |   699 |    36   (3)| 00:00:01 |
27	|  17 |    NESTED LOOPS OUTER            |                            |     3 |   576 |    30   (4)| 00:00:01 |
28	|  18 |     NESTED LOOPS OUTER           |                            |     3 |   534 |    24   (5)| 00:00:01 |
29	|  19 |      NESTED LOOPS                |                            |     3 |   483 |    18   (6)| 00:00:01 |
30	|  20 |       TABLE ACCESS BY INDEX ROWID| RE                         |     3 |   183 |     6   (0)| 00:00:01 |
31	|* 21 |        INDEX RANGE SCAN          | XPKRE_NBR                  |     3 |       |     3   (0)| 00:00:01 |
32	|  22 |       TABLE ACCESS BY INDEX ROWID| F_P                   |     1 |   100 |     4   (0)| 00:00:01 |
33	|* 23 |        INDEX RANGE SCAN          | XF_P_RE2                   |     1 |       |     2   (0)| 00:00:01 |
34	|* 24 |      INDEX RANGE SCAN            | XPKCICSI    |     1 |    17 |     2   (0)| 00:00:01 |
35	|  25 |     TABLE ACCESS BY INDEX ROWID  | CIT                  |     1 |    14 |     2   (0)| 00:00:01 |
36	|* 26 |      INDEX UNIQUE SCAN           | XPKCIT               |     1 |       |     1   (0)| 00:00:01 |
37	|* 27 |    TABLE ACCESS BY INDEX ROWID   | CMN              |     1 |    41 |     2   (0)| 00:00:01 |
38	|* 28 |     INDEX UNIQUE SCAN            | XPKCMN           |     1 |       |     1   (0)| 00:00:01 |
39	---------------------------------------------------------------------------------------------------------------

 ENV2:
----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |   213 | 54315 |   872   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | C_P               |     1 |    32 |     2   (0)|
|*  2 |   INDEX RANGE SCAN               | XPKC_P            |     1 |       |     1   (0)|
|   3 |  TABLE ACCESS BY INDEX ROWID     | LOC                   |     1 |    14 |     1   (0)|
|*  4 |   INDEX UNIQUE SCAN              | XPKLOC                |     1 |       |     0   (0)|
|   5 |  TABLE ACCESS BY INDEX ROWID     | R_C                |     1 |    28 |     2   (0)|
|*  6 |   INDEX RANGE SCAN               | AFXREC01                   |     1 |       |     1   (0)|
|   7 |  TABLE ACCESS BY INDEX ROWID     | NIO    |     1 |    25 |     4   (0)|
|*  8 |   INDEX RANGE SCAN               | XPKNIO |     1 |       |     3   (0)|
|   9 |  SORT AGGREGATE                  |                            |     1 |    40 |            |
|  10 |   NESTED LOOPS                   |                            |     1 |    40 |     5   (0)|
|* 11 |    TABLE ACCESS BY INDEX ROWID   | I_C         |     1 |    20 |     3   (0)|
|* 12 |     INDEX UNIQUE SCAN            | XPKI_C      |     1 |       |     2   (0)|
|  13 |    TABLE ACCESS BY INDEX ROWID   | FPT      |   646K|    12M|     2   (0)|
|* 14 |     INDEX UNIQUE SCAN            | FPT_PKEY |     1 |       |     1   (0)|
|  15 |  SORT ORDER BY                   |                            |   213 | 54315 |   872   (1)|
|* 16 |   HASH JOIN OUTER                |                            |   213 | 54315 |   871   (1)|
|* 17 |    HASH JOIN OUTER               |                            |   211 | 50851 |   670   (1)|
|  18 |     NESTED LOOPS                 |                            |   211 | 47053 |   571   (0)|
|  19 |      NESTED LOOPS                |                            |   211 | 36081 |   149   (0)|
|  20 |       TABLE ACCESS BY INDEX ROWID| RE                         |    46 |  1656 |    37   (0)|
|* 21 |        INDEX RANGE SCAN          | XPKRE_NBR                  |    46 |       |     1   (0)|
|  22 |       TABLE ACCESS BY INDEX ROWID| F_P                   |     5 |   675 |     3   (0)|
|* 23 |        INDEX RANGE SCAN          | XF_P_RE2                   |     5 |       |     2   (0)|
|* 24 |      TABLE ACCESS BY INDEX ROWID | CMN              |     1 |    52 |     2   (0)|
|* 25 |       INDEX UNIQUE SCAN          | XPKCMN           |     1 |       |     1   (0)|
|  26 |     INDEX FAST FULL SCAN         | XPKCICSI    | 83311 |  1464K|    97   (2)|
|  27 |    TABLE ACCESS FULL             | CIT                  | 74014 |  1011K|   200   (1)|
----------------------------------------------------------------------------------------------------
 


*The plan table name have been replaced with a meaning full name to understand (sorry for masking the name)
Re: Different plan for same sql [message #414551 is a reply to message #414550] Wed, 22 July 2009 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
5. env1 has more data compared to env2

So why the plan must be the same one?

Regards
Michel
Re: Different plan for same sql [message #414552 is a reply to message #414547] Wed, 22 July 2009 10:38 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
prachij593 wrote on Wed, 22 July 2009 16:10

Test 9 table have 100 million rows in env1 and in env2 it is around 74k.

Why table access is full in env2 for test9




So in the env2 test9 table you have 0.001% of the amount of data that's in the env1 test9 table.

I really wouldn't expect the plans to be the same in that case.
Re: Different plan for same sql [message #414553 is a reply to message #414547] Wed, 22 July 2009 10:49 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I have to add one more point here is that last analyzed date for env2 is july 21,2009 while in env1 its march3,2009
I asked the DBA that if both should be upto date...but his answer was env1 needs not to be upto date because data are not changing..

Please advice
Re: Different plan for same sql [message #414554 is a reply to message #414552] Wed, 22 July 2009 10:51 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, my apologies for the mistake

CIT has 74k in env 2
while in env1 its 12 million and plan1 for env1 is using index by rowid but in plan 2 it goes for full table scan though have less count for CIT table
Re: Different plan for same sql [message #414555 is a reply to message #414554] Wed, 22 July 2009 11:19 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
prachij593 wrote on Wed, 22 July 2009 16:51
Sir, my apologies for the mistake

CIT has 74k in env 2
while in env1 its 12 million and plan1 for env1 is using index by rowid but in plan 2 it goes for full table scan though have less count for CIT table


So instead of
0.001%
it's
0.01%

My point still stands.

Oracle thinks the full table scan is more efficient with that amount of data.
If you really think it's wrong you can always try added an index hint to the query to force it to use the index to see how long that takes, but I would assume it'll take longer.
Re: Different plan for same sql [message #414807 is a reply to message #414547] Thu, 23 July 2009 09:50 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Thanks for your valuable suggesion.


I had another concern here for the plan in env1

23	|  13 |    TABLE ACCESS BY INDEX ROWID   | FPT      |    20M|   412M|     2   (0)| 00:00:01 |
24	|* 14 |     INDEX UNIQUE SCAN            | FPT_PKEY |     1 |       |     1   (0)| 00:00:01 |


Why here the row number is so high? its 20M!! It access by index row id and using primary key (index unique scan)

Thanks
Re: Different plan for same sql [message #414948 is a reply to message #414807] Fri, 24 July 2009 02:15 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir/Madam,

I want to know why the value is so high 20M! while using Index
23	|  13 |    TABLE ACCESS BY INDEX ROWID   | FPT      |    20M|   412M|     2   (0)| 00:00:01 |
24	|* 14 |     INDEX UNIQUE SCAN            | FPT_PKEY |     1 |       |     1   (0)| 00:00:01 |



If we see the plan its always 1 in the plan other than for the above table.

21	|* 11 |    TABLE ACCESS BY INDEX ROWID   | I_C         |     1 |    19 |     3   (0)| 00:00:01 |
22	|* 12 |     INDEX UNIQUE SCAN            | XPKI_C      |     1 |       |     2   (0)| 00:00:01 |


What made the optimizer to go for 20M rows from index?
why such a high value? Is there anything wrong?

I just want to know the reason if anyone have found this
in the plan.

Is it something like that Oracle prefers to store the entire index rows for quick access for other queries as well.
Because in other few queries I am seeing the same thing.

Many Thanks
Prachi

Update: Added the question
[Is it something like that Oracle prefers to store the entire index rows for quick access for other queries as well.
Because in other few queries I am seeing the same thing.]




[Updated on: Fri, 24 July 2009 02:18]

Report message to a moderator

Re: Different plan for same sql [message #415386 is a reply to message #414547] Mon, 27 July 2009 09:23 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
All you need to do to investigate Optimizer behavior is to analyze trace files for 10053 event.

Here is a quick link
http://tonguc.wordpress.com/2007/01/20/optimizer-debug-trace-event-10053-trace-file/

The number of rows guessed by the explain is obtained analyzing the statistics values you can see on Dictionary Views:
- all_ind_statistics
- all_tab_statistics
- all_tab_col_statistics
- all_tab_histograms


Bye Alessandro
Previous Topic: Response time is high in 10g
Next Topic: sqlarea sql
Goto Forum:
  


Current Time: Wed Dec 07 18:54:43 CST 2016

Total time taken to generate the page: 0.27328 seconds