Home » RDBMS Server » Performance Tuning » High activity of "delete from smon_scn_time" in statspack report
High activity of "delete from smon_scn_time" in statspack report [message #210307] Wed, 20 December 2006 04:33 Go to next message
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)
Re: High activity of "delete from smon_scn_time" in statspack report [message #210316 is a reply to message #210307] Wed, 20 December 2006 06:14 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
could be related to bug no 5346269.
Raise a tar.
Previous Topic: Process tuning
Next Topic: Which One is better... VARCHAR or Number
Goto Forum:
  


Current Time: Wed Dec 07 02:48:33 CST 2016

Total time taken to generate the page: 0.06659 seconds