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

Selection rate and hard parsing

As we mentioned above, in the v$sql_cs_histogram view, if the count in both of the three buckets of the cursor has a non-zero value, then the subsequent parsing will pry into the value of the bound variable to calculate the predicate selection rate. If the calculation selection rate is not within the range of the existing cursor selection rate, a new cursor will be reparsed based on the value of the snooped bound variable. Of course, the execution plan for this new cursor may be the same as before. Let's take a look at an example and we will understand this mechanism very well.

SQL > create table t as select 1 id,a.* from dba_objects a dbathing objects b where rownumcreate index t_ind on t (id)

Index created.

SQL > insert into t select 2, a. * from dba_objects, a, b where rownuminsert into t select, 3, a. * from dba_objects, a, from dba_objects, b where rownuminsert into t select, 5, and, a. * from dba_objects, and

Commit complete.

SQL > begin

2 dbms_stats.gather_table_stats (user

3't'

4 method_opt = > 'for columns status size 5'

5 cascade = > true)

six

7 end

8 /

SQL > select id,count (*) from t group by id order by id

ID COUNT (*)

--

1 9

2 999

3 9999

4 99999

5 999999

The above code carefully constructs an example, the id field on table t has a total of five unique values, the number of each value is different, there is an index on the id field, and the histogram is analyzed. In this case, if we directly use character variables instead of binding variables, id will use index scanning when querying 1Magazine 2Magne3 and 4. In this case, the cost of index scanning is lower than that of full table scanning, and id will use full table scanning when querying 5. In this case, the cost of full table scanning is lower than that of index scanning. In the following table, I use the explain tool to get the cost of each execution plan after using text variables. We can see that the cost of the full table scan is 2911, when querying idvar a number.

SQL > exec: a: = 1

PL/SQL procedure successfully completed.

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

nine

SQL > exec: a: = 5

PL/SQL procedure successfully completed.

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

999999

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

999999

SQL > col PREDICATE for A10

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number; 4

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

--

56g5zg95hcxc1 1 = A 0 0.810076 0.990093

After a series of operations above, we have made this cursor become bind aware, and how to make SQL become bind aware we have already discussed above, and we will not elaborate here. After these steps, the optimizer has produced a new cursor with child_number 1, which is generated based on the value of the binding variable 5, with predicate selections ranging from 0.810076 to 0.990093. The relationship between this selection rate and the selection rate provided in our table above is: (0.8100760.990093) / 2 is approximately equal to the selection rate provided in our table above. 900085058 Oracle allows a little bit of leeway for selection, which is good. Let's execute a query with an id of 1 to see:

SQL > exec: a: = 1

PL/SQL procedure successfully completed.

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

nine

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

--

56g5zg95hcxc1 1 = A 0 0.810076 0.990093

56g5zg95hcxc1 2 = A 0 0.000007 0.000009

A subcursor with a child_number of 2 has been generated based on a value of id 1 with a selection rate ranging from 0.000007 to 0.000009. At this critical point in this section, let's query id 4 again to see what happens.

SQL > exec: a: = 4

PL/SQL procedure successfully completed.

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

99999

SQL >

SQL > col PREDICATE for A10

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

--

56g5zg95hcxc1 1 = A 0 0.810076 0.990093

56g5zg95hcxc1 2 = A 0 0.000007 0.000009

56g5zg95hcxc1 3 = A 0 0.000007 0.099008

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware,IS_SHAREABLE

3 FROM v$sql

4 WHERE sql_id='56g5zg95hcxc1'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

0 2 13690 Y N N

1 1 13162 Y Y Y

2 1 4 Y Y N

3 1 1495 Y Y Y

The optimizer has regenerated a subcursor with child_numer 3, and the range of selections has been expanded, from 0.000007 to 0.099008, that is, from id 1 to 4 are now included in subcursors with child_number 3. The child cursor with a child_number of 2 has been marked as invalid for sharing, and this piece of memory can be cleared if the shared pool is in short supply. Does that mean that when we query for a value with an id of 3, we will not have to regenerate a new cursor, but can use a subcursor with a child_number of 3? Let's take a look:

SQL > exec: a: = 3

PL/SQL procedure successfully completed.

SQL > col PREDICATE for A10

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

--

56g5zg95hcxc1 1 = A 0 0.810076 0.990093

56g5zg95hcxc1 2 = A 0 0.000007 0.000009

56g5zg95hcxc1 3 = A 0 0.000007 0.099008

SQL > select count (object_id) from t where id=:a

COUNT (OBJECT_ID)

-

9999

SQL >-selection rate

SQL > SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

--

56g5zg95hcxc1 1 = A 0 0.810076 0.990093

56g5zg95hcxc1 2 = A 0 0.000007 0.000009

56g5zg95hcxc1 3 = A 0 0.000007 0.099008

SQL > SELECT child_number, executions, buffer_gets, is_bind_sensitive

2 is_bind_aware,IS_SHAREABLE

3 FROM v$sql

4 WHERE sql_id='56g5zg95hcxc1'

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

0 2 13690 Y N N

1 1 13162 Y Y Y

2 1 4 Y Y N

3 2 1495 Y Y Y

No new subcursors have been regenerated, and the number of times of execution of subcursors with child_number 3 in v$sql has been increased by 1.

We can know from the above example that in the v$sql_cs_histogram view, if the count in both buckets in the three buckets of the cursor has a non-zero value, then the subsequent parsing will pry into the value of the binding variable to calculate the predicate selection rate. If the calculation selection rate is not within the range of the existing cursor selection rate, a new cursor will be reparsed based on the value of the snooped bound variable. Record the range of selections that this cursor can represent, and of course, as we can see in our example, the execution plan of the new cursor may be the same as before, but the range of selections is wider.

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.