Adaptive Cursor Sharing
Tom Kyte, author of many books related to Oracle databases, on his blog he once described the history of the impact of atmospheric precipitation on the speed of SQL queries in the Oracle database. This article is unfortunately no longer available, but I will try to describe this strange story myself.
But what’s the matter? And what do binded variables have in all this?
The database in question is a database copied using the so -called “cold backups”. Each Sunday at night, the base was turned off to make a copy of the files, then in the morning, re -run. This meant that every Monday morning all buffers would be practically empty. So it was a period of large hard pars.
The main user of the application using the base was the person who came to the office usually at 6 am. During work, she used selective queries that returned a very small number of poems. In this case, the optimizer tended to use indexes. When this happened – all later users were happy.
People working in the applications who asked less selective queries – they lived across the street, not far from the office. The weather situation did not affect the time of arrival, so they always appeared at 9 am. If it was raining, they were the first base users and, making cross -sectional, not very selective queries, they made full scanning of tables and thus created a little effective query plan.
When a person who, because of the rain, went through the city for a long time, when she got to work, she had to accept the slow work of the application. In critical cases, she made a phone call to the administrator saying “it’s Monday, it’s raining, you know what to do …”. The administrator restarted the base, after which everything happened much faster and this condition persisted for the rest of the week.
A bit of history
In the earlier than Oracle 11g of the base, each time the SQL query is launched, the same plan was used, regardless of the values of binded variables. In practice, it looked like the first query caused the plan to be determined by the optimizer, which was implemented consistently at subsequent launch of this question. This situation occurred even when this query differed in the values of binded variables.
This is illustrated by an example:
Let’s create a table with the main key and calculate its statistics
CREATE TABLE t AS SELECT rownum AS id, rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 1000;
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
BEGIN
dbms_stats.gather_table_stats(
ownname => user,
tabname => 't',
estimate_percent => 100,
method_opt => 'for all columns size 1'
);
END;
/
The first few table records:
Let’s now follow a few Select instructions with different ID variable values, and look at their query plans:
VARIABLE id NUMBER
EXECUTE :id := 10;
select count(pad) from t where id < :id;
COUNT(PAD)
----------
9
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |
EXECUTE :id := 990;
select count(pad) from t where id < :id;
COUNT(PAD)
----------
989
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |The most important aspect is the fact that as long as the cursor stays in the library’s cache and can be made available, it will be re -used. This happens regardless of whether the use of the plan in the context of the use of completely different variables is optimal or not.
Starting from the Oracle 11G version, the “Adaptive Cursor Sharing” technology was introduced, which caused the generation of various plans for the same query depending on the variable values given.
The optimizer reads them at the time of the first call of the cursor. The terms are then “selectivity” inquiries and more precisely the where.
When the distribution of data in the columns listed in the WHERE clause is uniform, all queries can use the same plan, but if the data distribution is “skewed”, Then the plan is selected by the optimizer When reading the binded variable value, it does not have to be suitable for all possible values of these variables.
It may be that the implementation plan will be appropriate for the variable value read by the optimizer, and completely inefficient for all other values
When do we say that the loudspeakers are “skewed”?
We can talk about “skewed” columns when the data contained in them are unevenly distributed. For example:
We have an Orders table containing 100 million poems. This table contains a column called customer_id. We have a million different customers, with an average customer usually placed several orders, while some (e.g. wholesalers) can submit them to up to several hundred thousand.
In the example above, data in Orders.customer_id is distorted. It can be expected that on average every separate customer_id will have 100 order lines (100 million poems divided by 1 million different customers). In practice, it turns out that some customers have only a few orders, while others have a lot, much more.
This situation is not optimal in terms of performance, because the optimizer bases its implementation plan on statistics. The database believes that SELECT operation should access Order_lines based on an inevitable index on customer_id and statistically read 100 records for each customer. These records can then attach to another table using Nested Loop operation.
But, when he actually receives a million orders for a specific customer, access to the index and connecting a nested loop will work in a very non -optimal way. It would be much better if the optimizer made a decision to fully scan the first table and attached a hash to the second table.
It follows that when the data is “skwed”, the optimal access plan depends on which specific customer we choose!
The Oracle database avoids this problem by gathering “histograms” in columns, thanks to which it knows which values have many poems and which only a few. In most cases, this gives the optimizer the information needed to generate the best plan
Returning, however, to the topic of adaptive cursor sharing
Starting from the Oracle 11.1 version, the following new columns are available in V $ SQL View:
IS_BIND_SENSITIVE not only indicates whether binded variables have been used to generate the implementation plan, but also whether you can use Adaptive Cursor Sharing technology.
Is_bind_aware indicates whether the cursor uses Adaptive Cursor Sharing.
Is_Shareable indicates whether the cursor can be made available. If you set the value n, the cursor will no longer be used again.
This is illustrated by an example
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SELECT sql_id FROM v$sqlarea
WHERE sql_text = 'SELECT count(pad) FROM t WHERE id < :id';
SQL_ID
-------------
asth1mx10aygn
SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable,
plan_hash_value FROM v$sql
WHERE sql_id = 'asth1mx10aygn';
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N N 4725643561
It is interesting when the cursor is performed several times with different values of the binded variable. Two new subordinate cursors will replace it using Adaptive Cursor Sharing:
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable,
plan_hash_value
FROM v$sql WHERE sql_id = 'asth1mx10aygn'
ORDER BY child_number;
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N N 4725643561
1 Y Y Y 2536785427
2 Y Y Y 4725643561
Looking at the plans of execution, as you might have expected, one of the new “children” of the query has a plan on the basis of full scanning of the table, while the other is based on index scanning:
Plan hash value: 4725643561
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID | T |
| 3 | INDEX RANGE SCAN | T_PK |
Plan hash value: 2536785427
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL | T |Adaptive Cursor Sharing is a functionality introduced in the Oracle Database 11G database version. In previous versions, administrators were forced to clean Buffer Cache in a situation where it seemed to them that the base was starting to use the wrong queries plan as a result of reading the values of binded variables. In the Oracle 11G or newer database version, the optimizer will automatically change the implementation plan when it detects “distorted” data. Thanks to Adaptive Cursor Sharing, the base uses many plans to make a query containing binded variables and provides in these
