Re: Tracing

From: joel garry <joel-garry_at_home.com>
Date: Thu, 19 Nov 2009 10:09:40 -0800 (PST)
Message-ID: <445c20a5-f130-4355-8eec-a7f8a82268a2_at_v15g2000prn.googlegroups.com>

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 see http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var3.html

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.

Received on Thu Nov 19 2009 - 12:09:40 CST