ITPub博客

首页 > 数据库 > Oracle > [20150904]exp slow.txt

[20150904]exp slow.txt

原创 Oracle 作者:lfree 时间:2015-09-06 09:16:12 0 删除 编辑

[20150904]exp slow.txt

--昨天看一个贴子,链接如下:
http://www.itpub.net/thread-1936560-1-1.html

--发现几个问题:
1.第1个问题:

delete from RecycleBin$ where bo=:1;
delete from RecycleBin$ where purgeobj=:1;

--都是全表扫描,如果你对象太多,一定很慢.

2.第2个问题:
可以看到exp会执行如下语句:
SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

--仔细查看awr的sql部分:
SQL ordered by Gets

Buffer Gets  Executions Gets per Exec   %Total Elapsed Time (s)  %CPU  %IO    SQL Id        SQL Module  SQL Text
188,385,092  211        892,820.34      19.71        10,324.37  66.84  29.40  81xv812rrxj0m exp.exe     SELECT SCHEMAOID FROM SYS.EXU9...

Gets per Exec=892,820.34 , 每次的逻辑读也太高了.

我在我的测试环境上测试看看:


SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> alter session set statistics_level=all;
Session altered.

SCOTT@test> variable x number ;
SCOTT@test> exec :x := 56060;

PL/SQL procedure successfully completed.

SCOTT@test> SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :x;
SCHEMAOID
--------------------------------
6C3FCF2D9D354DC1E03408002087A0B7

SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3vwz8ctc13xrr, child number 0
-------------------------------------
SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :x
Plan hash value: 918491496
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |       |   439 (100)|          |      1 |00:00:00.01 |    1654 |       |       |          |
|*  1 |  FILTER                              |          |      1 |        |       |            |          |      1 |00:00:00.01 |    1654 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN               |          |      1 |     25 |   525 |   439   (1)| 00:00:01 |     43 |00:00:00.01 |    1612 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | I_OBJ1   |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |    BUFFER SORT                       |          |      1 |     25 |   400 |   437   (1)| 00:00:01 |     43 |00:00:00.01 |    1609 |  4096 |  4096 | 4096  (0)|
|*  5 |     TABLE ACCESS FULL                | OPQTYPE$ |      1 |     25 |   400 |   437   (1)| 00:00:01 |     43 |00:00:00.01 |    1609 |       |       |          |
|*  6 |   FILTER                             |          |     42 |        |       |            |          |      0 |00:00:00.01 |      42 |       |       |          |
|*  7 |    CONNECT BY WITH FILTERING (UNIQUE)|          |     42 |        |       |            |          |      0 |00:00:00.01 |      42 |  1024 |  1024 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID      | NTAB$    |     42 |      2 |    16 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      42 |       |       |          |
|*  9 |      INDEX RANGE SCAN                | I_NTAB1  |     42 |      2 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |      42 |       |       |          |
|  10 |     NESTED LOOPS                     |          |      0 |      4 |    84 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |      CONNECT BY PUMP                 |          |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|  12 |      TABLE ACCESS CLUSTER            | NTAB$    |      0 |      2 |    16 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 13 |       INDEX UNIQUE SCAN              | I_OBJ#   |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / O@SEL$2
   5 - SEL$F5BB74E1 / OPQ@SEL$2
   6 - SEL$0EEC8FC1
   8 - SEL$6        / NT@SEL$6
   9 - SEL$6        / NT@SEL$6
  10 - SEL$5
  12 - SEL$5        / NT@SEL$5
  13 - SEL$5        / NT@SEL$5
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 56060
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("O"."OBJ#"="OPQ"."OBJ#" OR  IS NOT NULL))
   3 - access("O"."OBJ#"=:X)
   5 - filter(("OPQ"."TYPE"=1 AND BITAND("OPQ"."FLAGS",2)=2))
   6 - filter("NT"."NTAB#"=:B1)
   7 - access("NT"."OBJ#"=PRIOR NULL)
   9 - access("NT"."OBJ#"=:B1)
  13 - access("connect$_by$_pump$_005"."PRIOR nt.ntab# "="NT"."OBJ#")

--要全表扫描OPQTYPE$!逻辑读1654。

SCOTT@test> select count(*) from sys.OPQTYPE$;
  COUNT(*)
----------
       193

--共193行,而执行计划查询过滤条件(("OPQ"."TYPE"=1 AND BITAND("OPQ"."FLAGS",2)=2))返回43行。

--但是看定义
CREATE TABLE SYS.OPQTYPE$
(
  OBJ#       NUMBER                             NOT NULL,
  INTCOL#    NUMBER                             NOT NULL,
  TYPE       NUMBER,
  FLAGS      NUMBER,
  LOBCOL     NUMBER,
  OBJCOL     NUMBER,
  EXTRACOL   NUMBER,
  SCHEMAOID  RAW(16),
  ELEMNUM    NUMBER,
  SCHEMAURL  VARCHAR2(4000 BYTE)
)
CLUSTER SYS.C_OBJ#(OBJ#);

--是一个cluster table。如果对象很多实际上占用空间会很大的。

SCOTT@test> select num_rows,blocks from dba_tables where owner='SYS' and table_name='OPQTYPE$';
  NUM_ROWS     BLOCKS
---------- ----------
       193       1605

--占用块达到了1605. 按照一些提示,建立索引:
create index OPQTYPE_IDX1 on OPQTYPE$(TYPE,BITAND (FLAGS, 2));

SYS@test> execute dbms_stats.gather_table_stats ('SYS', 'OPQTYPE$');
PL/SQL procedure successfully completed.

SCOTT@test> SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :x;

SCHEMAOID
--------------------------------
6C3FCF2D9D354DC1E03408002087A0B7

SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3vwz8ctc13xrr, child number 0
-------------------------------------
SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :x

Plan hash value: 3256635089

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |       |    41 (100)|          |      1 |00:00:00.01 |      69 |       |       |          |
|*  1 |  FILTER                              |              |      1 |        |       |            |          |      1 |00:00:00.01 |      69 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN               |              |      1 |     57 |  1197 |    41   (0)| 00:00:01 |     43 |00:00:00.01 |      27 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | I_OBJ1       |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   4 |    BUFFER SORT                       |              |      1 |     57 |   912 |    39   (0)| 00:00:01 |     43 |00:00:00.01 |      24 |  4096 |  4096 | 4096  (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID      | OPQTYPE$     |      1 |     57 |   912 |    39   (0)| 00:00:01 |     43 |00:00:00.01 |      24 |       |       |          |
|*  6 |      INDEX RANGE SCAN                | OPQTYPE_IDX1 |      1 |     57 |       |     0   (0)|          |     43 |00:00:00.01 |       1 |       |       |          |
|*  7 |   FILTER                             |              |     42 |        |       |            |          |      0 |00:00:00.01 |      42 |       |       |          |
|*  8 |    CONNECT BY WITH FILTERING (UNIQUE)|              |     42 |        |       |            |          |      0 |00:00:00.01 |      42 |  1024 |  1024 |          |
|   9 |     TABLE ACCESS BY INDEX ROWID      | NTAB$        |     42 |      2 |    16 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      42 |       |       |          |
|* 10 |      INDEX RANGE SCAN                | I_NTAB1      |     42 |      2 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |      42 |       |       |          |
|  11 |     NESTED LOOPS                     |              |      0 |      4 |    84 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|  12 |      CONNECT BY PUMP                 |              |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |      TABLE ACCESS CLUSTER            | NTAB$        |      0 |      2 |    16 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 14 |       INDEX UNIQUE SCAN              | I_OBJ#       |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / O@SEL$2
   5 - SEL$F5BB74E1 / OPQ@SEL$2
   6 - SEL$F5BB74E1 / OPQ@SEL$2
   7 - SEL$0EEC8FC1
   9 - SEL$6        / NT@SEL$6
  10 - SEL$6        / NT@SEL$6
  11 - SEL$5
  13 - SEL$5        / NT@SEL$5
  14 - SEL$5        / NT@SEL$5
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 56060
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("O"."OBJ#"="OPQ"."OBJ#" OR  IS NOT NULL))
   3 - access("O"."OBJ#"=:X)
   6 - access("OPQ"."TYPE"=1 AND "OPQ"."SYS_NC00011$"=2)
   7 - filter("NT"."NTAB#"=:B1)
   8 - access("NT"."OBJ#"=PRIOR NULL)
  10 - access("NT"."OBJ#"=:B1)
  14 - access("connect$_by$_pump$_005"."PRIOR nt.ntab# "="NT"."OBJ#")

--逻辑读降为69.不过对方的逻辑读Gets per Exec=892,820.34 ,一定与我的不同。

SYS@test> drop  index sys.OPQTYPE_IDX1 ;
Index dropped.

--如果exp很慢,可以考虑建立这个索引,不过现在使用exp越来越少了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1791999/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2471
  • 访问量
    6278988