Home » RDBMS Server » Performance Tuning » Explain plan got changed suddenly (Oracle , 10.2.0.4)
Explain plan got changed suddenly [message #544771] Thu, 23 February 2012 12:23 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I have taken the below explain plan at around 10AM today.
The explain plan got changed suddenly. There is no any change happened in this database from morning to till now. I am really confused.
The index used in the previous plan is valid only. I dont know why this query is not taking this index. If I try force to use this index, the query is going index full scan.
Pls help me. I am really curious to find root cause of this issue

Query :


SELECT  * FROM 
           (SELECT ESAT_ORDER_NO, ACTION, ACTIVITY_TYPE FROM                       
	   (SELECT CD.ESAT_ORDER_NO,'C' AS action, CD.ACTIVITY_TYPE
	         FROM  CLI_DETAIL CD, CLI CL
                     WHERE  CD.PHONE = 575568600
                            AND CD.ACTIVITY_TYPE IN ('SMAS_ECLI_CHK_PEND', 'SMAS_ECLI_CHK_PEND2', 'SMAS_CHANGE_CHK_PEND')
                            AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
                         UNION ALL
SELECT CD.ESAT_ORDER_NO,'A' AS action, CD.ACTIVITY_TYPE
	FROM CLI_DETAIL CD, CLI CL
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE IN ('SMAS_CLI_ADD_PEND', 'SMAS_CHG_CLI_ADD_PEN')
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
UNION ALL
SELECT CD.ESAT_ORDER_NO,'M' AS action, CD.ACTIVITY_TYPE 
	FROM CLI_DETAIL CD, CLI CL
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE IN ('SMAS_CLI_MOD_PEND', 'SMAS_CHG_CLI_MOD_PEN','BAR_OPTION_MOD_PEND')
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
UNION ALL
SELECT CD.ESAT_ORDER_NO,'D' AS action, CD.ACTIVITY_TYPE
	FROM CLI_DETAIL,CLI
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE = 'DEACT_SMAS_PEND'
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO)
ORDER BY  ESAT_ORDER_NO DESC)
WHERE ROWNUM < 2;


Previous plan:
----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |     1 |    48 |    21   (5)| 00:00:01 |
|*  1 |  COUNT STOPKEY                    |                      |       |       |            |       |
|   2 |   VIEW                            |                      |     4 |   192 |    21   (5)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY          |                      |     4 |   192 |    21   (5)| 00:00:01 |
|   4 |     VIEW                          |                      |     4 |   192 |    20   (0)| 00:00:01 |
|   5 |      UNION-ALL                    |                      |       |       |            |       |
|   6 |       NESTED LOOPS                |                      |     1 |    32 |     5   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |     4   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | IDX_CLI_DETAIL_PHONE |     1 |       |     3   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  10 |       NESTED LOOPS                |                      |     1 |    32 |     5   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |     4   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN          | IDX_CLI_DETAIL_PHONE |     1 |       |     3   (0)| 00:00:01 |
|* 13 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  14 |       NESTED LOOPS                |                      |     1 |    32 |     5   (0)| 00:00:01 |
|* 15 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |     4   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN          | IDX_CLI_DETAIL_PHONE |     1 |       |     3   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  18 |       NESTED LOOPS                |                      |     1 |    32 |     5   (0)| 00:00:01 |
|* 19 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |     4   (0)| 00:00:01 |
|* 20 |         INDEX RANGE SCAN          | IDX_CLI_DETAIL_PHONE |     1 |       |     3   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------



Current explain plan :

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |    48 |   237K  (1)| 00:47:29 |
|*  1 |  COUNT STOPKEY          |                  |       |       |            |          |
|   2 |   VIEW                  |                  |     4 |   192 |   237K  (1)| 00:47:29 |
|*  3 |    SORT ORDER BY STOPKEY|                  |     4 |   192 |   237K  (1)| 00:47:29 |
|   4 |     VIEW                |                  |     4 |   192 |   237K  (1)| 00:47:29 |
|   5 |      UNION-ALL          |                  |       |       |            |          |
|   6 |       NESTED LOOPS      |                  |     1 |    32 | 59352   (1)| 00:11:53 |
|*  7 |        TABLE ACCESS FULL| CLI_DETAIL       |     1 |    26 | 59351   (1)| 00:11:53 |
|*  8 |        INDEX UNIQUE SCAN| PK_ESAT_ORDER_NO |     1 |     6 |     1   (0)| 00:00:01 |
|   9 |       NESTED LOOPS      |                  |     1 |    32 | 59344   (1)| 00:11:53 |
|* 10 |        TABLE ACCESS FULL| CLI_DETAIL       |     1 |    26 | 59343   (1)| 00:11:53 |
|* 11 |        INDEX UNIQUE SCAN| PK_ESAT_ORDER_NO |     1 |     6 |     1   (0)| 00:00:01 |
|  12 |       NESTED LOOPS      |                  |     1 |    32 | 59352   (1)| 00:11:53 |
|* 13 |        TABLE ACCESS FULL| CLI_DETAIL       |     1 |    26 | 59351   (1)| 00:11:53 |
|* 14 |        INDEX UNIQUE SCAN| PK_ESAT_ORDER_NO |     1 |     6 |     1   (0)| 00:00:01 |
|  15 |       NESTED LOOPS      |                  |     1 |    32 | 59336   (1)| 00:11:53 |
|* 16 |        TABLE ACCESS FULL| CLI_DETAIL       |     1 |    26 | 59335   (1)| 00:11:53 |
|* 17 |        INDEX UNIQUE SCAN| PK_ESAT_ORDER_NO |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------



SQL> select index_name,last_analyzed,status from user_indexes where table_name = 'CLI_DETAIL';

INDEX_NAME                     LAST_ANAL STATUS
------------------------------ --------- ---------
IDX_CLI_DETAIL_PHONE           13-FEB-12 VALID
FK_ESAT_ORDER_NO               03-FEB-12 VALID




Explain plan after forcing index:

explain plan for
SELECT  * FROM 
   (SELECT   ESAT_ORDER_NO, ACTION, ACTIVITY_TYPE FROM                       
   (SELECT  /*+ index(CD IDX_CLI_DETAIL_PHONE) */ CD.ESAT_ORDER_NO,'C' AS action, CD.ACTIVITY_TYPE
	         FROM  CLI_DETAIL CD,CLI CL
                     WHERE  CD.PHONE = 575568600
                            AND CD.ACTIVITY_TYPE IN ('SMAS_ECLI_CHK_PEND', 'SMAS_ECLI_CHK_PEND2', 'SMAS_CHANGE_CHK_PEND')
                            AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
                         UNION ALL
SELECT /*+ index(CD IDX_CLI_DETAIL_PHONE) */ CD.ESAT_ORDER_NO,'A' AS action, CD.ACTIVITY_TYPE
	FROM CLI_DETAIL CD,CLI CL
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE IN ('SMAS_CLI_ADD_PEND', 'SMAS_CHG_CLI_ADD_PEN')
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
UNION ALL
SELECT /*+ index(CD IDX_CLI_DETAIL_PHONE) */ CD.ESAT_ORDER_NO,'M' AS action, CD.ACTIVITY_TYPE 
	FROM CLI_DETAIL CD,CLI CL
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE IN ('SMAS_CLI_MOD_PEND', 'SMAS_CHG_CLI_MOD_PEN','BAR_OPTION_MOD_PEND')
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO 
UNION ALL
SELECT /*+ index(CD IDX_CLI_DETAIL_PHONE) */ CD.ESAT_ORDER_NO,'D' AS action, CD.ACTIVITY_TYPE
	FROM CLI_DETAIL CD,CLI CL
	WHERE CD.PHONE = 575568600
	AND CD.ACTIVITY_TYPE = 'DEACT_SMAS_PEND'
	AND CD.ESAT_ORDER_NO = CL.ESAT_ORDER_NO)
ORDER BY  ESAT_ORDER_NO DESC) 
WHERE ROWNUM < 2;



----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |     1 |    48 | 27753   (1)| 00:05:34 |
|*  1 |  COUNT STOPKEY                    |                      |       |       |            |       |
|   2 |   VIEW                            |                      |     4 |   192 | 27753   (1)| 00:05:34 |
|*  3 |    SORT ORDER BY STOPKEY          |                      |     4 |   192 | 27753   (1)| 00:05:34 |
|   4 |     VIEW                          |                      |     4 |   192 | 27752   (1)| 00:05:34 |
|   5 |      UNION-ALL                    |                      |       |       |            |       |
|   6 |       NESTED LOOPS                |                      |     1 |    32 |  6938   (1)| 00:01:24 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |  6937   (1)| 00:01:24 |
|*  8 |         INDEX FULL SCAN           | IDX_CLI_DETAIL_PHONE |     2 |       |  6935   (1)| 00:01:24 |
|*  9 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  10 |       NESTED LOOPS                |                      |     1 |    32 |  6938   (1)| 00:01:24 |
|* 11 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |  6937   (1)| 00:01:24 |
|* 12 |         INDEX FULL SCAN           | IDX_CLI_DETAIL_PHONE |     2 |       |  6935   (1)| 00:01:24 |
|* 13 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  14 |       NESTED LOOPS                |                      |     1 |    32 |  6938   (1)| 00:01:24 |
|* 15 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |  6937   (1)| 00:01:24 |
|* 16 |         INDEX FULL SCAN           | IDX_CLI_DETAIL_PHONE |     2 |       |  6935   (1)| 00:01:24 |
|* 17 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
|  18 |       NESTED LOOPS                |                      |     1 |    32 |  6938   (1)| 00:01:24 |
|* 19 |        TABLE ACCESS BY INDEX ROWID| CLI_DETAIL           |     1 |    26 |  6937   (1)| 00:01:24 |
|* 20 |         INDEX FULL SCAN           | IDX_CLI_DETAIL_PHONE |     2 |       |  6935   (1)| 00:01:24 |
|* 21 |        INDEX UNIQUE SCAN          | PK_ESAT_ORDER_NO     |     1 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

[Updated on: Thu, 23 February 2012 12:26]

Report message to a moderator

Re: Explain plan got changed suddenly [message #544784 is a reply to message #544771] Thu, 23 February 2012 15:06 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Perhaps the statistics changed. Have a look at which plans the CBO is considering using trace 10053
Examining 10053
True story
Ask Tom about the trace
apparently there is a viewer (haven't tested it yet)
Viewer making trace 10053 readable?

Report message to a moderator

Re: Explain plan got changed suddenly [message #544844 is a reply to message #544771] Fri, 24 February 2012 04:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html

But bear this point from next time. The most important step in your explain/execution plan is the predicate section. That's the one which would normally give you a clue why index is not been used. Also, FTS is not evil and Index are not always good.

For starters go through this links to understand why oracle is not using my index.

http://richardfoote.wordpress.com/2011/09/28/why-is-my-index-not-being-used-quiz-brain-damage/

http://richardfoote.wordpress.com/2011/10/01/why-is-my-index-not-being-used-solution-eclipse/

http://richardfoote.wordpress.com/2011/10/03/why-is-my-index-not-being-used-no-2-quiz-quicksand/

Regards

Raj

Report message to a moderator

Re: Explain plan got changed suddenly [message #544919 is a reply to message #544771] Fri, 24 February 2012 17:00 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Could you please send the output of

desc CLI_DETAIL

Report message to a moderator

Previous Topic: Issue with bulk collect over a db link
Next Topic: Slow Query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 21 03:39:09 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.