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

Hybrid View

  1. #1
    Join Date
    Dec 2001

    Capturing Baseline


    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!!


  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    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
    Dear PAVB,


    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.


    (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?


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.