Shulou(Shulou.com)06/01 Report--

Problem caused by Oracle data skew-bound variables

Reference arrangement->

Scenario 2:

In the previous experiment, we can know that when there are no bound variables, the problem of data skew can be solved by histogram in specific scenarios, so in the case of bound variables, can the problem of data skew be solved by histogram alone?

Obviously not, Oracle binding variable technology solves the problem of too much hard parsing of SQL statements and reduces the contention for resources. However, while the binding variable introduces cursor sharing and adds soft parsing, because the SQL text is the same, it often generates the same execution plan, which sometimes leads to performance problems when the data is unevenly distributed and the data is tilted seriously.

In the oracle 9i version, the binding variable snooping Bind Peeking technology is introduced. During the first hard parsing, the true value of the bound variable will be detected to generate a more accurate execution plan, but from the second soft parsing, the previous execution plan will always be used, and one execution plan will not apply all the bound values. When the distribution of filter column data is seriously skewed, it may generate an inefficient execution plan.

In order to make up for the defect of binding variable snooping Bind Peeking technology, 11g introduces adaptive cursor sharing technology (Adaptive Cursor Sharing). Through adaptive cursor sharing, cursors can be shared intelligently only for statements that use bound variables.

One: bind variables to see the influence of Bind Peeking on the execution plan.

Second: adaptive cursor sharing technology (Adaptive Cursor Sharing)

One: bind variables to see the influence of Bind Peeking on the execution plan.

1 View Bind Peeking and Adaptive Cursor Sharing parameters

Select name, value

From (select nam.ksppinm name

Val.KSPPSTVL value

-- nam.ksppdesc description

Val.ksppstdf isdefault

From sys.x$ksppi nam, sys.x$ksppcv val

Where nam.inst_id = val.inst_id

And nam.indx = val.indx)

Where name in ('_ optimizer_adaptive_cursor_sharing'

'_ optimizer_extended_cursor_sharing_rel'

'_ optimizer_extended_cursor_sharing'

'_ optim_peek_user_binds')

2 create test data

SQL > select banner_full from v$version

BANNER_FULL

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-Production

Version 19.3.0.0.0

SQL > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 CJCPDB READ WRITE NO

SQL > conn cjc/cjc@cjcpdb

Connected

Create a new test table T1:

SQL > create table T1 as select * from dba_objects

Create an index:

SQL > create index idx_t1_01 on T1 (object_id)

Add data:

SQL > insert into T1 select * from T1

/

SQL > update T1 set object_id=rownum

Update the data and use the uneven distribution of the data:

SQL > update T1 set object_id=10 where object_id > 10

290010 rows updated.

SQL > commit

Commit complete.

SQL > select object_id,count (1) from T1 group by object_id order by 1

-- collect the histogram of the field OBJECT_ID below:

SQL >

Begin

Dbms_stats.gather_table_stats ('CJC'

'T1'

Method_opt = > 'for columns object_id size auto'

Cascade = > true)

End

View histogram information:

Select table_name

Column_name

Histogram

Num_distinct

Density

Last_analyzed

From user_tab_col_statistics

Where table_name ='T1'

And column_name = 'OBJECT_ID'

Select *

From user_tab_histograms

Where table_name ='T1'

And column_name = 'OBJECT_ID'

Order by 5

3 binding variables to snoop on the impact of the execution plan

The binding variable snooping feature during hard parsing can generate an efficient execution plan based on the true value of the bound variable.

SQL > alter system flush shared_pool

System altered.

SQL > set autotrace traceonly

SQL > set linesize 200

SQL > set timing on

SQL > variable xxx varchar2

SQL > execute: xxx: 1 0

SQL > select * from T1 where object_id=:xxx

SQL >

Select sql_id

Child_number

Executions

Loads

Buffer_gets

Is_bind_sensitive as "bind_sensi"

Is_bind_aware as "bind_aware"

Is_shareable as "bind_share"

From v$sql

Where sql_text like 'select * from T1 where object_id%'

Select * from table (dbms_xplan.display_cursor ('2gr2tazfbjvsaja format = >' advanced'))

The second time the soft parsing is performed, the value of the bound variable is changed to 1, and there is only one item in the result set, but it is obviously unreasonable to follow the previous execution plan and scan the full table.

SQL > execute: xxx: = 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL > select * from T1 where object_id=:xxx

Elapsed: 00:00:00.05

Select sql_id

Child_number

Executions

Loads

Buffer_gets

Is_bind_sensitive as "bind_sensi"

Is_bind_aware as "bind_aware"

Is_shareable as "bind_share"

From v$sql

Where sql_text like 'select * from T1 where object_id%'

Second: adaptive cursor sharing technology (Adaptive Cursor Sharing)

Execute a statement with a binding variable equal to 1 multiple times.

SQL > execute: xxx: = 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL > select * from T1 where object_id=:xxx

SQL > execute: xxx: = 1

SQL > select * from T1 where object_id=:xxx

Execute a statement with a binding variable equal to 10 multiple times.

SQL > execute: xxx: 1 0

SQL > select * from T1 where object_id=:xxx

.

Cursor adaptation is in effect.

The Sql_id is the same, but the plan_hash_value is different, indicating that a different execution plan has been generated

Select sql_id

Plan_hash_value

Child_number

Executions

Loads

Buffer_gets

Is_bind_sensitive as "bind_sensi"

Is_bind_aware as "bind_aware"

Is_shareable as "bind_share"

From v$sql

Where sql_text like 'select * from T1 where object_id%'

View the generated execution plan

SELECT SQL_ID

PLAN_HASH_VALUE

LPAD ('', 4 * DEPTH) | | OPERATION | | OPTIONS OPERATION

OBJECT_NAME

CARDINALITY

BYTES

COST

TIME

FROM V$SQL_PLAN

Where SQL_ID = '2gr2tazfbjvsa'

Select * from v$sql_cs_histogram where sql_id='2gr2tazfbjvsa'

Note:

Cursor adaptation can sometimes cause a large number of SQL execution plans to be unstable, and in version 11.2.0.1, binding variable snooping features can cause ORA-03137:TTC protocol internal error: [12333] problems, and sometimes we choose to turn them off depending on the situation.

Select name, value, description

From (select nam.ksppinm name

Val.KSPPSTVL value

Nam.ksppdesc description

Val.ksppstdf isdefault

From sys.x$ksppi nam, sys.x$ksppcv val

Where nam.inst_id = val.inst_id

And nam.indx = val.indx)

Where name in ('_ optimizer_adaptive_cursor_sharing'

'_ optimizer_extended_cursor_sharing_rel'

'_ optimizer_extended_cursor_sharing'

'_ optim_peek_user_binds')

-- all dynamic parameters

-- bind peeking (binding variable peep

-alter system set "_ optim_peek_user_binds" = false

-- acs (adaptive cursor sharing)

Alter system set "_ optimizer_extended_cursor_sharing_rel" = NONE

Alter system set "_ optimizer_extended_cursor_sharing" = NONE

Alter system set "_ optimizer_adaptive_cursor_sharing" = false

After database-level cursor adaptation is turned off, you can manually turn on statement-level cursor adaptation as follows:

-19C test failed, but no specific reason has been found yet.

# 12.2 previous version

DECLARE

V_SQL CLOB

Begin

-take out the text of the original SQL

SELECT SQL_FULLTEXT

INTO V_SQL

FROM V$SQL

WHERE SQL_ID = '2gr2tazfbjvsa'

AND ROWNUM = 1

-- add HINT

Sys.dbms_sqldiag_internal.i_create_patch (sql_text = > V_SQL)

Hint_text = > 'BIND_AWARE'

Name = > 'sql_2gr2tazfbjvsa')

End

# 12.2 and later

# create sql patch

Declare

Patch_name varchar2 (30)

Begin

Patch_name: = dbms_sqldiag.create_sql_patch (sql_id = > '2gr2tazfbjvsa'

Hint_text = > 'select * from T1 where object_id=:xxx')

End

/

SQL >

Select name

To_char (created, 'yyyy-mm-dd hh34:mi:ss') as created

Status

Force_matching

Description

Substr (sql_text, 1,50) as sql_text

From dba_sql_patches

Order by created

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.