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

Today, we encountered a production problem. The business SQL is very simple, with a single table query, and the table has only three fields, there is a primary key ID, and through the primary key ID filtering, the business page will send more than 100 ID to call SQL. The data size of this table is more than 1 million, but this SQL execution has run for 15 seconds, which completely affects the business and cannot be used.

Select a dint bjorn c from t where t.id in (1111222333444555.)

I didn't check the watch design at first, but looked directly at the execution plan.

1 alter session set statistics_level=all

2 execute SQL

3 select * from table (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST'))

The execution plan goes directly to the full table scan, and there is a pile of information filtered by predicates.

To_number (t.id) = 1111 or to_number (t.id) = 2222.

See here immediately guess the specific problem lies, query T table ID field, is VARCHAR2 type, and SQL in is a numeric type, SQL is implicitly converted directly, can not go to the index, directly went to the whole table. This problem was found later, and it was also because the business did not pay attention, and the performance problem became increasingly prominent after the increasing number of table data.

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.