Home » RDBMS Server » Performance Tuning » SQL with BIND Variable Slow - Bind Peeking (11.2)
SQL with BIND Variable Slow - Bind Peeking [message #602863] Mon, 09 December 2013 18:23 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear friends,

I have a SQL Query which executes slowly with Bind variable compared to actual values. Please find the query and explain plan and statistics below. Based on my research I was able to find the difference in the execution plan and resulting in more disk reads is due to BIND PEEKING.

But what is the best way to fix this problem?, I read a lot on articles on this issue but nothings i found really points to a solution. I tried flushing the shared pool to force a hard parse but that did not help. Any pointer or help is really appreciated. Thanks for your help in advance.


Query with actual value executed in a second :
SELECT TE_BEGIN_POS, TE_CATEGORY, TE_COMPONENT_OC_ID, TE_COMPONENT_STOP_POSITION, TE_COMP_ORDER_NUM_BEGIN, TE_COMP_ORDER_NUM_END, TE_ELEMENT_ORDER_NUMBER, TE_ELEMENT_SIZE, TE_END_POSITION, TE_FOOTER_BEGIN_POS, TE_FOOTER_END_POS, TE_ID, TE_INSTANCE_ON_ID, TE_OBJECT_NAME, TE_TAPE_TA_ID, TE_TEMPORARY_EXPORT, TA_BARCODE, OC_COMPONENT_ORDER_NUMBER FROM DP_TAPE_INSTN_CMPT_ELEMS

            INNER JOIN DP_OBJECT_INSTANCES ON TE_INSTANCE_ON_ID = ON_ID

            INNER JOIN DP_OBJECT_COMPONENTS ON TE_COMPONENT_OC_ID = OC_ID

            INNER JOIN DP_ARCHIVED_OBJECTS ON ON_OBJECT_AO_ID = AO_ID AND OC_OBJECT_AO_ID = AO_ID

            INNER JOIN DP_TAPES ON TE_TAPE_TA_ID = TA_ID

            WHERE (AO_OBJECT_NAME = '060a2b340101010101010f0013-000000-4c5a777c33d40610-060e2b347f7f-2a80' AND AO_CATEGORY LIKE 'AVID%' AND ON_INSTANCE_ORDER_NUMBER = 2)

            ORDER BY OC_COMPONENT_ORDER_NUMBER ASC, TE_ELEMENT_ORDER_NUMBER ASC;



8 rows selected.

 

[b]Elapsed: 00:00:01.11[/b]

 

Execution Plan

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

Plan hash value: 4204792406

 

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

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

 

| Id  | Operation                         | Name                       | Rows  |

Bytes | Cost (%CPU)| Time     |

 

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

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

 

|   0 | SELECT STATEMENT                  |                            |     1 |

   266 |    17   (6)| 00:00:01 |

 

|   1 |  SORT ORDER BY                    |                            |     1 |

   266 |    17   (6)| 00:00:01 |

 

|   2 |   NESTED LOOPS                    |                            |       |

       |            |          |

 

|   3 |    NESTED LOOPS                   |                            |     1 |

   266 |    16   (0)| 00:00:01 |

 

|   4 |     NESTED LOOPS                  |                            |     1 |

   255 |    15   (0)| 00:00:01 |

 

|   5 |      NESTED LOOPS                 |                            |     3 |

   717 |     9   (0)| 00:00:01 |

 

|   6 |       NESTED LOOPS                |                            |     1 |

   102 |     6   (0)| 00:00:01 |

 

|   7 |        TABLE ACCESS BY INDEX ROWID| DP_ARCHIVED_OBJECTS        |     1 |

    87 |     4   (0)| 00:00:01 |

 

[b]|*  8 |         INDEX UNIQUE SCAN         | DP_AO_OBJECT_NAME_CATG_UK  |     1 |

       |     3   (0)| 00:00:01 |[/b]

 

|   9 |        TABLE ACCESS BY INDEX ROWID| DP_OBJECT_INSTANCES        |  1317K|

    18M|     2   (0)| 00:00:01 |

 

|* 10 |         INDEX UNIQUE SCAN         | DP_ON_OB_AO_ID_IN_OR_NB_UK |     1 |

       |     1   (0)| 00:00:01 |

 

|  11 |       TABLE ACCESS BY INDEX ROWID | DP_TAPE_INSTN_CMPT_ELEMS   |     3 |

   411 |     3   (0)| 00:00:01 |

 

|* 12 |        INDEX RANGE SCAN           | DP_TE_INSTANCE_ON_ID_IDX   |     3 |

       |     2   (0)| 00:00:01 |

 

|* 13 |      TABLE ACCESS BY INDEX ROWID  | DP_OBJECT_COMPONENTS       |     1 |

    16 |     2   (0)| 00:00:01 |

 

|* 14 |       INDEX UNIQUE SCAN           | DP_OC_ID_PK                |     1 |

       |     1   (0)| 00:00:01 |

 

|* 15 |     INDEX UNIQUE SCAN             | DP_TA_ID_PK                |     1 |

       |     0   (0)| 00:00:01 |

 

|  16 |    TABLE ACCESS BY INDEX ROWID    | DP_TAPES                   |     1 |

    11 |     1   (0)| 00:00:01 |

 

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

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

 

 

Predicate Information (identified by operation id):

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

 

   8 - access("DP_ARCHIVED_OBJECTS"."AO_OBJECT_NAME"='060a2b340101010101010f0013

-000000-4c5a777c33d40610

 

              -060e2b347f7f-2a80' AND "DP_ARCHIVED_OBJECTS"."AO_CATEGORY"='AVID'

)

 

  10 - access("DP_OBJECT_INSTANCES"."ON_OBJECT_AO_ID"="AO_ID" AND

              "DP_OBJECT_INSTANCES"."ON_INSTANCE_ORDER_NUMBER"=2)

  12 - access("TE_INSTANCE_ON_ID"="ON_ID")

  13 - filter("DP_OBJECT_COMPONENTS"."OC_OBJECT_AO_ID"="AO_ID")

  14 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_COMPONENT_OC_ID"="OC_ID")

  15 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_TAPE_TA_ID"="TA_ID")

 

 

Statistics

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

         71  recursive calls

          0  db block gets

[b]         77  consistent gets

         60  physical reads[/b]

          0  redo size

       2479  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          8  rows processed


Same Query with Bind Variable executed in 11 seconds :

          variable AO_OBJECT_NAME VARCHAR2(100);
            variable AO_CATEGORY VARCHAR2(100);

            variable ON_INSTANCE_ORDER_NUMBER NUMBER;

            exec :AO_OBJECT_NAME := '060a2b340101010101010f0013-000000-4c5a777c33d40610-060e2b347f7f-2a80'

            exec :AO_CATEGORY := 'AVID%';

            exec :ON_INSTANCE_ORDER_NUMBER :=2;

            set timing on

            SELECT   TE_BEGIN_POS, TE_CATEGORY, TE_COMPONENT_OC_ID, TE_COMPONENT_STOP_POSITION, TE_COMP_ORDER_NUM_BEGIN, TE_COMP_ORDER_NUM_END, TE_ELEMENT_ORDER_NUMBER, TE_ELEMENT_SIZE, TE_END_POSITION, TE_FOOTER_BEGIN_POS, TE_FOOTER_END_POS, TE_ID, TE_INSTANCE_ON_ID, TE_OBJECT_NAME, TE_TAPE_TA_ID, TE_TEMPORARY_EXPORT, TA_BARCODE, OC_COMPONENT_ORDER_NUMBER FROM DP_TAPE_INSTN_CMPT_ELEMS

            INNER JOIN DP_OBJECT_INSTANCES ON TE_INSTANCE_ON_ID = ON_ID

            INNER JOIN DP_OBJECT_COMPONENTS ON TE_COMPONENT_OC_ID = OC_ID

            INNER JOIN DP_ARCHIVED_OBJECTS ON ON_OBJECT_AO_ID = AO_ID AND OC_OBJECT_AO_ID = AO_ID

            INNER JOIN DP_TAPES ON TE_TAPE_TA_ID = TA_ID

            WHERE (AO_OBJECT_NAME = :AO_OBJECT_NAME AND AO_CATEGORY LIKE :AO_CATEGORY AND ON_INSTANCE_ORDER_NUMBER = :ON_INSTANCE_ORDER_NUMBER)

            ORDER BY OC_COMPONENT_ORDER_NUMBER ASC, TE_ELEMENT_ORDER_NUMBER ASC;

 

 

8 rows selected.

 

[b]Elapsed: 00:00:11.19[/b]

 

Execution Plan

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

Plan hash value: 107845955

 

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

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

 

| Id  | Operation                         | Name                       | Rows  |

Bytes | Cost (%CPU)| Time     |

 

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

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

 

|   0 | SELECT STATEMENT                  |                            |     1 |

   266 |  7770   (1)| 00:01:34 |

 

|   1 |  SORT ORDER BY                    |                            |     1 |

   266 |  7770   (1)| 00:01:34 |

 

|   2 |   NESTED LOOPS                    |                            |       |

       |            |          |

 

|   3 |    NESTED LOOPS                   |                            |     1 |

   266 |  7769   (1)| 00:01:34 |

 

|   4 |     NESTED LOOPS                  |                            |     1 |

   255 |  7768   (1)| 00:01:34 |

 

|   5 |      NESTED LOOPS                 |                            |  2022 |

   471K|  3728   (1)| 00:00:45 |

 

|   6 |       NESTED LOOPS                |                            |   717 |

73134 |  1582   (1)| 00:00:19 |

 

|   7 |        TABLE ACCESS BY INDEX ROWID| DP_ARCHIVED_OBJECTS        |   716 |

62292 |   149   (0)| 00:00:02 |

 

[b]|*  8 |         INDEX RANGE SCAN          | DP_AO_OBJECT_NAME_CATG_UK  |   172 |

       |     6   (0)| 00:00:01 |
[/b]
 

|   9 |        TABLE ACCESS BY INDEX ROWID| DP_OBJECT_INSTANCES        |     1 |

    15 |     2   (0)| 00:00:01 |

 

|* 10 |         INDEX UNIQUE SCAN         | DP_ON_OB_AO_ID_IN_OR_NB_UK |     1 |

       |     1   (0)| 00:00:01 |

 

|  11 |       TABLE ACCESS BY INDEX ROWID | DP_TAPE_INSTN_CMPT_ELEMS   |     3 |

   411 |     3   (0)| 00:00:01 |

 

|* 12 |        INDEX RANGE SCAN           | DP_TE_INSTANCE_ON_ID_IDX   |     3 |

       |     2   (0)| 00:00:01 |

 

|* 13 |      TABLE ACCESS BY INDEX ROWID  | DP_OBJECT_COMPONENTS       |     1 |

    16 |     2   (0)| 00:00:01 |

 

|* 14 |       INDEX UNIQUE SCAN           | DP_OC_ID_PK                |     1 |

       |     1   (0)| 00:00:01 |

 

|* 15 |     INDEX UNIQUE SCAN             | DP_TA_ID_PK                |     1 |

       |     0   (0)| 00:00:01 |

 

|  16 |    TABLE ACCESS BY INDEX ROWID    | DP_TAPES                   |     1 |

    11 |     1   (0)| 00:00:01 |

 

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

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

 

 

Predicate Information (identified by operation id):

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

 

   8 - access("DP_ARCHIVED_OBJECTS"."AO_OBJECT_NAME"=:AO_OBJECT_NAME AND

              "DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)

       filter("DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)

  10 - access("DP_OBJECT_INSTANCES"."ON_OBJECT_AO_ID"="AO_ID" AND

              "DP_OBJECT_INSTANCES"."ON_INSTANCE_ORDER_NUMBER"=TO_NUMBER(:ON_INS

TANCE_ORDER_NUMBER))

 

  12 - access("TE_INSTANCE_ON_ID"="ON_ID")

  13 - filter("DP_OBJECT_COMPONENTS"."OC_OBJECT_AO_ID"="AO_ID")

  14 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_COMPONENT_OC_ID"="OC_ID")

  15 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_TAPE_TA_ID"="TA_ID")

 

 

Statistics

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

         71  recursive calls

          0  db block gets

[b]     439423  consistent gets

     176879  physical reads[/b]

          0  redo size

       2479  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          8  rows process


Thanks for your help in advance.

Regards,

Prakash R
Re: SQL with BIND Variable Slow - Bind Peeking [message #602881 is a reply to message #602863] Tue, 10 December 2013 02:08 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
Can you please do some formatting?
To me this layout this hardly readable
Re: SQL with BIND Variable Slow - Bind Peeking [message #603002 is a reply to message #602863] Tue, 10 December 2013 16:05 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Based on my investigation it id due to histograms specifically to DP_ARCHIVED_OBJECTS table in the query.

Here is how I calculate statistics
execute dbms_stats.gather_table_stats(''$USER'','''||TABLE_NAME||''',DEGREE=>4,CASCADE=>TRUE); 

SQL> select dbms_stats.get_param('method_opt') method_opt from dual;

METHOD_OPT
----------------------------------------------------
FOR ALL COLUMNS SIZE AUTO


Histograms for table DP_ARCHIVED_OBJECTS for column AO_OBJECT_NAME, AO_CATEGORY which has a unique index on it.
COLUMN_NAME          	          ENDPOINT_NUMBER	                      TO_CHAR(ENDPOINT_VALUE)
AO_OBJECT_NAME	242	250329332361026000000000000000000000.00
AO_OBJECT_NAME	254	250329332361026000000000000000000000.00
AO_OBJECT_NAME	238	250329332361026000000000000000000000.00
AO_CATEGORY	3,686	339249387884079000000000000000000000.00
AO_CATEGORY	3,690	339249387884079000000000000000000000.00
AO_CATEGORY	3,698	339249387884084000000000000000000000.00
AO_CATEGORY	3,700	339249387884155000000000000000000000.00
AO_CATEGORY	4,949	339249387884169000000000000000000000.00
AO_CATEGORY	4,952	339249387884169000000000000000000000.00
AO_CATEGORY	4,960	339249387884169000000000000000000000.00
AO_CATEGORY	4,964	339249387884169000000000000000000000.00
AO_CATEGORY	4,968	339249387884169000000000000000000000.00
AO_CATEGORY	4,971	339249387884170000000000000000000000.00
AO_CATEGORY	5,047	339249387884170000000000000000000000.00
AO_CATEGORY	5,159	339249387884170000000000000000000000.00
AO_CATEGORY	5,161	339249387884170000000000000000000000.00
AO_CATEGORY	5,174	339249387884188000000000000000000000.00
AO_CATEGORY	5,213	339249387884212000000000000000000000.00
AO_CATEGORY	5,264	339249387884264000000000000000000000.00
AO_CATEGORY	5,266	339249387884264000000000000000000000.00
AO_CATEGORY	5,267	339249387884264000000000000000000000.00
AO_CATEGORY	5,279	339249387884264000000000000000000000.00
AO_CATEGORY	5,281	339249387884264000000000000000000000.00
AO_CATEGORY	5,466	339249387884264000000000000000000000.00
AO_CATEGORY	5,471	655614825050678000000000000000000000.00
AO_CATEGORY	3,684	339249387731522000000000000000000000.00

SQL PLan with Histograms results in more disk reads eve thought those block are avialble in cache resulting in query slowness.

8 rows selected.

Elapsed: 00:00:30.11

Execution Plan
----------------------------------------------------------
Plan hash value: 107845955

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |     1 |   266 | 10371   (1)| 00:02:05 |
|   1 |  SORT ORDER BY                    |                            |     1 |   266 | 10371   (1)| 00:02:05 |
|   2 |   NESTED LOOPS                    |                            |       |       |            |          |
|   3 |    NESTED LOOPS                   |                            |     1 |   266 | 10370   (1)| 00:02:05 |
|   4 |     NESTED LOOPS                  |                            |     1 |   255 | 10369   (1)| 00:02:05 |
|   5 |      NESTED LOOPS                 |                            |  2702 |   630K|  4971   (1)| 00:01:00 |
|   6 |       NESTED LOOPS                |                            |   956 | 97512 |  2102   (1)| 00:00:26 |
|   7 |        TABLE ACCESS BY INDEX ROWID| DP_ARCHIVED_OBJECTS        |   956 | 83172 |   189   (0)| 00:00:03 |
|*  8 |         INDEX RANGE SCAN          | DP_AO_OBJECT_NAME_CATG_UK  |   212 |       |     6   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| DP_OBJECT_INSTANCES        |     1 |    15 |     2   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN         | DP_ON_OB_AO_ID_IN_OR_NB_UK |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | DP_TAPE_INSTN_CMPT_ELEMS   |     3 |   411 |     3   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN           | DP_TE_INSTANCE_ON_ID_IDX   |     3 |       |     2   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID  | DP_OBJECT_COMPONENTS       |     1 |    16 |     2   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | DP_OC_ID_PK                |     1 |       |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN             | DP_TA_ID_PK                |     1 |       |     0   (0)| 00:00:01 |
|  16 |    TABLE ACCESS BY INDEX ROWID    | DP_TAPES                   |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   8 - access("DP_ARCHIVED_OBJECTS"."AO_OBJECT_NAME"=:AO_OBJECT_NAME AND
              "DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)
       filter("DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)
  10 - access("DP_OBJECT_INSTANCES"."ON_OBJECT_AO_ID"="AO_ID" AND
              "DP_OBJECT_INSTANCES"."ON_INSTANCE_ORDER_NUMBER"=TO_NUMBER(:ON_INSTANCE_ORDER_NUMBER))
  12 - access("TE_INSTANCE_ON_ID"="ON_ID")
  13 - filter("DP_OBJECT_COMPONENTS"."OC_OBJECT_AO_ID"="AO_ID")
  14 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_COMPONENT_OC_ID"="OC_ID")
  15 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_TAPE_TA_ID"="TA_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     440410  consistent gets
     440344  physical reads
          0  redo size
       2067  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

I cleanup the histogram with the following

exec DBMS_STATS.DELETE_COLUMN_STATS('TELEVISA_7_1_1_AFTER_UPGRADE', 'DP_ARCHIVED_OBJECTS','AO_OBJECT_NAME');
exec DBMS_STATS.DELETE_COLUMN_STATS('TELEVISA_7_1_1_AFTER_UPGRADE', 'DP_ARCHIVED_OBJECTS','AO_CATEGORY');



and removed the SQL cursor from shared pool using

exec DBMS_SHARED_POOL.PURGE ('00000002F1F68D20, 3023944587', 'C');


SQL Plan after cleaning histogram and removing SQL from shared pool, as you can see it uses the same explain plan but did not do the physical disk reads and executed in less than a second. I am sure this is not because the first execution cause it to cache it and that's why its not doing the disk reads again because It always does the disk read if the SQL was hard parsed with histograms and any subsequent calls to the sql due to bind peeking.

8 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 107845955

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |     1 |   333 | 13011   (1)| 00:02:37 |
|   1 |  SORT ORDER BY                    |                            |     1 |   333 | 13011   (1)| 00:02:37 |
|   2 |   NESTED LOOPS                    |                            |       |       |            |          |
|   3 |    NESTED LOOPS                   |                            |     1 |   333 | 13010   (1)| 00:02:37 |
|   4 |     NESTED LOOPS                  |                            |     1 |   322 | 13009   (1)| 00:02:37 |
|   5 |      NESTED LOOPS                 |                            |  3432 |  1025K|  6152   (1)| 00:01:14 |
|   6 |       NESTED LOOPS                |                            |  1214 |   200K|  2509   (1)| 00:00:31 |
|   7 |        TABLE ACCESS BY INDEX ROWID| DP_ARCHIVED_OBJECTS        |  1214 |   182K|    80   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | DP_AO_OBJECT_NAME_CATG_UK  |    87 |       |     5   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| DP_OBJECT_INSTANCES        |     1 |    15 |     2   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN         | DP_ON_OB_AO_ID_IN_OR_NB_UK |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | DP_TAPE_INSTN_CMPT_ELEMS   |     3 |   411 |     3   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN           | DP_TE_INSTANCE_ON_ID_IDX   |     3 |       |     2   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID  | DP_OBJECT_COMPONENTS       |     1 |    16 |     2   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | DP_OC_ID_PK                |     1 |       |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN             | DP_TA_ID_PK                |     1 |       |     0   (0)| 00:00:01 |
|  16 |    TABLE ACCESS BY INDEX ROWID    | DP_TAPES                   |     1 |    11 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   8 - access("DP_ARCHIVED_OBJECTS"."AO_OBJECT_NAME"=:AO_OBJECT_NAME AND
              "DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)
       filter("DP_ARCHIVED_OBJECTS"."AO_CATEGORY" LIKE :AO_CATEGORY)
  10 - access("DP_OBJECT_INSTANCES"."ON_OBJECT_AO_ID"="AO_ID" AND
              "DP_OBJECT_INSTANCES"."ON_INSTANCE_ORDER_NUMBER"=TO_NUMBER(:ON_INSTANCE_ORDER_NUMBER))
  12 - access("TE_INSTANCE_ON_ID"="ON_ID")
  13 - filter("DP_OBJECT_COMPONENTS"."OC_OBJECT_AO_ID"="AO_ID")
  14 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_COMPONENT_OC_ID"="OC_ID")
  15 - access("DP_TAPE_INSTN_CMPT_ELEMS"."TE_TAPE_TA_ID"="TA_ID")


Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
         79  consistent gets
          0  physical reads
          0  redo size
       2067  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed


So basically histograms are throwing off amount of disk reads even if the blocks are available in the cache. Can anyone please help me understand this behavior with histograms and how it affects number of disk reads. Thanks for your help and really appreciate it.

Regards,
Prakash R
Re: SQL with BIND Variable Slow - Bind Peeking [message #603026 is a reply to message #603002] Wed, 11 December 2013 01:10 Go to previous message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
I think you are using the AUTOTRACE facility? What that does is run the statement, and then run EXPLAIN PLAN on the statement. EXPLAIN PLAN does not expand bind variables. This means that the plan it generates is not necessarily the plan that was used. In your case, it is possible that the statement ran with different plans. To determine this, you need to determine the actual plans used. You could do this by enabling sql_trace and then using tkprof (be sure to use aggregate=no) or by using dbms_xplan.display_cursor to extract the plans(s) from the library cache.
Previous Topic: How to correct completely wrong cardinality shown in query plan
Next Topic: look like same query, but huge query plan different.
Goto Forum:
  


Current Time: Mon Oct 20 21:32:42 CDT 2014

Total time taken to generate the page: 0.08834 seconds