Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Reducing Parse elapsed time showed on TKProf

    Hi,
    I would like to know how to reduce the parse elapse time showed in TKprof. I have a very simple SQL statement that takes around 1.5 second to parse (elapsed time). On the other hand, parse elapsed time on a more complex SQL statements takes only .12 seconds.
    Below is an example of the tkprof (CPU time is always 0)

    Delete from TaBLEA
    Where
    PrimaryKey = :1

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 1.49 0 0 0 0
    Execute 1 0.00 0.00 0 3 9 1
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 0.00 1.49 0 3 9 1

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 185 (A011758A)

    Does anyone know what can be done to reduce the parsed elapse time?
    Thank you very much,

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    events 10046

    Interesting - since cpu is zero it means that you are waiting on something.

    Try getting the extended sql trace by issuing

    alter session set events '10046 trace name context forever, level 8';

    And then run the statement. The trace file will have the WAIT lines too.

    Then look at the WAIT lines. The ela=xxx is the time spent waiting (in centiseconds 'till 8i, microseconds since 9i).

    If all the waits are near-zero, then you have just a long ready-to-run process/thread queue on your instance (not enough cpu power), but that would mean that everything should be slow.

    Al

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Thanks alberto,
    I will give it a try. But if its the case that you have mentioned, I would expect to see some CPU time right?.

    Are there any other theory?

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by kulajak
    But if its the case that you have mentioned, I would expect to see some CPU time right?.

    No, the cpu time is the time you spend on the cpu;
    if you wait, you don't wait on the cpu.

    Only if the wait in on a latch on a multiprocessor system you have some amount of busy waiting, but then you sleep off the cpu anyway.

    The formula is

    ELAPSED = CPU + WAITING + STAYING-IN-RUN-QUEUE.

    CPU and WAITING are reflected in the extended trace file, STAYING-IN-RUN-QUEUE not (you compute it by difference).

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Thank you very much alberto

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •