Home » RDBMS Server » Performance Tuning » Please help me execution plan is looking bad (Oracle 11g)
Please help me execution plan is looking bad [message #613807] Mon, 12 May 2014 08:18 Go to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi Experts,

step 9 and 10 are looks bad.How to tune it.

These are the indexes on the tables.


CREATE INDEX CRD_FTO_MAIN_N2 ON CRD_FTO_MAIN(RTO_NO);
CREATE UNIQUE INDEX PK_CRD_RTO ON CRD_FTO_MAIN(FOT_NO); 
CREATE INDEX CRD_FTO_CUST_IXU ON CRD_FTO_RD_CHD(CHD_ID, RTL_TP);
CREATE UNIQUE INDEX IDX_CUST_PK ON CRD_FTO_RD_CHD(FOT_NO, RTL_TP);
CREATE INDEX XT_TN_DT_NK1 ON XT_TN_DT(OR_ID, LTS_TP, LTS_CODE);

CREATE INDEX XT_TN_DT_UK1 ON XT_TN_DT;
(CASE "LTS_TP" WHEN 'I4_DATA' THEN "ROW_BATCH_ID" ELSE NULL END );
ALTER TABLE XT_TN_DT ADD (CONSTRAINT XT_TN_DT_PK PRIMARY KEY(ROW_BATCH_ID, ROW_NUM));
 
CREATE UNIQUE INDEX PK_CRD_RTO_LIN ON CRD_RTO_LIN(FOT_NO, BID_NO, LIN_NO);

CREATE INDEX rto_idx ON CRD_FTO_MAIN (RTO_NO,NVL (SRS_FT, 'N'),NVL (CSR_FT, 'N'),IGNORED);

 SELECT CFM.FOT_NO,
         CFM.PREST,
         MSDTR.CHD_ID AS MSDTR_CHD_ID,
         CRL.DIT,
         CFM.SOURCE_FOT_NO,
         CFM.MODEL_NO,
         CASE
            WHEN NVL (CFM.MODEL_NO, 0) > 0 AND NVL (CFM.SOURCE_FOT_NO, 0) <> CFM.FOT_NO
            THEN CFM.SOURCE_FOT_NO || '/' || CFM.MODEL_NO
            ELSE TO_CHAR (CFM.FOT_NO)
         END AS FULL_FOT_NO,
         ROWNUM AS ROWNUMBER
    FROM CRD_FTO_MAIN CFM,
         CRD_FTO_RD_CHD MSDTR,
         CRD_RTO_LIN CRL,
         CRD_LTP CLP
   WHERE     MSDTR.FOT_NO = CFM.FOT_NO
         AND MSDTR.RTL_TP = 'MSDTR'
         AND MSDTR.CHD_ID = :CHD_ID
         AND CFM.RTO_NO = 0
         AND CRL.FOT_NO = CFM.FOT_NO
         AND CRL.BID_NO = 3
         AND CRL.LIN_NO = 3
         AND NVL (CFM.CSR_FT, 'N') ='N'
         AND NVL (CFM.SRS_FT, 'N') ='N'
         AND CFM.CDS_DATE >= TRUNC (SYSDATE - CFM.QT_VD_TM)
         AND CFM.IGNORED = 'N'
         AND PREST = 'C'
         AND CLP.LTS_TP(+) = 'DSC_REASON'
         AND CLP.LTS_CODE(+) = CFM.DSC_CODE
         AND ROWNUM <= :MAX_RECORDS
ORDER BY SOURCE_FOT_NO DESC, PREST, NVL (MODEL_NO, 0) DESC
 
Plan hash value: 2023432903
 
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |      1 |        |     50 |00:00:01.58 |     106K|       |       |          |
|   1 |  SORT ORDER BY                       |                       |      1 |      6 |     50 |00:00:01.58 |     106K| 13312 | 13312 |12288  (0)|
|*  2 |   COUNT STOPKEY                      |                       |      1 |        |     50 |00:00:01.57 |     106K|       |       |          |
|   3 |    NESTED LOOPS                      |                       |      1 |        |     50 |00:00:01.57 |     106K|       |       |          |
|   4 |     NESTED LOOPS                     |                       |      1 |      6 |     50 |00:00:01.57 |     106K|       |       |          |
|   5 |      NESTED LOOPS OUTER              |                       |      1 |      6 |     50 |00:00:01.57 |     106K|       |       |          |
|   6 |       NESTED LOOPS                   |                       |      1 |      6 |     50 |00:00:01.57 |     106K|       |       |          |
|*  7 |        INDEX RANGE SCAN              | IDX_CID_RTP_FTO_NO    |      1 |      6 |    110K|00:00:00.11 |     429 |       |       |          |
|*  8 |        TABLE ACCESS BY INDEX ROWID   | CRD_FTO_MAIN          |    110K|      1 |     50 |00:00:01.19 |     105K|       |       |          |
|*  9 |         INDEX UNIQUE SCAN            | PK_CRD_RTO            |    110K|      1 |    110K|00:00:00.35 |    7205 |       |       |          |
|  10 |       INLIST ITERATOR                |                       |     50 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |        TABLE ACCESS BY INDEX ROWID   | XT_TN_DT              |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 12 |         INDEX RANGE SCAN             | XT_TN_DT_NK1          |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |      PARTITION RANGE SINGLE          |                       |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|  14 |       PARTITION LIST SINGLE          |                       |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|* 15 |        INDEX UNIQUE SCAN             | PK_CRD_RTO_LIN        |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|  16 |     TABLE ACCESS BY LOCAL INDEX ROWID| CRD_RTO_LIN           |     50 |      1 |     50 |00:00:00.01 |      50 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=TO_NUMBER(:MAX_RECORDS))
   8 - access("MSDTR"."CHD_ID"=TO_NUMBER(:CHD_ID) AND "MSDTR"."RTL_TP"='MSDTR')
   9 - filter(("CFM"."RTO_NO"=0 AND "PREST"='C' AND "CFM"."CDS_DATE">=TRUNC(SYSDATE@!-INTERNAL_FUNCTION("CFM"."QT_VD_TM")) AND
              "CFM"."IGNORED"='N' AND NVL (CFM.SRS_FT, 'N') ='N' AND NVL (CFM.CSR_FT, 'N') ='N')))
  10 - access("MSDTR"."FOT_NO"="CFM"."FOT_NO")
  13 - access((("OR_ID"=0 OR "OR_ID"="CRD_CS_PKG"."GETMRONUMBER"('OR_ID'))) AND "LTS_TP"='DSC_REASON' AND
              "LTS_CODE"="CFM"."DSC_CODE")
       filter(("LTS_TP"<>'I4_DATA' AND "LTS_TP"<>'I4_PS' AND "LTS_TP"<>'I4_JB'))
  16 - access("CRL"."FOT_NO"="CFM"."FOT_NO" AND "CRL"."BID_NO"=3 AND "CRL"."LIN_NO"=3)



Please help me.

Thanks.

Re: Please help me execution plan is looking bad [message #613811 is a reply to message #613807] Mon, 12 May 2014 08:57 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Why do you think 9 and 10 are bad? Indeed, why is 1 .58 seconds bad?
Re: Please help me execution plan is looking bad [message #613813 is a reply to message #613807] Mon, 12 May 2014 10:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
What is the data type of column QT_VD_TM in the table CRD_FTO_MAIN ? I see INTERNAL_FUNCTION being applied which might be an internal data type conversion.

Edit : Not sure why there are two posts simultaneously at same time. Request a moderator to delete this duplicate post.

[Updated on: Mon, 12 May 2014 11:07]

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613814 is a reply to message #613807] Mon, 12 May 2014 11:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Lalit Kumar B wrote on Mon, 12 May 2014 11:50
What is the data type of column QT_VD_TM in the table CRD_FTO_MAIN ? I see INTERNAL_FUNCTION being applied which might be an internal data type conversion.


You will see it every time date arithmetic between date and number is applied:

SQL> explain plan for
  2  select * from emp
  3  where hiredate > sysdate - empno
  4  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

   1 - filter("HIREDATE">SYSDATE@!-INTERNAL_FUNCTION("EMPNO"))

13 rows selected.

SQL> explain plan for
  2  select * from emp
  3  where hiredate > hiredate - empno;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

   1 - filter("HIREDATE">INTERNAL_FUNCTION("HIREDATE")-INTERNAL_FUNCTION
              ("EMPNO"))

14 rows selected.


SY.

[Updated on: Mon, 12 May 2014 11:29]

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613815 is a reply to message #613814] Mon, 12 May 2014 11:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And compare:

SQL> select dump(hiredate),dump(hiredate - 0) from emp where rownum = 1;

DUMP(HIREDATE)
-------------------------------------------------------------------------
DUMP(HIREDATE-0)
-------------------------------------------------------------------------
Typ=12 Len=7: 119,180,12,17,1,1,1
Typ=13 Len=8: 188,7,12,17,0,0,0,0

SQL>


SY.
Re: Please help me execution plan is looking bad [message #613858 is a reply to message #613815] Tue, 13 May 2014 01:47 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

This field QT_VD_TM is NUMBER(2) data type.

Please help me.

Thanks
Re: Please help me execution plan is looking bad [message #613860 is a reply to message #613807] Tue, 13 May 2014 02:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you explain how you generated that plan? The predicate information doesn't match the operations.
You have also missed the definition of a rather important index, IDX_CID_RTP_FTO_NO,
which appears to be where the cardinality mis-estimate is occurring.
Re: Please help me execution plan is looking bad [message #613865 is a reply to message #613814] Tue, 13 May 2014 03:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Solomon Yakobson wrote on Mon, 12 May 2014 21:56
Lalit Kumar B wrote on Mon, 12 May 2014 11:50
What is the data type of column QT_VD_TM in the table CRD_FTO_MAIN ? I see INTERNAL_FUNCTION being applied which might be an internal data type conversion.


You will see it every time date arithmetic between date and number is applied:



Not every time. In arithmetic between DATE and NUMBER, at least one of the operands must be a fetched from DB for INTERNAL_FUNCTION to be applied. If both date and number are not DB values, there wouldn't be any internal function.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as LALIT@orcl

SQL> explain plan for select * from emp where hiredate > sysdate - 100;
Explained

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE">SYSDATE@!-100)
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected


Now, if a function is applied over any of the column, internal function will be applied again. If TRUNC is applied to hiredate in the above query :

SQL> explain plan for select * from emp where trunc(hiredate) > sysdate - 100;
Explained

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRUNC(INTERNAL_FUNCTION("HIREDATE"))>SYSDATE@!-100)
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected


[Updated on: Tue, 13 May 2014 03:49]

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613870 is a reply to message #613860] Tue, 13 May 2014 04:20 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

I have generated the plan as below.

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

IDX_CRD_FTO_RD_CHD Index definition

CREATE INDEX IDX_CRD_FTO_RD_CHD ON CRD_FTO_RD_CHD(CHD_ID, RTL_TP, FOT_NO);

Please help me.

Thanks
Re: Please help me execution plan is looking bad [message #613872 is a reply to message #613870] Tue, 13 May 2014 04:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Per the explain plan the query takes 1.58 seconds, and you want to tune it further? How much time does the query actually takes to execute?
Re: Please help me execution plan is looking bad [message #613873 is a reply to message #613872] Tue, 13 May 2014 05:26 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

It's taking 101K buffer gets.
And also some times the query taking 300 secs.

Thanks.
Re: Please help me execution plan is looking bad [message #613878 is a reply to message #613870] Tue, 13 May 2014 06:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ramya_162 wrote on Tue, 13 May 2014 10:20
Hi,

I have generated the plan as below.

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

IDX_CRD_FTO_RD_CHD Index definition

CREATE INDEX IDX_CRD_FTO_RD_CHD ON CRD_FTO_RD_CHD(CHD_ID, RTL_TP, FOT_NO);

Please help me.

Thanks
Sorry, I do not understand this. You plan should have predicate information for operations 2,7,8,9,12, and 15. But your predicate section is for operations 2,8,9,10,13, and 16. It is impossible to work out what is happening. You had better do it again, and this time use copy/paste to show what you did.

The problem is clear, though: the cardinality mistake that is causing 110k index lookups. More than likely, you need a hash join based on scans, not an indexed nested loop join.

[Updated on: Tue, 13 May 2014 08:20]

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613883 is a reply to message #613878] Tue, 13 May 2014 06:45 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi ,

Please find the latest plan after changing the where condition filter.

Instead of this
         AND NVL (CFM.CSR_FT, 'N') ='N'
         AND NVL (CFM.SRS_FT, 'N') ='N'

Used 

   AND (CFM.CSR_FT ='N' OR CFM.CSR_FT IS NULL
   AND  CFM.SRS_FT ='N' OR CFM.SRS_FT IS NULL)

Plan hash value: 2023432903
 
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |      1 |        |     50 |00:00:01.87 |     118K|       |       |          |
|   1 |  SORT ORDER BY                       |                       |      1 |      6 |     50 |00:00:01.87 |     118K| 13312 | 13312 |12288  (0)|
|*  2 |   COUNT STOPKEY                      |                       |      1 |        |     50 |00:00:01.87 |     118K|       |       |          |
|   3 |    NESTED LOOPS                      |                       |      1 |        |     50 |00:00:01.87 |     118K|       |       |          |
|   4 |     NESTED LOOPS                     |                       |      1 |      6 |     50 |00:00:01.87 |     118K|       |       |          |
|   5 |      NESTED LOOPS OUTER              |                       |      1 |      6 |     50 |00:00:01.87 |     118K|       |       |          |
|   6 |       NESTED LOOPS                   |                       |      1 |      6 |     50 |00:00:01.87 |     118K|       |       |          |
|*  7 |        INDEX RANGE SCAN              | IDX_CID_RTP_FTO_NO    |      1 |      6 |    122K|00:00:00.12 |     509 |       |       |          |
|*  8 |        TABLE ACCESS BY INDEX ROWID   | CRD_FTO_MAIN          |    122K|      1 |     50 |00:00:01.46 |     118K|       |       |          |
|*  9 |         INDEX UNIQUE SCAN            | PK_CRD_RTO            |    122K|      1 |    122K|00:00:00.43 |    7787 |       |       |          |
|  10 |       INLIST ITERATOR                |                       |     50 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |        TABLE ACCESS BY INDEX ROWID   | XT_TN_DT              |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 12 |         INDEX RANGE SCAN             | XT_TN_DT_NK1          |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |      PARTITION RANGE SINGLE          |                       |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|  14 |       PARTITION LIST SINGLE          |                       |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|* 15 |        INDEX UNIQUE SCAN             | PK_CRD_RTO_LIN        |     50 |      1 |     50 |00:00:00.01 |      14 |       |       |          |
|  16 |     TABLE ACCESS BY LOCAL INDEX ROWID| CRD_RTO_LIN           |     50 |      1 |     50 |00:00:00.01 |      50 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=50)
   7 - access("MSDTR"."CHD_ID"=34588 AND "MSDTR"."RTL_TP"='MSDTR')
   8 - filter(("CFM"."RTO_NO"=0 AND "CFM"."PREST"='C' AND "CFM"."CDS_DATE">=TRUNC(SYSDATE@!-INTERNAL_FUNCTION("CFM"."QT_VD_TM")) 
   AND "CFM"."IGNORED"='N' AND ("CFM"."CSR_FT" IS NULL OR "CFM"."CSR_FT"='N') AND 
              "CFM"."SRS_FT"='N'))
   9 - access("MSDTR"."FOT_NO"="CFM"."FOT_NO")
  12 - access((("OR_ID"=0 OR "OR_ID"="CRD_CS_PKG"."GETMRONUMBER"('OR_ID'))) AND "LTS_TP"='DSC_REASON' AND 
              "LTS_CODE"="CFM"."DSC_CODE")
       filter(("LTS_TP"<>'I4_DATA' AND "LTS_TP"<>'I4_PS' AND "LTS_TP"<>'I4_JB'))
  15 - access("CRL"."FOT_NO"="CFM"."FOT_NO" AND "CRL"."BID_NO"=3 AND "CRL"."LIN_NO"=3)



I have created the below index and commented this condition plan is looking good.

CREATE UNIQUE INDEX IDX_FTO_RTO_PREST ON CRD_FTO_MAIN
(FOT_NO, RTO_NO, PREST)

AND CFM.CDS_DATE >= TRUNC (SYSDATE - CFM.QT_VD_TM)


Plan hash value: 3068461363
 
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |      1 |        |     50 |00:00:00.01 |     928 |       |       |          |
|   1 |  SORT ORDER BY                       |                             |      1 |      6 |     50 |00:00:00.01 |     928 | 13312 | 13312 |12288  (0)|
|*  2 |   COUNT STOPKEY                      |                             |      1 |        |     50 |00:00:00.01 |     928 |       |       |          |
|   3 |    NESTED LOOPS                      |                             |      1 |        |     50 |00:00:00.01 |     928 |       |       |          |
|   4 |     NESTED LOOPS                     |                             |      1 |      6 |     50 |00:00:00.01 |     878 |       |       |          |
|   5 |      NESTED LOOPS OUTER              |                             |      1 |      6 |     50 |00:00:00.01 |     776 |       |       |          |
|   6 |       NESTED LOOPS                   |                             |      1 |      6 |     50 |00:00:00.01 |     776 |       |       |          |
|*  7 |        INDEX RANGE SCAN              | IDX_CID_RTP_FTO_NO          |      1 |      6 |    360 |00:00:00.01 |       6 |       |       |          |
|*  8 |        TABLE ACCESS BY INDEX ROWID   | CRD_FTO_MAIN                |    360 |      1 |     50 |00:00:00.01 |     770 |       |       |          |
|*  9 |         INDEX UNIQUE SCAN            | IDX_FTO_RTO_PREST           |    360 |      1 |     51 |00:00:00.01 |     719 |       |       |          |
|  10 |       INLIST ITERATOR                |                             |     50 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |        TABLE ACCESS BY INDEX ROWID   | XT_TN_DT                    |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 12 |         INDEX RANGE SCAN             | XT_TN_DT_NK1                |    100 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |      PARTITION RANGE SINGLE          |                             |     50 |      1 |     50 |00:00:00.01 |     102 |       |       |          |
|  14 |       PARTITION LIST SINGLE          |                             |     50 |      1 |     50 |00:00:00.01 |     102 |       |       |          |
|* 15 |        INDEX UNIQUE SCAN             | PK_CRD_RTO_LIN              |     50 |      1 |     50 |00:00:00.01 |     102 |       |       |          |
|  16 |     TABLE ACCESS BY LOCAL INDEX ROWID| CRD_RTO_LIN                 |     50 |      1 |     50 |00:00:00.01 |      50 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=50)
   7 - access("MSDTR"."CHD_ID"=34588 AND "MSDTR"."RTL_TP"='MSDTR')
   8 - filter(("CFM"."IGNORED"='N' AND ("CFM"."CSR_FT" IS NULL OR "CFM"."CSR_FT"='N') AND "CFM"."SRS_FT"='N'))
   9 - access("MSDTR"."FOT_NO"="CFM"."FOT_NO" AND "CFM"."RTO_NO"=0 AND "CFM"."PREST"='C')
  12 - access((("OR_ID"=0 OR "OR_ID"="CRD_CS_PKG"."GETMRONUMBER"('OR_ID'))) AND "LTS_TP"='DSC_REASON' AND 
              "LTS_CODE"="CFM"."DSC_CODE")
       filter(("LTS_TP"<>'I4_DATA' AND "LTS_TP"<>'I4_PS' AND "LTS_TP"<>'I4_JB'))
  15 - access("CRL"."FOT_NO"="CFM"."FOT_NO" AND "CRL"."BID_NO"=3 AND "CRL"."LIN_NO"=3)


Please help me.

Thanks.
Re: Please help me execution plan is looking bad [message #613884 is a reply to message #613883] Tue, 13 May 2014 06:47 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Your plans are STILL showing super fast executions.

Do you have data skew?
Re: Please help me execution plan is looking bad [message #613885 is a reply to message #613884] Tue, 13 May 2014 06:50 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

High buffer gets in the plan.

How to check data skew.

Thanks.
Re: Please help me execution plan is looking bad [message #613888 is a reply to message #613885] Tue, 13 May 2014 06:57 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Data skew means the data isn't evenly distributed. That can confuse the opimizer.

An example would be something like a workflow table which has an "in progress" column, the majority (lets say 99%) would be "no" and the few remaining "yes". That is skewed data.

As for how to check that, you need to run some queries your side.



High buffer gets are not necessarily a problem - what problem do you have? Excessive IO? Users complaining (unlikely with a <2 second query)? Something else?

You need to articulate the problem you face so a decent solution can be offered - point to a "big" number and saying that is bad without explaining why doesnt help us Sad
Re: Please help me execution plan is looking bad [message #613889 is a reply to message #613888] Tue, 13 May 2014 07:07 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Thanks for your explanation.

On what columns I have to check the data skew.

Thanks.
Re: Please help me execution plan is looking bad [message #613895 is a reply to message #613889] Tue, 13 May 2014 07:21 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The predicate columns.

You've still not said what the problem actually is. I'm sat in front of an AWR report right now with a bit of sql with 261504 gets per exe - but there's not a problem.
Re: Please help me execution plan is looking bad [message #613901 is a reply to message #613895] Tue, 13 May 2014 08:00 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Please find the data.

PREST COUNT(*)

3339511
C 4747314
N 2276084
F 7149148

IGNORED COUNT(*)

1
Y 743670
N 16768386


CSR_FT COUNT(*)

17512039
Y 18


CSR_FT COUNT(*)

345290
Y 198547
N 16968220

Thanks.
Re: Please help me execution plan is looking bad [message #613902 is a reply to message #613901] Tue, 13 May 2014 08:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your last two plans are correct: the predicates match the operations. I do not know how you managed to get that first one you posted, it is clearly nonsense.

Can you explain whether you have a problem or not? Your last plan executed in 0.01 seconds, the one before executed in 1.87 seconds. This is not surprising, because they are for different queries: you have removed one of the predicates. So, do you want to tune the original query, or are you happy with the modified query that gives (presumably) different results?
Re: Please help me execution plan is looking bad [message #613911 is a reply to message #613902] Tue, 13 May 2014 08:35 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi ,

I want to tune the original query.
Since that filter is mandatory.

Please help me.

Thanks.
Re: Please help me execution plan is looking bad [message #613912 is a reply to message #613911] Tue, 13 May 2014 08:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ramya_162 wrote on Tue, 13 May 2014 14:35
Hi ,

I want to tune the original query.
Since that filter is mandatory.

Please help me.

Thanks.

In that case, you need to investigate the cardinality estimate error. The CBO thinks that this predicate,
7 - access("MSDTR"."CHD_ID"=34588 AND "MSDTR"."RTL_TP"='MSDTR')
will return 6 rows. In fact, it is returning 122k rows. I would create extended statistics across those two columns, and try again. The optimizer may come up with a better plan immediately. If it doesn't there are other things to try.

And you need to provide the definition of the IDX_CID_RTP_FTO_NO index.
Re: Please help me execution plan is looking bad [message #613913 is a reply to message #613912] Tue, 13 May 2014 08:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've just noticed that you are obfuscating the truth. Again.

Your first plan posted was clearly a lie: the plan didn't match the predicates.

Your last two plans give different results for the same predicate, at operation 7. This
7 - access("MSDTR"."CHD_ID"=34588 AND "MSDTR"."RTL_TP"='MSDTR')
is returning 122k rows in one execution, only 360 rows in the other.

What are you doing?


Re: Please help me execution plan is looking bad [message #613931 is a reply to message #613913] Tue, 13 May 2014 10:50 Go to previous message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
HI,


The definition of the index IDX_CID_RTP_FTO_NO is
CREATE INDEX IDX_CID_RTP_FTO_NO ON CRD_FTO_RD_CHD (CHD_ID, RTL_TP, FOT_NO);

In one execution I have this filter AND CFM.CDS_DATE >= TRUNC (SYSDATE - CFM.QT_VD_TM)

In another execution I have removed filter AND CFM.CDS_DATE >= TRUNC (SYSDATE - CFM.QT_VD_TM) and created the below index.

CREATE UNIQUE INDEX IDX_FTO_RTO_PREST ON CRD_FTO_MAIN
(FOT_NO, RTO_NO, PREST)

Because of this there is a variation in plan.

I am not telling lie.

Please help me.

Thanks.
Previous Topic: SQL Query too slow
Next Topic: database performance issue
Goto Forum:
  


Current Time: Fri Mar 29 09:35:12 CDT 2024