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;
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
---------------------------------------------------------
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$');
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/