如何发现最消耗系统资源的SQL语句呢? 利用I/O优化鉴定耗费资源的方法即AWR报告,即可使之变得很容易。
当确定了一个最有可能出问题的 SQL 语句后,接下来就是如何追踪这一语句的执行,最普遍的方法就是找出该SQL语句的执行计划。
随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的主要问题之一，系统优化中一个很重要的方面就是SQL语句的优化。
大量的数据指出劣质的SQL语句和优质的SQL语句之间的速度差别可以达到上百倍，对一个系统不是简单的能实现功能就可以，而是要写出高质量的SQL语句提高系统的可用性。
01、 利用autotrace追踪SQL语句
利用autotrace追踪SQL语句的代码如下:
SQL> set autotrace on SQL> select * from dual; D - X 执行计划 ---------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 547 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
执行完语句后,会显示执行计划与统计信息。
在用该方法查看执行时间较长的SQL语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大的增加了，如果不想执行语句，而只是想得到执行计划，可以采用如下命令。
SQL> select * from dual; 执行计划 ---------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 547 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
上述的代码只列出了执行计划，而不会真正地执行语句，这就大大优化了时间，虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息是没有用处的。
也可以使用如下指令单纯地显示执行计划。
SQL> set autotrace traceonly explain SQL> select * from dual; 执行计划 ---------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
当然也可以只显示统计信息，代码如下。
SQL> set autotrace traceonly statistics; SQL> select * from dual; 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 547 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
追踪完毕SQL语句之后，可以使用如下命令关闭自动追踪功能。
SQL> set autotrace off SQL> show autotrace autotrace OFF
02、利用explain plan命令
explain plan命令被用来产生一个优化器所使用的执行计划。
这一命令将所产生的执行计划存储在一个表中，系统默认为表plan中，但是该命令并不真正的执行语句，而只是产生可能使用的执行计划。
如果仔细地观察这一执行计划，就可以了解到服务器是如何执行所解释的SQL语句。
expain plan命令的语法如下：
explainplansetstatement_id= '正文 ' into用户名.表名 for语句
其中，正文表示为语句的标示符是一个可选项。用户名.表名表示存放执行计划的表名,默认为plan table，这也是一个可选项。语句表示要解释的SQL语句正文。
03、案例分析
步骤1：以用户 sys 名登录,查询用户 hr 下表 departments 与表 employees 连接的数据，其代码如下：
SQL> conn sys as sysdba 输入口令: 已连接。 SQL> select e.last_name,d.department_name 2 from hr.employees e,hr.departments d 3 where e.department_id=d.department_id; LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration Fay Marketing Hartstein Marketing Tobias Purchasing Colmenares Purchasing ....... 已选择 106 行。
步骤2：开启追踪，其代码如下：
SQL> show autotrace autotrace OFF SQL> set autotrace on
步骤3：显示语句的执行计划和统计信息，其代码如下：
SQL> select e.last_name,d.department_name 2 from hr.employees e,hr.departments d 3 where e.department_id=d.department_id; LAST_NAME DEPARTMENT_NAME ------------------------- ------------------------------ Whalen Administration ....... 已选择 106 行。 执行计划 ---------------------------------------------------------- Plan hash value: 1473400139 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 |SELECT STATEMENT | | 106 | 2862 | 5 (20)| 00:00:01 | | 1 | MERGE JOIN | | 106 | 2862 | 5 (20)| 00:00:01 | | 2 |TABLE ACCESS BY INDEX ROWID| DEPARTMENTS|27 |432 | 2 (0)| 00:00:01 | | 3 |INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 (0)| 00:00:01 | |* 4 |SORT JOIN | | 107 | 1177 | 3 (34)| 00:00:01 | | 5 | VIEW | index$_join$_001 | 107 | 1177 | 2 (0)| 00:00:01 | |* 6 | HASH JOIN | | | | | | | 7 |INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX |107 |1177 |1 (0)| 00:00:01 | | 8 |INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 1177 | 1 (0)| 00:00:01 --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 6 - access(ROWID=ROWID) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 20 consistent gets 0 physical reads 0 redo size 3340 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 106 rows processed
步骤4：关闭trace自动跟踪功能，其代码如下：
SQL> set autotrace off SQL> show autotrace autotrace OFF
步骤5：使用explain plan命令获取执行计划，默认存放在plan_table表中，其代码如下：
SQL> explain plan set statement_id='test_project' 2 for 3 select e.last_name,d.department_name 4 from hr.employees e,hr.departments d 5 where e.department_id=d.department_id; 已解释。
步骤6：执行成功后,可以使用如下命令显示plan_table表中的所有执行计划了，其代码如下：
SQL> select id,operation,options,object_name,statement_id 2 from plan_table; ID OPERATION OPTIONS OBJECT_NAME STATEMENT_ID ---------- -------------------- -------------------- -------------------- -- 0 SELECT STATEMENT test_project 1 MERGE JOIN test_project 2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS test_project 3 INDEX FULL SCAN DEPT_ID_PK test_project 4 SORT JOIN test_project 5 VIEW index$_join$_001 test_project 6 HASH JOIN test_project 7 INDEX FAST FULL SCAN EMP_DEPARTMENT_IX test_project 8 INDEX FAST FULL SCAN EMP_NAME_IX test_project 已选择 9 行。
步骤7：当以上的explain plan命令执行后，就可使用如下命令利用dbms_xplan软件包中display函数显示explain plan命令的输出结果。
SQL select plan_table_output from table dbms_xplan display()); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- Plan hash value :1473400139 |Id |Operation |Name |Rows |Bytes |Cost %CPU |Time | --------------------------------------------------------------------- |0 |SELECT STATEMENT | |106 |2862 |5(20) | 00:00:01 | |1 |MERGE JOIN | |106 |2862 |5(20) | 00:00:01 | |2 |TABLE ACCESS BY INDEX ROWID|DEPARTMENTS |27 |432 |2(0) |00:00:01 | |3 |INDEX FULL SCAN |DEPT_ID_PK |27 | |1(0) | 00：00：01 | |* 4 |SORT JOIN | |107 |1177 |3（34） | 00 ：00 ：01 | |5 |VIEW |index$_join$_001|107 |1177 |2(0) | 00 :00 :01 | |* 6 |HASHJOIN | | | | | | |7 |INDEX FAST FULL SCAN |EMP_DEPARTMENT_IX|107 |1177 |1(0) | 00:00:01 | |8 |INDEX FAST FULL SCAN |EMP_NAME_IX |107 |1177 |1(0) | 00:00:01 | Predicate Information (identifiedbyoperationid): --------------------------------------------------- 4 - access ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID") filter ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID") 6 - access (ROWID=ROWID) 已选择 22 行
步骤8：为了方便DBA工作,Oracle还提供了一个名为utlxpls.sql的脚本文件，他的功能就是显示最后解释的SQL语句的执行计划。
这个脚本文件存放在$ORACLE_HOME\RDBMS\ADMIN\文件夹中，可以使用如下命令执行这一个脚本，效果与之前完全一样。
QL @D \SOFTWARE\ORACLE19C\RDBMS\ADMIN\UTLXPLS.SQL PLAN_TABLE_OUTPUT ---------------------------------------------------------------------- value: Plan hash1473400139 ---------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes |Cost (%CPU) |Time| ---------------------------------------------------------------------- |0|SELECT STATEMENT | |106 |2862 |5(20) |00:00:01| |1|MERGE JOIN | |106 |2862 |5(20) |00:00:01| |2|TABLE ACCESS BY INDEX ROWID |DEPARTMENTS |27 |432 |2(0) |00:00:01| |3|INDEX FULL SCAN |DEPT_ID_PK |27 | |1(0)|00:00:01| |*4|SORT JOIN | |107 |1177 |3(34) |00:00:01| 303 |5|VIEW |index$_join$_001 |107 |1177 |2(0) |00:00:01| |*6|HASH JOIN | | | | || |7|INDEX FAST FULL SCAN |EMP_DEPARTMENT_IX |107 |1177 |1(0)|00:00:01 |8|INDEX FAST FULL SCAN |EMP_NAME_IX |107 |1177 |1(0) |00:00:01| ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID") filter ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID") 6 - access (ROWID=ROWID) 已选择 22 行
其实打开这个文件会发现这个脚本使用的方法其实也是调用了dbms_xplan软件包中的display（）函数。
在utlxpls.sql文件中调用display函数时还用了几个参数,完整的display（）函数调用格式如下：
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
其中:
plan_table:存放SQL与执行计划的表;
serial:不显示并行操作的信息。
步骤9：利用数据字典v$SQL获取了上面SQL语句的dsql_id，其代码如下：
注意：在查询的过程中使用where语句来限制显示输出的结果，以方便阅读。
SQL> select sql_id,sql_text 2 from v$sql 3 where sql_text like'%select e.lastname,%'; SQL_ID ---------------------------------------------------------------------------- SQL_TEXT -------------------------------------------------------------------------- 3ygg74tbfngnj select SQL_ID,SQL_TEXT from v$SQL where SQL_TEXT like'%select e.lastname,%';
由查询结果可以发现，sql_id为3ygg74tbfngnj。
步骤10：利用sql_id调用dbms_xplay软件包中的display（）函数以显示刚刚执行过的SQL语句的执行计划。
SQL﹥colplan_table_output for a100 SQL﹥setlinesize200 304 SQL﹥selectplan_table_output 2 from table (dbms_xplan display_cursor ('3ygg74tbfngnj')); PLAN_TABLE_OUTPUT -------------------------------------------------------- SQL_ID 3ygg74tbfngnj child number 0 -------------------------------------------------------- selectSQL_ID, SQL_TEXT from v$SQL where SQL_TEXT like '%selecte lastname % '; Plan hash value 903671040 -------------------------------------------------------- Id|Operation |Name |Rows |Bytes |Cost( %CPU )| -------------------------------------------------------- -1412069179|SELECTST ATEMENT| | | |1 (100) | PLAN_TABLE_OUTPUT -------------------------------------------------------- Id|Operation |Name |Rows |Bytes |Cost (%CPU) | -------------------------------------------------------- *1|FIXED TABLE FULL|X$ KGLCURSOR_CHILD|1 | 523|0 (0) | -------------------------------------------------------- Predicate Information (identif ied by operation id); -------------------------------------------------------- 1 - filter ("KGLNAOBJ"IS NOT NULL AND"KGLNAOBJ"LIKE '%selecte lastname % 'AND"INST_ID" =USERENV 'INSTANCE' 已选择20行
由上面的结果了解，这两个表在连接时，最大的子表employees，居然使用的是全表扫描(fixed table full)。
