SQL调优公式T＝S/V

最新推荐文章于 2024-04-30 15:35:14 发布
最新推荐文章于 2024-04-30 15:35:14 发布
阅读量2.3k 收藏
点赞数
分类专栏： SQL--(探讨) 文章标签： sql 数据库相关 disk object 工作 session

比较愚钝，从事调优工作这么些年，处理过的性能问题也不少，也就总结出这么个公式来

 

S指sql所需访问的资源总量，V指sql单位时间所能访问的资源量，T自然就是SQL执行所需时间了

 

这个公式很简单，调优的目标是减少T，T＝S/V，那么我们要调的对象自然就要放到这里的S和V上，调优的方法就是围绕减少S和增大V

 

增大V（单位时间所能访问的资源量），在硬件设备不变的情况下所能做的只有充分利用现有资源，如：通过调整SGA充分利用物理MEMORY，通过并行处理充分利用CPU，通过调整IO分布充分利用硬盘处理能力，还有。。。。升级设备？

 

减少S（sql所需访问的资源总量），这通常是调优工作的重中之重，SQL调优的主要目的就是围绕着如何减少S在进行，在ORACLE中，所需访问的资源以block记，一条SQL执行所读写的block数直接影响到SQL的执行时间，如何知道SQL执行所读写的block数呢，简单的方法如下：

set autotrace traceonly

SQL> select count(*) from test;

 

已用时间:  00: 00: 10.01

 

执行计划

----------------------------------------------------------

Plan hash value: 1950795681

 

-------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 | 19342   (1)| 00:03:53 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST |  5285K| 19342   (1)| 00:03:53 |

-------------------------------------------------------------------

 

 

统计信息

----------------------------------------------------------

        204  recursive calls

          0  db block gets

      70655  consistent gets

      70616  physical reads

          0  redo size

        422  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

通过上面的信息我们可以看到此SQL访问的block数为70655，并且基本上是物理读，其执行时间为00: 00: 10.01

 

大家可能一眼就看出来了，这条SQL执行了全表扫描，加索引优化就可以了，没错，索引访问正是减少SQL所需访问资源的一个主要途径

其效果也很明显

已用时间:  00: 00: 01.89

 

执行计划

----------------------------------------------------------

Plan hash value: 826211483

 

---------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT      |         |     1 |  3089   (2)| 00:00:38 |

|   1 |  SORT AGGREGATE       |         |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| I_TEST1 |  5285K|  3089   (2)| 00:00:38 |

---------------------------------------------------------------------

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      11218  consistent gets

      11197  physical reads

          0  redo size

        422  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

其访问的block数变为11218，其执行时间随之变为00: 00: 01.89

 

通过减少S，我们可以看到T得到明显的减小

 

上面情况是在V不变的情况下（都是物理读）的差别

 

再看看V最大化的结果

 

SQL>  select  count(*) from test;

 

执行计划

----------------------------------------------------------

Plan hash value: 826211483

 

---------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT      |         |     1 |  3089   (2)| 00:00:38 |

|   1 |  SORT AGGREGATE       |         |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| I_TEST1 |  5285K|  3089   (2)| 00:00:38 |

---------------------------------------------------------------------

 

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      11218  consistent gets

          0  physical reads

          0  redo size

        422  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

已用时间:  00: 00: 00.79

 

SQL>  select /*+parallel(test 2)*/ count(*) from test;

 

 

执行计划

----------------------------------------------------------

Plan hash value: 826211483

 

---------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT      |         |     1 |  3089   (2)| 00:00:38 |

|   1 |  SORT AGGREGATE       |         |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| I_TEST1 |  5285K|  3089   (2)| 00:00:38 |

---------------------------------------------------------------------

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      11218  consistent gets

          0  physical reads

          0  redo size

        422  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

已用时间:  00: 00: 00.68

 

T被控制到了秒以下。

 

可是对于这样的一个业务11218个block的资源需求量是否也不是很合理呢，如果这个业务发生的很频繁，每次执行都需要重复扫描到相同资源并且这部分资源占总资源的绝大部分，这肯定也是很不合理的。

既然是减少S，那么减少这些重复访问的绝大部分资源理应得到更好的效果。

以上面的业务为例：

本业务的需求是实时统计表内的行数，数据都是具有生命周期的，通常情况下一个业务表内的数据大多是处于不活动状态，如此以来，预先统计好这部分数据并形成结果，每次需要统计这部分数据时直接调用结果必将大幅减少业务所需访问的资源

如本例，已知object_id小于等于13000的数据处于不活动状态

构建分析结果表

create table test_analyzed as select count(*) o_count，13000 as o_data from test where object_id<=13000;

 

SQL> select o_count from test_analyzed;

 

   O_COUNT

----------

   5242624

 

已用时间:  00: 00: 00.00

 

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        421  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> select count(*) from test where object_id>13000;

 

  COUNT(*)

----------

     42624

 

已用时间:  00: 00: 00.01

 

 

执行计划

----------------------------------------------------------

Plan hash value: 3544821501

 

---------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |     1 |     5 |   159   (1)| 00:00:02 |

|   1 |  SORT AGGREGATE   |         |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| I_TEST1 | 73774 |   360K|   159   (1)| 00:00:02 |

---------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID">13000)

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         98  consistent gets

          0  physical reads

          0  redo size

        421  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

S变成了3＋98＝101，T自然也就变成不到10毫秒了

 

当然现实工作中，业务需求不可能就可以如此简单的加以分解，这里只是提供一个思路

 

性能调优是产品全生命周期的工作，一条sql的调优很可能会追溯到产品需求的定义及产品设计等环节

而在调优工作中V的提升通常是不可持续的，一定程度的提升后再想有所提高是需要付出财力的，S的提升却是很有潜力可挖的。

 

昨天,客户来了需求要对2007年-2008年的数据进行统计,先

 

1.将本番数据库相关数据导入本地环境

 

2.先按需求写出SQL文如下

/* Formatted on 2008/01/22 12:30 (Formatter Plus v4.8.5) */

SELECT SUM (d.mileage) / MAX (ROWNUM)

  FROM (SELECT c.vinno, c.orderno, c.mileage, c.srvdate,

               ROW_NUMBER () OVER (PARTITION BY c.vinno ORDER BY c.vinno) rn

          FROM (SELECT DISTINCT a.vinno, a.orderno, a.mileage, a.srvdate

                           FROM a,

                                 b,

                                 c

                          WHERE a.dealercode = b.dealercode

                            AND a.orderno = b.orderno

                            AND a.datafrom = b.datafrom

                            AND a.vinno = c.vinno

                            AND TO_CHAR (srvdate, 'yyyy-mm-dd')

                                   BETWEEN '2007-01-01'

                                       AND '2007-12-31'

                            AND NVL (a.deleteflag, '0') != '1'

                            AND NVL (b.deleteflag, '0') != '1'

                            AND b.partscode = 'aaa'

                            AND a.dealercode = 'bbb'

                            AND c.model = 'ccc'

                       ORDER BY srvdate) c) d

WHERE d.rn = '2';

 

3.根据执行计划对model 字段建索引

 

4.对每张表进行表分析

 

5.因为要多次执行此SQL文以获得不同的统计数据(上万次),且每张表的数据量也很大,本人用JAVA写了个程序按客户要求循环执行以上SQL文生成数据报表,执行速度不能忍受,

 

6.建立物化视图

DBMS_MVIEW.EXPLAIN_REWRITE()

CREATE MATERIALIZED VIEW  SPK_PARTS_mv

ENABLE QUERY REWRITE

AS

select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate

from A,  B,  C

where a.dealercode = b.dealercode

and a.orderno = b.orderno

and a.DATAFROM = b.DATAFROM

and a.vinno = c.vinno

and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'

and nvl(a.deleteflag, '0') != '1'

and nvl(b.deleteflag, '0') != '1'

 

 

7.建立后发现并不能应用到SQL文中,速度仍然很慢,删除此视图,用如下重建

DBMS_MVIEW.EXPLAIN_REWRITE()

CREATE MATERIALIZED VIEW  SPK_PARTS_mv

ENABLE QUERY REWRITE

AS

select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate,a.dealercode, b.partscode, c.model

from  A,  B,  C

where a.dealercode = b.dealercode

and a.orderno = b.orderno

and a.DATAFROM = b.DATAFROM

and a.vinno = c.vinno

and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'

and nvl(a.deleteflag, '0') != '1'

and nvl(b.deleteflag, '0') != '1'

 

 

8.改写SQL如下

select  sum(d.mileage)/max(rownum)

from (select c.vinno, c.orderno, c.MILEAGE, c.srvdate,

row_number() over(Partition By c.vinno order by c.vinno) rn

from

(select * from (select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate,a.dealercode, b.partscode, c.model

from  A,  B,  C

where a.dealercode = b.dealercode

and a.orderno = b.orderno

and a.DATAFROM = b.DATAFROM

and a.vinno = c.vinno

and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'

and nvl(a.deleteflag, '0') != '1'

and nvl(b.deleteflag, '0') != '1') h

where h.dealercode = 'aaa'

and h.MODEL = 'bbb'

and h.partscode = 'ccc'

order by h.srvdate) c) d

where d.rn = '1'

 

9.此时再执行SQL可以看到数据已从建立的视图中获得,速度可以接受,

 

 

10.在执行程序的时候遇到新的问题,用如下SQL文根踪查看,发现开始很快,可是到后面每一个SQL文都执行的很慢

select /*+ordered*/

sql_text

from v$sqltext a

where (a.hash_value, a.address) in (

select decode (sql_hash_value,

0, prev_hash_value,

sql_hash_value

),

decode (sql_hash_value, 0, prev_sql_addr, sql_address)

from v$session b

where b.sid = '12')

order by piece asc;

查看v$session_wait,查看等待,db file sequential read DB,

因为这是本机的随便建的一个数据库,参数都设的比较小,

 

11.执行

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

查看发现比较正常

接下来执行

SELECT name,value

　　FROM V$SYSSTAT

　　WHERE name IN ('db block gets','consistent gets','physical reads');

用如下公式:

命中率=1-physical reads/(dbblock gets+consistent gets)

计算发现很低,分析为db_cache_size过小导致

将其增大,重启数据库,再执行正常

 

来源：http://space.itpub.net/27378/viewspace-157789

叶子
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
====sql调优============
10-28
Oracle9i 简要SQL语句调整  SQL语句优化方法  优化目标  最佳实例  SQL语句优化技巧  使用EXISTS和IN
我的SQL调优公式T＝S/V
cujiaowu9180的博客
01-23 83
比较愚钝，从事调优工作这么些年，处理过的性能问题也不少，也就总结出这么个公式来 S指sql所需访问的资源总量，V指sql单位时间所能访问的资源量，T自然就是SQL执行所需时间了 这个公式很简单，调优的目标是减少T，T...
SQL优化的一些总结
weixin_30706691的博客
12-20 87
转http://www.taobaodba.com/html/851_sql优化的一些总结.html SQL的优化是DBA日常工作中不可缺少的一部分，记得在学生时期，曾经在ITPUB上看到一篇帖子，当时楼主在介绍SQL优化的时候，用一个公式来讲解他在做sql优化的时候遵循的原则： T=S/V(T代表时间，S代表路程，V代表速度) S指SQL所需访问的资源总量，V...
轉貼 [精华] 我的SQL调优公式T＝S/V
csqm87956的博客
06-14 112
我的SQL调优公式T＝S/V 比较愚钝，从事调优工作这么些年，处理过的性能问题也不少，也就总结出这么个公式来S指sql所需访问的资源总量，V指sql单位时间所能访问的资源量，T自然就是SQL执行所需时间了这个公式很简单，调优的...
T-SQL性能调优秘笈基于SQLServer2012窗口函数
07-23
资源名称：T-SQL性能调优秘笈 基于SQL Server 2012窗口函数资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
ORACLE 19C SQL调优指南 中文版 Oracle DBA
10-26
ORACLE 19C SQL调优指南 中文版，很牛逼的文档，Oracle DBA必备
实战Oracle SQL调优 hint特性
03-05
实战Oracle SQL调优 hint特性
实战MySQL慢SQL调优
09-13
实战MySQL慢SQL调优
攻防演练作为红方：postgresql提权之UDF提权 ，及其好用的工具
keyboard专栏
04-28 339
在 PostgreSQL 中，用户定义的函数（UDF）提权是一种常见的提升权限的方法，尤其是当你已经获得了数据库的访问权限但需要更高级别系统权限时。UDF 提权涉及在数据库中创建或使用函数，这些函数可以执行系统级别的命令，从而允许攻击者在数据库服务器上执行任意代码。
优化SQL的方法
weixin_44139651的博客
04-26 896
使用join实现，但不适合join太多表，阿里巴巴开发者手册的规定，join表的数量不应该超过3个，join表数量太多时，会导致mysql在选错索引。一般SQL优化第一考虑的是索引优化，可使用explain命令，查看MySQL的执行计划，确认SQL是否有走索引。相对连接查询，子查询使用in关键字实现，具有结构化，相对简单，但是需要创建和删除临时表，增加资源消耗。索引可提升SQL效率，但索引需要额外的存储空间，而且还会有一定的性能消耗。left join，两个表的交集，以及左表剩余的数据，左表为驱动表。
SQL的基础语句
qq_51321722的博客
04-22 1372
因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。包含左边表的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行。包含右边表的全部行（不管右边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行。
Postgresql源码（127）投影ExecProject的表达式执行分析
最新发布
InternalsOf
04-30 389
无论是投影还是别的计算，表达式执行的入口和计算逻辑都是统一的，这里已投影为分析表达式执行的流程。
Thinkphp--in-sqlinjection
m0_74402888的博客
04-27 296
在 Builder 类的 parseData 方法中，由于程序没有对数据进行很好的过滤，将数据拼接进 SQL 语句，导致 SQL注入漏洞 的产生。影响版本在相应的文件夹位置打开终端后进行如下操作。
Bytebase 2.16.0 - 支持 Oracle 和 SQL Server DML 变更的事前备份
Bytebase的博客
04-26 496
支持通过 IAM 连接到 Google Cloud SQL (适用于 MySQL 和 PostgreSQL)。支持在 schema 编辑器中编辑分区表（适用于 MySQL 和 TiDB）。支持 Oracle 和 SQL Server DML 变更的事前备份。支持兼容 TDSQL 的 MySQL 和 PostgreSQL 版本。支持在上传 SQL 文件后按照指定的编码格式进行预览。如果从之前版本升级，获取新版本后，重新启动升级即可。支持在 SQL 编辑器中显示存储过程和函数。
mysql-sql-练习题-2
weixin_45705483的博客
04-25 168
mysql-sql-练习题-2，日期+topN，带答案，多方法
JDBC连接openGauss6.0和PostgreSQL16.2性能对比
SUOYUEのBLOG
04-28 893
JDBC在Linux终端直接编译运行JAVA程序连接PG🆚OG数据库
CTFHub（web sql）（四）
2301_81105268的博客
04-24 1373
Cookie 注入的原理也和其他注入一样，只不过是将提交的参数已 Cookie 方式提交，而一般的注入是使用 GET 或者 POST 方式提交，GET 方式提交就是直接在网址后面加上需要注入的语句，POST 方式则是通过表单，GET 和 POST 的不同之处就在于可以通过 IE 地址栏处看到我们提交的参数，而 Cookie 注入却不能。
T-SQL语言计算S=1/1+1/2+1/3…+1/n
10-25
可以使用T-SQL语言计算S=1/1+1/2+1/3…+1/n，具体步骤如下： 1. 使用WHILE循环计算1到n的分母之和，将结果存储在一个变量中。 2. 计算S=1/分母之和，将结果存储在另一个变量中。 3. 输出S的值。 下面是T-SQL代码...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值