Skip to Main Content
  • Questions
  • Not being utilized all the processors capacity

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Subba Rao.

Asked: June 30, 2020 - 6:00 am UTC

Last updated: July 01, 2020 - 1:12 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear sir,

Thank you for your support

I have Oracle 11g Database server which has 2 dual core processors. It is very critical database and very frequently we are getting performance issues.
We noticed that the CPU utilization is not crossing 5% and most of the processors are not being used (1 processors is being used out of 4).

Request you to suggest how to utilize all the processors and improve the performance of 11g Database.


Thanking you,

Regards
Subbarao Dasari

and Chris said...

If the application runs slowly, but you're only using 5% of the available CPU, the application/database is spending lots of time doing "something else".

This something else could be any combination of

- Disk I/O
- Network
- Blocking sessions
- Running application code
- ...

To figure out where the time is going, profile your application!

You can do this with SQL trace like so:

alter session set tracefile_identifier = app_trace;
exec sys.dbms_monitor.session_trace_enable ( session_id => :session, serial_num => :serial#, waits => true, binds => true );

... capture execution details ...
  
exec sys.dbms_monitor.session_trace_disable ( session_id => :session, serial_num => :serial# );


Then get the trace file from the database server with TKPROF (or other tool of your choice). Read more about how to do this at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

Or if you're calling PL/SQL, you could use the hierarchical profiler: https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

Once you have these profiles, you can see where the system is spending all its time. Without this, all we can do is give blind guesses (which will probably be wrong).

If you need help understanding the output of the above, post them as a review here and we'll see how we can help.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.