When the database resource consumption is high, two SQL statements that are simple and useful to quickly identify possible causes of problems, resource consumption SQL

When the database resource consumption is high, two SQL statements that are simple and useful to quickly identify possible causes of problems, resource consumption SQL

1. view the SQL statement being executed

(1) SQL statement:

Selectdistinct s. sid, s. SQL _id, s. event, s. program, s. MACHINE, q. SQL _textfrom v $ session s, v $ SQL q

Where s. SQL _id = q. SQL _idand s. status = 'active' orderby SQL _text;

(2) usage instructions:

When the database server load is high, most of the resources may be consumed by running SQL statements. querying the SQL statements that are being executed is the first step to open the high consumption reason box. This statement focuses on executing SQL statements, waiting events, initiating programs, initiating hosts and SQL code, and sorting by SQL text. When the database server load is high, you can use this statement to find the statement that the system is running when the current resource consumption is high. By sorting by text and displaying SQL _ID, wait for the event, you can determine the SQL statement that causes the problem from a program.

 

2. view the SQL statements with the most concurrent execution

(1) SQL statement:

Select SQL _id, count (*) from v $ sessiongroupby SQL _id order by 2 desc

(2) usage instructions:

When resources are consumed, there must be a certain number of SQL statements running at the same time, but the possibility of serious performance problems caused by the serial execution of a statement may occur in low-Configuration Systems, however, the high-configuration server does not cause a major impact. However, if the same problematic SQL statement is executed in high concurrency, the machines with high configuration will also be dragged down, resulting in sustained high concurrency, it also indicates that the SQL statement may run slowly and consume high resources. Therefore, you can objectively determine the cause of the problem by checking which statement is the most running.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244