Oracle SQL Tuning series methods for Locating production performance Problems

1. Overall analysis of AWR

Scenario: An urgent production problem is encountered recently, and service functions cannot be used normally due to database lock table. For this urgent problem, first of all, you should be calm and then analyze the problem reasonably. You can start from the whole point of view and obtain Oracle AWR report for overall analysis

2. JVM commands for monitoring

From the overall failure to locate the problem, or need to cooperate with the JVM tuning command to troubleshoot the problem:

[www@localhost ~]$top [www@localhost ~]$JPS -l [www@localhost ~]$jmap -dump:format=b,file=heap.hprof PID // Query the heap information directly [www@localhost ~]$jmap -heap PID // Check the stack information to see if there is program deadlock [www@localhost ~]$jstack PIDCopy the code

3, get lock table SQL

Ok, there is no problem with the program investigation, from the SQL aspect investigation

Check whether there is a lock table

SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHEREl.object_id= o.object_id
AND l.session_id = s.sid;	
Copy the code

Release the table lock

//The release of the SESSIONSQL:
alter system kill session 'sid, serial#';
ALTER system kill session 23, '1647';

Copy the code

View the specific lock table SQL

   
   select l.session_id sid, 
       s.serial#, 
       l.locked_mode, 
       l.oracle_username, 
       s.user#, 
       l.os_user_name, 
       s.machine, 
       s.terminal, 
       a.sql_text, 
       a.action 
  from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = s.sid 
   and s.prev_sql_addr = a.address 
order by sid, s.serial#;
Copy the code

4. Change the number of database connections

Ps: Of course, the lock table may also be insufficient connections

View the current number of database connections

select count(*) from v$process ; 
Copy the code

View the maximum number of connections allowed by the database

select value from v$parameter where name ='processes';
Copy the code

View the number of current session connections

select count(*) from v$session
Copy the code

View the number of concurrent connections

select count(*) from v$session where status='ACTIVE';
Copy the code

Example Change the maximum number of data connections

alter system set processes = 500 scope = spfile; 
Copy the code

Restarting and shutting down the database

Close database
shutdown immediate; 
-- Restart database
startup; 
Copy the code

5, slow positioning SQL

Ok, if the lock table problem can be located, also want to incidentally check which slow SQL, dragging system performance

Query which users are in use first

select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc; 
Copy the code

Take out slow SQL:

SELECT SQL_TEXT,
       SQL_FULLTEXT,
       ELAPSED_TIME,
       DISK_READS,
       BUFFER_GETS,
       EXECUTIONS,
       Round(ELAPSED_TIME / EXECUTIONS ,2),
       ROUND(DISK_READS / EXECUTIONS, 2),
       ROUND(BUFFER_GETS / EXECUTIONS , 2),
       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) 
  FROM V$SQLAREA 
 WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
   ORDER BY Round(ELAPSED_TIME / EXECUTIONS ,2) desc;

Copy the code

Then explain the meanings:

EXECUTIONS (DISK_READS/EXECUTIONS, 2) : result_executions (DISK_READS/EXECUTIONS, 2) : result_executions (DISK_READS/EXECUTIONS, 2) : SQL EXECUTIONS ROUND(BUFFER_GETS/EXECUTIONS, 2) : ROUND((buffer_gets-disk_reads)/BUFFER_GETS, 2) : SQL hit ratio

Ok, the performance problem is a very time-consuming problem, this blog only carries on some simple share, only for reference

Oracle SQL Tuning series methods for Locating production performance Problems