Oracle CPU usage Optimization

I like Oracle very much. I also like to sum up the experience and lessons on optimizing the CPU usage of Oracle in my work. Let's talk about this question in detail. CPU is one of the servers

I like Oracle very much. I also like to sum up the experience and lessons on optimizing the CPU usage of Oracle in my work. Let's talk about this question in detail. CPU is one of the servers

I like Oracle very much. I also like to sum up the experience and lessons on optimizing the CPU usage of Oracle in my work. Let's talk about this question in detail. CPU is an important resource of the server. A good working status of the server is that the CPU usage exceeds 90% during peak hours. If the CPU usage of Oracle optimization is above 90% during idle time, the server lacks CPU resources. If the CPU usage of Oracle optimization is still low during peak hours, the server CPU resources are surplus.

View Oracle CPU usage Optimization

Run the same command to view the CPU usage. For general Unix operating system servers, run the "sar u" command to view the Oracle optimized CPU usage and Windows NT operating system servers, you can use the Performance Manager to view CPU usage.

View the CPU usage time of the database

You can view the "CPU used by this session" statistical item in the V $ SYSSTAT data dictionary to learn the CPU time used by the Oracle database, view the "OS User level CPU time" statistical item to learn the CPU time in the User mode of the operating System, view the "OS System call CPU time" statistical item to know the CPU time in the System mode of the operating System, the total CPU time of the operating system is the sum of user and system state time. If the CPU time used by the Oracle database accounts for more than 90% of the total CPU time of the operating system, the server CPU is basically used by the Oracle database. Otherwise, the CPU of the server is too occupied by other programs, and the Oracle database cannot get more CPU time.

The database administrator can also view the V $ SYSSTAT data dictionary to obtain the CPU time occupied by each session currently connected to the Oracle database, so as to know which session consumes a large number of server CPUs. There are many situations where CPU resources are insufficient. Re-parsing SQL statements, inefficient SQL statements, and lock conflicts may cause insufficient CPU resources.

View SQL statement Parsing

The database administrator can execute the following statements to view the parsing of SQL statements:

SELECT * from v $ SYSSTAT

WHERE NAME IN

('Parse time CPUs ', 'parse time elapsed', 'parse count (hard )');

Here, "parse time cpu" is the system service time, "parse time elapsed" is the response time, and the user wait time is waite time = parse time elapsed-parse time cpu.

The average parsing wait time = waite time/parse count can be obtained. The average waiting time should be close to 0. If the average waiting time for resolution is too long, the database administrator can use the following statements to find out which SQL statements are less efficient.

SELECT SQL _TEXT, parse_cils, EXECUTIONS FROM V $ SQLAREA

Order by parse_cils;

Programmers can optimize these statements or add the Oracle parameter SESSION_CACHED_CURSORS. The preceding section describes how to optimize CPU usage in Oracle.