What is my session doing?

Session


Original Author: John Weeg





when a user sits in front of the terminal to submit a query but can not wait for the result, it is very much to restore the heart. They want the statements to work, but they don't know what they actually are. Let's find a way to get rid of their fears.





, who are you?





the first question, of course, is which session are we talking about? The user can use the following statement before doing anything else:





Select sid from V$mystat where Rownum=1;





in fact, the problem will not be raised until the statement you submit is normal, and if the user has a unique username, you can use the following statement to get that SID, such as checking the user John's SID.





Select Sid,machine,osuser,module from v$session where username= ' JOHN ';





SID MACHINE osuser MODULE


----- -------------------- ------------------------------ ----------


Mshome\john-laptop JOHN? Weeg Sql*plus





I use some other information to verify this is exactly my session





a bit worse is the status of the shared username being used, because we will need to see which sessions are running:





break on SID Skip 1


column SID format 99999


column Sql_text form A64





Select A.sid, A.last_call_et, B.sql_text


from V$session a


, V$sqltext b


where a.username is not null


and a.status = ' ACTIVE '


and a.sql_address = b.address


ORDER by A.last_call_et,a.sid,b.piece;





This gives us the statement and how long we are currently running, and you should be able to see which session you need to check.





, what should we do next?





We know that it is common to wait for the execution of a statement, to perform a CPU operation, or to perform an IO operation. By v$sessstat,v$sessio,v$session_wait These three tables we can get some information we want to know, through the SID to the list of our notes, but I find it very easy to combine this information.





Column event Format A30


Column SID format 9999


Column session_cpu heading "cpu|used"


Column physical_reads heading "Physical|reads"


Column consistent_gets heading "Logical|reads"


Column seconds_in_wait heading "seconds|waiting"





Select A.sid, A.value session_cpu, C.physical_reads,


c.consistent_gets,d.event,d.seconds_in_wait


from V$sesstat A,v$statname B, V$sess_io C, v$session_wait D


where a.sid=150


and b.name = ' CPU used by this session '


and a.statistic# = b.statistic#


and A.sid=c.sid


and A.sid=d.sid;








I execute this statement several times, because we need to find out which items are changing.





CPU Physical logical seconds


SID used reads reads EVENT waiting


--- ----- --------- -------- ------------------------------ ----------


1159 0 117476 sql*net message from client 5





/





CPU Physical logical seconds


SID used reads reads EVENT waiting


--- ----- --------- -------- ------------------------------ ----------


1970 0 204484 sql*net message from client 4





we can see this conversation waiting for the client when we check it out. It performed logical read operations and consumed CPU resources during our two inspections, which indicates that the session is running normally.





What circumstances do we need to check further?





There are usually a few events (event) Identification potential problems: ' Buffer busy waits ', ' db file sequential read ', ' db file scattered read ', ' Free buffer waits ', ' Latch free ', for the top 4 events, we can find out which object is relevant, the following statement:





Select Owner,segment_name,segment_type


from (select P1 file#, p2 block# from v$session_wait


where sid = 150


and event in (' Buffer busy waits '


, ' db file sequential read '


, ' db file scattered read '


, ' free buffer waits ') b


, dba_extents a


where a.file_id = b.file#


and b.block# between a.block_id and (A.BLOCK_ID+BLOCKS-1);





Here we can find that IO wait is caused by a lot of data access, or some things are not right, such as index loss, we can also remove the SID clause line to find those who are experiencing the waiting object.





for the last potential problem we can find out what's waiting for the Bolt (latch):





Select name


from (select P2 latch# from V$session_wait


where sid = 150


and event in (' Latch free ')) b


, V$latchname a


where a.latch# = b.latch#;





we can see whether the session is experiencing a tethered conflict, this conflict is not always happening





make your users relaxed





so now you have to be able to tell those restless users that the statements they are executing are waiting for some other resources. My ultimate approach is to help them optimize those statements so that the statements don't run too slowly to make the user uneasy.





(end of full text)