Full table scans 1

[jdemmi]

Posting in Oracle forum for additional review

HELP!!! I have a HUGE problem. We recently upgraded a 3rd party, packaged application and in doing so we also upgraded from Oracle 9.2 to 10g (10.2.0.1). Ever since then MANY of the application's queries are ignoring the indexes and executing full table scans.

The vendor is till researching the issue but seems to think that there is a character set issue which is causing Oracle to ignore the indexes on parameterized queries and execute full table scans. To the best of my knowledge I have verified that both the client and the server are using the same character set.

I even executed an explain plan on a query.

Once from TOAD on my PC -- indexes used.
Once from SQL+ on the application server -- indexes used.

But when similar queries are executed via the application (through w3wp.exe) the indexes are ignored.

The vendor seems to think that Microsoft IIS/ASP.NET (w3wp.exe) is receiving a request for a different character set when a connection attempt is made. Their theory here is that, since the character set doesn't match Oracle is ignoring it and therefore executing full table scans.

They seem fairly condfident as they have seen similar issuses resolved by changing the character set at other customer sites.

My question is where the heck can I see the character set being used by a background web process?

Oracle DB 10.2.0.1
Oracle Client 10.2.01
Microsoft Server 2003
IIS6

Note -- we upgraded our test environment to Oracle 10.2.0.3 -- no difference.

My second question is, what else can I check to see what the heck is going on?

-- Jason
"It's Just Ones and Zeros

 

[sem]

Is your statistics up to date?

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

Check if the NLS_LANG environment variable on the client side matches the database.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jdemmi]

Yes, they weren't -- we updated them, no difference, deleted them and recreated them.

No difference.

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Already checked the NLS settings -- NUMEROUS times

-- Jason
"It's Just Ones and Zeros

 

[Dagon]

I've seen problems in the past where indexes are not used because the parameters passed to the sql have a different datatype to the column being compared e.g.

select * from table where column_5 = 100

and column_5 is a varchar2. An implicit data conversion takes place, which suppresses the index usage. It's not necessarily something you would catch when running the SQL in TOAD, because the data type of the parameter is being defined by the client application.

 

[pete91z]

It does sound like a possible datatype issue as Dagon suggested. Just out of interest, are you able to add optimizer hints to the code/queries to get the expected behaviour?

 

[jdemmi]

Dagon -- thanks....with my help the vendor discovered the same thing early yesterday, we've been testing the fix ever since.

Our database columns are Varchar and the params were set to NVarchar

-- Jason
"It's Just Ones and Zeros