FAQ
Hello;
I try to understand why so much elapsed times with a stored procedure
124,037 s elapsed time against 968 s of Cpu time.
There isn't very much SQL activity, but with Time Model Statistics this 89.5
% of DB Time !!!

An extract of AWR report :
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 25358 26-Sept.-11 19:45:2 20 8.6
End Snap: 25402 27-Sept.-11 06:45:4 19 6.0
Elapsed: 660.28 (mins)
DB Time: 2,725.23 (mins)
...

Time Model Statistics DB/Inst: MACSTP/MACSTP Snaps:
25358-25402
-> Total time in database user-calls (DB Time): 163514s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 146,340.9 89.5
PL/SQL execution elapsed time 43,324.9 26.5
inbound PL/SQL rpc elapsed time 30,334.5 18.6
parse time elapsed 4,442.9 2.7
DB CPU 2,304.0 1.4
hard parse elapsed time 123.7 .1
PL/SQL compilation elapsed time 8.1 .0
connection management call elapsed time 5.6 .0
repeated bind elapsed time 1.7 .0
hard parse (sharing criteria) elapsed time 1.3 .0
sequence load elapsed time 0.2 .0
hard parse (bind mismatch) elapsed time 0.1 .0
failed parse elapsed time 0.0 .0
DB time 163,514.0 N/A
background elapsed time 5,042.1 N/A
background cpu time 70.7 N/A
..
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
124,037 968 1 124036.5 75.9 4mmh51uun7bcx
Module: mgrntw.exe
begin D_FORTIS.PRC_REMONTEE_PM_SUPPORT(:1 , :2 , :3 , :4 , :5 ,:FLD1 ,:FLD
2 ,:FLD3 ,:FLD4); end;

14,195 221 438,448 0.0 8.7 aps7tsk52h9ac
Module: mgrntw.exe
SELECT MVP.MVP_TAUX_SPECIFIQUE, M.MVT_ACTE_GESTION, M.MVT_SIGNE,
D.DDC_EXERCICE,
M.MVT_DATE_EFFET, M.MVT_N_ORIGINE, R.R_I_DATE_INVESTISSEMENT,
GREATEST(D.DDC_DA
TE_INV + 1, R.R_I_DATE_INVESTISSEMENT), F_CONVERSION_DEVISE(
D.DDC_PM_AU_TAUX_GA
RANTI + D.DDC_PARTICIPATION_BENEFICE + D.DDC_RETENUE, D.DDC_DEVISE, :B8 ),
F_CON

Thank you.

Search Discussions

  • Stephane Faroult at Sep 29, 2011 at 7:50 am
    Hmmm, F_CONVERSION_DEVISE() the kind of killer function I just love ...
    with a nice select of death to find the latest currency rate.
    Apparently there are at least two calls to this function in the SELECT
    LIST. Throw a few other function calls into the WHERE clause for good
    measure, and you don't need to look any further. I have seen this type
    of query, and repeated NOT to do it, much too often for my taste.

    HTH


    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



    On 09/29/2011 09:39 AM, thierry gascard wrote:
    Hello;
    I try to understand why so much elapsed times with a stored procedure
    124,037 s elapsed time against 968 s of Cpu time.
    There isn't very much SQL activity, but with Time Model Statistics this 89.5
    % of DB Time !!!

    An extract of AWR report :
    Snap Id Snap Time Sessions Curs/Sess
    --------- ------------------- -------- ---------
    Begin Snap: 25358 26-Sept.-11 19:45:2 20 8.6
    End Snap: 25402 27-Sept.-11 06:45:4 19 6.0
    Elapsed: 660.28 (mins)
    DB Time: 2,725.23 (mins)
    ...

    Time Model Statistics DB/Inst: MACSTP/MACSTP Snaps:
    25358-25402
    -> Total time in database user-calls (DB Time): 163514s
    -> Statistics including the word "background" measure background process
    time, and so do not contribute to the DB time statistic
    -> Ordered by % or DB time desc, Statistic name

    Statistic Name Time (s) % of DB Time
    ------------------------------------------ ------------------ ------------
    sql execute elapsed time 146,340.9 89.5
    PL/SQL execution elapsed time 43,324.9 26.5
    inbound PL/SQL rpc elapsed time 30,334.5 18.6
    parse time elapsed 4,442.9 2.7
    DB CPU 2,304.0 1.4
    hard parse elapsed time 123.7 .1
    PL/SQL compilation elapsed time 8.1 .0
    connection management call elapsed time 5.6 .0
    repeated bind elapsed time 1.7 .0
    hard parse (sharing criteria) elapsed time 1.3 .0
    sequence load elapsed time 0.2 .0
    hard parse (bind mismatch) elapsed time 0.1 .0
    failed parse elapsed time 0.0 .0
    DB time 163,514.0 N/A
    background elapsed time 5,042.1 N/A
    background cpu time 70.7 N/A
    ..
    Elapsed CPU Elap per % Total
    Time (s) Time (s) Executions Exec (s) DB Time SQL Id
    ---------- ---------- ------------ ---------- ------- -------------
    124,037 968 1 124036.5 75.9 4mmh51uun7bcx
    Module: mgrntw.exe
    begin D_FORTIS.PRC_REMONTEE_PM_SUPPORT(:1 , :2 , :3 , :4 , :5 ,:FLD1 ,:FLD
    2 ,:FLD3 ,:FLD4); end;

    14,195 221 438,448 0.0 8.7 aps7tsk52h9ac
    Module: mgrntw.exe
    SELECT MVP.MVP_TAUX_SPECIFIQUE, M.MVT_ACTE_GESTION, M.MVT_SIGNE,
    D.DDC_EXERCICE,
    M.MVT_DATE_EFFET, M.MVT_N_ORIGINE, R.R_I_DATE_INVESTISSEMENT,
    GREATEST(D.DDC_DA
    TE_INV + 1, R.R_I_DATE_INVESTISSEMENT), F_CONVERSION_DEVISE(
    D.DDC_PM_AU_TAUX_GA
    RANTI + D.DDC_PARTICIPATION_BENEFICE + D.DDC_RETENUE, D.DDC_DEVISE, :B8 ),
    F_CON

    Thank you.


    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Marcus Mönnig at Sep 29, 2011 at 8:02 am
    Hi Thierry,

    without seeing the complete report, it's hard to say.

    One idea would be that the procedure D_FORTIS.PRC_REMONTEE_PM_SUPPORT
    executes lots of different SQL statements (not using bind variables,
    but dynamic SQL). Each of these statements would not show up in the
    SQL sections of the report since the separate execution time is too
    low, but the sum of all these SQLs would be very high.

    Even with the full AWR report this might not be easy to track with
    instance data and you might want to look into a 10046 trace file for a
    session calling D_FORTIS.PRC_REMONTEE_PM_SUPPORT.

    Cheers,
    Marcus
  • Chitale, Hemant Krishnarao at Sep 29, 2011 at 8:38 am
    Can you post the Load Profile and the Top 5 Timed Events sections of the AWR ?


    Hemant K Chitale


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of thierry gascard
    Sent: Thursday, September 29, 2011 3:40 PM
    To: oracle-l@freelists.org
    Subject: Elapsed time for Stored Procedure and Time model

    Hello;
    I try to understand why so much elapsed times with a stored procedure
    124,037 s elapsed time against 968 s of Cpu time.
    There isn't very much SQL activity, but with Time Model Statistics this 89.5
    % of DB Time !!!

    <deleted>

    Thank you.


    --
    http://www.freelists.org/webpage/oracle-l



    This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

    Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
    --
    http://www.freelists.org/webpage/oracle-l
  • Thierry gascard at Sep 29, 2011 at 8:48 am
    This is it, i have elasped time without many wait events...
    Top 5 Timed Events Avg %Total
    ~~~~~~~~~~~~~~~~~~ wait Call
    Event Waits Time (s) (ms) Time Wait
    Class
    ------------------------------ ------------ ----------- ------ ------
    ----------
    db file sequential read 1,502,562 5,311 4 3.2 User
    I/O
    CPU time 2,304 1.4
    db file scattered read 709,287 1,235 2 0.8 User
    I/O
    control file parallel write 15,040 115 8 0.1 System
    I/O
    SQL*Net more data to client 2,185,044 72 0 0.0
    Network

    2011/9/29 Chitale, Hemant Krishnarao <Hemant.Chitale@sc.com>
    Can you post the Load Profile and the Top 5 Timed Events sections of the
    AWR ?


    Hemant K Chitale


    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of thierry gascard
    Sent: Thursday, September 29, 2011 3:40 PM
    To: oracle-l@freelists.org
    Subject: Elapsed time for Stored Procedure and Time model

    Hello;
    I try to understand why so much elapsed times with a stored procedure
    124,037 s elapsed time against 968 s of Cpu time.
    There isn't very much SQL activity, but with Time Model Statistics this
    89.5
    % of DB Time !!!

    <deleted>

    Thank you.


    --
    http://www.freelists.org/webpage/oracle-l



    This email and any attachments are confidential and may also be privileged.
    If you are not the addressee, do not disclose, copy, circulate or in any
    other way use or rely on the information contained in this email or any
    attachments. If received in error, notify the sender immediately and delete
    this email and any attachments from your system. Emails cannot be
    guaranteed to be secure or error free as the message and any attachments
    could be intercepted, corrupted, lost, delayed, incomplete or amended.
    Standard Chartered PLC and its subsidiaries do not accept liability for
    damage caused by this email or any attachments and may monitor email
    traffic.

    Standard Chartered PLC is incorporated in England with limited liability
    under company number 966425 and has its registered office at 1 Aldermanbury
    Square, London, EC2V 7SB.

    Standard Chartered Bank ("SCB") is incorporated in England with limited
    liability by Royal Charter 1853, under reference ZC18. The Principal Office
    of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In
    the United Kingdom, SCB is authorised and regulated by the Financial
    Services Authority under FSA register number 114276.

    If you are receiving this email from SCB outside the UK, please click
    http://www.standardchartered.com/global/email_disclaimer.html to refer to
    the information on other jurisdictions.

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 29, '11 at 7:41a
activeSep 29, '11 at 8:48a
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase