本文共 8123 字，大约阅读时间需要 27 分钟。
[20150904]exp slow.txt
--昨天看一个贴子,链接如下:
--发现几个问题:
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 GetsBuffer 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 -------------------------------- 6C3FCF2D9D354DC1E03408002087A0B7SCOTT@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
-------------------------------- 6C3FCF2D9D354DC1E03408002087A0B7SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3vwz8ctc13xrr, child number 0 ------------------------------------- SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :xPlan 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越来越少了。
转载地址：http://lvlda.baihongyu.com/