Re: Tracing
From: The Magnet <art_at_unsu.com>
Date: Thu, 19 Nov 2009 10:51:28 -0800 (PST)
Message-ID: <74f90afe-31f0-46c0-9533-246010acaf73_at_j19g2000yqk.googlegroups.com>
On Nov 19, 12:09 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 19, 8:45 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > Hi,
>
> > When tracing SQL statements from SQLPLUS to see consistent gets and
> > such, if the query contains bind variables or constants, how do you
> > 'emulate' that in SQLPLUS and perform the trace? Here is an example:
>
> > This is in one of our stored procedures. It does 5500 consistent
> > gets. I want to try and bring that down, but, I'm thinking I can't
> > because of the literals. If I change those to bind variables as well
> > as the 237 constant, how can I trace that from SQLPLUS?
>
> > SELECT subgroup_id, max_allowed_subscr, allow_new_subscriptions,
> > priority
> > FROM (SELECT ts.subgroup_id, max_allowed_subscr,
> > allow_new_subscriptions, priority, COUNT(ts.subgroup_id)
> > OVER (PARTITION BY ts.subgroup_id, ts.newsletter_id)
> > number_in_group, ROW_NUMBER()
> > OVER (PARTITION BY ts.subgroup_id, ts.newsletter_id ORDER BY
> > priority) rnum
> > FROM engine.ts_subgroups ts, engine.ts_customers tc,
> > engine.customer_order co,
> > engine.order_line ol, engine.product p
> > WHERE ts.subgroup_id = tc.subgroup_id
> > AND tc.customer_id = co.customer_id
> > AND co.order_id = ol.order_id
> > AND ol.product_id = p.product_id
> > AND ol.status IN ('Active','Pending')
> > AND p.subproduct_id = 237
> > AND ts.newsletter_id = 237)
> > WHERE rnum = 1;
>
> Never tried it myself, but seehttp://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var...
>
> Some people have blogged about this, so a google may turn that up.
>
> jg
> --
> _at_home.com is bogus.
> "You're a little hostile, Joel." - Ralph Garman, giving Joel McHale a
> hard time in a radio interview.
Date: Thu, 19 Nov 2009 10:51:28 -0800 (PST)
Message-ID: <74f90afe-31f0-46c0-9533-246010acaf73_at_j19g2000yqk.googlegroups.com>
On Nov 19, 12:09 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 19, 8:45 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > Hi,
>
> > When tracing SQL statements from SQLPLUS to see consistent gets and
> > such, if the query contains bind variables or constants, how do you
> > 'emulate' that in SQLPLUS and perform the trace? Here is an example:
>
> > This is in one of our stored procedures. It does 5500 consistent
> > gets. I want to try and bring that down, but, I'm thinking I can't
> > because of the literals. If I change those to bind variables as well
> > as the 237 constant, how can I trace that from SQLPLUS?
>
> > SELECT subgroup_id, max_allowed_subscr, allow_new_subscriptions,
> > priority
> > FROM (SELECT ts.subgroup_id, max_allowed_subscr,
> > allow_new_subscriptions, priority, COUNT(ts.subgroup_id)
> > OVER (PARTITION BY ts.subgroup_id, ts.newsletter_id)
> > number_in_group, ROW_NUMBER()
> > OVER (PARTITION BY ts.subgroup_id, ts.newsletter_id ORDER BY
> > priority) rnum
> > FROM engine.ts_subgroups ts, engine.ts_customers tc,
> > engine.customer_order co,
> > engine.order_line ol, engine.product p
> > WHERE ts.subgroup_id = tc.subgroup_id
> > AND tc.customer_id = co.customer_id
> > AND co.order_id = ol.order_id
> > AND ol.product_id = p.product_id
> > AND ol.status IN ('Active','Pending')
> > AND p.subproduct_id = 237
> > AND ts.newsletter_id = 237)
> > WHERE rnum = 1;
>
> Never tried it myself, but seehttp://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var...
>
> Some people have blogged about this, so a google may turn that up.
>
> jg
> --
> _at_home.com is bogus.
> "You're a little hostile, Joel." - Ralph Garman, giving Joel McHale a
> hard time in a radio interview.
Hopefully I can find a way to take the SQL statement, apply the bind variables, AND trace it to see the plan. Received on Thu Nov 19 2009 - 12:51:28 CST