(original) Performance test, Oracle server locates the bottleneck of high CPU utilization (SQL)

This blog record in a one-time testing process, the location of high CPU utilization bottleneck problem, the main location of SQL is subject to

One, sql command positioning
1. first with top command monitoring system resources, if aix system, use topas top When scrolling data is refreshed, Discovery usercpu Gundam 98% !!

Keep Top in the state, press shift+p , you can press all the processes CPU the usage rate is sorted so that you can understand the consumption CPU what are the most processes

as you can see, the current usercpu usage is up to 98%, and the TPS is no longer rising with the number of concurrent numbers, which can be considered to have reached a performance bottleneck , and is caused by CPU bottlenecks.

2. after sorting, record the highest CPU utilization PID in the first place (here is 172928),

① then a user with dba Authority,su oracle (can also enter with pl_sql )

② then go in. SQL command line and dba Authority

Sqlplus/as SYSDBA

③ now find the PID corresponding address addr in the V$process view, associate the process number PID with the Oracle session.

SQL:Select addr from v$process where spid=172928;

( introduction :The v$process view contains all the process information for the current system Oracle operation.) is often used to establish a connection between the operating system process ID of an Oracle or service process and the database session. That is, you can pass the process PID to find the session of the database)

④ again through the addr, in the V$session table to find the corresponding sql_id

Sql:select sql_id from v$session where paddr= ' 00000003cea444c8 ';

⑤ again through sql_id can find the corresponding SQL is which

Sql:select * from v$sql where sql_id = ' 00000003cea444c8 ';

In fact, the above is a three SQL can be linked table,

SQL is as follows:

select T3. Sql_text

From V$process T1

INNER JOIN V$session T2

on T1. ADDR = t2. Paddr

INNER JOIN V$sql T3

On T2. sql_id = T3. sql_id

where T1. SPID = 172928(this PID is the process ID);

The results from the command line are as follows:

Use Pl_sql to get the following results:

here, both have been positioned to occupy One of the CPUs high in SQL , which can combine the efficiency of business scenarios and SQL , and whether communication with developers such as/DBA is optimized or how to optimize

(By the way, it is suggested that the amount of data in the database will have a large impact on the performance gap, this test, 10W of data and 20W of data, TPS difference to one times!!) ）

second, with AWR report locates SQL with high CPU

AWR Report How to export, you can see my blog content

Http://www.cnblogs.com/life-for-test/p/6825127.html

Export After the AWR report,

① in the SQL statistics of main report, click Open, the result is as follows:

② Enter SQL Statistics, see the following result

③ Click SQL ordered by CPU time

in Total, you can see the cumulative consumption of the most CPU- intensive SQL,

④ Click on the SQL IDto see the full SQL results, as described below:

5. After opening, you can see the following SQL, the two are CPU-intensive SQL reasons, combined with business scenarios and communication, to see whether optimization bar ~ ~ ~

(original) Performance test, Oracle server locates the bottleneck of high CPU utilization (SQL)