时间戳

首页 > 解决方案 > Oracle 中的性能问题（公用表表达式和全局临时表之间的差异）

sql - Oracle 中的性能问题（公用表表达式和全局临时表之间的差异）

问题描述

我的一个查询存在性能问题。

查询的结构如下

With a02 as  
    (...
    );     

SELECT *
FROM
a02
inner join   
a03 on a02.id=a03.id;

表 a02 大约 10000 行，表 a03 大约 40000 行。查询运行大约需要1.5 小时。

但是，如果我将 a02 创建为全局临时表，然后运行下面的查询，则需要不到5 mins。这是正常行为吗？

SELECT *
FROM
a02
inner join   
a03 on a02.id=a03.id

我对使用全局临时表犹豫不决，因为我们有时会在删除表时收到以下消息：

删除表 A02；

SQL 错误：ORA-14452：尝试在已使用的临时表上创建、更改或删除索引 14452。00000 -“尝试在已使用的临时表上创建、更改或删除索引”

标签： sqloracledatabase-performance

解决方案

我建议确定查询的 SQL id，然后使用 SQL Monitor Report，因为它会准确地告诉您执行计划是什么以及 SQL 花费大部分时间的位置。

从 SQL*Plus 获取 SQL Monitor Report 的简单方法如下：

spool c:\temp\SQL_Monitor_rpt.html

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

alter session set "_with_subquery" = optimizer;

SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '&SQLID' ,
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;

spool off

很可能在不使用索引的情况下进行全表扫描和连接。您可能应该索引连接条件中涉及的两个表中的列。此外，您可以尝试在 WITH 子句子查询中使用 /*+ MATERIALIZE */ 提示来模拟全局临时表，而实际上不需要它。

推荐阅读