Row source statistics执行计划的统计信息

首页 » ORACLE [C]系列, ORACLE 9i-23c » Row source statistics执行计划的统计信息

Row source statistics执行计划的统计信息

2021/07/17
ORACLE [C]系列, ORACLE 9i-23c
145 views
Row source statistics执行计划的统计信息已关闭评论

在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
freq	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 —

打赏

Row source statistics

对不起，这篇文章暂时关闭评论。

上一篇： Troubleshooting LGWR waits for event ‘DLM cross inst call completion’ 案例

下一篇： Troubleshooting: Oracle 19c wait event ‘latch: MGA shared context root latch’

ANBOB™

Row source statistics执行计划的统计信息

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号