Original author: John Weeg
When a user is submitted to a query, it is a result, which is very resumed. They hope that the statements are running normally, but they don't know how actually. Because of some let us find a way to eliminate their concerns.
who are you?
The first question, of course, is what session we are referring to? Users can use the following statements before doing other things:
SELECT SID AUM = 1;
In fact, this problem will not be proposed until the statement you submit is normal, this problem will not be proposed. If the user has a unique username, you can use the following statement to get that SID, such as check the user John's SID.
Select Sid, Machine, Osuser, Module From V $ Session Where UserName = 'John';
SID Machine Osuser Module ------------------------------------------------------------- ------------------ 150 mshome / john-laptop john? Weeg SQL * Plus
I use some other information to verify this is just my conversation.
Worse, a shared user name is used, because we will need to look at what session is running:
Break on SID SKIP 1COLUMN SID FORMAT 99999COLUMN SQL_TEXT FORM A64
Select A.SID, A.last_Call_ET, B.SQL_TextFrom V $ Session A, V $ SQLText Bwhere A.USERNAME IS NULLAND A.STATUS = 'Active'and A.SQL_ADDRESS = B.AddressReder by a.last_call_et, a.sid , B.PIECE;
This gives us what we are currently running and how long you should see which session you need to check.
Because of some, what should I do?
We know that this time is always accompanied by the statement, and the CPU operation is being executed, or the IO operation is being executed. Three tables we want to know through v $ sessstat, v $ sesssstat, v $ sessions_wait, we can get some information we want to know, you can check the table of our attention through the SID, but I found that it is easy to combine this information. together.
Column event format a30Column sid format 9999Column 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. Conistent_gets, D. Event, D. Seconds_in_WaitFrom V $ SESSTAT A, V $ STATNAME B, V $ SESS_IO C, V $ session_wait dwhere a.sid = 150and B .name = 'cpu buy by this session'and a.statistic # = b.statistic # and a.sid = C.SIDAND A.SID = D.SID;
I execute this statement several times because we need to find out which items are changing. CPU Physical Logical Secondssid Used Reads Reads Event Waiting --- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------- ---------- 150 1159 0 117476 SQL * Net Message from Client 5
/
CPU Physical Logical Secondssid Used Reads Reads Event Waiting --- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------- ---------- 150 1970 0 204484 SQL * NET MESSAGE FROM Client 4
Because we can see this session is waiting for the client information when we check it. During our two inspections, it performs logically read operations and consumes CPU resources, which means that the session run is normal.
Under what we need to check further?
There are usually such a few events (Event) identify potential problems: 'buffer busy waits', 'DB File Sequential Read', 'DB File Scattered Read', 'Free Buffer Waits', 'Latch Free', for the first 4 events We can find which object is related to the statement:
select owner, segment_name, segment_typefrom (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 AWHERE A.FILE_ID = B.File # and b.block # between a.block_id and (a.block_id blocks-1);
Here we can find that IO waits are caused by a lot of data access, or by some things, such as index loss, we can also remove the SID clause to find out those objects that are experiencing waiting.
For the last potential problem, we can check what to stay (LATCH):
Select NameFrom (SELECT P2 LATCH # from v $ session_wait where sid = 150 and event in ('latch free')) B, V $ latchname awhered a.latch # = B.latch #;
Can we see the conflict of a session experience, this conflict is not often happening.
Easy your users easily
Therefore, you have to tell those statements they perform by those who are waiting for some other resources. My ultimate approach is to help them optimize those statements so that the statement is not as uncomfortable. (Full text)
