sql tuning一个例子_sql tuning 怎么做-CSDN博客

本文链接：https://blog.csdn.net/duqiangatom/article/details/8435289

1、sqlplus中 set autotrace traceonly statistics

2、查看一致读、物理读排序等谢谢（执行2遍）如下：

方案1：

HELIOS @ db-1:1521/cslyn >SELECT/*+ use_hash (c b)*/ post_balance + nvl(deal_amount, 0)
2    FROM (SELECT d.site_id,
3                  SUM(decode(deal_type_code, 1, nvl(deal_amount, 0), 2, (-1) * abs(nvl(deal_amount, 0)))) deal_amount
4             FROM deal d
5            WHERE d.log_time BETWEEN trunc(SYSDATE - 3) AND trunc(SYSDATE - 3) + 1 - 1 / 24 / 60 / 60
6              AND d.deal_type_code IN (1, 2)
7              AND d.success_flag = 0
8            GROUP BY site_id) b,
9         --b为第前N天的缴款
10         (SELECT t.site_id, nvl(t.post_balance, 0) post_balance
11             FROM site_daily_settlement t
12            WHERE t.settlement_date = trunc(SYSDATE) - 3 - 1) c
13         --c为第前N+1天日结完后post_balance余额
14   WHERE b.site_id(+) = c.site_id;

已选择20748行。

统计信息
----------------------------------------------------------
        605 recursive calls
        120 db block gets
      22009 consistent gets
      20162 physical reads
      21004 redo size
     274286 bytes sent via SQL*Net to client
      15586 bytes received via SQL*Net from client
       1385 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      20748 rows processed

HELIOS @ db-1:1521/cslyn >/

已选择20748行。

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      21993 consistent gets
      20160 physical reads
          0 redo size
     274286 bytes sent via SQL*Net to client
      15586 bytes received via SQL*Net from client
       1385 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      20748 rows processed

方案2：

HELIOS @ db-1:1521/cslyn >SELECT post_balance + nvl(deal_amount, 0)
2    FROM (SELECT d.site_id,
3                  SUM(decode(deal_type_code, 1, nvl(deal_amount, 0), 2, (-1) * abs(nvl(deal_amount, 0)))) deal_amount
4             FROM deal d
5            WHERE d.log_time BETWEEN trunc(SYSDATE - 3) AND trunc(SYSDATE - 3) + 1 - 1 / 24 / 60 / 60
6              AND d.deal_type_code IN (1, 2)
7              AND d.success_flag = 0
8            GROUP BY site_id) b,
9         --b为第前N天的缴款
10         (SELECT t.site_id, nvl(t.post_balance, 0) post_balance
11             FROM site_daily_settlement t
12            WHERE t.settlement_date = trunc(SYSDATE) - 3 - 1) c
13         --c为第前N+1天日结完后post_balance余额
14   WHERE b.site_id(+) = c.site_id;

已选择20748行。

统计信息
----------------------------------------------------------
         22 recursive calls
          0 db block gets
      11803 consistent gets
      10392 physical reads
          0 redo size
     274286 bytes sent via SQL*Net to client
      15586 bytes received via SQL*Net from client
       1385 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      20748 rows processed

HELIOS @ db-1:1521/cslyn >
HELIOS @ db-1:1521/cslyn >
HELIOS @ db-1:1521/cslyn >/

已选择20748行。
统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      11797 consistent gets
          0 physical reads
          0 redo size
     274286 bytes sent via SQL*Net to client
      15586 bytes received via SQL*Net from client
       1385 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      20748 rows processed

3、对比几种方案间的使用的资源差别，选用好的方案