[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越来越少了。
