Go Back   Wiki NewForum > Career Forum (Documents/Tutorials/Advice) > Tech Forum & Tutorial > Oracle Database, SQL, Application, Programming

Views: 271 Source: CNN18.com, India-Forums, TellyChakkar, India, Times of India, Youtube, BollywoodLife, Rediff  
Thread Tools Rate Thread Display Modes
Old 05-26-2009, 03:04 PM
bholus7 bholus7 is offline
Join Date:
Apr 2009
Posts: 1,322
Default Oracle Service Consuming Lot of CPU Resource

Oracle Service Consuming Lot of CPU Resource

System Config :
OS : Win 2000 A.S
Oracle 11.5.8
Rdbms : 8.1.7

Usaully my Oracle use to work very good but some times once in a blue moon , when I restarted my Database after performing a nightly backup , it startup quickly but when I look into the Task Manager , it shows Oracle.exe service is consuming a lot of CPU ( 70 80 % ) even wen no user is connected , usually it won't.

Because of this behavior my Oracle Applicatrion services takes up a long time to get UP. And after sometime Users starts complaining that Application is very slow.

I didn't understand what background process is running that is consuming a lot of CPU .
I was told to create a statspack but since I am new to DBA, can you pls. guide me how to create a statspack report.

Not getting it up quick enough is a real problem.
It could be a couiple of things. First, it takes time for your cache to "warm up". Initial database queries will take longer to run because of this. Second, maybe your database is not being brought down cleanly and it takes some time for SMON to clean things up.

Either way, I'd look at not using a cold backup.


1. Create a tablespace for this purpose called perfstat or any good name

2. Run catdbsyn.sql dbmsspool.sql
Now you must understand the data collection level. Each collection is in one of this level:

a. Level 0
b. Level 5
c. Level 6
d. Level 7
e. Level 10
- Study the STATSPACK architecture -. Very important
3. Do a snapshot to capture the RAM structure
SQL> exec statspack.snap;

Statspack report is easy to see the current dabase situation

1) create a tablespace of 100mb size

2) run the script: oracle_home\rdbms\admin\spcreate.sql
It will prompt for your default tablespace then give the above tablespace name. After that just give the temporary tablespace name other than system..

3) take some snapshots under perfstat user.
the command will be:

exec perfstat.statspack.snap
take as many snap shots u want within a time interval.

4) now run the script
it will ask for your destination and name of the output report file..
provide those...with to snapshot nos between which you want to see the reports...
now open the report file and enjoyyyyyyyyyyyy

Reply With Quote

New topics in Oracle Database, SQL, Application, Programming

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
asp web stats