Database - RAC/Scalability (MOSC)

MOSC Banner

Transactions hanging after plan changes

edited Sep 16, 2020 3:45PM in Database - RAC/Scalability (MOSC) 12 commentsAnswered

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

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center