OraFAQ Forum: Performance Tuning » Adaptive cursor sharing Doubt!!

Home » RDBMS Server » Performance Tuning » Adaptive cursor sharing Doubt!! (Oracle 11g (11.2.0.3.0 ) - Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Adaptive cursor sharing Doubt!! [message #572868]

Tue, 18 December 2012 05:59

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Hi All,

I have a table "t", with a highly skewed ID column
Cursor_sharing=force

select count(*) from t;
--79003

select count(*) from t where id=1;
--79002

select count(*) from t where id=99;
--1

I have a index on id column and since highly skewed data is there in id column therefore histogram is there

SQL> select table_name,column_name,histogram,num_buckets from user_tab_cols wher
e table_name='T';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM       NUM_BUCKETS
------------------------------ ------------------------------ --------------- ------------

T                              ID                             FREQUENCY           2

When i use literals , explain plan comes up with optimal execution plan
select * from t where id=1;
Full table scan

Select * from t where id=99;
Index range scan

But when i use bind variables,sub optimal execution plan

For id=1, it should use Full table scan but it goes for Index range scan , WHY? why ACS is not getting kicked?

alter system flush shared_pool;
set autotrace traceonly
variable n number;
exec :n=1;
select * from t where id=:n;

For id=99, it uses Index range scan which make sense,
Other thing i noticed is

SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
  2  FROM   v$sql
  3  where sql_text like 'select * from t%';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
8x041w5gquxb6            0 N N

Why is_bind_sensitive not "Y"?

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572871 is a reply to message #572868]

Tue, 18 December 2012 06:06

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please select the sql text, too:

SELECT sql_id, sql_text, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql
where sql_text like 'select * from t%';

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572872 is a reply to message #572871]

Tue, 18 December 2012 06:11

rishwinger
Messages: 132
Registered: November 2011

Senior Member


 SQL> SELECT sql_id, sql_text, child_number, is_bind_sensitive, is_bind_aware
  2  FROM   v$sql
  3  where sql_text like 'select * from t%';
SQL_ID          SQL_TEXT                 CHILD_NUMBER I I
-----------   ---------------------------------------------
8x041w5gquxb6	select * from t where id=:n	   0	          N	N

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572873 is a reply to message #572868]

Tue, 18 December 2012 06:12

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

You need to execute the queries several times, and you must have analyzed the table: for the analysis, I always use
method_opt=>'for all columns skewonly'
estimate_percent=>100
And most importantly, you need a good release. I was teaching this the other day, and it didn't work with 11.2.0.1 but did work with 11.2.0.3.

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572875 is a reply to message #572873]

Tue, 18 December 2012 06:24

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Hi John,

Thx for the reply

Quote:

you need to execute the queries several times

What do u mean by this? According to what i understand Oracle has this bind peeking feature, so when we use bind variable for first time it will check what literal value it will be using and accordingly it comes up with an optimal plan , so with ACS in place it should actually check for each execution and comes up with optimal execution plan, but in my case for id=1 it does Index range scan, WHY?

Quote:

you must have analyzed the table

I am not sure of that i was trying with both analyze as well as DBMS_STATS but our purpose is getting solved by both i guess, Histogram is there in place as well as other statistics

Quote:

it didn't work with 11.2.0.1 but did work with 11.2.0.3.

My oracle version is 11.2.0.3. so it should work!!!

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572879 is a reply to message #572875]

Tue, 18 December 2012 06:28

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Quote:

but in my case for id=1 it does Index range scan, WHY?

likely reasons are that you have not executed the queries repeatedly and that you have not analyzed with the options I gave you.
Also, autotrace may be lying to you. It does an EXPLAIN PLAN, which means a hard parse. You cannot rely on this to tell you what actually happened.

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572881 is a reply to message #572868]

Tue, 18 December 2012 06:32

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Does your table have histograms?

Sorry, I have overlooked your answer above.

[Updated on: Tue, 18 December 2012 06:34]

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572885 is a reply to message #572881]

Tue, 18 December 2012 06:58

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please try with the clone table.

create table t_clone as select * from t;

exec dbms_stats.gather_table_stats(user,'T_CLONE', method_opt=>'for columns id size 70')

select count(*) from user_histograms where table_name='T_CLONE' and column_name='ID';
 
select * from t where id=:n;

SELECT sql_id, sql_text, child_number, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select * from t%';

It should by something wrong with the histograms.

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572982 is a reply to message #572885]

Wed, 19 December 2012 03:19

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Hi LNossov,

Instead of t_clone table name i have used ACS

select count(*) from acs
--100001

select count(*) from acs where id=99
--100000

select count(*) from acs where id=1
--1

SQL> select table_name,column_name,histogram,num_buckets from user_tab_cols where table_name='ACS';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM       NUM_BUCKETS
------------------------------ ------------------------------ --------------- -----------
ACS                            ID                             FREQUENCY          2
ACS                            REC_TYPE                       NONE
ACS                            NAME                           NONE

SQL> select count(*) from user_histograms where table_name='ACS' and column_name='ID';

  COUNT(*)
----------
         2

Below is the test with bind variable, now i am confused why Full table scan wasn't used for id=99( count=1000000)

SQL> alter system flush shared_pool;

System altered.

SQL> var n number
SQL> exec :n:=99

PL/SQL procedure successfully completed.

SQL> select * from acs where id=:n;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 601343075

--------------------------------------------------------------------------------
-------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT            |         | 50001 |  1269K|    78   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACS     | 50001 |  1269K|    78   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACS | 50001 |       |    25   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:N))


Statistics
----------------------------------------------------------
         23  recursive calls
          1  db block gets
       7171  consistent gets
          0  physical reads
          0  redo size
    3351512  bytes sent via SQL*Net to client
      73664  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
     100000  rows processed

but i got one thing in place is_bind_sensitive=y

SQL> select sql_text,child_number, executions, buffer_gets, is_bind_sensitive, i
s_bind_aware,is_shareable from v$sql where sql_text like 'select * from acs%';

SQL_TEXT                    CHILD_NUMBER EXECUTIONS BUFFER_GETS  I  I  I
------------                 ---------- ----------- -----------  -  -  -
select * from acs where id=:n    0          1        7172        Y  N  Y

For id=1, execution plan make sense

SQL> exec :n:=1

PL/SQL procedure successfully completed.

SQL> select * from acs where id=:n;


Execution Plan
----------------------------------------------------------
Plan hash value: 601343075

--------------------------------------------------------------------------------
-------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT            |         | 50001 |  1269K|    78   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACS     | 50001 |  1269K|    78   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACS | 50001 |       |    25   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:N))


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        464  consistent gets
          0  physical reads
          0  redo size
        450  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sql_text,child_number, executions, buffer_gets, is_bind_sensitive, i
s_bind_aware,is_shareable from v$sql where sql_text like 'select * from acs%';

SQL_TEXT                     CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------                  ---------- -----------  --------   - - -
select * from acs where id=:n   0            2        7637       Y N Y

Now again for id=99 it goes with the same explain plan but less resource intensive and used new cursor and old cursor become unshareable

SQL> var n number
SQL> exec :n:=99

PL/SQL procedure successfully completed.

SQL> select * from acs where id=:n;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 601343075

--------------------------------------------------------------------------------
-------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT            |         | 50001 |  1269K|    78   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACS     | 50001 |  1269K|    78   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACS | 50001 |       |    25   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:N))


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       7120  consistent gets
          0  physical reads
          0  redo size
    3351512  bytes sent via SQL*Net to client
      73664  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


SQL> select sql_text,child_number, executions, buffer_gets, is_bind_sensitive, i
s_bind_aware,is_shareable from v$sql where sql_text like 'select * from acs%';

SQL_TEXT                       CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- -----------                                - - -
select * from acs where id=:n     0          2          7637       Y N N

select * from acs where id=:n     1          1          7121       Y Y Y

For id=1 , same explain plan, new cursor and less resource intensive

SQL> select * from acs where id=:n;


Execution Plan
----------------------------------------------------------
Plan hash value: 601343075

--------------------------------------------------------------------------------
-------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT            |         | 50001 |  1269K|    78   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACS     | 50001 |  1269K|    78   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACS | 50001 |       |    25   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:N))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        450  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sql_text,child_number, executions, buffer_gets, is_bind_sensitive, i
s_bind_aware,is_shareable from v$sql where sql_text like 'select * from acs%';

SQL_TEXT                      CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
select * from acs where id=:n    0          2          7637        Y N N

select * from acs where id=:n    1          1          7121        Y Y Y

select * from acs where id=:n    2          1           4          Y Y Y

Now for id=99

SQL> select sql_text,child_number, executions, buffer_gets, is_bind_sensitive, i
s_bind_aware,is_shareable from v$sql where sql_text like 'select * from acs%';

SQL_TEXT
--------------------------------------------------------------------------------

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
select * from acs where id=:n
           0          2        7637 Y N N

select * from acs where id=:n
           1          2       10190 Y Y Y

select * from acs where id=:n
           2          1           4 Y Y Y

It seems i was able to kick Adaptive cursor sharing(in a weird way No FTS for 1000000 rows)

[Updated on: Wed, 19 December 2012 03:21]

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572984 is a reply to message #572982]

Wed, 19 December 2012 03:28

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

This

SQL> exec :n:=99

PL/SQL procedure successfully completed.

SQL> select * from acs where id=:n;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 601343075

--------------------------------------------------------------------------------
-------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT            |         | 50001 |  1269K|    78   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACS     | 50001 |  1269K|    78   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACS | 50001 |       |    25   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:N))


Statistics
----------------------------------------------------------
         23  recursive calls
          1  db block gets
       7171  consistent gets
          0  physical reads
          0  redo size
    3351512  bytes sent via SQL*Net to client
      73664  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
     100000  rows processed

was a full table scan. Can't you see the 7171 consistent gets? As I told you before, you can't trust autotrace: it does an EXPLAIN PLAN which is a hard parse. It does NOT show you the plan that was used.
--
Update: and do you now see thqt you need repeated executions before ACS kicks in? It needs the cardinality feedback (which is another thing you may want to research)

[Updated on: Wed, 19 December 2012 03:30]

Report message to a moderator

Re: Adaptive cursor sharing Doubt!! [message #572993 is a reply to message #572984]

Wed, 19 December 2012 04:52

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Thanks John,

Quote:

you need repeated executions before ACS kicks in? It needs the cardinality feedback (which is another thing you may want to research)

I got this part pretty clear, ACS is all about adapting

Well i am not very happy with autotrace, i need to get hold of this

Report message to a moderator

Previous Topic:	How to run a query with an different plan_hash value then in GV$SQL_PLAN
Next Topic:	what's operation can cause these wait event?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 00:55:24 CST 2025