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 Go to next message
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 59 times)

[Updated on: Thu, 06 June 2013 18:56] by Moderator

Report message to a moderator

Re: SQL not using index from application. [message #586488 is a reply to message #586487] Thu, 06 June 2013 18:57 Go to previous messageGo to next message
BlackSwan
Messages: 22409
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I suspect that Bind Variable Peeking may be the culprit.
Re: SQL not using index from application. [message #586514 is a reply to message #586487] Fri, 07 June 2013 03:16 Go to previous messageGo to next message
John Watson
Messages: 4329
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 #586515 is a reply to message #586514] Fri, 07 June 2013 03:19 Go to previous messageGo to next message
John Watson
Messages: 4329
Registered: January 2010
Location: Global Village
Senior Member
I should have added: your EXPLAIN PLAN output is useless, it doesn't peek the bind so may give inaccurate results. You'll need to substitute the literal values for it to give meaningful information.
Re: SQL not using index from application. [message #586569 is a reply to message #586515] Fri, 07 June 2013 08:42 Go to previous messageGo to next message
kanas
Messages: 3
Registered: June 2013
Junior Member
I forgot to mention that the 1st query In v$sql output came from the application and the 2nd query was done in sqlplus. It had 163 executions from the application and 1 execution from sqlplus which I did. As such repeated executions from application is not changing anything. Do you they is something wrong in how the application is written which may cause this.
Re: SQL not using index from application. [message #586571 is a reply to message #586569] Fri, 07 June 2013 08:51 Go to previous messageGo to next message
John Watson
Messages: 4329
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 Go to previous messageGo to next message
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 #586587 is a reply to message #586586] Fri, 07 June 2013 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 22409
Registered: January 2009
Senior Member
*BlackSwan added {code} tags. please do the same ALWAYS in the future
Re: SQL not using index from application. [message #586588 is a reply to message #586586] Fri, 07 June 2013 11:33 Go to previous messageGo to next message
John Watson
Messages: 4329
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.
Re: SQL not using index from application. [message #586693 is a reply to message #586487] Mon, 10 June 2013 00:27 Go to previous message
michael_bialik
Messages: 599
Registered: July 2006
Senior Member
Can you post the output of:

SELECT * FROM V$SQL_PLAN WHERE SQL_ID = '2g2prrp3z56ah'
?
Previous Topic: System statistics: demonstration
Next Topic: Memory Allocation in 11 G
Goto Forum:
  


Current Time: Thu Jul 10 02:22:02 CDT 2014

Total time taken to generate the page: 0.11364 seconds