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.

The Scenario

What options do you have when users are complaining about the performance of applications running on WebLogic?

One very useful option is the capability of generating, gathering, and analyzing diagnostics data about WebLogic servers to find out and fix performance problems.

The WebLogic Diagnostic Framework – WLDF

The WebLogic Diagnostic Framework (WLDF) is a set of components orchestrated together to collect, archive, and access diagnostic information about a WebLogic Server instance and its applications.

Using WLDF services, diagnostic data generated by a running server and the applications deployed within its containers can be:

• Created
• Collected
• Analyzed
• Archived
• Accessed

By analyzing this diagnostic data, you can look into the run-time performance of WebLogic servers and its applications, isolate and solve performance problems.

Architecture

The WLDF Console Extension

The WLDF Console Extension provides a set of visual tools you can use to directly manipulate objects to display diagnostic data about WebLogic Server instances.

WLDF Console Extension Example

After starting up WebLogic and still in an idle state:

The number of execute threads remains constant at 3.

There are zero queued requests.

As the load starts to ramp up, more execute threads are spawned and at some points it time requests have to wait to be executed.

Conclusion

WLDF and the console extension can be extremely helpful while troubleshooting WebLogic server performance problems.

You can learn mote about WLDF by attending the WebLogic Monitoring and Performance Tuning course.

The greatest place to take such training is DesTech in Toronto, Ontario.

Here is a link to the Oracle WebLogic Monitoring and Performance Turning course by Oracle University and delivered by Destech.

Feedback

Let us know how you have been using WLDF, what you like about it and what challenges you have faced for utilizing WLDF.

You can send your feedback online at www.2dev.com or by sending us an email at feedback@2dev.com.

Download

Oracle_WebLogic_Monitoring_WLDF.pdf

Working in the classroom environment, students always ask me about references and resources that can be reliable and used outside of the training environment.  On the page linked below I have tried to keep a relatively a current list of references documents that I find most useful to the students and the enthusiasts.  Hope you find this page useful.

2Dev Links & Resources Page