Shulou(Shulou.com)06/01 Report--

July 8, 2016

Today the developer provides a statement to delete the data in the table, the delete statement. After being executed in plsql, it is found that the execution has not finished for half an hour. At this time, the development still has a script to execute. I wanted to create a new session to execute the statements provided by the development, but a simple table statement could not be executed.

I forcibly shut down the session of delete in plsql, but closed the plsql window, even though it took a long time to shut it down. But the construction table statement still can not be executed. It was executed, but there was no response. At this time, the tester reflects that the click interface is very slow, and the login client has been in a spinning state.

Based on the above, it is possible that the plsql window has just been forcibly closed, although the window has been closed, but the process has not been closed.

So how to check that the process exists.

Check this table

Select * from v$session

Select * from v$session a

Where a.USERNAMEMETHER Sys' and a.TERMINALITY SHAUMAI P07010086' and a.statusACTIVE'

This is because I execute delete with the sys user, and my computer name is SHA-P07010086

View process number

Select * from v$process

Select * from v$sqlarea b

Where b.SQL_ID in ('dauuu7kkf7xma','gs36qsq4dju1v')

-- dauuu7kkf7xma

Kill the process.

Alter system kill session '133. 15615' immediate

After doing this, my delete process is killed.

Then check the data, it will be better. After the tester logs back in to the app, it's all right.

Positioning performance sql

1. Find the top ten sql with poor performance

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS

COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea

Order BY disk_reads DESC) where ROWNUM6 AND st.

Wait_time=0 AND st.event NOT LIKE'% SQL%' ORDER BY physical_reads DESC

3. Check the exception sql through the process number

SELECT a.username

A.machine

A.program

A.sid

A.serial#

A.status

C.piece

C.sql_text

FROM v$session a

V$process b

V$sqltext c

WHERE b.spid=5200

AND b.addr=a.paddr

AND a.sql_address=c.address (+)

ORDER BY c.piece

Alter system kill session 'sid,serial#';-- View sql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.