Oracle Database SQL Performance View

As a development/ tester , more or less have to deal with the database, and the operation of the database is ultimately SQL statements, all operations to the end are operational data, then the control of SQL performance has become a very important task in our work. Here are some practical ways to look at Oracle performance:

1. Query the number of connections per machine

Select T.machine,count (*) from v$session T GROUP by T.machine

　　

Each machine referred to here refers to each server connected to the Oracle database, each server has a connection number configured to connect to the database, in WebSphere , for example, in the data source, each data source has configured its maximum / The minimum number of connections.

After executing SQL , you can see how many connections each server connects to the Oracle database, and if a server has a very large number of connections, or has reached its maximum number of connections, there may be a problem with the application on this server, causing its connection to not be released properly.

2, query the sql_text of each connection number

The connections that exist in the V$session table are not always performing operations, and if Sql_hash_value is empty or 0, the connection is idle, you can query which connections are not idle, web3 is the machine name, is the host name of the WebSphere application Server.

Select t.sql_hash_value,t.* from  v$session t where t.machine= ' web3 ' and t.sql_hash_value!=0

The result of this SQL query can not see the specific SQL statement, you need to look at the specific SQL statement to execute the following method.

3.query each active connection to execute what SQL

Select Sid,username,sql_hash_value,b.sql_textfrom v$session a,v$sqltext b where a.sql_hash_value = B.HASH_VALUE and A. Machine= ' web3 ' ORDER by sid,username,sql_hash_value,b.piece

The function of order byis thatsql_text Each record is not to save a complete sql, it needs to Sql_hash_value as the key ID, sorted in piece,

Username is the database user name that executes SQL, and the sql_text under a sql_hash_value is combined into a full SQL statement. This allows you to see what SQL is being executed by a connection .

4,. from V$sqlarea Query for most resource-intensive queries in

Select B.username username,a.disk_reads reads, a.executions exec,a.disk_reads/decode (a.executions,0,1,a.executions) Rds_exec_ratio,a.sql_text Statementfrom  

Replacing the disk_reads column with the buffer_gets column gives you information about the SQL statement that consumes the most memory .

V$sql is a SQL statement that has been resolved in a memory-shared SQL zone .

This table is used in SQL Performance View operation in a more frequent table, about the table details you can go to http://apps.hi.baidu.com/share/detail/299920# to learn, The introduction is more detailed. I would like to introduce a few of the most common operations of the table briefly:

1 . List the 5 most frequently used queries:

Select Sql_text,executions from (select Sql_text,executions,   rank () up (order by executions Desc) Exec_rank    

　　

The results of this query list the 5 Most frequently executed SQL statements. For this very useful SQL statement, we need to continuously optimize it to achieve optimal execution performance.

2 . Find the query that requires a lot of buffer read (logical read) operation:

Select Buffer_gets,sql_text from (select Sql_text,buffer_gets,    dense_rank () up      (ORDER by buffer_gets Desc) Buffer_gets_rank    

　　

This type of SQL , which requires a large amount of buffered reads (logical read) operations , is basically a large data volume and logically complex query that requires constant attention and optimization for such large data volume query SQL statements.

3 . Continuous tracking of SQLwith performance impact.

SELECT * FROM (    select Parsing_user_id,executions,sorts, Command_type,disk_reads,sql_text from V$sqlarea ORDER by di Sk_reads DESC) WHERE rownum<10

　　

This statement is used more in SQL Performance review, and it is clear what SQL affects database performance.

This article focuses on some Common ways to view Oracle database SQL performance using SQL queries . In addition, there are many tools to achieve SQL performance monitoring, you can search the relevant knowledge on the Internet to learn.

Oracle Database SQL Performance View