FAQ
On several occasions, we have had a "bad" plan generated by the CBO (I
know this is old hat for you veterans in the field *grin*). A few in
particular will demonstrate these symptoms:
* At some point in time (time A), the query is freshly parsed
(hard parsed) and a "less-than-optimal" plan is generated
* At some other point B (anytime > A), all other queries are soft
parsed (libcache hit) with the same grisly plan
* Further later on after B, the users finally get fed up and call
in the problem. DBAs get the call but cannot reproduce the problem in
Test. Altering a test query in Production with a dummy comment (to
change the signature), the query parses freshly (libcache miss) with a
"good" plan.
* Lastly, the shared pool is flushed and the original query parses
once again, but this time with a "good" plan

I have at least two different SRs open on cases like this, and Oracle
Support wants me to send them a test case, or generate 10053 and 10046
traces on a "bad" query. How do I do that? Ideally, in a perfect world,
I would have baseline profiles on all query statements and know when a
query is going against a new (whether "bad" or "good") plan. Alas, we
are stuck in an imperfect world and woefully must wait for the blessed
user to complain to us. (I am sure many of you will have helpful advice
on how to stop depending on users in such an embarrassing way.)
Ironically, after reading a little about the HotSOS Profiler and OraSRP,
I could not find OraSRP on Egor's site.

My one thought, which causes me to cringe, is to turn on a 10053 trace
at the system level to make sure we catch the dastardly plans in action.
But surely there is a better way. I am hoping that I am simply missing
something really obvious which would shame me, but at least should be a
simple solution. I thought about login triggers, but that requires that
you know which login conditions to watch for. While we have "usual
suspects", they are not consistent. We have tried setting up tracing
after the fact, but we never catch that initial bugger. I looked at the
various dba_hist_sql% views, but I was having a hard time getting hard
information out of them.

Where do I go from here?

charles schultz
oracle dba
aits - adsd
university of illinois

Search Discussions

  • Schultz, Charles at Jun 7, 2006 at 7:55 pm
    http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/opti
    mops.htm#sthref1254

    Ahh, I see how Bind variable Peeking becomes a bit of a problem. Thanks
    Wolfgang, for the heads up.
    Now, how do I fix this? =) Sometimes peeking is good, some times not.
    _____________________________________________
    From: Schultz, Charles
    Sent: Wednesday, June 07, 2006 2:17 PM
    To: oracle-l
    Subject: Need advice on tuning slippery queries

    On several occasions, we have had a "bad" plan generated by the CBO (I
    know this is old hat for you veterans in the field *grin*). A few in
    particular will demonstrate these symptoms:
    * At some point in time (time A), the query is freshly parsed
    (hard parsed) and a "less-than-optimal" plan is generated
    * At some other point B (anytime > A), all other queries are soft
    parsed (libcache hit) with the same grisly plan
    * Further later on after B, the users finally get fed up and call
    in the problem. DBAs get the call but cannot reproduce the problem in
    Test. Altering a test query in Production with a dummy comment (to
    change the signature), the query parses freshly (libcache miss) with a
    "good" plan.
    * Lastly, the shared pool is flushed and the original query parses
    once again, but this time with a "good" plan

    I have at least two different SRs open on cases like this, and Oracle
    Support wants me to send them a test case, or generate 10053 and 10046
    traces on a "bad" query. How do I do that? Ideally, in a perfect
    world, I would have baseline profiles on all query statements and know
    when a query is going against a new (whether "bad" or "good") plan.
    Alas, we are stuck in an imperfect world and woefully must wait for
    the blessed user to complain to us. (I am sure many of you will have
    helpful advice on how to stop depending on users in such an
    embarrassing way.) Ironically, after reading a little about the HotSOS
    Profiler and OraSRP, I could not find OraSRP on Egor's site.

    My one thought, which causes me to cringe, is to turn on a 10053 trace
    at the system level to make sure we catch the dastardly plans in
    action. But surely there is a better way. I am hoping that I am simply
    missing something really obvious which would shame me, but at least
    should be a simple solution. I thought about login triggers, but that
    requires that you know which login conditions to watch for. While we
    have "usual suspects", they are not consistent. We have tried setting
    up tracing after the fact, but we never catch that initial bugger. I
    looked at the various dba_hist_sql% views, but I was having a hard
    time getting hard information out of them.

    Where do I go from here?

    charles schultz
    oracle dba
    aits - adsd
    university of illinois
    --
    http://www.freelists.org/webpage/oracle-l
  • LeRoy Kemnitz at Jun 7, 2006 at 8:04 pm
    Below is a query I am trying to compile in 10.2.0.1. I keep getting
    PLS-00049 Bad bind Variable. I don't see anything wrong with this one.
    Can anyone help me?

    ##############################################

    create or replace procedure ddh_test2 as
    begin
    SELECT

    uwsias.i_maj_credits_cpc.year,
    uwsias.i_maj_credits_cpc.term,
    uwsias.i_maj_credits_cpc.unit,
    uwsias.i_maj_credits_cpc.unit_name,
    uwsias.i_maj_credits_cpc.major,
    uwsias.i_maj_credits_cpc.major_name,
    DECODE (
    SUM (uwsias.i_maj_credits_cpc.off_credits_lvl3),
    0, 0,
    SUM (uwsias.i_maj_credits_cpc.off_credits_lvl3

    * uwsias.i_maj_credits_cpc.cost_per_credit_lvl3) / SUM
    (uwsias.i_maj_credits_cpc.off_credits_lvl3)) major_cpc

    FROM
    uwsias.i_maj_credits_cpc
    WHERE
    uwsias.i_maj_credits_cpc.year = :year using
    year AND
    uwsias.i_maj_credits_cpc.term = '1' AND
    uwsias.i_maj_credits_cpc.major = :major_code
    using major_code
    GROUP BY
    uwsias.i_maj_credits_cpc.year,
    uwsias.i_maj_credits_cpc.term,
    uwsias.i_maj_credits_cpc.unit,
    uwsias.i_maj_credits_cpc.unit_name,
    uwsias.i_maj_credits_cpc.major,
    uwsias.i_maj_credits_cpc.major_name;

    end;

    ###################################################33
  • Rjamya at Jun 7, 2006 at 8:10 pm
    is ":year using year " and ":major_code using major_code" a valid sql syntax ??

    me don't think so ...
    Raj

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 7, '06 at 7:16p
activeJun 7, '06 at 8:10p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase