Tracing in 10g for query with bind variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tracing in 10g for query with bind variables

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Tracing in 10g for query with bind variables

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

    Code:
    '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.
    Code:
    variable bind1 number;
    begin
    :bind1:=10;
    end;
    /
    Do we have any other options apart from this?

    Thanks In Advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    you can do it on sqlplus...
    Code:
    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';
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

    Code:
    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%';

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    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

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Quote Originally Posted by 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.
    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
    Last edited by hrishy; 02-27-2008 at 07:30 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width