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;

On Nov 19, 8:45*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Never tried it myself, but see http://www.oracle.com/technology/sup.../sub_var3.html

Some people have blogged about this, so a google may turn that up.

jg
--
@home.com is bogus.
"You're a little hostile, Joel." - Ralph Garman, giving Joel McHale a
hard time in a radio interview.

On Nov 19, 12:09*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Hopefully I can find a way to take the SQL statement, apply the bind
variables, AND trace it to see the plan.

On Nov 19, 11:45*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
Here is the general procedure with a different SQL statement (you MUST
disable autotrace, the first line will have an impact later - ignore
for now):
SQL> ALTER SESSION SET STATISTICS_LEVEL='ALL';

SQL> VARIABLE N1 NUMBER
SQL> VARIABLE N2 NUMBER
SQL> EXEC :N1:=1
SQL> EXEC :N2:=100

SQL> SELECT
2 T3.C1,
3 T4.C2
4 FROM
5 T3,
6 T4
7 WHERE
8 T3.C1 BETWEEN :N1 AND :N2
9 AND T3.C1=T4.C1;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'TYPICAL'));

SQL_ID 41rcjcqgz38z2, child number 0
-------------------------------------
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1
BETWEEN :N1
AND :N2 AND T3.C1=T4.C1

Plan hash value: 1980773432
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T3"."C1"="T4"."C1")
3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
5 - access("T4"."C1">=:N1 AND "T4"."C1"<=:N2)

Autotrace and EXPLAIN PLAN will sometimes lie about the actual
execution plan, especially when the execution plan contains bind
variables. You could take the above a step further like this to see
the actual number of rows returned (the above *is* the actual plan):
SQL> VARIABLE N1 NUMBER
SQL> VARIABLE N2 NUMBER
SQL> EXEC :N1:=1
SQL> EXEC :N2:=100

SQL> EXEC :N2:=10000

SQL> SELECT
2 T3.C1,
3 T4.C2
4 FROM
5 T3,
6 T4
7 WHERE
8 T3.C1 BETWEEN :N1 AND :N2
9 AND T3.C1=T4.C1;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTAT S
LAST'));

Plan hash value: 1980773432
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T3"."C1"="T4"."C1")
3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
5 - access("T4"."C1">=:N1 AND "T4"."C1"<=:N2)

In the above, notice that the E-Rows column still shows that Oracle is
estimating that 100 rows will be returned by many steps in the plan,
while the A-Rows column shows that 10,000 rows were actually
returned. The Buffers column shows the number of consistent gets and
the Reads column shows the number of blocks read from disk. Note that
with ALTER SESSION SET STATISTICS_LEVEL='ALL' the query could require
much longer to execute, especially prior to Oracle 11.1.0.6. You
could add a /*+ GATHER_PLAN_STATISTICS */ hint to the SQL statement
rather than changing the STATISTICS_LEVEL parameter.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.