Query CPU status in Oracle and CPU resources/Linux

Address: http://ivan4126.blog.163.com/blog/static/2094910922012720115846705/

Address: http://www.linuxdiyf.com/viewarticle.php? Id = 9743

Query CPU status in Oracle and CPU resources/Linux

16:19:08 | category:

Oracle | label:
| Font size
Subscription

-- CPU space and Busy Conditions
Select
* From v $ osstat;

User: CPU usage of the user level (Application)
Nice: CPU usage of the user level with nice priority added
SYS: CPU usage of system level (kernel)
Idle: idle CPU resources

-- View the CPU usage of each session:
Select ss. Sid, se. Command, ss. Value CPU, se. username, se. Program
From v $ sesstat SS, V $ session se
Where ss. Statistic #
In
(Select statistic #
From v $ statname
Where name
= 'Cpu used by this session ')
And se. Sid = ss. Sid
And ss. Sid> 6
Order
By cpu desc;

Here there are a lot of items included, you can use select * to see.

-- Compare which session has the most CPU usage time, and then view the specific situation of the session:
Select S. Sid, S. event, S. Machine, S. osuser, S. wait_time, W. seq #, Q. SQL _text
From v $ session_wait W, V $ session S, V $ PROCESS p, V $ sqlarea Q
Where S. paddr = P. ADDR
And
S. Sid = & P
And
S. SQL _address = Q. address;

Query Based on the SID determined by the previous SQL statement.

Result:

Oracle and CPU resources

The Oracle Database shares CPU resources with all other software running on the same server. If the CPU processing capability is insufficient, reducing the CPU consumption of oracle or non-Oracle programs can improve the performance of all processes on the server.

If all the CPUs on the machine are busy, these processes will wait in queue for CPU usage. Because processes need to wait for CPU resources, the queues formed by these processes are also called running queues. The more busy the CPU, the longer the process waits in the running queue. Processes in the queue do not do any work. When the queue is run longer, the response time decreases.

You can use the standard operating system monitoring tool to check the CPU usage on the host.

Tuning the CPU utilization is essentially tuning each individual task: it will reduce the number of commands required to complete the task and/or reduce the number of commands to be executed. You can use Server Load balancer, SQL optimization, and application design improvement to perform CPU optimization. For such optimization, you need to have a deep understanding of the purpose of these tasks and how they are executed.

As mentioned above, an in-depth discussion of all Oracle Database optimization issues is not the topic of this book. However, some common tasks often lead to excessive CPU usage. If there is a lack of CPU resources in the database, consider the following factors:

Bad SQL statements

Poor SQL statements are the top cause of performance problems. Oracle databases always try to optimize the SQL statements submitted by customers. If the SQL statements in your application are poorly written, even if Oracle adopts the Best optimization solution, oracle will consume more resources than actually needed to execute this SQL statement. Since the adjustment of SL requires a deep understanding of Oracle's working principles and application requirements, the entire process is difficult and time-consuming. Early checks can detect design problems of the underlying database, resulting in modifying the table structure and indexes. Before the Oracle database is 10 Gb, you need to re-test the SQL statement and redeploy the application.

Oracle Database 10 Gb introduces the SQL Tuning Advisor tool, which not only recognizes SQL statements with code slots, but also creates optimizer plans to solve such problems, the improved plan is used to replace the standard optimization scheme. With such a tool, you can improve the performance of those bad SQL statements without modifying any code. The SQL advisor tool in Oracle Database 11g includes the functions of the SQL Tuning Advisor, SQL access advisor, and partition advisor components.

The Oracle Database GB can automatically detect SQL query operations with the largest workload and create SQL profile information as appropriate to improve their performance. In addition, this feature also provides suggestions for creating new indexes to improve the performance of these statements.

The Oracle Database 4th GB also supports tracking SQL statement execution plan changes. This is described in Chapter 1. Its optimizer maintains the history information of the execution plan. When a new plan is detected, the optimizer evaluates the performance of the new plan while using the old plan. Once the optimizer finds that the new plan can provide the same performance, it replaces the old plan with the new plan. This feature is not directly related to bad SQL statements, but it is related to the effects of planned changes, which leads to unpredictable performance degradation.

Excessive Parsing

As discussed in the "memory of SQL statements" section, Oracle must parse SQL statements before executing them. Parsing is CPU-intensive, including checking whether referenced tables and columns are valid against data dictionaries. The overhead of all execution plans of a statement is estimated through complex algorithms and computation to get the best execution plan. If no variable is bound to a statement (discussed in chapter 8th), the database must parse each statement it receives. This extra and unnecessary Parsing is one of the main causes of performance degradation. Another common cause is that the shared memory pool is too small, which has been discussed in the "Shared Memory Pool" section. Remember, you can avoid creating an execution plan by using the storage overview. Chapter 4th describes the storage overview. And corresponds to Oracle
9i, you can also create a storage Summary by editing the prompt. As mentioned above, Oracle Database 11g has the complete result set caching function, which can minimize the impact of repeated execution of the same query.

Database workload

When the application is well designed and the database is running in the optimal state, you may encounter a problem that the request workload is too large and there is not enough CPU resources to handle it. The lack of such CPU resources may be caused by a large workload of a database (if the machine on which the database is located is a dedicated server ), it may also be caused by the heavy workload of multiple databases running on one server. It is a long-term problem to underestimate the usage of CPU resources during capacity design. Unfortunately, the accurate estimation of CPU resources for a certain level of operation requires a detailed and in-depth analysis of the total amount of CPU resources consumed by each firm, and the number of transactions to be processed by the system per minute or per second, however, it must be estimated based on peak load and average load. Most organizations do not spend enough time and resources to answer these questions by analyzing systems and making prototypes. At this time, we will understand why the CPU resource shortage problem is so common. The most common solution to this problem is to simply add more
CPU continues until this problem disappears. Real-Time Application Clusters and Oracle grids are making various efforts to make it easier for managers to increase CPU horsepower.

Non-database workload

Not all organizations have the ability to configure a dedicated host for each Oracle database to ensure that they can occupy sufficient CPU resources. You can use the operating system tool to find the largest CPU resource consumer on the host. You may find that non-Oracle processes consume most of the CPU resources and seriously affect database performance.