| Different plan for same sql [message #414547] |
Wed, 22 July 2009 10:10  |
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 #414550 is a reply to message #414549] |
Wed, 22 July 2009 10:34  |
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 #414552 is a reply to message #414547] |
Wed, 22 July 2009 10:38 |
cookiemonster
Messages: 13925
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 |
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 |
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 |
cookiemonster
Messages: 13925
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 |
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
