Excellent...
Kamal Kishore, June 09, 2003 - 9:32 am UTC
Hi Tom,
That discussion in the link was excellent. Explained the concept in a concise and clear manner.
The question that now pops up in my mind is:
Will Oracle9i do this "bind variable peeking" ALWAYS first time for a hard parse or will it do it only when it sees that the data might be skewed?
If it does it always, will it pose a problem for our queries when we migrate to Oracle9i (soon)?
The concern that I have is if the first hard parse decides the execution plan and it is cached, the subsequent executions for other data values may be terrible?
Is this a problem that we need to address?
If yes, what we could (or rather should) do to get around this issue?
Thanks,
June 09, 2003 - 10:37 am UTC
it happens with the CBO during a hard parse.
As for the 9i migration -- you have that right now, without bind variable peeking. Consider the one example proposed in the link with "like". without bind variable peeking, it would never consider the index see, with it, it will. It goes both ways.
But -- test test test -- that is something you need to do upon an upgrade....
What if compatible = 8.1.7 is set for the Oracle9i development server...
Kamal Kishore, June 09, 2003 - 10:51 am UTC
Hi Tom,
I just checked and found out that the development server that was migrated to Oracle9i some time ago (in the process to migrate the production, some day), has the compatible parameter set to 8.1.7.
I'm assuming that this bind variable peeking will not be available when this parameter is set to this value?
Is this correct?
Do you see any problems with this setup? Do you suggest that the compatible parameter should ideally be set to something like 9.0 or 9.2?
What would be your choice in this case?
The argument is that since production is still 8.1.7, we should set the development server to compatible = 8.1.7 so as to avoid any problems during migration.
Is this the correct way to approach a migration from Oracle8i to Oracle9i?
Any suggestions? Are we going in the right direction?
Thanks,
June 09, 2003 - 12:21 pm UTC
compatible only affects "disk based structures" really -- it makes it so that if we come up with some new format for persisting data -- we won't use it (cause you cannot downgrade if we did).
features like bind variable peeking will work.
you should have the same setting on dev/test/prod. Next upgrade cycle -- bump it up.
Does bv peeking affect Card estimates?
Dr. Donald K. Demetrious, July 28, 2003 - 3:17 pm UTC
Shouldn't bind variable peeking affect the Card estimates on the execution plan? In the following example, why is Card=1526 instead of Card=5?
From Oracle 9.2, with optimizer_features_enable set to 9.2.0:
SQL> create table test_cols as select owner, table_name, column_name from dba_tab_columns;
Table created.
SQL> create index test_cols_n01 on test_cols (column_name);
Index created.
SQL> analyze table test_cols compute statistics;
Table analyzed.
SQL> set autotrace traceonly explain
SQL> select count(*) from test_cols where column_name like 'CHA%';
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
SORT (AGGREGATE)
INDEX (RANGE SCAN) OF 'TEST_COLS_N01' (NON-UNIQUE) (Cost=2 Card=5 Bytes=50)
SQL> variable p_test varchar2(30);
SQL> begin :p_test := 'CHA%'; end;
2 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test_cols where column_name like :p_test;
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
SORT (AGGREGATE)
INDEX (RANGE SCAN) OF 'TEST_COLS_N01' (NON-UNIQUE) (Cost=2 Card=1526 Bytes=15260)
July 28, 2003 - 7:56 pm UTC
explain plan can lie, especially with bind variable peeking.
so, this is a little view I explain in my next book due out next month -- it is useful in 9i especially 9ir2 with dbms_xplan:
ops$tkyte@ORA920> create or replace view dynamic_plan_table
2 as
3 select
4 rawtohex(address) || '_' || child_number statement_id,
5 sysdate timestamp, operation, options, object_node,
6 object_owner, object_name, 0 object_instance,
7 optimizer, search_columns, id, parent_id, position,
8 cost, cardinality, bytes, other_tag, partition_start,
9 partition_stop, partition_id, other, distribution,
10 cpu_cost, io_cost, temp_space, access_predicates,
11 filter_predicates
12 from v$sql_plan;
View created.
<b>now, we'll run your simulation:</b>
ops$tkyte@ORA920> drop table t;
Table dropped.
ops$tkyte@ORA920> create table t as select owner, table_name, column_name from dba_tab_columns;
Table created.
ops$tkyte@ORA920> create index t_idx on t(column_name);
Index created.
ops$tkyte@ORA920> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA920> set autotrace on explain
ops$tkyte@ORA920> select count(*) from t where column_name like 'CHA%';
COUNT(*)
----------
215
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=10 Card=2624 Bytes=26240)
<b>so the estimated cardinality in the plan was 2,624 -- but using a bind:</b>
ops$tkyte@ORA920> variable p_test varchar2(30)
ops$tkyte@ORA920> exec :p_test := 'CHA%'
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select /* LOOK FOR ME!!! */ count(*) from t where column_name like :p_test;
COUNT(*)
----------
215
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1742 Bytes=17420)
<b>it appears to be 1,742, but looking at the REAL plan used:</b>
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920>
ops$tkyte@ORA920> select plan_table_output
2 from TABLE( dbms_xplan.display
3 ( 'dynamic_plan_table',
4 (select rawtohex(address)||'_'||child_number x
5 from v$sql
6 where sql_text like 'select /* LOOK FOR ME!!! */ count(*) from t where column_name like :p_test%' ),
7 'serial' ) )
8 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | 1 | 10 | |
|* 2 | INDEX RANGE SCAN | T_IDX | 2624 | 26240 | 10 (10)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COLUMN_NAME" LIKE :P_TEST)
filter("COLUMN_NAME" LIKE :P_TEST)
14 rows selected.
<b>we see the 2,624 again. explain plan doesn't "see" the peeked binds</b>
bind variable
Pushparaj Arulappan, October 16, 2003 - 10:34 am UTC
Tom,
I have a SQL which is running very fast from the SQL PROMPT
but if I put the same SQL in a PL/SQL block, it is running
very very slow.
SQL> var var1 char(10);
SQL> var var2 char(7);
SQL> exec :var1 := '1234567890';
SQL> exec :var2 := '1234567';
SQL> SELECT ....
....
FROM tab1 a,
tab2 b
( SELECT ...
FROM tab1 x,
tab3 y
WHERE x.col1 = :var1
AND x.col2 = :var2
AND x.col1 = y.col1
AND x.col2 = y.col2
....
) zz
WHERE a.col1 = :var1
AND a.col2 = :var2
AND a.col1 = b.col1
AND a.col2 = b.col2
AND zz.col1 = a.col1
AND zz.col2 = a.col2
.....
This executes very fast...
But If I convert this into a PL/SQL program..
SQL>
DECLARE
var1 CHAR(10);
var2 CHAR(7);
CURSOR C1 IS
SELECT ....
....
FROM tab1 a,
tab2 b
(SELECT ...
FROM tab1 x,
tab3 y
WHERE x.col1 = var1
AND x.col2 = var2
AND x.col1 = y.col1
AND x.col2 = y.col2
....
) zz
WHERE a.col1 = var1
AND a.col2 = var2
AND a.col1 = b.col1
AND a.col2 = b.col2
AND zz.col1 = a.col1
AND zz.col2 = a.col2
.....
BEGIN
var1 := '1234567890';
var2 := '12345678';
FOR rec IN c1 LOOP
....
END LOOP;
END;
/
This is running for ever and ever and I have to kill it..
I also want to tell you that the same works fine in SQL prompt and also in PL/SQL if the WHERE clause value is different which will match less rows and retrieve very less rows.
This is only a concern for the WHERE class values
which will join many rows and retrieve many rows.
What I should do to this SQL to perform the same way
in a PL/SQL block as it does in the SQL prompt for the WHERE
clause values that will match and retrieve many rows.
Thanks
Pushparaj
October 16, 2003 - 12:30 pm UTC
where be the tkprof reports that would show us the different in execution performance and query plans used?
trace out
Pushparaj Arulappan, October 16, 2003 - 2:05 pm UTC
Tom,
The following is the trace output when I run it from the
SQL prompt. The SELECT columns are more, hence I only
put the FROM and WHERE clause of the original query here.
FROM TTSTCPK n,TTKLMTA x
WHERE x.nbn_num= :pNbnNum
and x.sgp_num= :pSgpNum
and n.nbn_num=x.nbn_num
and n.sgp_num=x.sgp_num
and n.pspl_num=x.pspl_num
and n.psps_num=x.psps_num
GROUP BY x.nbn_num,
x.sgp_num,
x.pspl_num,
x.psps_num) t
WHERE a.nbn_num = :pNbnNum
and a.sgp_num = :pSgpNum
and a.nbn_num = b.nbn_num
and a.sgp_num = b.sgp_num
and a.pspl_num = b.pspl_num
and b.nbn_num = t.nbn_num
and b.sgp_num = t.sgp_num
and b.pspl_num = t.pspl_num
and b.psps_num = t.psps_num
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 111 18.68 18.27 0 227237 0 1643
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 113 18.68 18.27 0 227237 0 1643
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 28 (SALES1)
Rows Row Source Operation
------- ---------------------------------------------------
1643 SORT GROUP BY (cr=227237 r=0 w=0 time=18259319 us)
4929 NESTED LOOPS (cr=227237 r=0 w=0 time=18152455 us)
4929 NESTED LOOPS (cr=123726 r=0 w=0 time=11201943 us)
4929 NESTED LOOPS (cr=108937 r=0 w=0 time=11096958 us)
4929 TABLE ACCESS BY INDEX ROWID TTKLMTA (cr=497 r=0 w=0 time=22826 us)
4929 INDEX RANGE SCAN IND_TTKLMTA (cr=60 r=0 w=0 time=5600 us)(object id 391566)
4929 TABLE ACCESS BY INDEX ROWID TTSTCPK (cr=108440 r=0 w=0 time=11051602 us)
4929 INDEX RANGE SCAN IND_TTSTCPK (cr=103511 r=0 w=0 time=11016616 us)(object id 391563)
4929 TABLE ACCESS BY INDEX ROWID TTCOLFP (cr=14789 r=0 w=0 time=84542 us)
4929 INDEX RANGE SCAN IND_TTCOLFP (cr=9860 r=0 w=0 time=53351 us)(object id 195300)
4929 INDEX RANGE SCAN IND_TTSTCPK (cr=103511 r=0 w=0 time=6933006 us)(object id 391563)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1643 SORT (GROUP BY)
4929 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TTSTCPK'
4929 NESTED LOOPS
4929 MERGE JOIN (CARTESIAN)
4929 NESTED LOOPS
4929 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTKLMTA'
4929 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTKLMTA'
(UNIQUE)
4929 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK'
(UNIQUE)
4929 BUFFER (SORT)
4929 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTCOLFP'
4929 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTCOLFP'
(UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK' (UNIQUE)
********************************************************************************
But Tom, the PL/SQL program is still running since morning and it is not complete yet and I will provide whatever so far in the trace file and you can see the change in the execution plan already.
FROM TTSTCPK n,TTKLMTA x
WHERE x.nbn_num= :b2
and x.sgp_num= :b1
and n.nbn_num=x.nbn_num
and n.sgp_num=x.sgp_num
and n.pspl_num=x.pspl_num
and n.psps_num=x.psps_num
GROUP BY x.nbn_num,
x.sgp_num,
x.pspl_num,
x.psps_num) t
WHERE a.nbn_num = :b2
and a.sgp_num = :b1
and a.nbn_num = b.nbn_num
and a.sgp_num = b.sgp_num
and a.pspl_num = b.pspl_num
and b.nbn_num = t.nbn_num
and b.sgp_num = t.sgp_num
and b.pspl_num = t.pspl_num
and b.psps_num = t.psps_num
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 28 (SALES1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TTKLMTA'
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTCOLFP'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTCOLFP'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTSTCPK'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK'
(UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK'
(UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTKLMTA' (UNIQUE)
********************************************************************************
Now, I ran the same PL/SQL program from another session
but with the different WHERE clause value which returns
joins very less rows and returns very less rows and the following is the trace output for that.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 109 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 109 0 5
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 28 (SALES1) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT GROUP BY (cr=109 r=0 w=0 time=1606 us)
15 FILTER (cr=109 r=0 w=0 time=1205 us)
15 TABLE ACCESS BY INDEX ROWID TTKLMTA (cr=109 r=0 w=0 time=1174 us)
41 NESTED LOOPS (cr=107 r=0 w=0 time=1090 us)
25 NESTED LOOPS (cr=30 r=0 w=0 time=430 us)
5 MERGE JOIN CARTESIAN (cr=8 r=0 w=0 time=219 us)
1 TABLE ACCESS BY INDEX ROWID TTCOLFP (cr=4 r=0 w=0 time=73 us)
1 INDEX RANGE SCAN IND_TTCOLFP (cr=3 r=0 w=0 time=47 us)(object id 195300)
5 BUFFER SORT (cr=4 r=0 w=0 time=106 us)
5 INDEX RANGE SCAN IND_TTSTCPK (cr=4 r=0 w=0 time=47 us)(object id 391563)
25 TABLE ACCESS BY INDEX ROWID TTSTCPK (cr=22 r=0 w=0 time=176 us)
25 INDEX RANGE SCAN IND_TTSTCPK (cr=17 r=0 w=0 time=96 us)(object id 391563)
15 INDEX RANGE SCAN IND_TTKLMTA (cr=77 r=0 w=0 time=546 us)(object id 391566)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
5 SORT (GROUP BY)
15 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TTKLMTA'
15 NESTED LOOPS
41 NESTED LOOPS
25 NESTED LOOPS
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTCOLFP'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTCOLFP'
(UNIQUE)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TTSTCPK'
5 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK'
(UNIQUE)
5 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTSTCPK'
(UNIQUE)
25 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IND_TTKLMTA' (UNIQUE)
October 16, 2003 - 5:29 pm UTC
what do you mean "the select columns are more"
more
A reader, October 16, 2003 - 8:52 pm UTC
Tom,
The list of columns in the SELECT is more and hence
instead of pasting the entire query as it will look very large query, I just avoided that portion of the query here.
Thanks
Pushparaj
October 16, 2003 - 9:00 pm UTC
so, the select lists were IDENTICAL right?
SELECT LIST
Pushparaj Arulappan, October 17, 2003 - 8:44 am UTC
Tom,
The SELECT list is exactly the same and
absoulyely no difference.
Thanks
Pushparaj
October 17, 2003 - 11:03 am UTC
what command exactly do you use to analyze all of the tables and are the statistics all up to date?
Pushparaj Arulappan, October 17, 2003 - 4:18 pm UTC
Tom,
We use COMPUTE STATISTICS to collect the staticts
for all our tables and indexes.
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name in ('TTSTCPK','TTKLMTA','TTCOLFP');
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ -------- -----------
TTCOLFP 4250390 2003-10-11.08.00.36
TTSTCPK 3807971 2003-10-12.16.13.05
TTKLMTA 1423913 2003-10-12.15.46.45
I have checked the indexes also and they all have statistics.
The above three are the tables joined in this query...
The following are the row counts for the bind variable
values used in the WHERE cluase and which is taking very long time.
TTCOLFP = 1
TTSTCPK = 1643
TTKLMTA = 9381
The following are the row counts for the bind variable
values used in the WHERE cluase and which executes in no
time.
TTCOLFP = 1
TTSTCPK = 5
TTKLMTA = 15
The query goes faster for fewer counts but for the large counts it takes hours.
Thanks
Pushparaj
October 19, 2003 - 5:46 pm UTC
*exact* command please. there are hundreds of possible ways to compute statistics.
analyze
Pushparaj Arulappan, October 20, 2003 - 10:04 am UTC
Tom,
We use the following analyze command for collecting
statistics for tables and the indexes. We run this every
week during the weekend.
ANALYZE TABLE ttcolfp COMPUTE STATISTICS;
ANALYZE TABLE ttstcpk COMPUTE STATISTICS;
ANALYZE TABLE ttklmta COMPUTE STATISTICS;
Thanks for your help.
Pushparaj
October 20, 2003 - 10:51 am UTC
I've a feeling it is a bind variable "peeking" issue.
Can you try this
a) flush your shared pool
b) run the plsql with the BAD inputs first (with the values that make it run slow)
what I think might be happening is -- you run it with inputs that should use a merge join cartesian (you have histograms on those tables) and that plan "sticks". You try in sqlplus with "bad" values and get a different plan.
It could be that you don't want histograms in this particular case (compute statistics takes the MOST resources -- you might consider using just for table for all indexes -- and maybe for "some columns"
Here is a for example of bind variable peeking -- SAME query more or less (differs only in the correlation name in the query) but run with different starting inputs:
ops$tkyte@ORA920> create table t
2 as
3 select 1000 id, all_objects.* from all_objects;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t
2 select 1 id, all_objects.* from all_objects where rownum = 1;
1 row created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t_idx on t(id);
Index created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table t compute statistics
2 for table
3 for all indexes
4 for all indexed columns;
Table analyzed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter system flush shared_pool;
System altered.
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable x number
ops$tkyte@ORA920> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> exec :x := 1000
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t x_1000 where id = :x;
30189 rows selected.
Statistics
----------------------------------------------------------
593 recursive calls
0 db block gets
2530 consistent gets
223 physical reads
0 redo size
2155534 bytes sent via SQL*Net to client
22631 bytes received via SQL*Net from client
2014 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
30189 rows processed
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec :x := 1
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from t x_1 where id = :x;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
1216 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace off
tkprof shows us:
select * from t x_1000 where id = :x
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2014 0.34 0.36 223 2440 0 30189
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2016 0.35 0.37 223 2440 0 30189
Rows Row Source Operation
------- ---------------------------------------------------
30189 TABLE ACCESS FULL T (cr=2440 r=223 w=0 time=193232 us)
********************************************************************************
select * from t x_1 where id = :x
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 4 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 r=1 w=0 time=254 us)
1 INDEX RANGE SCAN T_IDX (cr=3 r=1 w=0 time=214 us)(object id 41314)
<b>different plans -- that will stay with that query until the shared pool is flushed/it ages out</b>
Can we flush just one query plan from shared pool?
Arun Gupta, December 29, 2003 - 4:15 pm UTC
Tom,
If a bad execution plan gets into the shared pool because of bind variable peeking, what are the ways to get rid of it? The query is heavily used and chances of it's getting flushed out of shared pool are low. Is flushing the entire shared pool our only option here?
Thanks.
December 29, 2003 - 5:55 pm UTC
analyze the table, grant/revoke on the table (eg: grant select on it to scott, revoke select on it from scott)
Not missing the point: Grasping for some explanation of how bind peeking is useful
Kerry, March 19, 2004 - 5:36 pm UTC
I don't think Kamal's question can be easily dismissed. The hypothetical, more dynamic peeking he is talking about is just an attempt to understand an otherwise perplexing feature...
I think people's difficulty in understanding what bind peeking does is that they are grasping for a set of behaviors that actually make it beneficial. Now that I understand that the current implementation (in 9i), is simply not useful, it is much easier to comprehend. With the plan cached and spanning sessions, it is not a reasonable assumption that any "representative" bind values can be used at the hard parse:
(1) If data isn't skewed, then bind peeking isn't necessary - the overall statistics for the tables and indexes, independent of bind values - should suffice
(2) If data is skewed and peeking makes a significant difference, then it will produce a plan for the initial set of values different than what it would choose on subsequent executions: likely a poor choice for any other set of bind values.
How could I possibly want this?
I do not want my application to know when the cache has flushed, so I cannot supply "representative" values at the correct time. Furthermore, if there is "representative" data, how skewed can the data actually be?
We have tables partitioned by clients, and bind peeking is nothing but trouble. Coding all dynamic SQL in PL/SQL would be an absolute joke...
March 20, 2004 - 9:54 am UTC
1) correct
2) and the initial plan should be the right plan for that query at that place. The concept is "i have an oltp app that picks "where x = :x". the count(*) for the x's i use is "small" reletive to the table. I SHOULD use an index. Therefore the first time we run, we see x = :x and :x = 5 and 5 should use an index -- for that exact query (cause remember the plans are by EXACT queries) the optimizer will choose an index. that is the right plan for that application. no matter what happens -- that query should use and index and every time I run that program that query at that place will supply bind inputs that lean the optimizer towards the index. I need to bind because there are potentially hundreds of codes there -- but all would use the index. The value(s) that would not use the index would never be called there (remember, we are "transactional")
sorry you don't see the use, I sure do and I am glad it is there -- else it would lead to lots more hinting going on.
Data in table may be skewed, but data seen by query must not be?
Kerry, March 24, 2004 - 12:02 pm UTC
To summarize: Bind peeking may be useful if the data in the table is skewed, but the data that I query is of a consistent proportion relative to the size of the table, for a given query.
Consider the skewness as falling into two equivalence classes: BIG fanout and SMALL fanout.
Here is where I find this problematic: Unless I code my application to recognize the equivalence classes and handle the BIG and SMALL cases with two different queries then there will always be a chance that a BIG dataset will cause a plan to cache that is not at all appropriate for the SMALL case, or vice versa. Since I don't define the equivalence classes or provide the data, I can't control it. (And this is a simple case: assuming just 2 equivalence classes)
It may be worth note that our database is hardly OLTP. It's an ASP-model (website) with some OLTP aspects, some data mining, and some data warehousing. I believe this is why bind peeking was particularly bad for us.
March 24, 2004 - 1:23 pm UTC
the concept is -- in a given point in a program -- they will be "the same".
at point X in the program (say the user interactive part), always index, the binds will drive you that way.
at point Y in the program (system), full scan, the binds will drive you that way.
without bind peeking, you would always have that 50/50 chance of "not getting it right".
BIND PEEKING
Leonard Williams, July 23, 2004 - 12:09 pm UTC
This was very good. We were going back and forth in my whether to use CURSOR_SHARING or BIND VARIABLES.
I stressed to them the "Proper use of bind variables" and the affects of turning on "cursor_sharing" in the DB configuration.
They went with Bind Variables and noticed that EXPLAIN PLAN sort of lied to them. This article or discussion help shed some light on a lot things regarding Bind Peeking and it's relevance or affect on the optimizer and the explain plan output.
Thanks Much,
Skewed data used once only
Adrian, February 07, 2005 - 9:49 am UTC
Tom,
>> the concept is -- in a given point in a program -- they will be "the same".
I understand fully your position on peeking and that different parts of the application might require different "pieces" of the skewed data. My concern is how this helps when we have an on-line application with only one point of reference to the skewed, "histogrammed" data - where genuinely, a lookup with binds + peeking could really screw the application once and for all if we got the wrong one on the fabled Monday morning.
I read somewhere on usenet that somebody's mother's uncle's brother thought that 10g might be introducing a "re-peek and parse" under certain conditions. Don't suppose you know anything about this ?
Regards
Adrian
February 07, 2005 - 10:47 am UTC
you just sort of decribed "cursor_sharing=similar"
Only if...
Adrian, February 07, 2005 - 11:36 am UTC
If the app was using literals in the SQL, then I'd agree and we'd be on the way to solving this.
However, the app is at a halfway house where it is binding a user-input but has a skewed column with a histogram. I'm not convinced peeking will provide anything in this instance over the 8i and earlier days which said "binds won't make use of histograms". We'd still see the same proportion of people unhappy either way. But then again, SOME people will get the quick response they'd expect though ;o)
I don't know what the answer to this is.
Regards
Adrian
February 07, 2005 - 11:42 am UTC
Dont bind that query, that is a possibility.
Do the math, if there are a handful of inputs to that "bind", use a literal
Dont use histograms
Get the general plan Consistent Plan, but typically
not the best plan for all
Use your domain knowledge of the data
Input dates within the last month use this query, else use that query
if ( date > sysdate-30 )
then
open this_query using select * from t DT_CLOSE_TO_TODAY where ....
else
open this_query using select * from t DT_FAR_IN_THE_PAST where...
end if
Codes less than 50 use this query, else use that query
Status values of A, M and N . Else.
You can disable it (using undocumented, hence not talked about parameter) but that is like dont use histograms in a system that uses binds.
And beware of partitioned tables...
kerry, February 10, 2005 - 1:12 pm UTC
Simply turning of the gathering of histograms is not enough if you have partitioned tables. Bind peeking allows oracle to use partition level statistics as well. You can have the same issues if the distribution of data differs between partitions: the number of distinct values for some column in a partition may be drastically different from other partitions. But the same cached plan will be used regardless.
Does Peeking works when cursor_sharing is left at the default value ?
VLS, September 14, 2006 - 10:47 am UTC
Hi Tom,
Does Bind Variable peeking works when the cursor_sharing is at default value ?
Regards
VLS
September 14, 2006 - 11:17 am UTC
of exact, yes - absolutely.
It happens with all settings actually.