概述，

开发反馈一条SQL执行较耗时（UAT环境正常、SIT环境较慢），现对其进行分析。

1、分别登录（SIT、UAT）的数据库，进行初始设置，如下：

# sqlplus / as sysdba
SQL> alter session set current_schema=fid_xxx；
SQL> set autot on 
SQL> set timing on （也可以打开：set time on）
SQL> set line 1000 pages 20000

2、核对两边的表类型是否一致（eg：结构、索引、数据量等）

SQL> tbsql tabstat TABLE_NAME;
SQL> tbsql index TABLE_NAME;

3、在排查时，将select * from XXX 改为了 select count(*) from XXX，考虑到实际情况：* 会回表，count(*)不会。后又直接使用开发反馈的SQL语句（select * from XXXX），在执行SQL满屏幕都在刷刷的显示结果，可执行如下语句，避免该现象。

SQL> set autot trace;

4、执行SQL，分析（此处伪SQL）

SQL> select * from dba_users;

31 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 96174755

------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |    11 |  2409 |    27   (0)| 00:00:01 |
|*  1 |  HASH JOIN                 |                         |    11 |  2409 |    27   (0)| 00:00:01 |
|*  2 |   HASH JOIN                |                         |    11 |  2288 |    20   (0)| 00:00:01 |
|*  3 |    HASH JOIN               |                         |    11 |  2167 |    13   (0)| 00:00:01 |
|*  4 |     HASH JOIN OUTER        |                         |    11 |  1958 |    11   (0)| 00:00:01 |
|*  5 |      HASH JOIN             |                         |    11 |  1540 |     9   (0)| 00:00:01 |
|*  6 |       HASH JOIN            |                         |    11 |  1430 |     7   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN|                         |     1 |    26 |     4   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL  | PROFILE$                |     1 |    13 |     2   (0)| 00:00:01 |
|   9 |         BUFFER SORT        |                         |     1 |    13 |     2   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL | PROFILE$                |     1 |    13 |     2   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL   | USER$                   |    31 |  3224 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL    | PROFNAME$               |     1 |    10 |     2   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |     1 |    38 |     2   (0)| 00:00:01 |
|  14 |     TABLE ACCESS FULL      | USER_ASTATUS_MAP        |     9 |   171 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | TS$                     |    17 |   187 |     7   (0)| 00:00:01 |
|  16 |   TABLE ACCESS FULL        | TS$                     |    17 |   187 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("U"."TEMPTS#"="TTS"."TS#")
   2 - access("U"."DATATS#"="DTS"."TS#")
   3 - access("U"."ASTATUS"="M"."STATUS#")
   4 - access("CGM"."VALUE"(+)="U"."NAME")
   5 - access("U"."RESOURCE$"="P"."PROFILE#")
   6 - access("U"."RESOURCE$"="PR"."PROFILE#")
   8 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
  10 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
  11 - filter("U"."TYPE#"=1)
  13 - filter("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         60  consistent gets
          0  physical reads
          0  redo size
       3460  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         31  rows processed

SQL>

ITPub博客

Oracle - 执行计划

最新文章