CURSOR_SHARING = FORCE !!!!

[sambavan]

I would like to see how many use this option in your instance setting??

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

Sam

[pando]

used once in a customer's place, why?

[sambavan]

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

[Raminder]

Very useful Tip. Wish there were more such tips from Pros in this forum.

[roobaron]

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

[sambavan]

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

[sambavan]

Let me see what the fresh minds have to say? Looks like not very many are using this feature??

Sam

[roobaron]

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.

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

[sudip]

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

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