5 Replies Latest reply: Feb 19, 2013 11:33 AM by Nikolay Savvinov RSS

    Help in explain plan - 11gr2

    IamHariKrishna
      11gr2, windows

      From a awr reports. the following sql is under 'SQL ordered by Elapsed Time'

      select * from rator.History where account_id =:"SYS_B_0" and create_date BETWEEN to_date(:"SYS_B_1", :"SYS_B_2")
      AND to_date(:"SYS_B_3", :"SYS_B_4")+:"SYS_B_5" ORDER BY create_date desc;

      I have an index on ID and create_date columns.

      when I tried to get plan by set auotrace traceonly, its asking for bind variable values. How do get plan for this sql and can change plan by providing hints.?



      Thanks
        • 1. Re: Help in explain plan - 11gr2
          vlethakula
          You can get values for those bind variables from
          v$sql_bind_capture
          • 2. Re: Help in explain plan - 11gr2
            rahulras
            In the "SQL ordered by elapsed time" table, there will be a column of "Executions", check the value there.
            If there were thousands of executions of this query in your snapshot window, are you still worried?

            Also, AWR report will give you the SQL_ID for that SQL. Try running query
            select * from TABLE( dbms_xplan.display_cursor('<sql id>') ) ;
            This might give you plan for the query, if it still exists in cache. If not, I am not sure how you will obtain the plan.
            • 3. Re: Help in explain plan - 11gr2
              IamHariKrishna
              rahulras wrote:
              In the "SQL ordered by elapsed time" table, there will be a column of "Executions", check the value there.
              If there were thousands of executions of this query in your snapshot window, are you still worried?

              Also, AWR report will give you the SQL_ID for that SQL. Try running query
              select * from TABLE( dbms_xplan.display_cursor('<sql id>') ) ;
              This might give you plan for the query, if it still exists in cache. If not, I am not sure how you will obtain the plan.
              >
              In the "SQL ordered by elapsed time" table, there will be a column of "Executions", check the value there.
              If there were thousands of executions of this query in your snapshot window, are you still worried?

              Also, AWR report will give you the SQL_ID for that SQL. Try running query
              select * from TABLE( dbms_xplan.display_cursor('<sql id>') ) ;
              This might give you plan for the query, if it still exists in cache. If not, I am not sure how you will obtain the plan. Looks like plan is cleared from cache.

              SQL> select * from TABLE( dbms_xplan.display_cursor('ampxc8ktagvsj') ) ;

              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------
              SQL_ID: ampxc8ktagvsj, child number: 0 cannot be found

              this sql id has 18,337     executions and also doing lot of physical IO.

              im able to get its plan but eventhough I passed the hints,i m getting same plan.
              -----------------------------------------------------------------------------------------------
              | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                   |          |     1 |   217 |    18  (23)| 00:00:01 |
              |   1 |  SORT ORDER BY                     |          |     1 |   217 |    18  (23)| 00:00:01 |
              |*  2 |   FILTER                           |          |       |       |            |          |
              |   3 |    TABLE ACCESS BY INDEX ROWID     | HISTORY  |     1 |   217 |    17  (18)| 00:00:01 |
              |   4 |     BITMAP CONVERSION TO ROWIDS    |          |       |       |            |          |
              |   5 |      BITMAP AND                    |          |       |       |            |          |
              |   6 |       BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
              |   7 |        SORT ORDER BY               |          |       |       |            |          |
              |*  8 |         INDEX RANGE SCAN           | HIST_ACC |   471 |       |     2   (0)| 00:00:01 |
              |   9 |       BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
              |  10 |        SORT ORDER BY               |          |       |       |            |          |
              |* 11 |         INDEX RANGE SCAN           | HIST#IDX |   471 |       |    13   (0)| 00:00:01 |
              -----------------------------------------------------------------------------------------------
              • 4. Re: Help in explain plan - 11gr2
                977635
                Thank you for your suggestion. That's awesome.
                Now when I look in OEM top activity, I can capture the sql statements and use real values for the variables to tune the statements in my test database.

                SQL> select SQL_ID, NAME, VALUE_STRING from v$sql_bind_capture where sql_id = 'ghmxz4jgq62ns'
                  2  /
                
                SQL_ID           NAME                 VALUE_STRING
                ---------------- -------------------- --------------------
                ghmxz4jgq62ns    :SYS_B_0             0
                ghmxz4jgq62ns    :SYS_B_1             1
                ghmxz4jgq62ns    :SYS_B_2             1361091456
                ghmxz4jgq62ns    :SYS_B_3             300
                ghmxz4jgq62ns    :SYS_B_4             1361091456
                ghmxz4jgq62ns    :SYS_B_5             1
                
                6 rows selected.
                • 5. Re: Help in explain plan - 11gr2
                  Nikolay Savvinov
                  Hi,

                  Use dbms_xplan.display_awr to produce the explain plan.

                  Best regards,
                  Nikolay