Tracing in 10g for query with bind variables

[ggnanaraj]

On a performance test system, I'm planning to use instance wide tracing by

'alter system set events '10046 trace name context forever,level 12';

However, how can we execute a query with bind variables and trace it successfully?

I assume that it can be done only after declaring the bind variables in a PL/SQL block; and then executing the query with tracing enabled.

variable bind1 number;
begin
:bind1:=10;
end;
/

Do we have any other options apart from this?

Thanks In Advance.

[PAVB]

you can do it on sqlplus...

alter system set events '10046 trace name context forever,level 12';
variable bind1 number;
exec :bind1 := 10;
your-query
alter session set events '10046 trace name context off';

[ggnanaraj]

Thanks for the information provided.

Can we do the tracing without declaring the bind variables in the query? I know this sounds absurd! Still wanted to double check!!

TIA.

[hrishy]

Hi

Yes you need to set the bind variables.

You can also use dbms_xplan in 10gr2 to get the values of the bind variables

dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));

something like

SELECT plan_table_output
FROM v$sql s,
table(dbms_xplan.display_cursor(s.sql_id,
s.child_number, 'basic+PEEKED_BINDS')) t
WHERE s.sql_text like 'select  statement%';

[hrishy]

Hi

Well the way it works is it peeks the variable the first time you declare it and from next time onwards even if you change the variable the initial plan which was cached will be used.

its necessary that the variable is set the first time.

regards
Hrishy

[hrishy]

Thanks for the information provided.

Can we do the tracing without declaring the bind variables in the query? I know this sounds absurd! Still wanted to double check!!

TIA.

No You cannot do the tracing without declaring the bind variables because for tracing the query execution you need to execute the query for which a value needs to binded to the query

if you are interested in the plan then you can use dbms_xplan