Re: CURSOR_SHARING in 9i: FORCE vs. SIMILAR
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 May 2004 13:04:03 +0100
Your memory that the plan should be the same
for both queries when cursor_sharing = force
is correct. Depending on the test sequence, though,
the plan might be a tablescan or a range scan.
Because of bind variable peeking, the first one
through will dictate the path, and the second will
follow it.
I tried to repeat your test - but only on 9.2.0.1,
and whichever plan was first generated for
the query after "flush shared pool", that plan
persisted for all subsequence executions.
How are you checking the execution plan ?
If you use autotrace, you WILL be told about
two different plans, because Oracle does not
use cursor_sharing for explain plan.
e.g.
set autotrace on
select * from t1 where id = 88
Two rows appear in v$sql
EXPLAIN PLAN SET STATEMENT_ID='PLUS701' FOR select * from t1 where id =
88
select * from t1 where id = :"SYS_B_0"
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, May 18, 2004 9:45 PM
Subject: CURSOR_SHARING in 9i: FORCE vs. SIMILAR
So, I'm looking up differences between CURSOR_SHARING=3DFORCE (CS=3DF) =
and CURSOR_SHARING=3DSIMILAR (CS=3DS) for 9.2.0.5 on HP-UX 11.11. I'm =
not able to come up with a scenario to show the difference, though.
We're using CS=3DF now in 8.1.7.4.0. From the description in the "FM"s =
I "R"d, I thought that perhaps CS=3DF would be bad in this case:
1) 3M row table "MYTAB" with avg row len of 149, 8K blocks.
2) Index "MYTAB_MYCOL" on VARCHAR2(1) column "MYCOL" (no, I didn't =
create this table).
3) Column has two and only two distinct values: 'N' and 'Y'.
4) Data is skewed so that 2999500 rows are 'Y' and 500 are 'N'.
5) Stats are gathered using DBMS_STATS and FOR ALL INDEXED COLUMNS SIZE =
AUTO
6) User does SELECT * FROM TTT WHERE MYCOL =3D 'Y'
7) Same user then does SELECT * FROM TTT WHERE MYCOL =3D 'N'
Using CS=3DF, the explain plan for line 6 says FTS, line 7 says range =
scan on the "MYTAB_MYCOL" index. From what I've read, I thought that =
CS=3DF would cause the explain plan for line 7 to FTS and that the "fix" =
was to use CS=3DS.
Can anyone shed some light on this? I'm not really complaining because =
the explain plans are correct, I'm just confused as usual.
TIA,
Rich
Rich Jesse System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
