Oracle 19C 报告简单数据库操作：方案

原创 Asher.HU 2021-02-04

1128

在这种情况下，查询将在几秒钟内完成，但仍在继续执行。

在此示例中，假设您以user身份登录数据库sh，然后运行以下查询：

SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id

查询未完成。从Oracle Database 19c开始，低特权用户sh可以在其会话中为简单的数据库操作（单个SQL和PL / SQL语句）生成SQL Monitor报告。要确定问题的根源，可以使用SQL Monitor进行诊断，如下所示：

取消查询。

通过调用获取文本报告DBMS_SQL_MONITOR.REPORT_SQL_MONITOR：

SET LONG 1000000
VARIABLE my_rept CLOB;
BEGIN
  :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
                report_level => 'ALL', 
                TYPE         => 'text');
END;
/
PRINT :my_rept

部分样本输出如下所示：

SQL Text
------------------------------
SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id

Global Information
------------------------------
 Status              :  DONE (ERROR)
 Instance ID         :  1
 Session             :  SH (42:3617)
 SQL ID              :  d9w9dw5v007xp
 SQL Execution ID    :  16777217
 Execution Started   :  09/18/2018 14:08:13
 First Refresh Time  :  09/18/2018 14:08:13
 Last Refresh Time   :  09/18/2018 14:08:34

MY_REPT
--------------------------------------------------------------------------------
 Duration            :  21s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@slc16iva (TNS V1-V3)
 Fetch Calls         :  1
Global Stats

MY_REPT
--------------------------------------------------------------------------------
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Reqs  | Bytes |

================================================================================

|      21 |      11 |       10 |     1 |    204 |  233 |   3MB |  4568 | 909MB |

================================================================================


SQL Plan Monitoring Details (Plan Hash Value=2036849021)
================================================================================

MY_REPT
--------------------------------------------------------------------------------
================================================================================
=================================
| Id |             Operation              |      Name      |  Rows   | Cost |
Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Tem
p  | Activity | Activity Detail |
|    |                                    |                | (Estim) |      | Ac
tive(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Ma
x) |   (%)    |   (# samples)   |
================================================================================
================================================================================
=================================

MY_REPT
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                |         |      |
     19 |     +2 |     1 |        0 |      |       |       |       |     . |
 . |          |                 |
|  1 |   SORT ORDER BY                    |                |     51G | 316M |
     20 |     +1 |     1 |        0 |      |       |  4496 | 908MB |  99MB | 909
MB |          |                 |
|  2 |    MERGE JOIN CARTESIAN            |                |     51G |   2M |
     19 |     +2 |     1 |      50M |      |       |       |       |     . |
 . |          |                 |
|  3 |     TABLE ACCESS FULL              | CUSTOMERS      |   55500 |  414 |
     20 |     +2 |     1 |       54 |    3 | 120KB |       |       |     . |

MY_REPT
--------------------------------------------------------------------------------
 . |          |                 |
|  4 |     BUFFER SORT                    |                |    919K | 316M |
     19 |     +2 |    54 |      50M |      |       |       |       |  28MB |
 . |          |                 |
|  5 |      PARTITION RANGE ALL           |                |    919K |   29 |
      1 |     +2 |     1 |     919K |      |       |       |       |     . |
 . |          |                 |
|  6 |       BITMAP CONVERSION TO ROWIDS  |                |    919K |   29 |
      1 |     +2 |    28 |     919K |      |       |       |       |     . |
 . |          |                 |
|  7 |        BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |         |      |

MY_REPT
--------------------------------------------------------------------------------
      1 |     +2 |    28 |     1074 |   32 | 512KB |       |       |     . |
 . |          |                 |
================================================================================
================================================================================
=================================

由于格式问题，前面的输出很难读取。您决定创建一个活动的SQL Monitor报告，该报告是图形的。

创建一个包含以下命令的SQL脚本：

SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000

SPOOL /tmp/long_sql.htm
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
         report_level => 'ALL', 
         TYPE         => 'active') 
FROM   DUAL;
SPOOL OFF

在SQL * Plus中，运行在上一步中创建的SQL脚本。
在浏览器中打开输出HTML文件，然后查看报告：

性能问题的原因在第2行中显示：笛卡尔联接。该查询的作者无意中忽略了该WHERE子句。该查询没有返回大约100万行，就像对salesand 进行内部连接一样，customers而是在取消之前返回了5000万行。从两个表中对联接的数据进行排序消耗了大部分的数据库时间（第1行）。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 报告简单数据库操作：方案

评论