5 Replies Latest reply on May 2, 2013 2:20 PM by LANCERIQUE

    Explain Plan not showing correct results.

    LANCERIQUE
      Hi Gurus,

      Please help me to understand below issue.

      Whenever I am doing explain plan on any of the sql statements it says as explained but when retriving the explain plan output it shows same results again and again.

      DB - 11gR2 Stand alone
      ASM - Configured.
      OS - RHEL 6.2.


      SQL> select count(*) from t2114;

      COUNT(*)
      ----------
      639292

      SQL> explain plan for select count(*) from t2114;

      Explained.

      SQL> @?/rdbms/admin/utlxpls.sql

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 1497650422

      ----------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 6634 | 524K| 2993 (19)| 00:00:01 |
      | 1 | SORT ORDER BY | | 6634 | 524K| 2993 (19)| 00:00:01 |
      | 2 | TABLE ACCESS BY INDEX ROWID| T2210 | 6634 | 524K| 2947 (17)| 00:00:01 |
      |* 3 | INDEX RANGE SCAN | T2210_T | 6842 | | 108 (22)| 00:00:01 |
      ----------------------------------------------------------------------------------------

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

      3 - access("T2210"."C490008000"=:SYS_B_0 AND "T2210"."C490009100"=:SYS_B_2
      AND "T2210"."C301363300"=TO_NUMBER(:SYS_B_1))

      16 rows selected.


      SQL> explain plan for
      2 SELECT T2114.C1 FROM T2114 WHERE ((T2114.C1000000001 = :"SYS_B_0") AND (T2114.C536871442 = :"SYS_B_1") AND (T2114.C536871477 = :"SYS_B_2") AND ((:"SYS_B_3" - T2114.C3) >= :"SYS_B_4") AND ((:"SYS_B_5" - T2114.C3) <= :"SYS_B_6") AND (T2114.C1000000217 LIKE :"SYS_B_7") AND (T2114.C536871478 < :"SYS_B_8")) ORDER BY C1000000161 DESC, :"SYS_B_9" ASC;

      Explained.

      SQL> SELECT * FROM TABLE(dbms_xplan.display);

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 1497650422

      ----------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 6634 | 524K| 2993 (19)| 00:00:01 |
      | 1 | SORT ORDER BY | | 6634 | 524K| 2993 (19)| 00:00:01 |
      | 2 | TABLE ACCESS BY INDEX ROWID| T2210 | 6634 | 524K| 2947 (17)| 00:00:01 |
      |* 3 | INDEX RANGE SCAN | T2210_T | 6842 | | 108 (22)| 00:00:01 |
      ----------------------------------------------------------------------------------------

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

      3 - access("T2210"."C490008000"=:SYS_B_0 AND "T2210"."C490009100"=:SYS_B_2
      AND "T2210"."C301363300"=TO_NUMBER(:SYS_B_1))

      16 rows selected.
        • 1. Re: Explain Plan not showing correct results.
          sb92075
          Explain Plan not showing correct results.
          what do you think the correct plan should be & look like?
          • 2. Re: Explain Plan not showing correct results.
            Dom Brooks
            Double check how many objects called plan_table you have.
            SELECT *
            FROM   all_objects
            WHERE  object_name = 'PLAN_TABLE';
            In 11.2, you should have one public synonym which points to SYS.PLAN_TABLE$.
            You should no longer need any private copies etc which may have been created for legacy reasons in older versions.
            1 person found this helpful
            • 3. Re: Explain Plan not showing correct results.
              LANCERIQUE
              Hi Dom,

              Thanks for your immediate response.

              I am getting two values for PLAN_TABLE. Please let me know if we need to drop any of them??



              SQL> SELECT *
              FROM all_objects
              WHERE object_name = 'PLAN_TABLE'; 2 3

              OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
              ------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- ------------------ ------------------ ------------------- ------- - - - ---------- ------------------------------
              PUBLIC PLAN_TABLE 5127 SYNONYM 17-SEP-11 17-SEP-11 2011-09-17:09:47:47 VALID N N N 1
              ARADMIN PLAN_TABLE 219426 219426 TABLE 07-APR-13 07-APR-13 2013-04-07:23:01:12 VALID N N N 1
              • 4. Re: Explain Plan not showing correct results.
                In the future, please don't use * on all_objects.
                Use owner, object_type, object_name
                You avoid unreadable output by doing so.

                In the output you show, you would need to find out where the PUBLIC synonym is pointing to, by querying dba_synonyms or all_synonyms.
                If it SYS.PLAN_TABLE, you can drop ARADMIN.PLAN_TABLE.
                If there is no PLAN_TABLE in SYS, you need to fix this.

                -----------
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: Explain Plan not showing correct results.
                  LANCERIQUE
                  Thanks a lot Sybrand for quick response.

                  Thanks for the tip of not doing select *.

                  Regards,
                  Nikhil Mehta.