Performance issues caused by inaccurate Oracle statistics (predicate out of bounds)

What is predicate out of bounds? predicate out-of-bounds is the fact that the query condition of the SQL statement exceeds the scope recorded by the database statistics. predicate out-of-bounds causes the Oracle optimizer to incorrectly select the execution plan of the SQL statement, resulting in performance issues.

Here is a simple example of a predicate crossing that causes the optimizer to select the wrong execution plan.

CREATE TABLE T1 (col1 number); CREATE index idx_t1 on T1 (col1); Begin    for inch 1.. 10000 loop  insert into T1 values (i);  End Loop;  Commit;end; /

This creates the T1 table, creates an index on the col1 column, and writes 10,000 data to the table. The T1 table is provided with statistical information that can be used to obtain the predicate condition of the current table T1.

Sql> exec Dbms_stats.gather_table_stats ('Salp','T1'); SQL>SelectLow_value,high_value fromDba_tab_col_statisticswhereTable_name='T1'and owner='Salp'; Low_value High_value---------- ----------C102 C302 SQL>varx number; SQL> Exec Dbms_stats.convert_raw_value ('C102',: x); PL/SQL procedure successfully completed. SQL>Select: X fromdual; : X----------1SQL> Exec Dbms_stats.convert_raw_value ('C302',: x); PL/SQL procedure successfully completed. SQL>Select: X fromdual; : X----------10000

A System package is used to convert the upper and lower bounds of the statistics table to a readable value.

The execution plan for a conditional query within the predicate range is

Explain plan for Select* fromT1whereCol1 between1and10000;Select* fromtable (Dbms_xplan.display); Plan_table_output--------------------------------------------------------------------------------Plan Hash Value:1387720244-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------|0|        SELECT STATEMENT | |10000|40000|7(0)|xx:xx: on|| *1| INDEX FAST Full scan| Idx_t1 |10000|40000|7(0)|xx:xx: on|-------------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------1-Filter ("COL1">=1and"COL1"<=10000) -Rows selected.

Because the conditions here contain all the data in the T1 table, the execution plan with multiple reads and no return table is optimal (table access Full/index fast full scan), which is actually using index fast full scan.

Next, continue writing data to the T1 table

begin    for inch 10001. 10000000 loop  insert into T1 values (i);  End Loop;  Commit;end; /

To check the statistics of a table without re-collecting statistics

Select  from where table_name=' T1' and owner='salp'; Low_value  high_value--------------------C102       C302

Now to make a query that predicates out of bounds, use the predicate condition col1 between 10001 and 10000000. In principle, the statement that chooses table 99.9% data should use a multi-block read and do not return to the table's Execution Plan (table access Full/index fast full scan). Let's have a practical test.

Sql>Settiming on; SQL> Select count (*) fromT1whereCol1 between10001and10000000; COUNT (*)----------9990000Elapsed:xx:xx:11.17SQL>Select* fromTable (Dbms_xplan.display_cursor (NULL,NULL,' Advanced')); Plan_table_output--------------------------------------------------------------------------------sql_id 86kr1tnhns36d, child number0-------------------------------------Select Count (*) fromT1whereCol1 between10001and10000000Plan Hash Value:1970818898----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |----------------------------------------------------------------------------|0|        SELECT STATEMENT |       |       | |2( -)| ||1|        SORT AGGREGATE | |1|4|          | || *2| INDEX RANGE scan| Idx_t1 |1|4|2(0)|xx:xx: on|----------------------------------------------------------------------------

You can see that this statement executes 11s before the result, and the execution plan chooses a single-block read index range scan instead of one of the two execution plans that we expect to read back to the table and the rows and bytes that are returned have a critical estimate error.

We re-collect the statistics for the T1 table, execute the same statement again, and check the execution plan.

Sql> exec Dbms_stats.gather_table_stats ('Salp','T1'); PL/SQL procedure successfully completed. Elapsed:xx:xx:07.92SQL>SelectCOUNT (*) fromT1whereCol1 between10001and10000000; COUNT (*)----------9990000Elapsed:xx:xx:00.31SQL>Select* fromTable (Dbms_xplan.display_cursor (NULL,NULL,' Advanced')); Plan_table_output--------------------------------------------------------------------------------sql_id G47843NV7GSDQ, child number0-------------------------------------SelectCOUNT (*) fromT1whereCol1 between10001and10000000Plan Hash Value:3724264953---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |---------------------------------------------------------------------------|0|      SELECT STATEMENT |       |       | |4434( -)| ||1|      SORT AGGREGATE | |1|6|          | || *2| TABLE ACCESS full|  T1 |    9990k| 57m|4434(2)|xx:xx: on|---------------------------------------------------------------------------

This time out of our desired execution plan (table access full), the estimated rows and bytes are normal, and the statement takes 310ms to run out.

What scenario does the predicate cross-border generally occur in?

1 Temporary tables

This refers to a temporary table on the business rather than the temporary table of the Oracle database itself. In some systems, the foreground table and the background table are created according to the business conditions, the data first enters the foreground table, after processing, it is stored in the background table, and the data of the foreground table is cleaned with the DELETE statement, and the foreground table acts as a temporary table. We know that the default time window for Oracle to automatically collect statistics is 22 to 2 o'clock in the morning on weekdays, or 6 a.m. on weekends to 2 o'clock in the morning next day. In the Automatic collection of Statistics window, the database foreground table is basically no data, or the amount of data is very small, then the resulting statistical information and the actual processing of business data during the day, there is a possibility of predicate cross-border situation.

2 great Watches

Oracle triggers automatic collection of statistics for a table when the amount of data modified in the table exceeds 10% of the total amount of data in the table, assuming a table adds 1w of data per day, and a year later the table becomes 365w data. Then this means that it will take another one months for the table to trigger a job that automatically collects statistical information. The predicate query on this table, especially the query on the self-increment condition such as time and sequence, can occur when the predicate is out of bounds, affecting the optimizer to choose the execution plan correctly.

Performance issues caused by inaccurate Oracle statistics (predicate out of bounds)