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

This article introduces the relevant knowledge of "what are the performance problems of the database?" in the operation of actual cases, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Predicate out of bounds often occurs when the where predicate is a time field. Generally speaking, the statistics record an old time, while the time passed in by SQL is the latest time range (usually select min (object_ID), dump (min (object_id), 16) from test_obj MIN (OBJECT_ID) DUMP (MIN (OBJECT_ID), 16)-2 Typ=2 Len=2: C1 Typ=2 Len=2 3-- C103

If the statistics are not collected, the statistics of the statistics column are too old and the HIGH_VALUE is still the original value of 78179.

TEST@PROD1 > select low_value, high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST' Distinct Number LOW_VALUE HIGH_VALUE Values Nulls -C103 C3085250 72462 (original) 0

The query result returns a result set of 2081 rows.

TEST@PROD1 > select count (*) from test_obj where object_id between 78200 and 81000 COUNT (*)-2801 is calculated as follows: selectivity= ((VAL2-VAL1) / (HIGH_VALUE-LOW_VALUE) + 2 / NUM_DISTINCT) * null_adjust null_adjust= (NUM_ROES-NUM_NULLS) / NUM_ROES: TEST@PROD1 > select round ((81000-78200) / (100000-2) + 2gam94283) * (94283-0) / 94283) from dual ROUND ((81000-78200) / (100000-2) + 2max 94283) * (94283-0) / 94283 / 94283)-2642

Looking at the result set, it is found that the dictionary value is 1, which is obviously a wrong execution plan. Because the statistics are too old and lower than the predicate condition interval (predicate out of bounds), CBO underestimates the query cost.

TEST@PROD1 > select count (*) from test_obj where object_id between 78200 and 81000 Execution Plan-Plan hash value: 2217143630- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 5 | 289 (1) | 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 5 | * 2 | TABLE ACCESS FULL | TEST_OBJ | 1 | 5 | 289 (1) | 00:00:04 |- -Predicate Information (identified by operation id):- -2-filter ("OBJECT_ID" > = 78200 AND "OBJECT_ID" exec dbms_stats.gather_table_stats ('test') 'test_obj') TEST@PROD1 > select low_value, high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST' Distinct Number LOW_VALUE HIGH_VALUE Values Nulls-C103 C30B 94283 0

At this point, the statistical information HIGH_VALUE is equal to the originally calculated value, Typ=2 Len=2: C3. Looking at the execution plan again, CBO has been able to produce the correct execution plan.

The implementation plan is:

TEST@PROD1 > select count (*) from test_obj where object_id between 78200 and 81000 Execution Plan-Plan hash value: 2217143630- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 5 | 314 (1) | 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 5 | * 2 | TABLE ACCESS FULL | TEST_OBJ | 2642 | 13210 | 314 (1) | 00:00:04 |- -Predicate Information (identified by operation id):- -2-filter ("OBJECT_ID" > = 78200 AND "OBJECT_ID")

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.