Interesting this topic, because It's very very specific. So, the best thing is know how to do that because the chance of having it in the exam is 100%.
"When statistics are updated for a database object, Oracle Database invalidates any currently parsed SQL statements that access the object. The next time such a statement executes, the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics." -> Oracle Docs
All the DBMS_STATS.GATHER procedures that have the "no_invalidate" option, where you can decide if you will invalidate the cursors or not uppon gathering new tables statistics. You can also use the procedure DBMS_STATS.SET_TABLE_PREFS to define a default value.
Play querying v$sql on columns (INVALIDATIONS,OBJECT_STATUS) and checking what happens when you gather the stats with both values for the "no_invalidate" parameter.
P.S: One quick way to change this behaviour (if allowed) is using Enterprise Manager instead of writing a PL/SQL block code.
Path to Documentation:
PL/SQL Packages and Types Reference -> 141 DBMS_STATS
Click here to go back to the Main OCM 11g Preparation page.
Have you enjoyed? Please leave a comment or give a 👍!
