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.

Our expertise span the following categories:

• Project Management
  • Project and Portfolio Management
  • Govern, Manage & Measure Project
  • Project Review & Recovery
  • Requirement Planning
  • Project Management Office (PMO)
• Oracle Database including:
  • Architecture and Design
  • Administration
  • Real Application Cluster (RAC) implementation
  • Data Guard implementation
  • Backup and Recovery Strategy Planning and Implementation
  • Performance Tuning
  • Monitoring; Remote and Onsite
  • Migration Planning and Implementation
  • Security Audits and Management
• Grid Control
  • Implementation
  • Troubleshooting
• Oracle Application Server including:
  • Architecture and Design
  • Installation and Configuration including Forms Services, Reports Services, Discoverer Services and Oracle Containers for Java EE (OC4J)
• Identity Management
  • Planning
  • Implementation
• Application Development
  • Java
  • J2EE including ADF and JSF
  • Oracle Forms
  • Oracle Reports
  • Oracle Discoverer
• IT Services Outsourcing
  • Planning
  • Management
• Application Migration Services
  • Forms to Forms
  • Forms to Java & Forms Modernization
  • Reports
• Oracle and Virtualization
  • Analysis, Design and Implementation