Home » SQL & PL/SQL » SQL & PL/SQL » oracle server CPU spikes (oracle 12c)
oracle server CPU spikes [message #651329] Sun, 15 May 2016 22:20 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member

Hello experts,

In my current environment i am seeing 100% cpu spike what are the factors for CPU spikes, how to trouble shoot the issues. I have AWR reports but not sure which query/sessions causing more CPU load.

Also i noticed number DB locks in application logs files. Basically i am doing stress testing by pumping more data.

Do you think db locks will cause any cpu spikes , appreciate your inputs.


Thanks,
Re: oracle server CPU spikes [message #651331 is a reply to message #651329] Sun, 15 May 2016 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What is OS name & version?

> i am seeing 100% cpu spike
How do you see 100% CPU
Would you rather see 100% CPU or 0% CPU & why?

When CPU is less than 100%, it is like a light bulb on a dimmer switch & light is not fully lit.

> not sure which query/sessions causing more CPU load.
Use normal OS utilities

>Basically i am doing stress testing by pumping more data.
So you are the cause of the load & CPU spikes.
Re: oracle server CPU spikes [message #651333 is a reply to message #651331] Sun, 15 May 2016 22:59 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks BS for your response .. please see my comments


> i am seeing 100% cpu spike
How do you see 100% CPU
Would you rather see 100% CPU or 0% CPU & why?

it is linux OS, using nmon reports i saw CPU 100% usage .. hence in application i see ORA-12170: TNS:Connect timeout occurred


When CPU is less than 100%, it is like a light bulb on a dimmer switch & light is not fully lit.

> not sure which query/sessions causing more CPU load.
Use normal OS utilities

any useful sample queries ?

>Basically i am doing stress testing by pumping more data.
So you are the cause of the load & CPU spikes

i want to know out of 100 queries fired ..if any query/session causing more spikes .. based on it DB cpu cores can be increased or query get tuned.
Re: oracle server CPU spikes [message #651334 is a reply to message #651333] Sun, 15 May 2016 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i see ORA-12170: TNS:Connect timeout occurred
Most common cause of TIMEOUT is FireWall on or between client & DB Server

>any useful sample queries ?
top

>based on it DB cpu cores can be increased or query get tuned
yes, either might avoid 100% CPU load; then again might not.
Re: oracle server CPU spikes [message #651404 is a reply to message #651334] Tue, 17 May 2016 08:10 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Why not use simple tools available if you don't want to query oracle tables : http://www.myorasql.com/
Previous Topic: Batch file to run sql script
Next Topic: Compare 2 tables and upadte 1
Goto Forum:
  


Current Time: Fri Mar 29 05:53:57 CDT 2024