linux 32位平台下Oracle 9.2.0.4删除表空间慢问题-latch等待？？？

rongshiyuan

linux 32位平台下Oracle 9.2.0.4删除表空间问题
操作系统：SUSE Linux Enterprise Server 10 SP1 (i586)
数据库：Oracle9i Enterprise Edition Release 9.2.0.4.0
问题描述：该环境下表空间数据量接近5W，删除表空间比较缓慢
开启10046事件，执行drop tablespace...语句，如下：

1. SQL> alter session set events '10046 trace name context forever,level 12';
   
2. 
   
3. Session altered.
   
4. 
   
5. SQL> begin
   
6.   2  execute immediate 'drop tablespace TSSOCKET1120121231_20 including contents';
   
7.   3  end;
   
8.   4  /
   
9. 
   
10. SQL> alter session set events '10046 trace name context off';
    
11. 
    

复制代码

根据trace文件中出现大量的信息“WAIT #2: nam='latch free' ela= 52 p1=-2013707200 p2=147 p3=4”
查询latch信息如下：

1. SQL> select latch#,name from v$$latch where latch#=147;
   
2. 
   
3.     LATCH# NAME
   
4. ---------- ----------------------------------------------------------------
   
5.        147 row cache objects
   

复制代码

删除一个表空间耗时大概在5~10分钟左右，麻烦各位高手帮具体分析如果进一步诊断及优化，感谢！

tkprof格式上述trace文件如下：

TKPROF: Release 9.2.0.4.0 - Production on Tue Mar 19 17:25:32 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: /opt/oracle/admin/CMCCWAP/udump/cmccwap_ora_834.trc
Sort options: fchela  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

error connecting to database using: system/oracle
ORA-01017: invalid username/password; logon denied

EXPLAIN PLAN option disabled.
********************************************************************************

SELECT count(*) FROM all_policies v WHERE v.object_owner =
:b2 AND v.object_name = :b1 AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.04          5        101          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.04          5        101          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35     (recursive depth: 3)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         5        0.03          0.04
********************************************************************************

SELECT count(*) FROM user_policies v WHERE v.object_name
= :b1 AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         96          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         96          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35     (recursive depth: 3)
********************************************************************************

BEGIN
  IF (xdb.DBMS_XDBZ.is_hierarchy_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name)) THEN
    xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
  END IF;
  EXCEPTION
    WHEN OTHERS THEN
     null;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.04          5        134          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.04          5        134          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 35     (recursive depth: 2)
********************************************************************************

declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
    end if;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 32     (recursive depth: 3)

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.01          0          0          0           1

Misses in library cache during parse: 2
Misses in library cache during execute: 1

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      159.00        165.35
  log file sync                                   1        0.10          0.10


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      220      0.05       0.26          0          0          0           0
Execute    322    103.21     572.91         25       2191        817         139
Fetch      248      2.70      11.20     226246     251017          0         136
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      790    105.97     584.37     226271     253208        817         275

Misses in library cache during parse: 92

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  latch free                                 115181        0.34        467.05
  db file scattered read                      16418        0.09          9.18
  db file sequential read                       941        0.06          0.45
  log file sync                                   3        0.14          0.21
  control file sequential read                  106        0.04          0.24
  control file parallel write                     9        0.10          0.47
  rdbms ipc reply                                 4        0.52          0.65
  enqueue                                         2        0.53          0.64

    9  user  SQL statements in session.
  217  internal SQL statements in session.
  226  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: /opt/oracle/admin/CMCCWAP/udump/cmccwap_ora_834.trc
Trace file compatibility: 9.00.01
.....

Maclean Liu(刘相兵

SELECT count(*) FROM user_policies v WHERE v.object_name
= :b1 AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%')
END OF STMT
EXEC #14:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,tim=1331722764617516
FETCH #14:c=0,e=181,p=0,cr=48,cu=0,mis=0,r=1,dep=3,og=4,tim=1331722764617735
EXEC #3:c=0,e=744,p=0,cr=63,cu=0,mis=0,r=1,dep=2,og=4,tim=1331722764617764
WAIT #2: nam='latch free' ela= 37240 p1=-2013707200 p2=147 p3=0
WAIT #2: nam='latch free' ela= 89 p1=-2013707200 p2=147 p3=1
WAIT #2: nam='latch free' ela= 93 p1=-2013707200 p2=147 p3=2
WAIT #2: nam='latch free' ela= 91 p1=-2013707200 p2=147 p3=3
WAIT #2: nam='latch free' ela= 26965 p1=-2013707200 p2=147 p3=0
WAIT #2: nam='latch free' ela= 529 p1=-2013707200 p2=147 p3=1


latch free ==. -2013707200  FFFFFFFF87F94440

Maclean Liu(刘相兵

该环境下表空间数据量接近5W==> 5w是啥？

9i 特别是 9.2.0.8 之前 drop tablespace 还是先考虑把 表空间上的对象 都truncate => drop掉之后再DROp吧

rongshiyuan

感谢刘大回复
表空间数据量接近5万个

rongshiyuan

是表空间数量接近5万