1 Reply Latest reply: Jun 4, 2014 6:03 AM by Top.Gun RSS

    Query Going for FTS after adopting a SQL Profile.

    Prashant Dixit

      Hi,

      I am facing a performance issue with one of our production query which was taking high elapsed time to complete.

       

      -------------------------------------------------------------------------------------------------------

      | Id  | Operation                         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

      -------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                  |                   |     1 |    49 | 45768   (1)| 00:09:10 |

      |   1 |  SORT ORDER BY                    |                   |     1 |    49 | 45768   (1)| 00:09:10 |

      |*  2 |   TABLE ACCESS BY INDEX ROWID     | T188              |     1 |    49 | 45767   (1)| 00:09:10 |

      |   3 |    BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |          |

      |   4 |     BITMAP OR                     |                   |       |       |            |          |

      |   5 |      BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |

      |*  6 |       INDEX RANGE SCAN            | I188_1101090600_1 |       |       |  1846   (1)| 00:00:23 |

      |   7 |      BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |

      |*  8 |       INDEX RANGE SCAN            | I188_1101090600_1 |       |       |  1846   (1)| 00:00:23 |

      |   9 |      BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |

      |* 10 |       INDEX RANGE SCAN            | I188_1101083000_2 |       |       |     1   (0)| 00:00:01 |

      -------------------------------------------------------------------------------------------------------

       

       

      As per SQL TUNING ADVISOR:

      A potentially better execution plan was found for this statement.

       

                                 Original Plan  With SQL Profile  % Improved

                                 -------------  ----------------  ----------

        Completion Status:            COMPLETE          COMPLETE

        Elapsed Time (s):            4.137166            .00055      99.98 %

        CPU Time (s):                 4.13637           .000599      99.98 %

        User I/O Time (s):                  0                 0

        Buffer Gets:                   161632                10      99.99 %

        Physical Read Requests:             0                 0

        Physical Write Requests:            0                 0

        Physical Read Bytes:                0                 0

        Physical Write Bytes:               0                 0

        Rows Processed:                     0                 0

        Fetches:                            0                 0

        Executions:                         1                 1

       

       

      execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_122812',

                  task_owner => 'ARADMIN', replace => TRUE);

       

      - SQL Profile "SYS_SQLPROF_013fe75c46ee0000" exists for this statement and

        was ignored during the tuning process.

       

       

      10:31:35 SQL> conn / as sysdba

      Connected.

      10:31:47 SQL>  execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_122812',task_owner => 'ARADMIN', replace => TRUE);

      PL/SQL procedure successfully completed.

       

       

       

      10:32:31 SQL> @xplan

       

      PLAN_TABLE_OUTPUT

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Plan hash value: 552925884

       

      ----------------------------------------------------------

      | Id  | Operation         | Name | Rows  | Bytes | Cost  |

      ----------------------------------------------------------

      |   0 | SELECT STATEMENT  |      |     1 |    49 | 15335 |

      |*  1 |  TABLE ACCESS FULL| T188 |     1 |    49 | 15335 |

      ----------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         1 - filter("T188"."C1209080010" IS NULL AND "T188"."C1209080008" IS

                    NULL AND ("T188"."C536870930"<>:SYS_B_00 AND

                    "T188"."C1101090600"=TO_NUMBER(:SYS_B_01) AND

                    "T188"."C1805011401"<>TO_NUMBER(:SYS_B_02) AND "T188"."C1101083000" IS

                    NULL AND "T188"."C1101083001" IS NULL OR "T188"."C536870930"<>:SYS_B_03

                    AND "T188"."C1101090600"=TO_NUMBER(:SYS_B_04) AND

                    "T188"."C1805011401"<>TO_NUMBER(:SYS_B_05) AND "T188"."C1101083000" IS

                    NULL AND "T188"."C1101083001" IS NULL OR "T188"."C1101083000"=:SYS_B_06

                    AND "T188"."C1101083001"<>:SYS_B_07 AND

                    "T188"."C1101090600"=TO_NUMBER(:SYS_B_08) AND

                    "T188"."C1805011401"<>TO_NUMBER(:SYS_B_09)))

       

      Note

      -----

         - cpu costing is off (consider enabling it)

         - SQL profile "SYS_SQLPROF_013fe75c46ee0000" used for this statement

       

       

      DETAILS:

      1- Original With Adjusted Cost

      --------------------------------------------------

      Plan hash value: 1227640257

       

      2- Using SQL Profile

      ---------------------------------------------------

      Plan hash value: 2296671510

       

      10:38:22 SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('3a4s0qvv6udrx'));

       

      PLAN_TABLE_OUTPUT

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      SQL_ID 3a4s0qvv6udrx

      Plan hash value: 1227640257

       

       

      NOW THE QUERY STARTED DOING FULL TABLE SCANS IN SYSTEM.

       

      How can i go back and force optimzer to adopt old plan hash ...?

      Please Help!

       

      Thanks

      Prashant Dixit