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  |
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 #603002 is a reply to message #602863] |
Tue, 10 December 2013 16:05  |
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 |
John Watson
Messages: 8960
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.
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 11:25:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
