欢迎您光临【澳门新葡亰】官方网站!

如何利用分析函数改写范围判断自关联查询详解_数据库其它_脚本之家

时间:2019-12-22 22:28

前言

Oracle SQL优化之sql tuning advisor(STA)  

眼前碰到三个单条SQL运转效用不好引致数据库全体运维负载较高的难点。

SQL优化器SQL Tuning Advisor (STA卡塔尔(قطر‎,是oracle的sql优化帮忙理工科程师具。

剖判、定位数据库的关键负载是那条语句引起的进度绝对轻易,通过AWENVISION报告就足以比比较容易于的完成一定,这里就不赘述了。

实际上优化sql主要有八个方案,其一是改写sql本身,改写sql要求对sql语法、数据库的试行办法都要有较好地精晓。

于今径直看一下这几个导致质量难题的SQL语句,其对应的SQL REPORT总括如下:

那一个正是以此STA,它归属DBMS_SQLTUNE包,它的基本点功用是对此sql使用到的表创立正确的目录。

Stat Name Statement Total Per Execution % Snap Total Elapsed Time 363,741 363,740.78 8 .42 CPU Time 362,770 362,770.00 8 .81 Executions 1 Buffer Gets 756 756.00 0.00 Disk Reads 0 0.00 0.00 Parse Calls 1 1.00 0.01 Rows 50,825 50,825.00 User I/O Wait Time 0 Cluster Wait Time 0 Application Wait Time 0 Concurrency Wait Time 0 Invalidations 0 Version Count 1 Sharable Mem 28

 

从SQL的质量指标上看,其单次试行须要6分钟左右,管理5万多条记下,逻辑度独有756,主要消耗费时间间在CPU上。而那边就存在难题,逻辑读如此之低,而CPU时间耗费又这么之高,那么这一个CPU都消耗在何地啊?当然这些问通过SQL的总结消息中是找不到答案的,大家上边关心SQL的实践安顿:

使用STA前提:

Id Operation Name Rows Bytes TempSpc Cost Time 0 SELECT STATEMENT 1226 1 SORT ORDER BY 49379 3375K 3888K 1226 00:00:05 2 HASH JOIN ANTI 49379 3375K 2272K 401 00:00:02 3 TABLE ACCESS FULL T_NUM 49379 1687K 88 00:00:01 4 TABLE ACCESS FULL T_NUM 49379 1687K 88 00:00:01

 

从实行安插看,Oracle选拔了HASH JOIN ANTI,JOIN的两张表都以T_NUM,且都施用了全表扫描,并未接纳索引。仅靠施行安顿也只等获得地点的结论,至于为什么不选用索引,甚至为啥推行时间过长,还亟需更进一层的分析。

要保险优化器是CBO情势下。

将原SQL进行轻易脱离机密改写后, SQL文本相仿如下:

show parameter OPTIMIZER_MODE

SELECT BEGIN, END, ROWID, LENGTHFROM T_NUM AWHERE NOT EXISTS (SELECT 1FROM T_NUM BWHERE B.BEGIN <= A.BEGINAND B.END >= A.ENDAND B.ROWID != A.ROWIDAND LENGTH = LENGTH;

all_rows  /*CBO,sql全数再次回到行都接受基于成本的艺术运维*/

借使分条析理SQL语句,会开掘这是多个自关联语句,在BEGIN字段长度相等的前提下,想要找到怎么着不真实BEGIN比前段时间记录BEGIN小且END比当下记录END大的笔录。

first_rows  /*CBO,使用开销和试探法相结合的艺术,查找大器晚成种能够最快回来前面少数行*/

大约一点说,表中的记录表示的是由BEGIN开头到END截至的节制,那么当前想要获取的结果是搜索哪些未有节制所包涵的界定。供给在乎的是,对于如今的SQL逻辑,倘若存在两条范围完全肖似的记录,那么最后这两条记下都会被遗弃。

first_rows_n  /*CBO,全体施用基于花费的优化措施CBO,并以最快的进程,再次回到前N行记录*/

事情的逻辑并不是特别复杂,但是要缓慢解决一条记下与任何记录进行相比较,多半使用的法子是自关系,而在此个自关系中,既有超乎等于又有小于等于,还会有不等于,仅部分三个约等于的涉及条件,来自范围段BEGIN的尺寸的可比。

choose  /*万后生可畏有总计音讯,采取CBO,不然选择RBO*/

猛烈的是,假若是限量段本身的比较,其接纳度平日还是不错的,但是只要只是比较其尺寸,那么可相信轻便发生多量的再度,比如在这里个例子中:

rule  /*RBO*/

SQL> select length from t_num group by length order by 2 desc; LENGTH————- ———-12 2209611 901113 899914 818616 499 458 417 27

 

大气重新的数据出以往长度为11到14的范围上,在这里种场合下,独有的贰个等值衡量尺度LENGTH是相当的低效的,当时一条记下依据这么些等值条件会提到到近万条记下,设置关联到三万多条记下,显著多量的实践消耗在低效的连年进程中。

执行DBMS_SQLTUNE包举行sql优化内需有advisor的权限:

再来看一下切实的SQL语句,会发觉大致一向不艺术创立目录,因为LENGTH的选项度极度查,而其他的尺度都是例外查询,采用度也不会好,即便建构目录,免强实践接纳索引,功效也不会好。

grant advisor to scott;

那便是说只要想要继续优化那一个SQL,就只剩余一个主意,那便是SQL的改写。对于自关系查询来讲,最棒的改写方法是使用剖析函数,其有力的行级管理工夫,能够在一遍扫描进程中拿到一条记下与其它记录的关系,进而免去了自关系的供给性。

 

SELECT BEGIN, OLDEND END, LENGTHFROM (SELECT BEGIN, OLDEND, END, LENGTH OVER(PARTITION BY LENGTH, BEGIN, OLDEND) CN,ROW_NUMBER() OVER(PARTITION BY LENGTH, END ORDER BY BEGIN) RNFROM(SELECT BEGIN, END OLDEND, MAX OVER(PARTITION BY LENGTH ORDER BY BEGIN, END DESC) ENDFROM T_NUM))WHERE RN = 1AND CN = 1;

例子:

归纳的说,内层的剖释函数MAX用来依照BEGIN从小到大,END从大到小的准绳,分明每种范围对应的最大的END的值。而外层的五个深入分析函数,COUNT用来去掉完全重复的笔录,而ROW_NUMBEEnclave用来获得范围最大的笔录。

 

改写后,那一个SQL防止对自关系,也就不设有涉嫌条件重新值过高的属性隐患了。在模仿条件中,品质对譬如下:

 

SQL> SELECT BEGIN, END, ROWID, LENGTH2 FROM T_NUM A3 WHERE NOT EXISTS (4 SELECT 15 FROM T_NUM B6 WHERE B.BEGIN <= A.BEGIN7 AND B.END >= A.END8 AND B.ROWID != A.ROWID9 AND LENGTH = LENGTH10 ; 48344 rows selected. Elapsed: 00:00:57.68 Execution Plan———————————————————-Plan hash value: 2540751655 ————————————————————————————| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Time |————————————————————————————| 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 | 00:00:04 ||* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 | 00:00:04 || 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 | 00:00:01 || 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 | 00:00:01 |———————————————————————————— Predicate Information (identified by operation id):————————————————— 1 – access(LENGTH=LENGTH)filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND“B”.ROWID<>”A”.ROWID) Statistics———————————————————-0 recursive calls0 db block gets404 consistent gets0 physical reads0 redo size2315794 bytes sent via SQL*Net to client35966 bytes received via SQL*Net from client3224 SQL*Net roundtrips to/from client0 sorts 48344 rows processed SQL> SELECT BEGIN, OLDEND END, LENGTH2 FROM (3 SELECT BEGIN, OLDEND, END, LENGTH OVER(PARTITION BY LENGTH, BEGIN, OLDEND) CN,4 ROW_NUMBER() OVER(PARTITION BY LENGTH, END ORDER BY BEGIN) RN5 FROM6 (7 SELECT BEGIN, END OLDEND, MAX OVER(PARTITION BY LENGTH ORDER BY BEGIN, END DESC) END8 FROM T_NUM9 )10 )11 WHERE RN = 112 AND CN = 1; 48344 rows selected. Elapsed: 00:00:00.72 Execution Plan———————————————————-Plan hash value: 1546715670 ——————————————————————————————| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Time |——————————————————————————————| 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 | 00:00:10 ||* 1 | VIEW | | 48454 | 2460K| | 800 | 00:00:10 ||* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 | 00:00:10 || 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 | 00:00:10 || 4 | VIEW | | 48454 | 1845K| | 311 | 00:00:04 || 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 | 00:00:04 || 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 | 00:00:01 |—————————————————————————————— Predicate Information (identified by operation id):————————————————— 1 – filter2 – filter OVER ( PARTITION BY LENGTH,”END”ORDER BY “BEGIN”)<=1) Statistics———————————————————-0 recursive calls0 db block gets202 consistent gets0 physical reads0 redo size1493879 bytes sent via SQL*Net to client35966 bytes received via SQL*Net from client3224 SQL*Net roundtrips to/from client3 sorts 48344 rows processed

 

原SQL运维时刻周围1分钟,而改写后的SQL语句只供给0.72秒,实践时间成为原来的1/80,逻辑读减弱四分之二。

SQL> create table obj as select * from dba_objects;

总结

 

如上正是那篇文章的全体内容了,希望本文的源委对我们的读书大概事业具有自然的参照学习价值,要是有疑难大家能够留言交流,多谢大家对剧本之家的帮衬。

表已开立。

 

SQL> create table ind as select * from dba_indexes;

 

表已开立。

 

SQL> insert into obj select * from obj;

 

已创建 74603 行。

 

SQL> insert into obj select * from obj;

 

已创建 149206 行。

 

SQL> insert into obj select * from obj;

 

已创建 298412 行。

 

SQL> insert into ind select * from ind;

 

已创建 5134 行。

 

SQL> insert into ind select * from ind;

 

已创建 10268 行。

 

SQL> insert into ind select * from ind;

 

已创建 20536 行。

 

那边创办两张表,下边履行叁个查询:

 

 

 

SQL> set timing on

SQL> set autot trace

SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;

 

已用时间:  00: 00: 00.15

 

试行布置


Plan hash value: 380737209

 


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


|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 |

|   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          |

|*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 |

|   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 |

|   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |


 

Predicate Information (identified by operation id):


 

   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")

 

Note


   - dynamic sampling used for this statement (level=2)

 

 

总括新闻


          9  recursive calls

          4  db block gets

      10406  consistent gets

          0  physical reads

          0  redo size

        425  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

如上能够见见,在履行以上五个查询的时候,两张表走的全表扫和hash join。

使用STA:

第一步:成立优化职务

通过调用函数CREATE_TUNING_TASK来成立优化职责,调用存款和储蓄进程EXECUTE_TUNING_TASK试行该职责:

 

 

SQL> set autot off

SQL> set timing off

DECLARE

my_task_name VARCHAR2(30);

my_sqltext  CLOB;

BEGIN

my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text    => my_sqltext,

user_name   => 'SCOTT', 

scope       => 'COMPREHENSIVE',

time_limit  => 30,

task_name   => 'tuning_sql_test',

description => 'tuning');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');

END;

/

PL/SQL 进程已成功完结。

 

函数CREATE_TUNING_TASK,

sql_text是索要优化的言辞,

user_name是该语句通过哪些客商施行,客商名大写,

scope是优化范围(limited或comprehensive),

time_limit优化进度的小时范围,

task_name优化职称,

description优化职分描述。

 

其次步: 施行优化任务

由此调用dbms_sqltune.execute_tuning_task进程来实行前边创立好的优化职责。

 

SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

PL/SQL 进度已成功做到。

 

 

其三步:检查优化职责的气象

经过翻看user_advisor_tasks/dba_advisor_tasks视图能够查看优化职务的当前事态。

 

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

 

TASK_NAME         STATUS


tuning_sql_test         COMPLETED

 

 

 

第四步:查看优化结果

通过dbms_sqltune.report_tning_task函数能够获得优化职分的结果。

 

SQL> set long 999999

SQL> set serveroutput on size 999999

SQL> set line 120

SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')


GENERAL INFORMATION SECTION


Tuning Task Name   : tuning_sql_test

Tuning Task Owner  : SCOTT

Workload Type      : Single SQL Statement

Execution Count    : 2

Current Execution  : EXEC_112

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 30

Completion Status  : COMPLETED

Started at         : 08/29/2013 11:10:10

Completed at       : 08/29/2013 11:10:12

 


Schema Name: SCOTT

SQL ID     : 6wruu2mxyu8g3

SQL Text   : select count(*) from obj o, ind i where

             o.object_name=i.index_name

 


FINDINGS SECTION (3 findings)


 

1- Statistics Finding


  还未解析表 "SCOTT"."IND"。

 

  Recommendation

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

  - 考虑采摘此表的优化程序总计信息。

    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

  Rationale

  ---------

    为了选取好的实行安排, 优化程序须要此表的新颖计算消息。

 

2- Statistics Finding


  还未有剖判表 "SCOTT"."OBJ"。

 

  Recommendation

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

  - 酌量采摘此表的优化程序总括信息。

    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

  Rationale

  ---------

    为了选用好的施行陈设, 优化程序要求此表的新颖计算新闻。

 

3- Index Finding (see explain plans section below)


  通过创设叁个或多少个目录能够改良此语句的实行安顿。

 

  Recommendation (estimated benefit: 75.74%)

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

  - 酌量运营可以改进物理方案设计的探访指点也许创建推荐的目录。

    create index SCOTT.IDX$$_00790001 on SCOTT.OBJ("OBJECT_NAME");

 

  - 思索运维能够修改物理方案设计的探问指引大概创建推荐的目录。

    create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME");

 

  Rationale

  ---------

    创设推荐的目录能够确定地校勘此语句的实施安插。然而, 使用标准的 SQL 职业量运

行 "访问指点"

    大概比单个语句更可取。通过这种方法可以获取完美的目录提议案, 富含计算索引维护

的支出和叠加的半空中消耗。

 


EXPLAIN PLANS SECTION


 

1- Original


Plan hash value: 380737209

 


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

|


|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 |

|   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          |

|*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 |

|   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 |

|   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |


 

Predicate Information (identified by operation id):


 

   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")

 

2- Using New Indices


Plan hash value: 4048334321

 



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



|   0 | SELECT STATEMENT        |                |     1 |    83 |       |  1228   (2)| 00:00:15 |

|   1 |  SORT AGGREGATE         |                |     1 |    83 |       |            |

|

|   2 |   MERGE JOIN            |                |  5861K|   463M|       |  1228   (2)| 00:00:15 |

|   3 |    INDEX FULL SCAN      | IDX$$_00790001 |   577K|    36M|       |   944   (1)| 00:00:12 |

|*  4 |    SORT JOIN            |                | 44789 |   743K|  2120K|   268

   (1)| 00:00:04 |

|   5 |     INDEX FAST FULL SCAN| IDX$$_00790002 | 44789 |   743K|       |    18   (0)| 00:00:01 |



 

Predicate Information (identified by operation id):


 

   4 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")

       filter("O"."OBJECT_NAME"="I"."INDEX_NAME")

 

告知如上,此报告根本包涵建议的目录和上下推行布署相比较,可以见到到成效果还能的。

 

五、删除优化职分

因此调用dbms_sqltuen.drop_tuning_task能够去除已经存在的优化职分

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

 

上一篇:没有了
下一篇:子句和having子句中的差距介绍_oracle_脚本之家