This discussion is archived
1 Reply Latest reply: Dec 6, 2012 4:28 AM by Dom Brooks RSS

Query tuning -- Bind and plan

874051 Newbie
Currently Being Moderated
HI Forum ,
I am trying to understand the below part .

-- created a dummy table
create table test_spm( a number,b varchar2(100));
create index test_spm_a_ind on test_spm(a);
inserted data as
select count(1) from test_spm--1111110
select a,count(1) from test_spm group by a order by 2
0 10
1 100
2 1000
3 10000
4 100000
5 1000000
gathered stats EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'TAB_OWNER',tabname=>'TEST_SPM' ,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO', DEGREE=>8,CASCADE=>TRUE);

histograms
select * from dba_tab_histograms where table_name='TEST_SPM'
IGOAPP_ADMIN TEST_SPM A 2 1
IGOAPP_ADMIN TEST_SPM A 5 2
IGOAPP_ADMIN TEST_SPM A 54 3
IGOAPP_ADMIN TEST_SPM A 570 4
IGOAPP_ADMIN TEST_SPM A 5504 5
IGOAPP_ADMIN TEST_SPM B 0 2.56091583960353E35
IGOAPP_ADMIN TEST_SPM B 1 6.35842717277519E35

select num_distinct,num_buckets,sample_size,histogram from dba_tab_col_statistics where table_name='TEST_SPM' and column_name='A'
6 5 5504 FREQUENCY

execution:
SQL> variable a number;
SQL> exec :a:=0;

PL/SQL procedure successfully completed.

SQL> select count(1) from test_spm where a=:a;

COUNT(1)
----------
10


Execution Plan
----------------------------------------------------------
Plan hash value: 514245925

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
-------------------------------------------------------------------------------

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

2 - filter("A"=TO_NUMBER(:A))

SQL> exec :a:=1;

PL/SQL procedure successfully completed.

SQL> select count(1) from test_spm where a=:a;

COUNT(1)
----------
100


Execution Plan
----------------------------------------------------------
Plan hash value: 514245925

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
-------------------------------------------------------------------------------

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

2 - filter("A"=TO_NUMBER(:A))

SQL> exec :a:=3;

PL/SQL procedure successfully completed.

SQL> select count(1) from test_spm where a=:a;

COUNT(1)
----------
10000


Execution Plan
----------------------------------------------------------
Plan hash value: 514245925

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
-------------------------------------------------------------------------------

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

2 - filter("A"=TO_NUMBER(:A))

SQL> exec :a:=5;

PL/SQL procedure successfully completed.

SQL> select count(1) from test_spm where a=:a;

COUNT(1)
----------
1000000


Execution Plan
----------------------------------------------------------
Plan hash value: 514245925

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
-------------------------------------------------------------------------------

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

2 - filter("A"=TO_NUMBER(:A))

SQL>



select sql_id,address,child_address,is_bind_sensitive,is_bind_aware,is_shareable,exact_matching_signature,
force_matching_signature,bind_data from v$sql where sql_text like 'select count(1) from test_spm where a=:a'

4amggdrpyddkf 00000003E0613138 00000003EAD51F70 Y N N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C08013000101C002160180
4amggdrpyddkf 00000003E0613138 00000003E03007F0 Y Y N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C080F4000101C0021602C103
4amggdrpyddkf 00000003E0613138 00000003E0C5EBA8 Y Y N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C0820B000101C002160180
4amggdrpyddkf 00000003E0613138 000000041B38B3F0 Y Y Y 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C084AC000101C0021602C105

SQL> show parameter cursor_sharing

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
cursor_sharing string EXACT
SQL>

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


-- I am trying to understabd the behaviour of optimizer on bind values .


Thanks ,
Mahesh
  • 1. Re: Query tuning -- Bind and plan
    Dom Brooks Guru
    Currently Being Moderated
    How are you getting the actual execution plans?

    Use DBMS_XPLAN.DISPLAY_CURSOR i.e. straight after executing:
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
    select sql_id,address,child_address,is_bind_sensitive,is_bind_aware,is_shareable,exact_matching_signature,
    force_matching_signature,bind_data from v$sql where sql_text like 'select count(1) from test_spm where a=:a'
    Can you include the plan_hash_value?


    This is probably the single best resource on the adaptive cursor sharing (is_bind_aware / is_bind_sensitive) that you're seeing:
    https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points