Home » RDBMS Server » Performance Tuning » SQL not using index from application. (Oracle 11gR1)
| SQL not using index from application. [message #586487] |
Thu, 06 June 2013 18:40  |
 |
kanas
Messages: 3
Registered: June 2013
|
Junior Member |
|
|
We have a DELETE statement when coming from application is not using index but when run from Toad or SQLplus as same user uses index. Explain plan also shows using index.
I did a query on v$sql below is the output of the query( I have attached the same as a txt file). Any ideas what to look for ?. All the stats are up to date and confirmed from the developer the variable B1 is using the same datatype as column MAXMKY.
SQL_TEXT SQL_ID DISK_READS OPTIMIZER_HASH_VALUE
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah 19,099,189 1,846,735,884
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah 0 1,846,735,884
OPTIMIZER_COST HASH_VALUE PLAN_HASH_VALUE MODULE PARSING_SCHEMA_NAME
30,858 1,207,081,296 2,992,192,226 httpd@xx (TNS V1-V3) EXXX
3 1,207,081,296 111,174,705 httpd@xx (TNS V1-V3) EXXX
BIND_DATA CPU_TIME ELAPSED_TIME IS_BIND_SENSITIVE IS_BIND_AWARE
BEDA0B10070051B0F82C000101C0021606C53402555D3D 2,531,930,000 6,176,835,658 N N
BEDA0B10070051B0DCF3000101F0012003670130 10,000 0 Y N
Thanks,
SK
*BlackSwan added {code} tags. please do the same ALWAYS in the future
-
Attachment: attach.txt
(Size: 0.61KB, Downloaded 1891 times)
[Updated on: Thu, 06 June 2013 18:56] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL not using index from application. [message #586514 is a reply to message #586487] |
Fri, 07 June 2013 03:16  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
To expand on SB's suggestion: I don't have access to an 11.1.x DB to check, but I remember that adaptive cursor sharing and feedback based optimization sometimes resulted in erratic performance. The mechanisms have stabilized in later releases, and are now (I believe) often excellent. In the current release, an equality predicate on a non-primary key column (which i assume is what you have) will immediately cause the CBO to flag the query as bind_sensitive, then if a repeated execution with different binds shows different execution statistics it will be marked as bind_aware, the bind re-peeked, and re-parsed for the third execution. In your example, only the second cursor is bind_aware: perhaps 11.1 did this only on the second execution. So you need to execute a couple more times, with different binds, and see if you get executions switching between bind_aware cursors.
|
|
|
|
|
|
|
|
| Re: SQL not using index from application. [message #586571 is a reply to message #586569] |
Fri, 07 June 2013 08:51 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, I don't think I can assist: you are concealing too much information. For example, both the cursors you describe in your first post were parsed by a web module, but in your last post you say that one of them was SQL*Plus.
Perhaps someone else can make sense of this.
|
|
|
|
| Re: SQL not using index from application. [message #586586 is a reply to message #586571] |
Fri, 07 June 2013 11:13 |
|
kanas
Messages: 3
Registered: June 2013
|
Junior Member |
|
|
Sorry, I didn't mean to conceal information,I forgot yesterday. I understand it is saying webmodule but it is coming from sqlplus. Is this because both have the same SQL_ID and Oracle is considering it came for webmodule as that was the original source. This is what I found today morning.
When I did a query on v$sql on the same instance I saw only statement which matched the DELETE LOTA statement with the output below. See the LAST_LOAD_TIME for the 2 sql which is the time I executed the query from sqlplus.
SQL_TEXT SQL_ID LOADED_VERSIONS EXECUTIONS
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah 1 46
FIRST_LOAD_TIME DISK_READS ROWS_PROCESSED OPTIMIZER_MODE OPTIMIZER_COST
2013-05-23/22:03:04 5,171,420 0 ALL_ROWS 30,858
OPTIMIZER_ENV_HASH_VALUE MODULE ELAPSED_TIME LAST_LOAD_TIME IS_BIND_SENSITIVE IS_BIND_AWARE
1,846,735,884 httpd@xx(TNS V1-V3) 1,646,923,800 2013-06-07/00:48:01 N N
After this I ran the same statement twice from sqlplus as EXXX user and this is the output of v$sql
SQL_TEXT SQL_ID LOADED_VERSIONS EXECUTIONS FIRST_LOAD_TIME
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah 1 46 2013-05-23/22:03:04
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah 1 2 2013-05-23/22:03:04
DISK_READS ROWS_PROCESSED OPTIMIZER_MODE OPTIMIZER_COST OPTIMIZER_ENV_HASH_VALUE
5,171,420 0 ALL_ROWS 30,858 1,846,735,884
1 0 ALL_ROWS 3 1,846,735,884
MODULE ELAPSED_TIME LAST_LOAD_TIME IS_BIND_SENSITIVE IS_BIND_AWARE
httpd@xx(TNS V1-V3) 1,646,923,800 2013-06-07/00:48:01 N N
httpd@xx(TNS V1-V3) 0 2013-06-07/10:42:38 Y N
Any help is greatly appreciated.
|
|
|
|
|
|
| Re: SQL not using index from application. [message #586588 is a reply to message #586586] |
Fri, 07 June 2013 11:33 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, one more try. I've already given you a few hints.
First, please read How to use [code] tags and make your code easier to read Your posts as written are unreadable.
Second, you need to provide a lot more information. For example, you haven't said how many rows are impacted by the query when you run it with various values for the bind, or how many rows are in the table.
Third, why do you think there is a problem? When you get multiple plans for one sql_id it is often because different plans are needed for different binds.
Fourth, read our OraFAQ Forum Guide there are instructions on how to post a performance problem.
I'm sure this is fixable.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 10:12:33 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
