Re: CURSOR_SHARING in 9i: FORCE vs. SIMILAR

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 19 May 2004 13:04:03 +0100
Message-ID: <030601c43d99$640cfd40$7102a8c0@Primary>

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_at_quadtechworld.com> To: <oracle-l_at_freelists.org> 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:

3M row table "MYTAB" with avg row len of 149, 8K blocks.
Index "MYTAB_MYCOL" on VARCHAR2(1) column "MYCOL" (no, I didn't = create this table).
Column has two and only two distinct values: 'N' and 'Y'.
Data is skewed so that 2999500 rows are 'Y' and 500 are 'N'.
Stats are gathered using DBMS_STATS and FOR ALL INDEXED COLUMNS SIZE = AUTO
User does SELECT * FROM TTT WHERE MYCOL =3D 'Y'
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

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

Received on Wed May 19 2004 - 07:01:03 CDT