• oracle绑定变量测试及性能对比


    1.创建测试数据

    2.查看cursor_sharing的值

    SQL> show parameter cursor_sharing;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing                       string      EXACT

    3.打开SQL追踪

    SQL> alter session set sql_trace=true;
    
    Session altered.
    
    SQL> select value from v$diag_info where name='Default Trace File';
    
    VALUE
    --------------------------------------------------------------------------------
    /oracle/diag/rdbms/monkey/monkey/trace/monkey_ora_26356.trc

    4.执行语句块(使用绑定变量)

    SQL> begin
      2  for x in 1..10000 loop
      3  execute immediate 'select * from monkey.testtable where id=:x' using x;
      4  end loop;
      5  end;
      6  /

    5.关闭SQL追踪

    SQL> alter session set sql_trace=false;
    
    Session altered.

    6.格式化trace文件

    $ cd /oracle/diag/rdbms/monkey/monkey/trace/
    $ tkprof monkey_ora_26356.trc out.txt
    $ more out.txt

    7.结果

    select * 
    from
     monkey.testtable where id=:x
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute  10000      0.03       0.03          0          1          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    10001      0.03       0.03          0          1          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.00       0.01          0          0          0           0
    Execute      4      0.16       0.16          0          0          0           1
    Fetch        2      0.00       0.01          0          0          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        9      0.16       0.19          0          0          0           2
    
    Misses in library cache during parse: 3
    Misses in library cache during execute: 1
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute  10001      0.03       0.03          0          1          0           0
    Fetch        1      0.00       0.00          0         75          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    10004      0.03       0.03          0         76          0           1

    從最後的整體統計可以看到,包括執行產生的遞歸和非遞歸類SQL,總共解析了5次,花費了0.22秒

     8.执行语句块(不适用绑定变量)

    SQL> begin
      2  for x in 1..10000 loop
      3  execute immediate 'select * from monkey.testtable where id ='||x;
      4  end loop;
      5  end;
      6  /

    9.结果

    select * 
    from
     monkey.testtable where id =1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    SQL ID: 2q3s22f8bw5wx Plan Hash: 2994338341
    
    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
      NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) 
    FROM
     (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TESTTABLE") FULL("TESTTABLE") 
      NO_PARALLEL_INDEX("TESTTABLE") */ 1 AS C1, CASE WHEN "TESTTABLE"."ID"=2 
      THEN 1 ELSE 0 END AS C2 FROM "monkey"."TESTTABLE" SAMPLE BLOCK (1.065089 ,
       1) SEED (1) "TESTTABLE") SAMPLESUB
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.00          0         75          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.00       0.00          0         75          0           1
    
    
    select * 
    from
     monkey.testtable where id =2
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    select * 
    from
     monkey.testtable where id =3
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    總這裡可以看到,每一個值都解析了一遍,並且每一個值都要對表進行採樣
    ..............................................
    select * 
    from
     monkey.testtable where id =10000
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          1          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      3      0.69       0.70          0          0          0           1
    Fetch        2      0.00       0.02          0          0          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        7      0.69       0.72          0          0          0           2
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse    20000      9.86       9.91          0      10000          0           0
    Execute  20000      0.19       0.16          0          0          0           0
    Fetch    10000     10.60      10.60          0     750000          0       10000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    50000     20.65      20.68          0     760000          0       10000

    從最後的整體統計可以看到,包括執行產生的遞歸和非遞歸類SQL,總共解析了20002次,花費了21.4秒

  • 相关阅读:
    【bzoj 4710】 [Jsoi2011]分特产
    【bzoj 3622】已经没有什么好害怕的了
    【bzoj 2839】集合计数
    [HAOI2018]染色
    [JSOI2016]扭动的回文串
    [APIO2014]回文串
    【AT987】高橋君
    [CEOI2017]Building Bridges
    [ZJOI2017]树状数组
    [SDOI2017]树点涂色
  • 原文地址:https://www.cnblogs.com/monkey6/p/13475729.html
Copyright © 2020-2023  润新知