FAQ
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.

Search Discussions

  • Jonathan Lewis at May 19, 2004 at 7:01 am
    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"
    To:
    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

    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
  • Jesse, Rich at May 19, 2004 at 10:02 am
    I was checking the explain plan with EXPLAIN via TOAD. Once I fired up =
    a 10046 trace, I clearly saw the diffs between the two executions in the =
    STAT lines. And, as a bonus, the trace has the updating of =
    SYS.OBJECT_USAGE after the query that used the index. :)

    Prepare to change CS=3DF to CS=3DS...

    Thanks, Jonathan!

    Rich

    Rich Jesse System/Database Administrator
    rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
    From: oracle-l-bounce_at_freelists.org
    Sent: Wednesday, May 19, 2004 7:04 AM
    Subject: Re: CURSOR_SHARING in 9i: FORCE vs. SIMILAR
    =20
    How are you checking the execution plan ?
    =20
    If you use autotrace, you WILL be told about
    two different plans, because Oracle does not
    use cursor_sharing for explain plan.
    =20
    e.g.
    set autotrace on
    select * from t1 where id =3D 88
    =20
    Two rows appear in v$sql
    EXPLAIN PLAN SET STATEMENT_ID=3D'PLUS701' FOR select * from=20
    t1 where id =3D
    88
    select * from t1 where id =3D :"SYS_B_0"
    =20
    =20
    Regards
    =20
    Jonathan Lewis
    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
    -----------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 18, '04 at 3:44p
activeMay 19, '04 at 10:02a
posts3
users2
websiteoracle.com

2 users in discussion

Jesse, Rich: 2 posts Jonathan Lewis: 1 post

People

Translate

site design / logo © 2022 Grokbase