The value of the bind variable has a direct impact on the selectivity of your SQL predicates.  Often it is useful to capture the value of the bind variables to investigate the SQL performance issues.

Here are the steps to capture your Oracle SQL trace file that includes the value of the bind variables.

Alter session set statistics_level=ALL;
Alter session set max_dump_file_size=UNLIMITED;
Alter session set events ’10046 trace name context forever, level 12‘;

var g_city char(20)

begin
:g_city := ‘Toronto‘;
end;
/

select * from customers where cust_city = :g_name;

alter session set events ’10046 trace name context off’;

Open up the trace file and search for string “Bind” and followed by value and of course the value.  Not the prettiest output but it does not the job;

BINDS #2:
Bind#0
oacdty=96 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=128 off=0
kxsbbbfp=006061e4  bln=128  avl=30  flg=05
value=”Toronto “

Recall that in Oracle 11g you can provide an identifier for for your trace file to ease the pain of locating the right trace file.

Payam Moghtader is a Senior Consultant and currently working in capacities of Oracle Solutions Architect, IT Project Manager (PMP), and Certified Oracle University Instructor.  He also is a contributor to 2Dev.com. Payam has over 17 years of experience in management and implementation of Information Technology solutions. His areas of expertise are Oracle based solutions including Oracle Fusion Middleware, Oracle WebLogic Server, Oracle Application Server, Oracle ADF, Java EE, Oracle Database, SQL Tuning, Server Architecture and Design as well as Project Management.

He managed his own start-up company in the mid 90’s and was responsible for directing the technology needs and modernizing processes in the manufacturing industry. He also worked as a freelance consultant, technical trainer and project manager for a number of small and midsize organizations in the late 90’s.

Payam has been with his current role since 2000 as a senior consultant at DesTech, a highly regarded Professional Services firm in Toronto, Ontario, Canada.

In the past 12 years, through DesTech and other corporate training companies and colleges, Payam has taught many courses for Oracle University across North America with many awards to his name.