| High activity of "delete from smon_scn_time" in statspack report [message #210307] |
Wed, 20 December 2006 04:33  |
Peter Babka
Messages: 2
Registered: March 2003
|
Junior Member |
|
|
Hi,
This statement is periodically on the top eating up all CPU resources. Is there anybody ever seen this?
Any idea?
The op.sys is SLES9, Oracle 10gR2.
The snapshot period was 60 minutes.
Thanks
Peter
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: LKSZDB1/lkszdb1 Snaps: 151-161
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 742
-> Captured SQL accounts for 182.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
1352.37 31,835 0.04 182.1 1352.60 278,756,378 146931816
delete from smon_scn_time where thread=0 and scn = (select min(
scn) from smon_scn_time where thread=0)
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: LKSZDB1/lkszdb1 Snaps: 151-161
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 1,709
-> Captured SQL accounts for 79.4% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
1352.60 31,835 0.04 79.2 1352.37 0 146931816
delete from smon_scn_time where thread=0 and scn = (select min(
scn) from smon_scn_time where thread=0)
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: LKSZDB1/lkszdb1 Snaps: 151-161
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 283,493,779
-> Captured SQL accounts for 98.4% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
278,756,378 31,835 8,756.3 98.3 1352.37 1352.60 146931816
delete from smon_scn_time where thread=0 and scn = (select min(
scn) from smon_scn_time where thread=0)
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: LKSZDB1/lkszdb1 Snaps: 151-161
-> End Executions Threshold: 100 Total Executions: 59,846
-> Captured SQL accounts for 75.5% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
31,835 0 0.0 0.04 0.04 146931816
delete from smon_scn_time where thread=0 and scn = (select min(
scn) from smon_scn_time where thread=0)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
