Tracing a Process

[Duke3D]

I'm tracing a process in a Peoplesoft-Oracle8 <br>
environment. The results give 20 or 30 update/<br>
insert statements that generate only 10 seconds<br>
CPU time, but 180 to 280 seconds elapsed time. Manual<br>
says the process is waiting for another process to<br>
terminate. However I was the only one on that database,<br>
locking statistics give no problem in that area. However,<br>
statistics show a lot of disk accesses.<br>
What is the cause of this problem? Disk accesses or <br>
something else?

 

[mrkoors]

Probably due to disk access. Your process is waiting for the OS to return data. I assume that you are using sql trace and tkprof. Look for the DISK column in the report for the number of physical disk reads needed.<br>
<br>
call count cpu elapsed disk query current rows<br>
------- ------ -------- ---------- ---------- ---------- ---------- ----------<br>
Parse 1 0.76 3.48 0 1566 0 0<br>
Execute 1 174.56 393.40 17903 2652544 109 1<br>
Fetch 0 0.00 0.00 0 0 0 0<br>
------- ------ -------- ---------- ---------- ---------- ---------- ----------<br>
total 2 175.32 396.88 17903 2654110 109 1<br>
<br>

 

[TimG]

SQL Tracing and TKPROF are certainly your best bet. Another useful indicator is the &quot;session wait&quot; interface, which is comprised of the following V$ views:<br>
<br>
V$SESSION_WAIT: which is a &quot;real-time&quot; view of sessions and what they might be waiting upon. There is only 1 or 0 rows in this view for each session (i.e. each row in V$SESSION). If a session is waiting for something (i.e. an I/O to complete, a lock, etc), then there will be one row. If the session is not waiting, then there will be no row for that session. Wait Events are listed in the &quot;Appendix A&quot; of the &quot;Oracle8 Server Reference&quot; manual, and there are some good white papers on Craig Shallahamer's website at &quot;<A HREF="

http://www.orapub.com&quot"

TARGET="_new">

http://www.orapub.com&quot</A>;

and I've got a good one by Anjo Kolk on my website at &quot;<A HREF="

http://www.evdbt.com/library.htm&quot"

TARGET="_new">

http://www.evdbt.com/library.htm&quot</A>;...<br>

<br>
V$SESSION_EVENT: shows similar info to V$SESSION_WAIT, but cumulative for all active sessions in the database, not &quot;real-time&quot;. It is useful to have TIMED_STATISTICS set to TRUE when using this view.<br>
<br>
V$SYSTEM_EVENT: show similar info to V$SESSION_EVENT, but cumulative for the entire database instance since startup. Again, it is very useful to have TIMED_STATISTICS set to TRUE when using this view.<br>
<br>
Two of the most prevalent wait events for I/O are:<br>
<br>
db file sequential read: indexed or single-block I/O<br>
<br>
db file scattered read: multiblock I/O resulting from<br>
full-table scans<br>
<br>
Look for the multiblock I/O event, which indicates full-table scans, and try to eliminate it by making sure the SQL statement uses indexes.<br>
<br>
Hope this helps...<br>
<br>
-Tim