在Oracle SQL优化时获取SQL执行计划每步的时间是很棒的方法，在11g 以后可以使用SQL monitor report 工具，但是需要DIAGNOSTIC+TUNING License, 但是Row source statistics并不需要。
Row source statistics是在执行 rowsource（执行计划的一个步骤）期间花费的时间、返回的行数、缓冲区获取数和物理读取和写入以及工作区使用情况的一些统计数据。当启用统计信息收集时，这些统计信息填充在V$SQL_PLAN_STATISTICS和V$SQL_PLAN_STATISTICS_ALL（基于X$QESRSTAT和X$QESRSTATALL）中。此外，如果启用了 sql_trace（10046 事件），那么这些统计信息将作为“STAT #”行内的详细信息记录跟踪文件中，并且可以在 tkprof 报告中作为执行计划查看。
仅当在查询执行之前打开统计信息收集时，才会为查询启用统计信息收集，并且只有在查询完成（有错误的事件）或取消时才能看到该统计信息收集。
Oracle 9i 中引入了Row source statistics，但没有有用的界面以方便的形式查看它们。在 10g 中引入了非常重要的工具 – 函数DBMS_XPLAN.DISPLAY_CURSOR具有许多有用的选项。该工具已成为SQL调优不可或缺的工具。
如何使用
可以通过以下选项之一启用行源统计信息的收集：
1. 将参数statistics_level设置为all（在会话或系统级别）
2. 使用hint gather_plan_statistics运行查询
3. 启用 SQL trace，及event++
采样频率
如果想计算统计信息，必定会涉及到一个采样频率，如果采样过多就会增加SQL的运行时间 。与rowsource statistic采样相关的，Oracle 中有一个隐藏参数 (_rowsource_statistics_sampfreq)，默认为 128。这个参数必须始终设置为 2 的幂。
1, 如果此参数设置为 0，则rowsource statistics中没有时间计算。函数qerstSnapStats() / qerstUpdateStats()不获取时间戳.
2, 如果此参数设置为 1，则始终计算时间。每对qerstSnapStats() / qerstUpdateStats()都获得时间戳
3, 如果此参数设置为 N（默认值为 128），则每 N 个元组将获取时间戳。这意味着只有在某个行源级别上调用qerstSnapStats() / qerstUpdateStats()才会获得时间戳。
4, 有一个特殊值 3 可以只收集行数。
三种方式的使用
1， SQL trace是生成trace文件，在这种情况下只使用_rowsource_statistics_sampfreq。
而对于gather_plan_statistics和statistics_level这两个选项实际上是有区别的。
2， 每当您使用 /*+ gather_plan_statistics */ 提示时，Oracle 都会使用 _rowsource_statistics_sampfreq 参数确定采样计时信息的频率。 _rowsource_statistics_sampfreq 参数默认为 128。将其设置为更高的值将导致 Oracle 的采样次数更少。将其设置为较低的值将导致 Oracle 更频繁地进行采样。
3， 在会话或系统级别将参数statistics_level 设置为“all”，会将另一个隐藏参数参数 _rowsource_execution_statistics 设置为 TRUE，在这种情况下，_rowsource_statistics_sampfreq仅在参数已显示设置时使用，否则会忽略_rowsource_statistics_sampfreq 参数，而使用值为 1。
SQL> show parameter statistics_level
PARAMETER_NAME TYPE VALUE
---------------------------- ----------- --------------
client_statistics_level string TYPICAL
statistics_level string TYPICAL
SQL> @pd rowsource
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- ----------------------------------- - ------------------------------ ----------------------------------------------------------------------------- -
3907 F43 _rowsource_execution_statistics FALSE if TRUE, Oracle will collect rowsource statistics
3908 F44 _rowsource_profiling_statistics TRUE if TRUE, Oracle will capture active row sources in v$active_session_history
3909 F45 _rowsource_statistics_sampfreq 128 frequency of rowsource statistic sampling (must be a power of 2)
4703 125F _olap_adv_comp_stats_max_rows 100000 do additional predicate stats analysis for AW rowsource
4704 1260 _olap_adv_comp_stats_cc_precomp 20 do additional predicate stats analysis for AW rowsource
SQL> alter session set statistics_level=all;
Session altered.
SQL> @pd rowsource
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------- ---------- - -----------------------------------------------------------------------------
3907 F43 _rowsource_execution_statistics TRUE if TRUE, Oracle will collect rowsource statistics
3908 F44 _rowsource_profiling_statistics TRUE if TRUE, Oracle will capture active row sources in v$active_session_history
3909 F45 _rowsource_statistics_sampfreq 128 frequency of rowsource statistic sampling (must be a power of 2)
4703 125F _olap_adv_comp_stats_max_rows 100000 do additional predicate stats analysis for AW rowsource
4704 1260 _olap_adv_comp_stats_cc_precomp 20 do additional predicate stats analysis for AW rowsource
Alexander Anokhin做了一个测试在10G 11g不同频率的一个SQL全表扫时，使用sql trace资源消耗。
| freq | 10g | 11g | ||
| cpu | timer calls | cpu | timer calls | |
| default (128) | 0,66 | 234 392 | 0,65 | 156 262 |
| 0 | 0,59 | 5 | 0,57 | 5 |
| 128 | 0,66 | 234 392 | 0,64 | 156 262 |
| 16 | 1,13 | 1 875 017 | 1,02 | 1 250 015 |
| 8 | 1,64 | 3 750 020 | 1,32 | 2 500 017 |
| 4 | 2,50 | 7 500 019 | 2,02 | 5 000 019 |
| 2 | 4,44 | 15 000 026 | 3,41 | 10 000 025 |
| 1 | 8,18 | 30 000 027 | 6,28 | 20 000 033 |
如果判断这一频率行为可以使用Digger （Dtrace）跟踪call 的调用。函数qer*（ query execute rowsource）与此相关。可能在不同的版本不同的环境会有所不同，下面是在19c on linux 使用pstack简单的看一下函数调用
[oracle@oel7db1 ~]$ ora
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 17 08:20:56 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-anbob19c oel7db1 1 1 13017 19.0.0.0.0 20210717 10643 33 10642 0000000078081028 0000000078CF7F48
SQL> select count(*) from dba_objects, dba_objects,dba_objects;
*
# session 2
[root@oel7db1 oracle]# pstack 13321
#0 0x0000000012565858 in smbget ()
#1 0x0000000012560502 in sorgetqbf ()
#2 0x00000000126dee4e in qersoFetchSimple ()
#3 0x00000000126db192 in qersoFetch ()
#4 0x0000000003cd80ba in qerjoCartesianFetch ()
#5 0x00000000126e56e3 in qergsFetch ()
#6 0x00000000124042a6 in opifch2 ()
#7 0x0000000002d64d98 in kpoal8 ()
#8 0x000000001240ad02 in opiodr ()
#9 0x00000000127bdb56 in ttcpip ()
#10 0x00000000026d7987 in opitsk ()
#11 0x00000000026dc1c8 in opiino ()
#12 0x000000001240ad02 in opiodr ()
#13 0x00000000026d3466 in opidrv ()
#14 0x0000000003185685 in sou2o ()
#15 0x0000000000daa016 in opimai_real ()
#16 0x0000000003191821 in ssthrdmain ()
#17 0x0000000000da9e40 in main ()
SQL> select /*+gather_plan_statistics*/ count(*) from dba_objects, dba_objects,dba_objects;
# session 2
[root@oel7db1 oracle]# pstack 13321
#0 0x00000000126def1e in qersoFetchSimple ()
#1 0x00000000126db192 in qersoFetch ()
#2 0x0000000008c327b1 in qerstFetch ()
#3 0x0000000003cd80ba in qerjoCartesianFetch ()
#4 0x0000000008c327b1 in qerstFetch ()
#5 0x00000000126e56e3 in qergsFetch ()
#6 0x0000000008c327b1 in qerstFetch ()
#7 0x00000000124042a6 in opifch2 ()
#8 0x0000000002d64d98 in kpoal8 ()
#9 0x000000001240ad02 in opiodr ()
#10 0x00000000127bdb56 in ttcpip ()
#11 0x00000000026d7987 in opitsk ()
#12 0x00000000026dc1c8 in opiino ()
#13 0x000000001240ad02 in opiodr ()
#14 0x00000000026d3466 in opidrv ()
#15 0x0000000003185685 in sou2o ()
#16 0x0000000000daa016 in opimai_real ()
#17 0x0000000003191821 in ssthrdmain ()
#18 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# pstack 13321
#0 0x0000000008c313d4 in qerstSnapStats ()
#1 0x0000000008c324a9 in qerstRowP ()
#2 0x00000000126def0c in qersoFetchSimple ()
#3 0x00000000126db192 in qersoFetch ()
#4 0x0000000008c327b1 in qerstFetch ()
#5 0x0000000003cd80ba in qerjoCartesianFetch ()
#6 0x0000000008c327b1 in qerstFetch ()
#7 0x00000000126e56e3 in qergsFetch ()
#8 0x0000000008c327b1 in qerstFetch ()
#9 0x00000000124042a6 in opifch2 ()
#10 0x0000000002d64d98 in kpoal8 ()
#11 0x000000001240ad02 in opiodr ()
#12 0x00000000127bdb56 in ttcpip ()
#13 0x00000000026d7987 in opitsk ()
#14 0x00000000026dc1c8 in opiino ()
#15 0x000000001240ad02 in opiodr ()
#16 0x00000000026d3466 in opidrv ()
#17 0x0000000003185685 in sou2o ()
#18 0x0000000000daa016 in opimai_real ()
#19 0x0000000003191821 in ssthrdmain ()
#20 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# pstack 13321
#0 0x0000000008c3148a in qerstUpdateStats ()
#1 0x0000000008c32230 in qerstRowP ()
#2 0x00000000126def0c in qersoFetchSimple ()
#3 0x00000000126db192 in qersoFetch ()
#4 0x0000000008c327b1 in qerstFetch ()
#5 0x0000000003cd80ba in qerjoCartesianFetch ()
#6 0x0000000008c327b1 in qerstFetch ()
#7 0x00000000126e56e3 in qergsFetch ()
#8 0x0000000008c327b1 in qerstFetch ()
#9 0x00000000124042a6 in opifch2 ()
#10 0x0000000002d64d98 in kpoal8 ()
#11 0x000000001240ad02 in opiodr ()
#12 0x00000000127bdb56 in ttcpip ()
#13 0x00000000026d7987 in opitsk ()
#14 0x00000000026dc1c8 in opiino ()
#15 0x000000001240ad02 in opiodr ()
#16 0x00000000026d3466 in opidrv ()
#17 0x0000000003185685 in sou2o ()
#18 0x0000000000daa016 in opimai_real ()
#19 0x0000000003191821 in ssthrdmain ()
#20 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# pstack 13321|sh os_explain.sh
kpoal8
SELECT FETCH:
QUERY EXECUTION STATISTICS: Fetch
GROUP BY SORT: Fetch
QUERY EXECUTION STATISTICS: Fetch
NESTED LOOP OUTER: CartesianFetch
QUERY EXECUTION STATISTICS: Fetch
SORT: Fetch
SORT: FetchSimple
QUERY EXECUTION STATISTICS: RowP
QUERY EXECUTION STATISTICS: RowP
QUERY EXECUTION STATISTICS: SnapStats
SQL> alter session set statistics_level=all;
Session altered.
SQL> select count(*) from dba_objects, dba_objects,dba_objects;
# session 2
[root@oel7db1 oracle]# pstack 17757
#0 0x0000000012723398 in sltrgftime64 ()
#1 0x0000000008c3134a in qerstSnapStats ()
#2 0x0000000008c324a9 in qerstRowP ()
#3 0x0000000008c32461 in qerstRowP ()
#4 0x00000000126def0c in qersoFetchSimple ()
#5 0x00000000126db192 in qersoFetch ()
#6 0x0000000008c327b1 in qerstFetch ()
#7 0x0000000003cd80ba in qerjoCartesianFetch ()
#8 0x0000000008c327b1 in qerstFetch ()
#9 0x00000000126e56e3 in qergsFetch ()
#10 0x0000000008c327b1 in qerstFetch ()
#11 0x00000000124042a6 in opifch2 ()
#12 0x0000000002d64d98 in kpoal8 ()
#13 0x000000001240ad02 in opiodr ()
#14 0x00000000127bdb56 in ttcpip ()
#15 0x00000000026d7987 in opitsk ()
#16 0x00000000026dc1c8 in opiino ()
#17 0x000000001240ad02 in opiodr ()
#18 0x00000000026d3466 in opidrv ()
#19 0x0000000003185685 in sou2o ()
#20 0x0000000000daa016 in opimai_real ()
#21 0x0000000003191821 in ssthrdmain ()
#22 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# pstack 17757
#0 0x0000000008c31470 in qerstUpdateStats ()
#1 0x0000000008c32230 in qerstRowP ()
#2 0x0000000008c32461 in qerstRowP ()
#3 0x00000000126def0c in qersoFetchSimple ()
#4 0x00000000126db192 in qersoFetch ()
#5 0x0000000008c327b1 in qerstFetch ()
#6 0x0000000003cd80ba in qerjoCartesianFetch ()
#7 0x0000000008c327b1 in qerstFetch ()
#8 0x00000000126e56e3 in qergsFetch ()
#9 0x0000000008c327b1 in qerstFetch ()
#10 0x00000000124042a6 in opifch2 ()
#11 0x0000000002d64d98 in kpoal8 ()
#12 0x000000001240ad02 in opiodr ()
#13 0x00000000127bdb56 in ttcpip ()
#14 0x00000000026d7987 in opitsk ()
#15 0x00000000026dc1c8 in opiino ()
#16 0x000000001240ad02 in opiodr ()
#17 0x00000000026d3466 in opidrv ()
#18 0x0000000003185685 in sou2o ()
#19 0x0000000000daa016 in opimai_real ()
#20 0x0000000003191821 in ssthrdmain ()
#21 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# pstack 17757
#0 0x00007fffc01b6bc6 in clock_gettime ()
#1 0x00007f17f01907ed in clock_gettime () from /lib64/libc.so.6
#2 0x0000000012723398 in sltrgftime64 ()
#3 0x0000000008c315ef in qerstUpdateStats ()
#4 0x0000000008c32230 in qerstRowP ()
#5 0x00000000126def0c in qersoFetchSimple ()
#6 0x00000000126db192 in qersoFetch ()
#7 0x0000000008c327b1 in qerstFetch ()
#8 0x0000000003cd80ba in qerjoCartesianFetch ()
#9 0x0000000008c327b1 in qerstFetch ()
#10 0x00000000126e56e3 in qergsFetch ()
#11 0x0000000008c327b1 in qerstFetch ()
#12 0x00000000124042a6 in opifch2 ()
#13 0x0000000002d64d98 in kpoal8 ()
#14 0x000000001240ad02 in opiodr ()
#15 0x00000000127bdb56 in ttcpip ()
#16 0x00000000026d7987 in opitsk ()
#17 0x00000000026dc1c8 in opiino ()
#18 0x000000001240ad02 in opiodr ()
#19 0x00000000026d3466 in opidrv ()
#20 0x0000000003185685 in sou2o ()
#21 0x0000000000daa016 in opimai_real ()
#22 0x0000000003191821 in ssthrdmain ()
#23 0x0000000000da9e40 in main ()
[root@oel7db1 oracle]# for i in {1..10} ; do pstack 17757 |awk '{print $4}' ; usleep 100000 ; done | sort -r | uniq -c
10 ttcpip
10 ssthrdmain
10 sou2o
1 sorgetqbf
3 sltrgftime64
1 rworupo
4 qerstUpdateStats
3 qerstSnapStats
12 qerstRowP
30 qerstFetch
10 qersoFetchSimple
10 qersoFetch
10 qerjoCartesianFetch
10 qergsFetch
10 opitsk
20 opiodr
10 opimai_real
10 opiino
10 opifch2
10 opidrv
10 main
10 kpoal8
6 clock_gettime
— enjoy —