+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 26

need help on statspack report, Parse CPU to Parse Elapsd %

  1. need help on statspack report, Parse CPU to Parse Elapsd %

    Hi Oracle Experts,

    I need some help on performance tuning. Hope someone can give me some hints.
    I run the statspack report yesterday, the following is part of the report

    Snap Id Snap Time Sessions
    ------- ------------------ --------
    Begin Snap: 4 04-Mar-04 09:00:05 140
    End Snap: 13 04-Mar-04 18:00:02 140
    Elapsed: 539.95 (mins)

    Cache Sizes
    ~~~~~~~~~~~
    db_block_buffers: 60000 log_buffer: 1048576
    db_block_size: 8192 shared_pool_size: 600000000

    Load Profile
    ~~~~~~~~~~~~ Per Second Per Transaction
    --------------- ---------------
    Redo size: 12,923.83 10,119.23
    Logical reads: 24,560.24 19,230.43
    Block changes: 91.95 72.00
    Physical reads: 557.54 436.55
    Physical writes: 51.18 40.08
    User calls: 71.64 56.09
    Parses: 25.66 20.09
    Hard parses: 0.26 0.21
    Sorts: 12.12 9.49
    Logons: 0.17 0.14
    Executes: 208.73 163.43
    Transactions: 1.28

    % Blocks changed per Read: 0.37 Recursive Call %: 81.57
    Rollback per transaction %: 7.24 Rows per Sort: 62.75

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait %: 99.99
    Buffer Hit %: 97.73 In-memory Sort %: 99.97
    Library Hit %: 99.83 Soft Parse %: 98.98
    Execute to Parse %: 87.71 Latch Hit %: 99.95
    Parse CPU to Parse Elapsd %: 1.80 % Non-Parse CPU: 98.94

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 21.78 72.57
    % SQL with executions>1: 45.64 60.15
    % Memory for SQL w/exec>1: 74.31 73.98

    Everything seems fine (if it is not, please tell me), except the Parse CPU to Parse Elapsd %. It is 1.80% only, which is far below the target 100%, and I think it is unreasonable low. I am wondering if this somehow related to high CPU utilization of Oracle (I find the Oracle process taking up a unreasonable high CPU resources on my system).

    I want to know

    1. What Parse CPU to Parse Elapsd % means?
    2. How can I improve it?

    Thanks for any input.

    Best Regards,
    Joe




  2. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Joe,
    The duration of your statspack (about 9 hrs) is high, normally
    Statspack reports are generated for a half-hour duration during peak
    load.
    Think you are running 8i.
    A low value for Parse CPU to Parse Elapsd % means that the during
    parsing, the server process is waiting for something, most likely
    contending for a latch (shared pool and library cache latches).
    But in your case this is not much of a disadvantage as you have a high
    % Non-Parse CPU (98.94 %).
    You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    from 9.2) and the latch information from the statspack.
    You also have a high soft parse % - 98.98%.
    So most of your parse calls are resulting in soft parses.
    In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    (you can start with a value of 150).
    You may also want to look at the code, to see why there are so many
    parse calls.
    Ideally you should parse once and execute many times.
    regards
    Srivenu

  3. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Joe,
    The duration of your statspack (about 9 hrs) is high, normally
    Statspack reports are generated for a half-hour duration during peak
    load.
    Think you are running 8i.
    A low value for Parse CPU to Parse Elapsd % means that the during
    parsing, the server process is waiting for something, most likely
    contending for a latch (shared pool and library cache latches).
    But in your case this is not much of a disadvantage as you have a high
    % Non-Parse CPU (98.94 %).
    You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    from 9.2) and the latch information from the statspack.
    You also have a high soft parse % - 98.98%.
    So most of your parse calls are resulting in soft parses.
    In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    (you can start with a value of 150).
    You may also want to look at the code, to see why there are so many
    parse calls.
    Ideally you should parse once and execute many times.
    regards
    Srivenu

  4. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Joe,
    The duration of your statspack (about 9 hrs) is high, normally
    Statspack reports are generated for a half-hour duration during peak
    load.
    Think you are running 8i.
    A low value for Parse CPU to Parse Elapsd % means that the during
    parsing, the server process is waiting for something, most likely
    contending for a latch (shared pool and library cache latches).
    But in your case this is not much of a disadvantage as you have a high
    % Non-Parse CPU (98.94 %).
    You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    from 9.2) and the latch information from the statspack.
    You also have a high soft parse % - 98.98%.
    So most of your parse calls are resulting in soft parses.
    In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    (you can start with a value of 150).
    You may also want to look at the code, to see why there are so many
    parse calls.
    Ideally you should parse once and execute many times.
    regards
    Srivenu

  5. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Joe,
    The duration of your statspack (about 9 hrs) is high, normally
    Statspack reports are generated for a half-hour duration during peak
    load.
    Think you are running 8i.
    A low value for Parse CPU to Parse Elapsd % means that the during
    parsing, the server process is waiting for something, most likely
    contending for a latch (shared pool and library cache latches).
    But in your case this is not much of a disadvantage as you have a high
    % Non-Parse CPU (98.94 %).
    You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    from 9.2) and the latch information from the statspack.
    You also have a high soft parse % - 98.98%.
    So most of your parse calls are resulting in soft parses.
    In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    (you can start with a value of 150).
    You may also want to look at the code, to see why there are so many
    parse calls.
    Ideally you should parse once and execute many times.
    regards
    Srivenu

  6. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Joe,
    The duration of your statspack (about 9 hrs) is high, normally
    Statspack reports are generated for a half-hour duration during peak
    load.
    Think you are running 8i.
    A low value for Parse CPU to Parse Elapsd % means that the during
    parsing, the server process is waiting for something, most likely
    contending for a latch (shared pool and library cache latches).
    But in your case this is not much of a disadvantage as you have a high
    % Non-Parse CPU (98.94 %).
    You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    from 9.2) and the latch information from the statspack.
    You also have a high soft parse % - 98.98%.
    So most of your parse calls are resulting in soft parses.
    In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    (you can start with a value of 150).
    You may also want to look at the code, to see why there are so many
    parse calls.
    Ideally you should parse once and execute many times.
    regards
    Srivenu

  7. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Hi Srivenu,

    Thanks a lots for your reply.
    Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    enqueue 33,290 10,195,809 57.13
    latch free 1,887,651 6,092,953 34.14
    db file scattered read 1,788,217 537,596 3.01
    db file sequential read 1,589,366 369,621 2.07
    log file sync 32,896 164,777 .92
    -------------------------------------------------------------

    I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
    I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

    Can you give me some advise on latch free and enqueue wait?
    Any input is appreciated.
    Thanks a lots.

    Regards,
    Joe




    "srivenu" ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
    > Joe,
    > The duration of your statspack (about 9 hrs) is high, normally
    > Statspack reports are generated for a half-hour duration during peak
    > load.
    > Think you are running 8i.
    > A low value for Parse CPU to Parse Elapsd % means that the during
    > parsing, the server process is waiting for something, most likely
    > contending for a latch (shared pool and library cache latches).
    > But in your case this is not much of a disadvantage as you have a high
    > % Non-Parse CPU (98.94 %).
    > You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    > from 9.2) and the latch information from the statspack.
    > You also have a high soft parse % - 98.98%.
    > So most of your parse calls are resulting in soft parses.
    > In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    > (you can start with a value of 150).
    > You may also want to look at the code, to see why there are so many
    > parse calls.
    > Ideally you should parse once and execute many times.
    > regards
    > Srivenu


  8. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Hi Srivenu,

    Thanks a lots for your reply.
    Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    enqueue 33,290 10,195,809 57.13
    latch free 1,887,651 6,092,953 34.14
    db file scattered read 1,788,217 537,596 3.01
    db file sequential read 1,589,366 369,621 2.07
    log file sync 32,896 164,777 .92
    -------------------------------------------------------------

    I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
    I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

    Can you give me some advise on latch free and enqueue wait?
    Any input is appreciated.
    Thanks a lots.

    Regards,
    Joe




    "srivenu" ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
    > Joe,
    > The duration of your statspack (about 9 hrs) is high, normally
    > Statspack reports are generated for a half-hour duration during peak
    > load.
    > Think you are running 8i.
    > A low value for Parse CPU to Parse Elapsd % means that the during
    > parsing, the server process is waiting for something, most likely
    > contending for a latch (shared pool and library cache latches).
    > But in your case this is not much of a disadvantage as you have a high
    > % Non-Parse CPU (98.94 %).
    > You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    > from 9.2) and the latch information from the statspack.
    > You also have a high soft parse % - 98.98%.
    > So most of your parse calls are resulting in soft parses.
    > In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    > (you can start with a value of 150).
    > You may also want to look at the code, to see why there are so many
    > parse calls.
    > Ideally you should parse once and execute many times.
    > regards
    > Srivenu


  9. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Hi Srivenu,

    Thanks a lots for your reply.
    Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    enqueue 33,290 10,195,809 57.13
    latch free 1,887,651 6,092,953 34.14
    db file scattered read 1,788,217 537,596 3.01
    db file sequential read 1,589,366 369,621 2.07
    log file sync 32,896 164,777 .92
    -------------------------------------------------------------

    I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
    I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

    Can you give me some advise on latch free and enqueue wait?
    Any input is appreciated.
    Thanks a lots.

    Regards,
    Joe




    "srivenu" ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
    > Joe,
    > The duration of your statspack (about 9 hrs) is high, normally
    > Statspack reports are generated for a half-hour duration during peak
    > load.
    > Think you are running 8i.
    > A low value for Parse CPU to Parse Elapsd % means that the during
    > parsing, the server process is waiting for something, most likely
    > contending for a latch (shared pool and library cache latches).
    > But in your case this is not much of a disadvantage as you have a high
    > % Non-Parse CPU (98.94 %).
    > You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    > from 9.2) and the latch information from the statspack.
    > You also have a high soft parse % - 98.98%.
    > So most of your parse calls are resulting in soft parses.
    > In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    > (you can start with a value of 150).
    > You may also want to look at the code, to see why there are so many
    > parse calls.
    > Ideally you should parse once and execute many times.
    > regards
    > Srivenu


  10. Re: need help on statspack report, Parse CPU to Parse Elapsd %

    Hi Srivenu,

    Thanks a lots for your reply.
    Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    enqueue 33,290 10,195,809 57.13
    latch free 1,887,651 6,092,953 34.14
    db file scattered read 1,788,217 537,596 3.01
    db file sequential read 1,589,366 369,621 2.07
    log file sync 32,896 164,777 .92
    -------------------------------------------------------------

    I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
    I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

    Can you give me some advise on latch free and enqueue wait?
    Any input is appreciated.
    Thanks a lots.

    Regards,
    Joe




    "srivenu" ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
    > Joe,
    > The duration of your statspack (about 9 hrs) is high, normally
    > Statspack reports are generated for a half-hour duration during peak
    > load.
    > Think you are running 8i.
    > A low value for Parse CPU to Parse Elapsd % means that the during
    > parsing, the server process is waiting for something, most likely
    > contending for a latch (shared pool and library cache latches).
    > But in your case this is not much of a disadvantage as you have a high
    > % Non-Parse CPU (98.94 %).
    > You should have also shown the Top 5 Wait Events (Top 5 Timed Events
    > from 9.2) and the latch information from the statspack.
    > You also have a high soft parse % - 98.98%.
    > So most of your parse calls are resulting in soft parses.
    > In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
    > (you can start with a value of 150).
    > You may also want to look at the code, to see why there are so many
    > parse calls.
    > Ideally you should parse once and execute many times.
    > regards
    > Srivenu


+ Reply to Thread
Page 1 of 3 1 2 3 LastLast