sql - Oracle 查询使用比平时更多的临时空间

Question

我们的一位客户通知我们，某个进程无法完成，因为它们的临时空间 (20GB) 已用完。该过程是标准软件的一部分，我们通常需要不超过 300MB 的临时空间。

我们开始监控临时空间(Metalink 注释:364417.1)并发现了违规查询。我们还在我们的客户数据库和我们的数据库上运行了 sql 跟踪的过程。
(两个 Oracle 10.2.0.5，我们的应用程序完全相同的版本，完全相同的数据)

这是区别:

从我们的数据库跟踪:

SELECT OBC1.BCT_ID BCT_ID 
FROM
 NGG_OBJECTBASISCOMPONENT OBC1 ,NGG_OBJECTBASISCOMPONENT OBC2 ,NGG_OBJECT 
  OBJ1 ,NGG_OBJECT OBJ2 ,NGG_LAAGBASISCOMPONENT LBC1 ,NGG_LAAGBASISCOMPONENT 
  LBC2 WHERE OBC1.BCT_ID = OBC2.BCT_ID AND OBC1.OBJ_ID = OBJ1.ID AND 
  OBC2.OBJ_ID = OBJ2.ID AND OBJ1.ODE_ID IS NULL AND OBJ2.ODE_ID IS NULL AND 
  OBC1.LBC_ID = LBC1.ID AND OBC2.LBC_ID = LBC2.ID AND OBJ1.ID > OBJ2.ID AND 
  OBJ1.TRE_ID_V IS NULL AND OBJ2.TRE_ID_V IS NULL AND LBC1.LDE_ID = :B2 AND 
  LBC1.LDE_ID = LBC2.LDE_ID AND OBJ1.TRE_ID_O = :B1 AND LBC1.FOUT = 0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       24      0.01       0.00          0          0          0           0
Execute     26      0.04       0.04          0          0          0           0
Fetch       26      0.15       0.14          0      11932          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       76      0.21       0.18          0      11932          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 63     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS  (cr=3 pr=0 pw=0 time=181 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=155 us)
      0    NESTED LOOPS  (cr=3 pr=0 pw=0 time=133 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=110 us)
      0      NESTED LOOPS  (cr=3 pr=0 pw=0 time=91 us)
      0       TABLE ACCESS BY INDEX ROWID NGG_OBJECT (cr=3 pr=0 pw=0 time=65 us)
      0        INDEX RANGE SCAN NGG_OBJ_TRE_FK_O_I (cr=3 pr=0 pw=0 time=40 us)(object id 49579)
      0       TABLE ACCESS BY INDEX ROWID NGG_OBJECTBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX RANGE SCAN NGG_OBC_OBJ_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 49586)
      0      TABLE ACCESS BY INDEX ROWID NGG_OBJECTBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX RANGE SCAN NGG_OBC_BCT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 49585)
      0     TABLE ACCESS BY INDEX ROWID NGG_OBJECT (cr=0 pr=0 pw=0 time=0 us)
      0      INDEX UNIQUE SCAN NGG_OBJ_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49596)
      0    TABLE ACCESS BY INDEX ROWID NGG_LAAGBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX UNIQUE SCAN NGG_LBC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49591)
      0   TABLE ACCESS BY INDEX ROWID NGG_LAAGBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX UNIQUE SCAN NGG_LBC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49591)

********************************************************************************

除了获取的行数外，来自我们客户数据库的跟踪是相同的:

********************************************************************************

SELECT OBC1.BCT_ID BCT_ID 
FROM
 NGG_OBJECTBASISCOMPONENT OBC1 , NGG_OBJECTBASISCOMPONENT OBC2 , NGG_OBJECT 
  OBJ1 , NGG_OBJECT OBJ2 , NGG_LAAGBASISCOMPONENT LBC1 , 
  NGG_LAAGBASISCOMPONENT LBC2   WHERE OBC1.BCT_ID =  OBC2.BCT_ID  AND 
  OBC1.OBJ_ID =  OBJ1.ID  AND OBC2.OBJ_ID =  OBJ2.ID  AND OBJ1.ODE_ID IS NULL 
    AND OBJ2.ODE_ID IS NULL   AND OBC1.LBC_ID =  LBC1.ID  AND OBC2.LBC_ID =  
  LBC2.ID  AND OBJ1.ID >  OBJ2.ID  AND OBJ1.TRE_ID_V IS NULL   AND 
  OBJ2.TRE_ID_V IS NULL   AND LBC1.LDE_ID =  :b1  AND LBC1.LDE_ID =  
  LBC2.LDE_ID  AND OBJ1.TRE_ID_O =  :b2  AND LBC1.FOUT =  0   


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       24      0.00       0.00          0          0          0           0
Execute     26      0.04       0.04          0          0          0           0
Fetch       26   2414.90    2521.04     258210  624771631          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       76   2414.95    2521.09     258210  624771631          0           0

Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 64     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS  (cr=3 pr=0 pw=0 time=51 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=47 us)
      0    NESTED LOOPS  (cr=3 pr=0 pw=0 time=43 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=42 us)
      0      NESTED LOOPS  (cr=3 pr=0 pw=0 time=38 us)
      0       TABLE ACCESS BY INDEX ROWID NGG_OBJECT (cr=3 pr=0 pw=0 time=35 us)
      0        INDEX RANGE SCAN NGG_OBJ_TRE_FK_O_I (cr=3 pr=0 pw=0 time=31 us)(object id 49947)
      0       TABLE ACCESS BY INDEX ROWID NGG_OBJECTBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX RANGE SCAN NGG_OBC_OBJ_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 49954)
      0      TABLE ACCESS BY INDEX ROWID NGG_OBJECTBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX RANGE SCAN NGG_OBC_BCT_FK_I (cr=0 pr=0 pw=0 time=0 us)(object id 49953)
      0     TABLE ACCESS BY INDEX ROWID NGG_OBJECT (cr=0 pr=0 pw=0 time=0 us)
      0      INDEX UNIQUE SCAN NGG_OBJ_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49964)
      0    TABLE ACCESS BY INDEX ROWID NGG_LAAGBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX UNIQUE SCAN NGG_LBC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49959)
      0   TABLE ACCESS BY INDEX ROWID NGG_LAAGBASISCOMPONENT (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX UNIQUE SCAN NGG_LBC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49959)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write temp                      17522        0.00          0.06
  direct path read temp                       17214        0.00          0.07
  latch: cache buffers chains                     1        0.00          0.00

此查询是否生成笛卡尔积？
为什么只在这个特定的数据库实例上？

我还能做些什么来弄清楚发生了什么？

Answer 1

我想知道该计划是否是 TKPROF 中的 EXPLAIN 选项生成的，而不是运行时的实际执行计划。尝试通过查询 AWR 数据或 v$sql/v$sql_plan 在运行时(或在失败时捕获)捕获查询 - 参见 this thread on asktom更多。

(我这样说是因为计划中没有任何内容会导致使用临时空间，假设这些都不是全局临时表)