OraFAQ Forum: Performance Tuning » Please help me execution plan is looking bad

Home » RDBMS Server » Performance Tuning » Please help me execution plan is looking bad (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me execution plan is looking bad [message #613807]

Mon, 12 May 2014 08:18

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613811 is a reply to message #613807]

Mon, 12 May 2014 08:57

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?

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613813 is a reply to message #613807]

Mon, 12 May 2014 10:50

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

Solomon Yakobson
Messages: 3303
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

Solomon Yakobson
Messages: 3303
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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613858 is a reply to message #613815]

Tue, 13 May 2014 01:47

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

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613860 is a reply to message #613807]

Tue, 13 May 2014 02:12

John Watson
Messages: 8962
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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613865 is a reply to message #613814]

Tue, 13 May 2014 03:14

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

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

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613872 is a reply to message #613870]

Tue, 13 May 2014 04:45

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?

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613873 is a reply to message #613872]

Tue, 13 May 2014 05:26

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613878 is a reply to message #613870]

Tue, 13 May 2014 06:09

John Watson
Messages: 8962
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

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613884 is a reply to message #613883]

Tue, 13 May 2014 06:47

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Your plans are STILL showing super fast executions.

Do you have data skew?

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613885 is a reply to message #613884]

Tue, 13 May 2014 06:50

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

High buffer gets in the plan.

How to check data skew.

Thanks.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613888 is a reply to message #613885]

Tue, 13 May 2014 06:57

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

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613889 is a reply to message #613888]

Tue, 13 May 2014 07:07

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613895 is a reply to message #613889]

Tue, 13 May 2014 07:21

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613901 is a reply to message #613895]

Tue, 13 May 2014 08:00

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613902 is a reply to message #613901]

Tue, 13 May 2014 08:14

John Watson
Messages: 8962
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?

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613911 is a reply to message #613902]

Tue, 13 May 2014 08:35

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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613912 is a reply to message #613911]

Tue, 13 May 2014 08:42

John Watson
Messages: 8962
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.

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613913 is a reply to message #613912]

Tue, 13 May 2014 08:49

John Watson
Messages: 8962
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?

Report message to a moderator

Re: Please help me execution plan is looking bad [message #613931 is a reply to message #613913]

Tue, 13 May 2014 10:50

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.

Report message to a moderator

Previous Topic:	SQL Query too slow
Next Topic:	database performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 18:01:48 CST 2025