Shulou(Shulou.com)05/31 Report--

The main content of this article is "Analysis of the working situation of ACS and PL/SQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the work Analysis of ACS and PL/SQL".

ACS and PL/SQL

Let's take a look at ACS's work in PL/SQL, which may disappoint you very much.

First construct a PL/SQL that uses the table T we created in this chapter:

SQL > var sql_id varchar2 (255)

SQL > alter system flush shared_pool

System altered.

SQL > declare

2 x integer

3 n number

4 begin

5 for i in 1..10 loop

6 if I = 1 then

7 x: = 500000

8 else

9 x: = 1

10 end if

11 select count (object_id) into n from t where id > x

12 end loop

13 end

14 /

PL/SQL procedure successfully completed.

This PL/SQL will first execute 'select count (object_id) into n from t where id > 100000' once, and then execute 'select count (object_id) into n from t where id > 1' 9 times. When the execution is complete, let's see if ACS will be used.

SQL > select

2 sql_id

3, child_number

4, executions

5, parse_calls

6, buffer_gets

7, is_bind_sensitive

8, is_bind_aware

9 from

10 v$sql

11 where

12 sql_id = 'gp03v5aw085v3'

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS

-

Gp03v5aw085v3 0 10 1 646875 Y N

Unfortunately, this SQL does not produce multiple subcursors, although the SQL has been recognized as a binding-sensitive is_bind_sensitive='Y', but is_bind_aware='N'.

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='gp03v5aw085v3'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

3087275875 gp03v5aw085v3 0 0 1

3087275875 gp03v5aw085v3 0 2 9

3087275875 gp03v5aw085v3 0 1 0

Although v$sql_cs_histogram has monitored a significant change in the number of rows processed, no new cursors have been generated.

SQL > select * from table (dbms_xplan.display_cursor (: sql_id,null,'+PEEKED_BINDS'))

PLAN_TABLE_OUTPUT

-

SQL_ID gp03v5aw085v3, child number 0

-

SELECT COUNT (OBJECT_ID) FROM T WHERE ID >: B1

Plan hash value: 3694077449

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 4 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10 | 4 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | I | 1 | | 3 (0) | 00:00:01 |

-

Peeked Binds (identified by position):

-

1 -: B1 (NUMBER): 5000000

Predicate Information (identified by operation id):

3-access ("ID" >: B1)

The execution plan has always been the execution plan generated for the first time, which can be inferred from the value of Peeked Binds (identified by position): 5000000. Let's try to add bind_aware hint to SQL. The role of this hint is described in the best practices section of this chapter and will not be repeated here.

SQL > declare

2 x integer

3 n number

4 begin

5 for i in 1..10 loop

6 if I = 1 then

7 x: = 5000000

8 else

9 x: = 1

10 end if

11 select / * + bind_aware * / count (object_id) into n from t where id > x

12 end loop

13 end

14 /

PL/SQL procedure successfully completed.

SQL > select

2 sql_id

3, child_number

4, executions

5, parse_calls

6, buffer_gets

7, is_bind_sensitive

8, is_bind_aware

9 from

10 v$sql

11 where

12 sql_id = '5542a2rzny69v'

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS

-

5542a2rzny69v 0 10 1 687396 Y Y

Although the optimizer has marked the cursor is_bind_aware='Y', no new cursors have been generated. Look for similar problems on MOS and you will find a BUG:

Bug 8357294: ADAPTIVE cursor SHARING DOESN'T WORK FOR STATIC SQL cursorS FROM PL/SQL

The title means that because BUG,ACS does not work in the static cursor of PL/SQL. But the dynamic cursor does not work according to the test.

SQL > declare

2 x integer

3 n number

4 begin

5 for i in 1..10 loop

6 if I = 1 then

7 x: = 5000000

8 else

9 x: = 1

10 end if

11 execute immediate

12 'select count (object_id) from t where id >: X' into n using x

13 end loop

14 end

15 /

PL/SQL procedure successfully completed.

SQL > select

2 sql_id

3, child_number

4, executions

5, parse_calls

6, buffer_gets

7, is_bind_sensitive

8, is_bind_aware

9 from

10 v$sql

11 where

12 sql_id = '6qwg6gauwbpm8'

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS

-

6qwg6gauwbpm8 0 10 1 687580 Y N

It is mentioned in the article that when Session_Cached_Cursors is set to 0, ACS can work normally, as it says after testing.

SQL > alter session set Session_Cached_Cursors=0

Session altered.

SQL > alter system flush shared_pool

System altered.

SQL > declare

2 x integer

3 n number

4 begin

5 for i in 1..10 loop

6 if I = 1 then

7 x: = 5000000

8 else

9 x: = 1

10 end if

11 select count (object_id) into n from t where id > x

12 end loop

13 end

14 /

PL/SQL procedure successfully completed.

SQL > select

2 sql_id

3, child_number

4, executions

5, parse_calls

6, buffer_gets

7, is_bind_sensitive

8, is_bind_aware

9 from

10 v$sql

11 where

12 sql_id = 'gp03v5aw085v3'

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS IS

-

Gp03v5aw085v3 0 2 3 76405 Y N

Gp03v5aw085v3 1 8 7 517480 Y Y

SQL > select * from table (dbms_xplan.display_cursor ('gp03v5aw085v3',null))

PLAN_TABLE_OUTPUT

-

SQL_ID gp03v5aw085v3, child number 0

-

SELECT COUNT (OBJECT_ID) FROM T WHERE ID >: B1

Plan hash value: 3694077449

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 4 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10 | 4 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | I | 1 | | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-access ("ID" >: B1)

SQL_ID gp03v5aw085v3, child number 1

-

SELECT COUNT (OBJECT_ID) FROM T WHERE ID >: B1

Plan hash value: 2966233522

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 14373 | | |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | * 2 | TABLE ACCESS FULL | T | 4999K | 47m | 14373 (2) | 00:02:53 |

Predicate Information (identified by operation id):

2-filter ("ID" >: B1)

You can see that ACS is already working, and new rows are generated for new cursors in v$sql_cs_histogram.

SQL > SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='gp03v5aw085v3'

4 ORDER BY sql_id, child_number

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

--

3087275875 gp03v5aw085v3 0 1 0

3087275875 gp03v5aw085v3 0 0 1

3087275875 gp03v5aw085v3 0 2 1

3087275875 gp03v5aw085v3 1 1 0

3087275875 gp03v5aw085v3 1 0 0

3087275875 gp03v5aw085v3 1 2 8

At this point, I believe you have a deeper understanding of the "Analysis of the work of ACS and PL/SQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.