Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Poor Performance
- Thread starter unixkid
- Start date
Oracle does not eat a lot of cpu by itself.
If some oracle process are using a lot of cpu for a long time, it usually means that a big request was made to the database (query or update).
if the procees starts with oracle, then it a user connection else if it starts with ora_ , it is an oracle core process.
Is it one process that grabs most of the cpu or multiple processes ?
How many users do you have connected and what is the hardware ?
If some oracle process are using a lot of cpu for a long time, it usually means that a big request was made to the database (query or update).
if the procees starts with oracle, then it a user connection else if it starts with ora_ , it is an oracle core process.
Is it one process that grabs most of the cpu or multiple processes ?
How many users do you have connected and what is the hardware ?
- Thread starter
- #3
Server is E6500 14 cpu/10gb memory. I see a proc called ora_snp0_SID (SID Being the true name of our dbase) I'm not sure what a "snp0" is?
Only one use connected which is DBA. All other are coming in via Oracle ERP application "app mnger & con mgr"
vmstat 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s5 s9 s9 s9 in sy cs us sy id
4 0 0 72304 3392 7 250 2223 0 2540 0 546 3 0 3 0 2823 14206 1877 63 4 32
7 0 0 10704672 293680 6 31 1990 0 1841 0 799 0 0 0 0 2307 23988 1007 94 6 1
5 0 0 10703808 293944 1 19 1662 0 2892 0 785 0 0 0 0 2345 11043 1087 92 6 2
9 0 0 10703008 297544 33 42 1659 0 3374 0 682 1 0 0 0 2586 28809 1365 94 6 0
8 0 0 10702224 304136 7 317 1457 0 2673 0 511 0 0 0 0 2632 22773 1427 93 6 1
5 0 0 10701880 305600 5 35 2404 0 2804 0 447 0 0 0 0 2227 19103 916 93 5 2
7 0 0 10700912 307920 39 211 1110 0 2185 0 401 2 0 0 0 2559 31006 1409 94 5 1
8 0 0 10699648 307400 13 55 1504 0 2283 0 415 0 0 0 0 2539 12884 1325 93 6 1
10 0 0 10699120 302344 2 22 816 0 2788 0 531 0 0 0 0 2589 23358 1362 94 6 0
9 0 0 10698408 290952 2 31 886 0 4633 0 814 0 0 0 0 2947 19618 1797 93 7 0
10 0 0 10697000 283616 8 193 2267 0 5368 0 1003 1 0 0 0 2929 29259 1746 92 8 0
10 0 0 10694944 286960 4 338 2352 0 5912 0 969 0 0 0 0 2993 12747 1792 94 6 0
Everything is slow, even my mailq is held up. I know the CPU is thrashed but there must be a way to track it back to the data the system is trying to access.
Only one use connected which is DBA. All other are coming in via Oracle ERP application "app mnger & con mgr"
vmstat 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s5 s9 s9 s9 in sy cs us sy id
4 0 0 72304 3392 7 250 2223 0 2540 0 546 3 0 3 0 2823 14206 1877 63 4 32
7 0 0 10704672 293680 6 31 1990 0 1841 0 799 0 0 0 0 2307 23988 1007 94 6 1
5 0 0 10703808 293944 1 19 1662 0 2892 0 785 0 0 0 0 2345 11043 1087 92 6 2
9 0 0 10703008 297544 33 42 1659 0 3374 0 682 1 0 0 0 2586 28809 1365 94 6 0
8 0 0 10702224 304136 7 317 1457 0 2673 0 511 0 0 0 0 2632 22773 1427 93 6 1
5 0 0 10701880 305600 5 35 2404 0 2804 0 447 0 0 0 0 2227 19103 916 93 5 2
7 0 0 10700912 307920 39 211 1110 0 2185 0 401 2 0 0 0 2559 31006 1409 94 5 1
8 0 0 10699648 307400 13 55 1504 0 2283 0 415 0 0 0 0 2539 12884 1325 93 6 1
10 0 0 10699120 302344 2 22 816 0 2788 0 531 0 0 0 0 2589 23358 1362 94 6 0
9 0 0 10698408 290952 2 31 886 0 4633 0 814 0 0 0 0 2947 19618 1797 93 7 0
10 0 0 10697000 283616 8 193 2267 0 5368 0 1003 1 0 0 0 2929 29259 1746 92 8 0
10 0 0 10694944 286960 4 338 2352 0 5912 0 969 0 0 0 0 2993 12747 1792 94 6 0
Everything is slow, even my mailq is held up. I know the CPU is thrashed but there must be a way to track it back to the data the system is trying to access.
Tne ORA_SNP process is the process which run the jobs scheduled in DBMS_JOBS. The number Oracle starts up is controlled by the job_queue_processes init.ora parameter.
if you are not using the DBMS_JOBS utility then set the parameter to 0.
This process has also caused me problems with many versions of Oracle RDBMS. It seems that it loops and chews up CPU. I am not using it anymore (use cron instead).
There may be a patch for the version of Oracle you are using (doubt it).
if you are not using the DBMS_JOBS utility then set the parameter to 0.
This process has also caused me problems with many versions of Oracle RDBMS. It seems that it loops and chews up CPU. I am not using it anymore (use cron instead).
There may be a patch for the version of Oracle you are using (doubt it).
- Thread starter
- #5
DBMS_JOBS are user defined jobs. It is the scheduler from Oracle.
If you do not have any jobs that you want to schedule, then do as I said prior , set job_queue_processes to 0 in your init file, and then do a shutdown and restart of the database. ora_snp... process sould not be there anymore.
If you do have user scheduled jobs than you need to run, (usually PLSQL) then you can wrtie a script that will run the job and then put than script in cron.
If you have the replication or advanced queuing, these features also use DBMS_JOBS.
Also look at the tables DBA_JOBS and USER_JOBS to find out if you have any jobs scheduled. look at next scheduled time
You can also look at the content of table DBA_JOBS_RUNNING to see what is currently running.
If you do not have any jobs that you want to schedule, then do as I said prior , set job_queue_processes to 0 in your init file, and then do a shutdown and restart of the database. ora_snp... process sould not be there anymore.
If you do have user scheduled jobs than you need to run, (usually PLSQL) then you can wrtie a script that will run the job and then put than script in cron.
If you have the replication or advanced queuing, these features also use DBMS_JOBS.
Also look at the tables DBA_JOBS and USER_JOBS to find out if you have any jobs scheduled. look at next scheduled time
You can also look at the content of table DBA_JOBS_RUNNING to see what is currently running.
- Status
Similar threads
- Locked
- Question