DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Capturing Baseline

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Capturing Baseline

    Hello,

    I do have a query with multiple bind variables. I dont the value of those bind variables to pass, as well as I could not able to figure those values in trace file.

    Now I am trying to run that sql with an additional hint (first_rows) and would like to capture that plan in baseline. How to capture baseline of that query with bind variables?

    I thought I will enable automatic baseline acpture and then run this sql with hint and could see the baseline.

    Any help will be appreciated!!

    Regards
    sumit

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Be sure STATISTICS_LEVEL is NOT set to BASIC then query V$SQL_BIND_CAPTURE system view.
    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
    Dec 2001
    Posts
    203
    Dear PAVB,

    Thanks.

    Now I have a different question. Let me explain you step by step

    (1) Presently the query is running from a middleware without any hint. And making code changes (adding hint) in the middleware requires time. The query has 6 bind variables defined. I located the query in the database with sqlid XXXX.

    (2) I am running the same query from sqlplus adding a hint and defining those bind variables like below

    variable B6 number;
    variable B5 number;
    variable B4 number;
    variable B3 number;
    variable B2 varchar2(20);
    variable B1 varchar2(20);
    exec :B6 := null;
    exec :B5 := null;
    exec :B4 := null;
    exec :B3 := null;
    exec :B2 := null;
    exec :B1 := null;

    (3) I got a SQLID, e.g YYYY for the query I ran above from sqlplus.

    Question

    (1) If I capture a baseline using the sqlid (YYYY) above, can I use that plan for the query coming from middleware?Hash_plan is different though.
    (2) If not, how to create a baseline for the query coming from middleware with a HINT?

    Regards
    sumit

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