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.

Creating STATSPACK:

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

oracle_home\rdbms\admin\spreport.sql
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.,,