Hi experts,
I need a clarification about how INVALIDATIONS and LOADS columns are incremented in V$SQL.
I understand that:
- A cursor is invalidated due to some factors (gather statistics, change tables' structure,...)
- A RELOAD occur after a previous INVALIDATION and a cursor is aged out.
- A RELOAD implies an hard parse
My questions are:
- Are these statements correct?
- Are there some other factor that impact INVALIDATION ? (for example dynamic sampling)
- If I have some child generated due to PQ_SLAVE_MISMATCH, how this impact on INVALIDATIONS and RELOAD? (I know that PQ_SLAVE_MISMATCH is a sintom of incorrect parallel usage and I report this situation at Developers Team)
- Is it possible to have a number of EXECUTIONS much lower than LOADS and INVALIDATIONS? If yes could you explain?
- When a cursor is aged out, is it possible that only some information are purged from library cache? (for example timing informations, such as APPLICATION_WAIT_TIME, ELAPSED_TIME and so on, are purged but RELOAD and INVALIDATIONS are maintained)
These questions are to find a reson for below situation:
SQL> select inst_id,child_number,executions,invalidations,loads,trunc(elapsed_time/1000000) elapsed_secs,
2 trunc(application_wait_time/1000000) application_wait_secs,trunc(concurrency_wait_time/1000000) concurrency_wait_secs,
3 trunc(cluster_wait_time/1000000) cluster_wait_secs,trunc(user_io_wait_time/1000000) user_io_wait_secs,trunc(cpu_time/1000000) cpu_time_secs,rows_processed,plan_hash_value
4 from gv$sql where sql_id = '<sql_id>' and executions > 0 order by inst_id,child_number;
INST_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS LOADS ELAPSED_SECS APPLICATION_WAIT_SECS CONCURRENCY_WAIT_SECS CLUSTER_WAIT_SECS USER_IO_WAIT_SECS CPU_TIME_SECS ROWS_PROCESSED PLAN_HASH_VALUE
---------- ------------ ---------- ------------- ---------- ------------ --------------------- --------------------- ----------------- ----------------- ------------- -------------- ---------------
1 1 1 42 54 4 0 0 0 0 3 1 1990401962
2 1 6 390 762 7 0 0 0 0 3 15 395703849
2 4 2 36 77 11 0 0 0 0 10 2 3669144543
2 7 54 707 1049 31 0 4 0 0 14 345 395703849
3 9 8 0 1 6 0 0 0 0 4 21 1990401962
4 0 19 422 589 15 0 1 0 0 9 73 395703849
Thanks in advance.
- Are these statements correct?Yes.
- Are there some other factor that impact INVALIDATION ? (for example dynamic sampling)Lots of potential factors. Adaptive cursor sharing. Cardinality feedback. Adpative statistics.
- If I have some child generated due to PQ_SLAVE_MISMATCH, how this impact on INVALIDATIONS and RELOAD?
Yes that can impact it. When we generate a new child, it is often because an existing one could not be used so you might see it. An example of this here
https://asktom.oracle.com/pls/apex/asktom.search?tag=invalidation-of-child-cursor - Is it possible to have a number of EXECUTIONS much lower than LOADS and INVALIDATIONS? If yes could you explain?Yes. I can parse a statement and never run it. Often when we open a cursor, we don't actually run it until the first fetch because if the first fetch is not issued, it is not worth executing it.
- When a cursor is aged out, is it possible that only some information are purged from library cache? (for example
timing informations, such as APPLICATION_WAIT_TIME, ELAPSED_TIME and so on, are purged but RELOAD and INVALIDATIONS are maintained)
Yes. A statement sits in a number of different memory structures (heaps). We can purge or age parts out and keep others.