CURSOR_SHARING in 9i: FORCE vs. SIMILAR
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 May 2004 15:45:11 -0500
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
-----------------------------------------------------------------
Other related posts:
