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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Subba Rao.

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

Answered by: Chris Saxon - Last updated: July 01, 2020 - 1:12 pm UTC

Category: Database Administration - Version: 11g

Viewed 100+ 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 we 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.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.