CURSOR_SHARING = FORCE !!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: CURSOR_SHARING = FORCE !!!!

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I would like to see how many use this option in your instance setting??

    Very curious!!!!!!!!!!!!!!!!!!!!!

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    used once in a customer's place, why?

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    There is a huge hidden cost that would effect the query performance.

    Here is a sample of the query


    CREATE TABLE t as
     SELECT * FROM all_objects;

    CREATE INDEX t_index1 on t(OBJECT_NAME);

    CREATE INDEX t_index2 on t(OBJECT_TYPE);

    ANALYZE TABLE t COMPUTE STATISTICS
     FOR ALL INDEXED COLUMNS
     FOR TABLE;
    Query 1

    ALTER SESSION SET TIMED_STATISTICS=TRUE;

    ALTER SESSION SET CURSOR_SHARING=EXACT;


    variable search_str VARCHAR2(25)
    exec :search_str := '%';

    ALTER SESSION SET SQL_TRACE=TRUE;

    SELECT * FROM t t1
     WHERE object_name LIKE :search_str
     AND object_type IN ('FUNCTION','PROCEDURE','TRIGGER');
    Query 2

    ALTER SESSION SET CURSOR_SHARING= FORCE;

    ALTER SESSION SET SQL_TRACE=TRUE;

    SELECT * FROM t t1
      WHERE object_name LIKE :search_str
     AND object_type IN ('FUNCTION','PROCEDURE','TRIGGER');
    Here if you check your execution plan, it would be the same, but if you look into the statistics, there would be the difference.

    Now if you notice the TKPROF of your trace output, it would be something like this

    Query 1 trace
    SELECT * FROM t t1
    WHERE object_name LIKE :search_str
    AND object_type IN ('FUNCTION','PROCEDURE','TRIGGER')

    call     count     cpu     elapsed     disk     query     current     rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse     ***1     0.03     0.03     0     0     0     0
    Execute    1     0.00     0.00     0     0     0     0
    Fetch     4     0.00     0.00     0     27     0     44
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total     6     0.03     0.03     0     27     0     44

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 122

    Rows     Row Source Operation
    ------- ---------------------------------------------------
    44     INLIST ITERATOR
    44     TABLE ACCESS BY INDEX ROWID T
    47     INDEX RANGE SCAN (object id 73467)
    [/p]

    Quert 2 Trace

    SELECT * FROM t t1
     WHERE object_name LIKE :search_str
     AND object_type IN (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2")

    call     count     cpu     elapsed     disk     query     current     rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse     1     0.01     0.04     0     0     0     0
    Execute    1     0.00     0.00     0     0     0     0
    Fetch     4     0.59     1.71     416     20871    0     44
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total     6     0.60     1.75     416     20871     0     44

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 122

    Rows     Row Source Operation
    ------- ---------------------------------------------------
    44     TABLE ACCESS BY INDEX ROWID T
    22320     INDEX RANGE SCAN (object id 73466)

    Though it is being said that the FORCE would lead to the better performance. But its not always true!.

    Guess why?

    sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Very useful Tip. Wish there were more such tips from Pros in this forum.

  5. #5
    Join Date
    Aug 2001
    Posts
    111
    looking at the explain plan
    clearly the index used in the second case if different to the first query. Noting the object_name index was created first and hence has a lower object_id. This index was used to instead of object_type.
    But the kicker here is that the search is % hence it is using a RANGE SCAN to do what is essentially a FULL TABLE SCAN...very bad!
    I am not sure at the number of blocks in t, but the 20781 query (block gets) is probably at least 3 times as large as the number of blocks in the t table.
    Clearly FORCE is using the wrong index, i think from memory FORCE uses RULE based optimisation?? correct?

    So do i pass?

    Perhaps the moral of the story is,
    check the stats and execution plans. And use indexes like surgical tools not blunt instruments.

    Have Fun
    Performance... Push the envelope!

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Roobaron
    But the kicker here is that the search is % hence it is using a RANGE SCAN to do what is essentially a FULL TABLE SCAN...very bad!
    Remember, it was the same query. So both were using the '%'. The table has only 311 Blocks.

    One other tip where most of you folks could go wrong is, try the same on setting the auto trace on and see what you get?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Let me see what the fresh minds have to say? Looks like not very many are using this feature??

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Aug 2001
    Posts
    111
    hmmm no banana for this monkey just yet eh??

    yet both use % but they are each using a different index. (object_id)

    Okay went back to the documentation (8.1.7) see below

    Clearly it has chosen a suboptimal execution plan. From the doco the increase in length of the string literals has caused the optimiser to choose the other index in preference to the correct plan and thus do a FTS via an INDEX RANGE scan.

    Am I warmer yet?

    If you haven't guessed yet, the databases i support do not use the feature

    I remember reading something elsewhere on the net maybe Jonathan's website about this as well.

    Here is the doco extract:

    This parameter should be set to FORCE only when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.


    --------------------------------------------------------------------------------
    Note:
    Setting CURSOR_SHARING to FORCE causes an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals (in a SELECT statement). However, the actual length of the data returned will not change.

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

    You should consider setting CURSOR_SHARING to FORCE if you can answer 'yes' to both of the following questions:

    Are there statements in the shared pool that differ only in the values of literals?

    Is the response time low due to a very high number of library cache misses?

    Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly.

    --------------------------------------------------------------------------------
    Note:
    Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries, query rewrite, or stored outlines.

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


    Performance... Push the envelope!

  9. #9
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356

    Cool

    Hi! Guys!
    DO check out this link for some more info abt crsor_sharing

    http://www.revealnet.com/newsletter-v2/mir.htm

    There Nothing You cannot Do, The problem is HOW.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width