1 2 Previous Next 18 Replies Latest reply on Mar 6, 2013 7:52 PM by Rahul_India Go to original post
      • 15. Re: Tuning a query -First time
        Rahul_India
        Hi
        Jeenesh
        i am still getting the old explain plan after dropping and re-creating the plan_table.

        SomeOneElse
        how do i use the global plan_table.
        i dropped the plan_table in my schema .Then also i am getting the old explain plaIN
        I THINK

        But when i am executing
        select * from table(dbms_xplan.display);


        i am not getting any message like

        "PLAN_TABLE is old version”
        • 16. Re: Tuning a query -First time
          SomeoneElse
          how do i use the global plan_table.
          You don't have to do anything special. When you do an explain plan it will be used.

          But it sounds like you might have something else going on.
          select * from all_objects where object_name = 'PLAN_TABLE';
          There should be only 1 public synonym (which points to sys.plan_table$).

          Edited by: SomeoneElse on Mar 6, 2013 7:00 AM

          (huge sigh)
          • 17. Re: Tuning a query -First time
            APC
            Honestly SomeoneElse, if you won't use {noformat}
            {noformat} tags to format your code samples how can we expect the newbies to?
            
            Cheers, APC                                                                                                                                                                                                                                                                                                        
            • 18. Re: Tuning a query -First time
              Rahul_India
              I hope this is not the old plan. i am getting this result.Now can you tell how to improve the query ?
              thanks.
              DB--11gR2
              select * from table(sys.dbms_xplan.display)
              Plan hash value: 2892198326
               
              -------------------------------------------------------------------------------------------------------------
              | Id  | Operation                  | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
              -------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT           |                        |     1 |  2269 |       |  9253   (1)| 00:01:52 |
              |*  1 |  COUNT STOPKEY             |                        |       |       |       |            |          |
              |   2 |   VIEW                     |                        |  1168 |  2588K|       |  9253   (1)| 00:01:52 |
              |*  3 |    SORT ORDER BY STOPKEY   |                        |  1168 |   528K|       |  9253   (1)| 00:01:52 |
              |*  4 |     HASH JOIN SEMI         |                        |  1168 |   528K|       |  9252   (1)| 00:01:52 |
              |*  5 |      HASH JOIN             |                        |  2835 |  1267K|       |  5574   (1)| 00:01:07 |
              |*  6 |       TABLE ACCESS FULL    | TBL_ADDRESS            |  2835 |   645K|       |  2221   (1)| 00:00:27 |
              |*  7 |       TABLE ACCESS FULL    | TBL_ORGANIZATION       |   198K|    42M|       |  3352   (1)| 00:00:41 |
              |   8 |      VIEW                  | VW_NSO_1               |   103K|   503K|       |  3676   (1)| 00:00:45 |
              |*  9 |       HASH JOIN            |                        |   103K|  1812K|  2120K|  3676   (1)| 00:00:45 |
              |* 10 |        TABLE ACCESS FULL   | TBL_ORGANIZATION       | 86716 |  1100K|       |  3336   (1)| 00:00:41 |
              |  11 |        INDEX FAST FULL SCAN| TBL_SALESREP_ORG_INDEX |   160K|   782K|       |   105   (1)| 00:00:02 |
              -------------------------------------------------------------------------------------------------------------
               
              Predicate Information (identified by operation id):
              ---------------------------------------------------
               
                 1 - filter(ROWNUM<=1)
                 3 - filter(ROWNUM<=1)
                 4 - access("THIS_"."ORG_ID"="Y0_")
                 5 - access("THIS_"."ORG_ID"="ADDRESSALI1_"."ORG_ID")
                 6 - filter(LOWER("ADDRESSALI1_"."COUNTRY")=U'irn')
                 7 - filter("THIS_"."ORG_TYPE_ID"=53 AND ("THIS_"."LOGICAL_DELETE_INDICATOR"='N' OR 
                            "THIS_"."LOGICAL_DELETE_INDICATOR" IS NULL) AND ("THIS_"."OPCO_GEO_ID"=410000006 OR 
                            "THIS_"."OPCO_GEO_ID"=410000007 OR "THIS_"."OPCO_GEO_ID"=410000008 OR 
                            "THIS_"."OPCO_GEO_ID"=410000009 OR "THIS_"."OPCO_GEO_ID"=410000020 OR 
                            "THIS_"."OPCO_GEO_ID"=410000021))
                 9 - access("THIS_"."ORG_ID"="THIS_"."ORG_ID")
                10 - filter("THIS_"."ORG_TYPE_ID"=52)
              Edited by: Rahul India on Mar 7, 2013 1:20 AM
              1 2 Previous Next