While working on Performance issues, I came across a rarely used user property known as ‘Use Literals For Like’.
Purpose:
If you have gone through logs sometime then you might have noticed that if you query with a text in a field then the query generated at backend has lot of like clauses which queries with all the permutations and combinations for that text. For example if I query on the Last Name Field with “SADMIN” in application the query generated at backend might have clause as shown below
((T11.LAST_NAME LIKE :2 OR T11.LAST_NAME LIKE :3 OR T11.LAST_NAME LIKE :4 OR T11.LAST_NAME LIKE :5) AND UPPER(T11.LAST_NAME) = UPPER(:6))
And the bind variables will have values like
Now, this can lead to performance issues in cases if the query is done on long fields such as Comments or Description field in Service Request BC. In that case using this user property might help.
- 2:= ‘sa%’
- 3:= ‘Sa%’
- 4: = ’sA%’
- 5:= ‘SA%’
- 6:= ‘SADMIN’
If you use this user property on a particular field then for that field bind variables will not be used, it will be replaced by the values directly.
Usage:
It is a field level user property. Follow the steps given below to define this property.
- Open Siebel Tools
- Query for the BC which contains the field on which you want to define the user property
- Query for the Field
- Click on the + sign on Field Object in Object Explorer
- Select Field User Property Object
- In the Field User Property List Applet create a New Record
- Provide following detail
Name: Use Literals For Like
Value: TRUE
And you are done. After you do that the query generated on backend will not use bind variables for this particualr column and your query will look like
(T11.LAST_NAME LIKE ‘SADMIN%’)
(6 votes, average: 3.5 out of 5)
Loading ...
2 Comments at "Use Literals For Like"
This is an absolutely terrible idea. Bind variables are critical to efficiently use the buffer cache on the database.
IF you do this widely over the application you will fill the Library cache on the database with single use SQL statements and kill performance, as each statement will have have be re-parsed constantly.
Please, please NEVER do this.
Much better to turn off case-insensitivity for the application that do this.
Yes, I absolutely agree with you PH.
Never use it without proper analysis and testing as correctly pointed by PH. I used it where I observed the behaviour that using bind variables for a particular query and a particular column was hampering the performance…
I will be writing a follow up post explaining the impact and care that you need to take before you go for it. Instead of saying NEVER do this I would say USE EXTREME CAUTION BEFORE DOINIG THIS.
Comment Now!