Transactions hanging after plan changes
Hi Experts,
I'm having some troubles with a RAC 11.2.0.4 database after running this statistics update job:
SQL> BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;
I've a schedule to run this job weekly in this database and in other databases, but here, every time that this schedule runs (or the Automated Database Maintenance Tasks updating specific tables), some queries suddenly increase the response time. Check the query bellow, that at regular execution, in the worst-case scenario gives ~55000ms, and after stats update gets something like 1.8E+10 ms for Query Time:
´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´
````````` GV$SQL During regular performance `````````````````````````````
´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´
SQL> select sql_id, PLAN_HASH_VALUE, CHILD_NUMBER