Oracle 11.2 auto optimizer stats collection
After upgrading from oracle 10 to oracle 11.2, we noticed performance drop on a select statement. An oracle process was using 100% of the CPU time. It was tracked to the following statement.
SELECT C1, C2, C3, C4 from table1 WHERE C3 = 'READY' AND C4 = 1;
SELECT C1, C2, C3, C4 from table1 WHERE C3 = 'READY' AND C4 = 1;
Please note: C3 has only 20 different values and C4 has only 4.
Execution of explain when the performace is bad:
SQL> explain plan for SELECT C1, C2, C3, C4 from table1 WHERE C3 = 'READY' AND C4 = 1;
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'TYPICAL'));
Plan hash value: 310948982
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
1