当前位置:Gxlcms > mysql > 评估和提升oracle数据库性能的主要工具

评估和提升oracle数据库性能的主要工具

时间:2021-07-01 10:21:17 帮助过:33人阅读

SQL是一种非过程化的语言,我们定义的sql语句仅仅告诉数据库我们需要什么样的数据,并没有告诉数据库如何获取这些数据。而执行计划正式数据库获取这些数据的方式和步骤。在日常开发中,为了优化数据库的需要,我们常常需要获取sql语句的执行计划,通过它判断

SQL是一种非过程化的语言,我们定义的sql语句仅仅告诉数据库我们需要什么样的数据,并没有告诉数据库如何获取这些数据。而执行计划正式数据库获取这些数据的方式和步骤。在日常开发中,为了优化数据库的需要,我们常常需要获取sql语句的执行计划,通过它判断语句执行性能的高低,进而进行优化。那么怎么获得执行计划呢?

1、获取SQL执行计划的方式

  1. 使用explain plan工具
  2. 查询动态性能视图
  3. 启用执行计划跟踪功能,即autotrace功能
  4. 使用PL/SQL Developer提供的获取执行计划方法

下面分别详细介绍下及几种获取执行计划的方式:

1.1 使用explain plan工具

explain plan命令可以将一条SQL语句预估的执行计划插入到计划表plan table,然后查询该表来获取预估的执行计划。 explain plan命令将SQL语句的执行计划插入到计划表,并不会执行隐式commit,所以如果想要持久化到数据库,需要显示commit。

提示:explain plan命令获得的执行计划是预估的,因为它并不是该SQL语句真正执行时的执行计划,随着oracle库中参数、表数据等因素的变化(比如表的数据的太少,虽然某些字段上建了索引,在执行时索引并不一定真正其效果,但是explain plan获得执行计划可能是走索引的),预估的执行计划往往和实际是有出入的。

1.1.1 前提条件

预估的执行计划要存入plan table表,所以执行explain plan之前要确保plan table表的存在。在oracle 10g之前,plan table表是需要手动创建的,从oracle 10g开始,oracle会自动创建全局plan table表(表名为PLAN_TABLE),供所有用户使用。

如何要创建表plan_table,或创建其他的计划表呢?

找到$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本文件,可以根据该脚本创建自己想要的计划表,只需要根据情况修改表名plan_table即可。


提示:关于计划表plan_table介绍请参考《Oracle 10g版本计划表PLAN_TABLE列的说明》

1.1.2 explain plan语法

EXPLAIN PLAN

[SET STATEMENT_ID = 'statement_id']

[INTO table_name]

FOR sql_statement

说明:

1、方括号[]中的语句是可选的。

2、相关选项说明

选项
说明
statement_id
标识存入表plan_table的sql语句的执行计划,不同sql语句的执行计划可以共用相同的statement_id标识,但是为了进行区分,尽量唯一,可以通过statement_id作为条件查询plan_table表中的执行计划。
table_name
执行计划表的名称,如果没有指定,默认表名plan_table,如果指定的表不是plan_table表,而是自定义的,必须要确保该表和标准的计划表结果相同。
sql_statement
sql语句,该sql语句可以包含绑定变量。

1.1.3 执行计划查询和展现

下面介绍两种查询执行计划的方法:使用dbms_xplan包、自定义层次查询语句,这两种方法都是从计划表查询数据,通过使用dbms_xplan包查询执行计划,默认计划表名为plan_table, 而且查询出来的参数数据也是固定的;但是通过自定义的层次查询语句不但可以自由指定计划表,还可以根据情况选择查询参数。

  • 使用DBMS_XPLAN包查看执行计划

语句如下:

SELECT * FROM TABLE(dbms_xplan.display());
如:

SQL> explain plan 
  2  for select * from t_student where gid = 1;
Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1947974170
--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   595 |     0   (0)
|   1 |  TABLE ACCESS BY INDEX ROWID| T_STUDENT    |     1 |   595 |     0   (0)
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010947 |     1 |       |     0   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("GID"=1)
14 rows selected
这种查看执行计划的方法比较简便,但是也有局限性,它查询参数数据有限,只能从名为plan_table的计划表查询数据,而是展现参数也是固定的(仅限Operation、Name、Rows、Bytes和Cost的参数数据),不过这些数据一般足够了。如果想获得更多的参数数据,可以使用自定义的层次查询语句。
  • 自定义层次查询语句执行计划

查询语句如下:

SELECT RTRIM(LPAD(' ', 2*LEVEL)||
       RTRIM(operation)||' '||
       RTRIM(options)||' '||
                object_name) query_plan, cost, cardinality, time
  FROM plan_table
  CONNECT BY PRIOR id = parent_id
  START WITH ID = 0;
这种方法使用者可以根据自己的需要查看计划表中字段,上面语句只写了query_plan、cost、cardinality和time,其他字段请参考《Oracle 10g版本计划表PLAN_TABLE列的说明》。

如:

SQL> explain plan
  2  for select * from t_student where gid = 1;
Explained

SQL> SELECT RTRIM(LPAD(' ', 2*LEVEL)||
  2         RTRIM(operation)||' '||
  3         RTRIM(options)||' '||
  4                  object_name) query_plan, cost, cardinality, time
  5    FROM plan_table
  6    CONNECT BY PRIOR id = parent_id
  7    START WITH ID = 0;
QUERY_PLAN                                                                                                          COST                             CARDINALITY                                    TIME
-------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
  SELECT STATEMENT                                                                                                     0                                       1                                       1
    TABLE ACCESS BY INDEX ROWID T_STUDENT                                                                              0                                       1                                       1
      INDEX UNIQUE SCAN SYS_C0010947                                                                                   0                                       1                                       1


1.2、查询动态性能视图

上面1.1章节介绍查询的执行计划,都是预估的执行计划。而动态性能视图中缓存的SQL执行信息,则是真实的执行计划。

下面介绍一下几个动态视图:

  1. V$SQL:缓存着已执行的SQL语句的时间消耗、CPU或IO需求等信息。
  2. V$SQL_PLAN:缓存SQL语句的执行计划信息。
  3. V$SQL_PLAN_STATICS:记录着执行计划每个步骤执行次数、IO次数以及处理记录数。

通过动态性能视图查询较高SQL的执行计划的思路是:

1.通过挖掘V$SQL中具有较高时间消耗、CPU或IO需求的SQL语句。这一步通过自定义SQL完成。比如消耗时间最多的前10条语句,

查询语句如下:

SELECT sql_id, child_number, sql_text, elapsed_time
  FROM ( SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, disk_reads,
          RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
              FROM v$sql)
 WHERE elapsed_rank <= 10; 

2.通过使用V$SQL_PLAN和V$SQL_PLAN_STATICS找到不尽如人意的SQL语句的执行计划。这一步不需要我们写SQL,DBMS_XPLAN已经为提供可查询方法DBMS_XPLAN.display_cursor().具体使用请参考《DBMS_XPLAN包中函数的使用》。


例子:

SQL> SELECT sql_id, child_number, sql_text, elapsed_time
  2    FROM ( SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, disk_reads,
  3            RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
  4                FROM v$sql)
  5   WHERE elapsed_rank <= 10;
SQL_ID        CHILD_NUMBER SQL_TEXT                                                                         ELAPSED_TIME
------------- ------------ -------------------------------------------------------------------------------- ------------
6gvch1xu9ca3g            0 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :     40748737
b6usrg82hwsa3            0 call dbms_stats.gather_database_stats_job_proc (  )                                  28589079
cvn54b7yz0s8u            0 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher     13061969
5jvf84zg4c49n            0  select s.synonym_name as object_name, o.object_type   from sys.all_synonyms s,      10839744
02577v815yp77            0 BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END;                          8716832
3am9cfkvx7gq1            0 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_guid,:metric_val      7427452
4jrfrtx4u6zcx            0 SELECT TASK_TGT.TARGET_GUID TARGET_GUID, LEAD(TASK_TGT.TARGET_GUID,1) OVER (ORDE      6817926
3ktacv9r56b51            0 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper      6396579
c0j6cx9kzjf7g            0 SELECT EXTRACTVALUE(VALUE(T), '/select_list_item/pos') + 1 POS, EXTRACTVALUE(VAL      6221768
f6cz4n8y72xdc            0 SELECT space_usage_kbytes  FROM  v$sysaux_occupants  WHERE occupant_name = 'SQL_      5162914
10 rows selected

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('at5b8k7swu5w4', '0', 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  at5b8k7swu5w4, child number 0
-------------------------------------
select * from t_student where gid = 2
Plan hash value: 1947974170
--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)
|   1 |  TABLE ACCESS BY INDEX ROWID| T_STUDENT    |     1 |   595 |     0   (0)
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010947 |     1 |       |     0   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("GID"=2)
19 rows selected

1.3、启用执行计划跟踪功能

工具EXPLAIN PLAN和DBMS_XPLAN可以获取预估执行计划和缓存中的执行信息,他们是主要的SQL调优工具,但是他们只是获取单个SQL的执行信息。如果想获取特定事务或是应用优化一批SQL语句,跟踪(Tracing)是个有效的方法。跟踪可以提供EXPLAIN PLAN与DBMS_XPLAIN能提供的所有信息,并且更加准确详尽,因为它是执行SQL时的真实的执行计划。

           SQL跟踪包括两个基本组成部分:
           1. SQL跟踪:在当前会话或其他会话中启动跟踪。
            2. tkprof工具:格式化跟踪文件,因为生成的跟踪文件对使用者不容易读取,使用该工具可以转换为使用者跟容易读取的格式。具体请参加《tkprof命令格式化分析跟踪文件》
           启动跟踪的方式有:
             1. 启动当前会话的跟踪
             2.启动其他会话的跟踪
             3.使用登陆触发器启动跟踪

下面分别详细介绍3种跟踪启动的方式。

1.3.1 启动当前会话的跟踪

1.3.1.1启动当前会话跟踪的两种方法

启动当前会话的跟踪的两种方法:

              1.设置参数SQL_TRACE 为true,语句如下:
                        ALTER SESSION SET SQL_TRACE = TRUE;

该语句创建基本的跟踪,跟踪信息包括SQL语句的执行统计信息与执行计划,但不会包含绑定变量的

值,也不包含等待各种事件花费的时间。如果要获取这些信息,请使用第2种启动方式。

               2.调用DBMS_SESSION方法,语法如下:
                      DBMS_SESSION.session_trace_enable (
                                   waits                IN         BOOLEAN    DEFAULT   TRUE,
                                   binds                IN         BOOLEAN    DEFAULT   FALSE                       );

下面介绍下函数参数的含义:

参数 说明
waits 如果为TRUE,将收集等待信息;如果为FALSE,则不收集。
binds 如果为TRUE,将收集绑定变量信息;如果为FALSE,则不收集。

调用例子,在Command Window中输入如下命令:

begin
   dbms_session.session_trace_enable(true, 
                                     true);
end;
/  

执行完后,执行如下语句查询是否执行成功:

SQL> SELECT t.sql_trace, t.sql_trace_waits, t.sql_trace_binds FROM v$session t WHERE t.sid = USERENV('SID');
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------- --------------- ---------------
ENABLED   TRUE            TRUE
收集的结果如下(tkprof工具格式化后):
SQL ID: 518m7y4zt6xkp
Plan Hash: 1947974170
select * 
from
 t_student where gid = 1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.01          2          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.02          2          2          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T_STUDENT (cr=2 pr=2 pw=0 time=0 us cost=1 size=19 card=1)
      1   INDEX UNIQUE SCAN SYS_C0010947 (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 73110)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.04          0.04
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         2        0.01          0.01
其中第20到26行就是时间等待信息,如果使用第1种方法启动跟踪信息,将没有这部分的内容。

1.3.1.2 定位跟踪文件

启动会话跟踪之后,如何才能找到当前会话的跟踪文件呢?有两种方法

1.3.1.2.1 通过查询语句

下面语句用来查询当前会话跟踪文件:

select p.TRACEFILE from v$session s, v$process p
where s.PADDR = p.ADDR
and s.SID = USERENV('SID');

说明:USERENV('SID')得到的是当前会话的SID。

如下:

SQL> select p.TRACEFILE from v$session s, v$process p
  2           where s.PADDR = p.ADDR
  3              and s.SID = USERENV('SID');
TRACEFILE
--------------------------------------------------------------------------------
d:\oracle\diag\rdbms\david\orcl\trace\orcl_ora_7220.trc

1.3.1.2.2 标识跟踪文件

为会话指定一个跟踪文件的标识符,通过设置tracefile_identifier参数实现:
ALTER SESSION SET tracefile_identifier = 标识符
如果我们设定tracefile_identifier为LGL,即执行:

SQL> alter session set tracefile_identifier=LGL;
那么跟踪文件目录中将生成一个类似orcl_ora_3668_LGL.trc格式的跟踪文件。
跟踪文件的目录保存在user_dump_dest参数:
SQL> show parameter user_dump_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\oracle\diag\rdbms\david\orcl\trace

1.3.2 启动其他会话的跟踪

通过DBMS_MONITOR包中的session_trace_enable()方法启动别的会话的跟踪,语法如下:

                 DBMS_MONITOR.session_trace_enable(
                          session_id                IN      BINARY       DEFAULT  NULL,
                          serial_num                IN      BINARY       DEFAULT  NULL,
                          waits                        IN      BOOLEAN    DEFAULT  TRUE,
                          binds                        IN      BOOLEAN    DEFAULT  FALSE
                   )

下面介绍下函数参数的含义:

参数
说明
session_id 对应V$SESSION视图中的列SID的值。
serial_num 对应V$SESSION视图中的列SERIAL#的值。
waits
如果为TRUE,将收集等待信息;如果为FALSE,则不收集。
binds
如果为TRUE,将收集绑定变量信息;如果为FALSE,则不收集。
在使用该函数之前先通过查询V$SESSION查找要跟踪会话,比如查询实例名包含david的会话状态:

SQL> SELECT t.sid, t.serial#, t.sql_trace FROM v$session t WHERE LOWER(t.service_name) LIKE '%david%';
       SID    SERIAL# SQL_TRACE
---------- ---------- ---------
         7        249 DISABLED
         8         41 ENABLED
        71        125 ENABLED
        通过上面查询结果可知SID为7的会话的没有启动会话跟踪,使用session_trace_enable()方法启动SID为7的
会话的跟踪,打开Command Window窗口,执行如下语句:

begin
   dbms_monitor.session_trace_enable(session_id       =>7,
                                     serial_num       =>249,
                                     waits            =>true, 
                                     binds            =>true);
end;
/ 

再次查询SID为7的会话跟踪情况,语句如下:

SQL> SELECT t.sid, t.serial#, t.sql_trace FROM v$session t WHERE t.sid = 7;
       SID    SERIAL# SQL_TRACE
---------- ---------- ---------
         7        249 ENABLED

从查询结果可以看出,SID为7的会话已经启动了跟踪。


1.3.3 使用登陆触发器启动跟踪

建立一个触发器(执行到sys用户中),用户登录数据库之后就启动会话的跟踪,触发器创建脚本如下:

CREATE OR REPLACE TRIGGER trace_login_trigger
     AFTER LOGON ON DATABASE
BEGIN
   --设置用户ADMIN的会话跟踪
   IF  USER = 'ADMIN' THEN
       --设置跟踪文件标识
       EXECUTE IMMEDIATE 'alter session set tracefile_identifier=ADMINCC';
       
       --启动会话跟踪
       DBMS_SESSION.session_trace_enable( waits  =>TRUE,
                                          binds  =>FALSE
                                         );                                       
   END IF;
END;
/                                         









人气教程排行