Analysis of the working situation of ACS and PL/SQL
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!
