Oracle 关于V $ SQL中的负载和无效的澄清

嗨，专家们，
我需要澄清如何在V $ SQL中增加无效和负载列。
我明白:
-由于某些因素 (收集统计信息，更改表的结构，...)，游标无效。
-在上一次失效后重新加载，光标老化。
-重新加载意味着硬解析

我的问题是:
-这些说法正确吗？
-还有其他影响无效的因素吗？(例如动态采样)
-如果由于PQ_SLAVE_MISMATCH而生成了一些孩子，这对失效和重新加载有何影响？(我知道PQ_SLAVE_MISMATCH是不正确的并行使用的原因，我在开发人员团队中报告了这种情况)
-执行次数是否可能比加载和无效次数低得多？如果是的话，你能解释一下吗？
-光标老化后，是否可能仅从库缓存中清除某些信息？(例如，定时信息，如APPLICATION_WAIT_TIME、ELAPSED_TIME等，被清除，但重新加载和失效被保持)

这些问题是为以下情况找到答案:

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 = '' 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

复制

提前谢谢。

- 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?


是的，这会影响它。当我们生成一个新的孩子时，通常是因为无法使用现有的孩子，所以您可能会看到它。这里的一个例子

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?

是的。 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)


是的。 A statement sits in a number of different memory structures (heaps). We can purge or age parts out and keep others.