CURSOR_SHARING in 9i: FORCE vs. SIMILAR

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Tue, 18 May 2004 15:45:11 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93998@qtiexch2.qgraph.com>

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_at_quadtechworld.com      QuadTech, Sussex, WI USA




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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Tue May 18 2004 - 15:44:21 CDT