This discussion is archived
2 Replies Latest reply: Jan 30, 2013 11:27 AM by P.Forstmann RSS

Help with trace file

490297 Newbie
Currently Being Moderated
Hello,

I have from some a few doubts about the trace files, researched in various locations, but still could not solve my doubts. I hope you can help me.

First doubt:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      352      0.03       0.04          0          0          0           0
Execute    434      0.05       0.05          0          0          0           0
Fetch     2359      6.97     104.50        130     460857          0        3665
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3145      7.05     104.60        130     460857          0        3665
For I know the average time of each call to this query, which account should I do?

104/434 = 0.24
104/3145 = 0.03

Another? Why?


Second doubt:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       45      0.00       0.00          0          0          0           0
Execute  19232      0.79       0.92          0          0          0           0
Fetch    19232      0.90       0.90          0          0          0       19232
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    38509      1.70       1.82          0          0          0       19232
How do I know how many times this query was executed?
I thought that number 19,232 too high, and I have looked at the trace file and select and only appears 41 times.


Thanks,
Everson

Edited by: Everson Piza on Jan 30, 2013 4:28 PM
  • 1. Re: Help with trace file
    riedelme Expert
    Currently Being Moderated
    Everson Piza wrote:
    Hello,

    I have from some a few doubts about the trace files, researched in various locations, but still could not solve my doubts. I hope you can help me.

    First doubt:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse      352      0.03       0.04          0          0          0           0
    Execute    434      0.05       0.05          0          0          0           0
    Fetch     2359      6.97     104.50        130     460857          0        3665
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3145      7.05     104.60        130     460857          0        3665
    For I know the average time of each call to this query, which account should I do?

    104/434 = 0.24
    104/3145 = 0.03
    What are you trying to determine by computing such an average? The times listed are a guage to indicate how much time was spent for each operation. Apart from that the computation you listed did not look right. You dived 104 by 434 but the elapsed time for Execute was 0.05. Why did you divide the total elapsed time (assuming you did not use the elapsed fetch time) by only the Execute time when it includes the Parse and Fetch times?

    The statistics listed indicate how much time was spent on each operation. Most of the time was spent in the Fetch opeation.

    >
    Second doubt:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       45      0.00       0.00          0          0          0           0
    Execute  19232      0.79       0.92          0          0          0           0
    Fetch    19232      0.90       0.90          0          0          0       19232
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    38509      1.70       1.82          0          0          0       19232
    How do I know how many times this query was executed?
    I thought that number 19,232 too high, and I have looked at the trace file and select and only appears 41 times.
    Although there might be 41 Execute operations in the raw trace file the added-up sum of the counts should be 19,232. if not then there is a problem. If you have My Oracle Support account you can log in and find a document explaining raw trace files. This information might be available on-line but is probably proprietary by oracle.

    Edited by: riedelme on Jan 30, 2013 10:55 AM
  • 2. Re: Help with trace file
    P.Forstmann Guru
    Currently Being Moderated
    From the database client point of view a SELECT query execution is always a mix of parse/execute/fetch.

    The number of execute calls in the SQL trace file is the number of times the client code has asked the database to execute the query: it should be the number of query executions.
    The number of fetch calls depends on the number of rows in the result set and on the array fetch size = the number of rows database will send in one Oracle Net round trip from server to client.

    You can find more detail in http://shallahamer-orapub.blogspot.fr/2010/05/understanding-user-calls.html.

    Edited by: P. Forstmann on 30 janv. 2013 20:27

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points