解决exp导出慢的异常(ID 729248.1)

 

In this Document

Symptoms

Cause

Solution

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 08-FEB-2013***

Symptoms

Conventional and direct path exports/imports are extremely slow, the performance is very low.

Cause

The following statement is consuming all CPU and I/O:

SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

Please run in SQL*Plus:

set time on timing on
connect / as sysdba
select count(*) from OPQTYPE$ where TYPE = 1 AND BITAND (FLAGS, 2) = 2;

-- How long does it take ?

explain plan for select count(*) from OPQTYPE$ where TYPE = 1 AND BITAND (FLAGS, 2) = 2;

You can check the explain plan. It is using full table scan, which takes too long time and degrading performance.

A good way to confirm this is by running AWR report that should show something similar to the following:

#1 Segments by Logical Reads shows OPQTYPE$ at 94% reads
---------------------------------------------------------
Segments by Logical Reads DB/Inst: xxx/xxx Snaps: 221-222
-> Total Logical Reads: 1,059,182,829
-> Captured Segments account for 100.1% of Total

Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM OPQTYPE$ TABLE ############ 94.64
SYS SYSTEM I_OBJ# INDEX 14,976,112 1.41
SYS SYSTEM TAB$ TABLE 7,869,328 .74
SYS SYSTEM I_OBJ1 INDEX 6,094,608 .58
SYS SYSTEM SEG$ TABLE 5,901,136 .56
-------------------------------------------------------------

#2
Top SQL ordered by Cluster Wait Time
----------------------------------------
SQL ordered by Cluster Wait Time DB/Inst: XXX/xxx1
Snaps: 221-222
Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
------------- ---------- ----------- ----------- -------------- ------------
6.46 0.2 3,125.84 2,987.63 2,766 81xv812rrxj0m

Module: exp@xxx (TNS V1-V3) SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

#3
SQL ordered by Gets

DB/Inst: XXX/xxx Snaps: 0001-0002 ->
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

-> Total Buffer Gets: 1,059,182,829
-> Captured SQL account for 99.8% of Total Gets

Gets CPU Elapsed
Buffer Gets    Executions   per Exec       %Total Time (s) Time (s)  SQL Id
-------------- ------------ ------------ ------ -------- --------- ---------
 1,000,909,019        2,766  361,86  1.5   94.5  2987.63   3125.84 81xv812rrxj0m
Module: exp@XXX (TNS V1-V3) SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

#4
-----
SQL ordered by CPU Time

DB/Inst: XXXX/XXX
Snaps: 221-222 ->
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. ->
% Total DB Time is the Elapsed Time of the SQL statement divided into the
Total Database Time multiplied by 100

CPU Elapsed CPU per % Total
Time (s)   Time (s)   Executions   Exec (s)    DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
     2,988      3,126        2,766        1.08    66.0 81xv812rrxj0m
Module: exp@XXX (TNS V1-V3) SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

SQL ordered by Elapsed Time

DB/Inst: XXX/xxx1
Snaps: 221-222 ->
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. ->
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed CPU Elap per % Total
Time (s)   Time (s)   Executions     Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- ------------
     3,126      2,988        2,766        1.1    66.0 81xv812rrxj0m
Module: Xxx (TNS V1-V3) SELECT SCHEMAOID FROM SYS.EXU9XMLST WHERE TOBJID = :1

Solution

# sqlplus /nolog

connect / as sysdba
create index OPQTYPE_IDX1 on OPQTYPE$(TYPE,BITAND (FLAGS, 2));
execute dbms_stats.gather_table_stats ('SYS', 'OPQTYPE$');

Then re-execute the same test as performed above.

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

转载于:http://blog.itpub.net/26442672/viewspace-760711/