Re: Running TKPROF output

spy_234432_at_yahoo.com (Margie) wrote in message news:<8a535156.0404161407.7a433e31_at_posting.google.com>...
> If I run tkprof and I get the output, e.g.,
>
> SELECT Name
> FROM USER.USER_view
> where "PHONE"=:1 AND Name LIKE :2 AND ("Date" is NULL OR "Date" >=
> :3)
>
> Can I get actual values instead of :1 :2 and :3? I would like to
> re-run the query to see how it performs instead of tracing it.
>
> Thanks, Margie

Margie, you can look at the table or query the view for data to use; however be advised that the CBO will often choose a different plan when constants are substituted for bind variables in an SQL statement.  The CBO has more information about selectivity available with constants when the statement is parsed.

If the time statistics information in the tkprof output are not good enough then for your time test I suggest you run the SQL via pl/sql where you pass or set the bind variables to several sets of values. Alternately you could use hints to force the CBO into the same plan, if necessary, and then compare the time for the query to alternate versions.

HTH -- Mark D Powell -- Received on Fri Apr 16 2004 - 21:59:02 CDT