FAQ
Hi

I have TKPROFed some batch processes running in Oracle 8i. After upgraded to
9i I monitored the same batch processes and I notice Parsing has been
reduced by 50% without changing any code.

Does anyone know what Optimization is this? I have not changed any
parameters. Only added automatic pga.

TIA

Alex

8i:

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 120 0.43 0.44 0 0
0 0
Execute 60 0.01 0.03 0 0
0 0
Fetch 60 0.05 1.51 157 829
0 0

------- ------ -------- ---------- ---------- ---------- ----------

total 240 0.49 1.98 157 829
0 0

9i

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 60 0.01 0.01 0 0
0 0
Execute 60 0.01 0.03 0 0
0 0
Fetch 60 0.05 1.33 165 828
0 0

------- ------ -------- ---------- ---------- ---------- ----------

total 180 0.07 1.37 165 828
0 0

Search Discussions

  • Anjo Kolk at Aug 30, 2006 at 7:39 pm
    Without seeing the statement, I would say max_permutations. Your parse
    elapse time consists of mostly cpu time, so that could be it. However it may
    not be the (only) one.

    Anjo.
    On 8/30/06, amonte wrote:

    Hi

    I have TKPROFed some batch processes running in Oracle 8i. After upgraded
    to 9i I monitored the same batch processes and I notice Parsing has been
    reduced by 50% without changing any code.

    Does anyone know what Optimization is this? I have not changed any
    parameters. Only added automatic pga.


    TIA
    Alex


    8i:

    call count cpu elapsed disk query
    current rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 120 0.43 0.44 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.51 157 829
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 240 0.49 1.98 157 829
    0 0

    9i

    call count cpu elapsed disk query
    current rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 60 0.01 0.01 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.33 165 828
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 180 0.07 1.37 165 828
    0 0
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Aug 30, 2006 at 8:04 pm
    optimizer_max_permutations would only reduce the time spent on parsing,
    not the number of parses. In the original post, the parse "count" was
    reduced from 120 to 60. Seems to me like maybe it was some sort of
    pl/sql optimization that kept the cursor open so it didn't have to be
    reparsed?

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Anjo Kolk

    Sent: Wednesday, August 30, 2006 12:39 PM



    Without seeing the statement, I would say max_permutations.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Jonathan Lewis at Aug 30, 2006 at 8:33 pm
    You don't say what the front-end code is, but there
    are environments where the client libraries do a parse
    call followed by a parse and execute call - which
    gives you the two parses per execute that your
    8i tkprof file is showing.

    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    Original Message -----
    From: "amonte"
    To:
    Sent: Wednesday, August 30, 2006 5:20 PM
    Subject: 9i Parsing Optimization
    Hi

    I have TKPROFed some batch processes running in Oracle 8i. After upgraded to
    9i I monitored the same batch processes and I notice Parsing has been
    reduced by 50% without changing any code.

    Does anyone know what Optimization is this? I have not changed any
    parameters. Only added automatic pga.


    TIA
    Alex


    8i:

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 120 0.43 0.44 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.51 157 829
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 240 0.49 1.98 157 829
    0 0

    9i

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 60 0.01 0.01 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.33 165 828
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 180 0.07 1.37 165 828
    0 0
    --
    http://www.freelists.org/webpage/oracle-l
  • Amonte at Aug 30, 2006 at 8:50 pm
    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the second call
    is not "called" in 9i?

    TIA

    Alex
    On 8/30/06, Jonathan Lewis wrote:



    You don't say what the front-end code is, but there
    are environments where the client libraries do a parse
    call followed by a parse and execute call - which
    gives you the two parses per execute that your
    8i tkprof file is showing.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte"
    To:
    Sent: Wednesday, August 30, 2006 5:20 PM
    Subject: 9i Parsing Optimization

    Hi

    I have TKPROFed some batch processes running in Oracle 8i. After
    upgraded to
    9i I monitored the same batch processes and I notice Parsing has been
    reduced by 50% without changing any code.

    Does anyone know what Optimization is this? I have not changed any
    parameters. Only added automatic pga.


    TIA
    Alex


    8i:

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 120 0.43 0.44 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.51 157 829
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 240 0.49 1.98 157 829
    0 0

    9i

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 60 0.01 0.01 0 0
    0 0
    Execute 60 0.01 0.03 0 0
    0 0
    Fetch 60 0.05 1.33 165 828
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 180 0.07 1.37 165 828
    0 0
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Aug 30, 2006 at 8:58 pm
    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.

    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    Original Message -----
    From: "amonte"
    To:
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization
    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the second call
    is not "called" in 9i?

    TIA
    Alex
    --
    http://www.freelists.org/webpage/oracle-l
  • Anjo Kolk at Aug 31, 2006 at 7:35 am
    Part of the OCI8 interface is the capability to execute SQL statements
    without doing defines (for the select list). So you can do an execute and
    then after that execute you know what the select list looks like (types,
    sizes), for this an implicit DESCRIBE (which requires a parse) is done,
    after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many years ago),
    they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.
    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte"
    To:
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the second call
    is not "called" in 9i?

    TIA
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • Amonte at Aug 31, 2006 at 8:21 am
    Hi

    I am checking the ODBC version being used.

    It is Merant ODBC v4.1 and has not been changed. My doubt is if this ODBC
    driver depends internallly on OCI version. If that is the case then it
    probably was using OCI8 and now using OCI9 and may be we are talking about
    OCI9 optimization.

    All I have is a libodbc.sl which is supposed to be the Merant ODBC, how can
    I check if this has dependency on OCI?

    Thanks all

    Alex
    On 8/31/06, Anjo Kolk wrote:

    Part of the OCI8 interface is the capability to execute SQL statements
    without doing defines (for the select list). So you can do an execute and
    then after that execute you know what the select list looks like (types,
    sizes), for this an implicit DESCRIBE (which requires a parse) is done,
    after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many years
    ago), they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.

    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte"
    To: < jonathan_at_jlcomp.demon.co.uk>
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the
    second call
    is not "called" in 9i?

    TIA

    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    --
    http://www.freelists.org/webpage/oracle-l
  • Anjo Kolk at Aug 31, 2006 at 8:39 am
    try ldd on the executable or the library. Another thing you can try is: nm
    libodbc.sl| grep OCI

    Anjo.
    On 8/31/06, amonte wrote:

    Hi

    I am checking the ODBC version being used.

    It is Merant ODBC v4.1 and has not been changed. My doubt is if this ODBC
    driver depends internallly on OCI version. If that is the case then it
    probably was using OCI8 and now using OCI9 and may be we are talking about
    OCI9 optimization.

    All I have is a libodbc.sl which is supposed to be the Merant ODBC, how
    can I check if this has dependency on OCI?

    Thanks all

    Alex


    On 8/31/06, Anjo Kolk wrote:

    Part of the OCI8 interface is the capability to execute SQL statements
    without doing defines (for the select list). So you can do an execute and
    then after that execute you know what the select list looks like (types,
    sizes), for this an implicit DESCRIBE (which requires a parse) is done,
    after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many years
    ago), they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.

    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte" < ax.mount_at_gmail.com>
    To: < jonathan_at_jlcomp.demon.co.uk>
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the
    second call
    is not "called" in 9i?

    TIA

    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Aug 31, 2006 at 8:42 am
    Hi Alex

    I think the improvemente might be cuased by the new OCIStmtPrepare2 call
    (introduced in 9.2), this uses a client side sql statement cache to avoid a
    server side parsing.

    Regards

    --
    LSC
    On 8/31/06, amonte wrote:

    Hi

    I am checking the ODBC version being used.

    It is Merant ODBC v4.1 and has not been changed. My doubt is if this ODBC
    driver depends internallly on OCI version. If that is the case then it
    probably was using OCI8 and now using OCI9 and may be we are talking about
    OCI9 optimization.

    All I have is a libodbc.sl which is supposed to be the Merant ODBC, how
    can I check if this has dependency on OCI?

    Thanks all

    Alex


    On 8/31/06, Anjo Kolk wrote:

    Part of the OCI8 interface is the capability to execute SQL statements
    without doing defines (for the select list). So you can do an execute and
    then after that execute you know what the select list looks like (types,
    sizes), for this an implicit DESCRIBE (which requires a parse) is done,
    after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many years
    ago), they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.

    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte" < ax.mount_at_gmail.com>
    To: < jonathan_at_jlcomp.demon.co.uk>
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the
    second call
    is not "called" in 9i?

    TIA

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


    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    --
    http://www.freelists.org/webpage/oracle-l
  • Anjo Kolk at Aug 31, 2006 at 10:52 am
    Only when the code has been written to use that call.

    Anjo.
    On 8/31/06, LS Cheng wrote:

    Hi Alex

    I think the improvemente might be cuased by the new OCIStmtPrepare2 call
    (introduced in 9.2), this uses a client side sql statement cache to avoid
    a server side parsing.

    Regards

    --
    LSC



    On 8/31/06, amonte wrote:

    Hi

    I am checking the ODBC version being used.

    It is Merant ODBC v4.1 and has not been changed. My doubt is if this
    ODBC driver depends internallly on OCI version. If that is the case then it
    probably was using OCI8 and now using OCI9 and may be we are talking about
    OCI9 optimization.

    All I have is a libodbc.sl which is supposed to be the Merant ODBC, how
    can I check if this has dependency on OCI?

    Thanks all

    Alex


    On 8/31/06, Anjo Kolk wrote:

    Part of the OCI8 interface is the capability to execute SQL statements
    without doing defines (for the select list). So you can do an execute and
    then after that execute you know what the select list looks like (types,
    sizes), for this an implicit DESCRIBE (which requires a parse) is done,
    after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many years
    ago), they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.

    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis


    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte" < ax.mount_at_gmail.com>
    To: < jonathan_at_jlcomp.demon.co.uk>
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the
    second call
    is not "called" in 9i?

    TIA

    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • Amonte at Aug 31, 2006 at 12:02 pm
    Hmm

    I need to have a look at that, the Merant ODBC Driver being used can be used
    for both Oracle 8i and 9i. I am not sure if it would be clever enough to
    detect oci version and run OCIStmtPrepare for 8i and OCIStmtPrepare2 for 9i?

    Thanks all
    On 8/31/06, Anjo Kolk wrote:

    Only when the code has been written to use that call.

    Anjo.

    On 8/31/06, LS Cheng wrote:

    Hi Alex

    I think the improvemente might be cuased by the new OCIStmtPrepare2 call
    (introduced in 9.2), this uses a client side sql statement cache to
    avoid a server side parsing.

    Regards

    --
    LSC



    On 8/31/06, amonte wrote:

    Hi

    I am checking the ODBC version being used.

    It is Merant ODBC v4.1 and has not been changed. My doubt is if this
    ODBC driver depends internallly on OCI version. If that is the case then it
    probably was using OCI8 and now using OCI9 and may be we are talking about
    OCI9 optimization.

    All I have is a libodbc.sl which is supposed to be the Merant ODBC,
    how can I check if this has dependency on OCI?

    Thanks all

    Alex


    On 8/31/06, Anjo Kolk wrote:

    Part of the OCI8 interface is the capability to execute SQL
    statements without doing defines (for the select list). So you can do an
    execute and then after that execute you know what the select list looks like
    (types, sizes), for this an implicit DESCRIBE (which requires a parse) is
    done, after that a normal regular parse is done. So that could explain the 2
    parses in 8. May be that was optimized in oracle9. When you did the defines,
    one can Fetch the data.

    If I remember correctly (talking to some folks from siebels many
    years ago), they do use OCI (not sure if it was OCI8, but most likely)

    Anjo.

    On 8/30/06, Jonathan Lewis wrote:


    I don't know what technology Siebel Tools uses,
    but your earlier double call may have been a
    "feature" of the ODBC or JDBC or whatever
    driver was between Siebel and Oracle, rather
    than a bit of the application code.

    I believe there used to be a feature in the Perl
    driver, for example, that made it do a parse
    call without bind values, followed by an execute
    call with bind values - but if the bind types weren't
    all character, the execute call would require a
    second parse call. It's something like that that
    may have gone away on the upgrade.

    Don't ask me to quote specifics - it's a long time
    ago and in another version.


    Regards

    Jonathan Lewis
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis


    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


    ----- Original Message -----
    From: "amonte" < ax.mount_at_gmail.com>
    To: < jonathan_at_jlcomp.demon.co.uk>
    Cc:
    Sent: Wednesday, August 30, 2006 9:50 PM
    Subject: Re: 9i Parsing Optimization

    Anjo and Brandon, I forgot to mention we use RULE BASED
    OPTIMIZER so
    permutations shouldnt be a problem. This is not PL/SQL so it
    cant be some
    pl/sql optimization. This is a Siebel Workflow Manager Process.

    Jonathan, it is Siebel Tools, I understand that from application codes you
    can invoke two parse calls but what I dont understand is why the
    second call
    is not "called" in 9i?

    TIA

    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    --
    http://www.freelists.org/webpage/oracle-l
  • Mladen Gogala at Aug 31, 2006 at 2:32 pm

    On 08/31/2006 08:02:54 AM, amonte wrote:
    Hmm

    I need to have a look at that, the Merant ODBC Driver being used can be used
    for both Oracle 8i and 9i. I am not sure if it would be clever enough to
    detect oci version and run OCIStmtPrepare for 8i and OCIStmtPrepare2 for 9i?
    It's not that hard to be "smart". There is POSIX interface to the dynamic libraries.
    You open a library with dlopen and check for symbols with dlsym. That works on so
    called "nice shared libraries", the ones with extensions like ".so" or ".sl". There
    are also the ugly shared libraries, with extensions like ".dll" that I know nothing
    about. Those used in the land of Redmond where the shadows lie.
  • Polarski, Bernard at Aug 31, 2006 at 9:36 am
    In the upgrade process, did you changed 'session_cached_cursors' from 0
    (default in 8i) to something like 50+?
    In this case your hits are much better and there is less pression on the
    shared pool, hence your parsing decrease also.

    From: Allen, Brandon

    optimizer_max_permutations would only reduce the time spent on parsing,
    not the number of parses. In the original post, the parse "count" was
    reduced from 120 to 60. Seems to me like maybe it was some sort of
    pl/sql optimization that kept the cursor open so it didn't have to be
    reparsed?

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Anjo Kolk


    Without seeing the statement, I would say max_permutations.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.
  • Amonte at Aug 31, 2006 at 9:57 am
    Hi Bernard

    session_cached_cursors has same value in 8i and 9i, 100.

    TIA
    On 8/31/06, Polarski, Bernard wrote:

    In the upgrade process, did you changed 'session_cached_cursors' from 0
    (default in 8i) to something like 50+?
    In this case your hits are much better and there is less pression on the
    shared pool, hence your parsing decrease also.

    ------------------------------
    *From:* Allen, Brandon
    optimizer_max_permutations would only reduce the time spent on parsing,
    not the number of parses. In the original post, the parse "count" was
    reduced from 120 to 60. Seems to me like maybe it was some sort of pl/sql
    optimization that kept the cursor open so it didn't have to be reparsed?

    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Anjo Kolk
    Without seeing the statement, I would say max_permutations.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do not
    consent to Internet email for messages of this kind. Opinions, conclusions
    and other information in this message that do not relate to the official
    business of this company shall be understood as neither given nor endorsed
    by it.
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Aug 31, 2006 at 4:17 pm
    If I understand correctly, session_cached_cursors causes a "softer"
    parse, but a parse would still show up as a parse in the 10046 output so
    I don't believe this parameter could be responsible for the 50%
    reduction in parse count. Someone please correct me if I'm wrong.


    Thanks,
    Brandon

    From: Polarski, Bernard

    Sent: Thursday, August 31, 2006 2:37 AM



    In the upgrade process, did you changed 'session_cached_cursors'
    from 0 (default in 8i) to something like 50+?

    In this case your hits are much better and there is less
    pression on the shared pool, hence your parsing decrease also.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Bobak, Mark at Aug 31, 2006 at 4:31 pm
    Right, session_cached_cursors can have an impact in reducing the amount
    of work a parse does, but, any time a piece of client code makes a parse
    call, Oracle MUST parse. It may be a hard parse, a soft parse, or a
    session cursor cache hit, but it is a parse, none the less. The *ONLY*
    way to reduce parsing is to change the program to parse less.


    As others speculated previously, perhaps there's an ODBC or JDBC driver,
    or something, that has an optimization that reduces the number of parse
    calls, that was picked up on the upgrade from 8i to 9i.....


    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "A human being should be able to change a diaper, plan an invasion,
    butcher a hog, conn a ship, design a building, write a sonnet, balance
    accounts, build a wall, set a bone, comfort the dying, take orders, give
    orders, cooperate, act alone, solve equations, analyze a new problem,
    pitch manure, program a computer, cook a tasty meal, fight efficiently,
    die gallantly. Specialization is for insects." --Robert A. Heinlein

    ________________________________

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Allen, Brandon
    Sent: Thursday, August 31, 2006 12:17 PM
    To: Polarski, Bernard; anjo.kolk_at_oraperf.com; ax.mount_at_gmail.com;
    oracle-l_at_freelists.org
    Subject: RE: 9i Parsing Optimization

    If I understand correctly, session_cached_cursors causes a "softer"
    parse, but a parse would still show up as a parse in the 10046 output so
    I don't believe this parameter could be responsible for the 50%
    reduction in parse count. Someone please correct me if I'm wrong.

    ________________________________

    From: Polarski, Bernard
    Sent: Thursday, August 31, 2006 2:37 AM

    In the upgrade process, did you changed 'session_cached_cursors' from 0
    (default in 8i) to something like 50+?
    In this case your hits are much better and there is less pression on the
    shared pool, hence your parsing decrease also.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Laimutis Nedzinskas at Aug 31, 2006 at 4:31 pm
    If I understand correctly, session_cached_cursors causes a "softer" parse, but a parse would still show up as a parse in the 10046 output


    so far the best desciption of shared pool and parsing I know is here:


    http://download-uk.oracle.com/oowsf2005/003wp.pdf#search=%22understanding%20shared%20pool%20memory%20structures%22


    It indirectly confirms the "softer parse" notion.





    From: oracle-l-bounce_at_freelists.org On Behalf Of Allen, Brandon
    Sent: 31. ágúst 2006 16:17
    To: Polarski, Bernard; anjo.kolk_at_oraperf.com; ax.mount_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: 9i Parsing Optimization

    If I understand correctly, session_cached_cursors causes a "softer" parse, but a parse would still show up as a parse in the 10046 output so I don't believe this parameter could be responsible for the 50% reduction in parse count. Someone please correct me if I'm wrong.




    Fyrirvari/Disclaimer
    http://www.landsbanki.is/disclaimer

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 30, '06 at 4:20p
activeAug 31, '06 at 4:31p
posts18
users9
websiteoracle.com

People

Translate

site design / logo © 2021 Grokbase