Link to home
Avatar of dbmechanic
dbmechanicFlag for United States of America

asked on

table statistics

we have a frequently executed query by users from web or by connecting directly to the database. web sessions are not using the correct index, where as the non-web sessions are using the correct index.  web sessions are connecting to the database using JDBC thin client. is there any setting on the web session, which would cache the execution plan of the frequently executed queries.
ASKER CERTIFIED SOLUTION
Avatar of Wasim Akram Shaik
Wasim Akram Shaik
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of dbmechanic

ASKER

this is in oracle 10gR2.  

the query that is being called from web and non-web session is same.

we gathered stats on the tables and indexes.

i used awrsqrpt.sql report after finding out the sql_id from v$sqlarea view.  The SQL statistics report generated by awrsqrpt.sql script, had execution plan.  the execution plan showed that query execution path did not use the correct index.

non-web sessions are the session that i connected using sqlplus, it showed the right index in the access path.

Thanks
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
author, after flushing out cache(or a db bounce) as suggested  by johnsone, if you still get the plans different, then along with the query, post a sample plan too..!!
i use SQL developer to monitor the sessions and was able to see query that was in progress in web session, as it was calling a common package, that was used by batch operations as well as the web.  so i know for sure, without any doubt that the query is same.  After gathering new stats on the table, the query is working fine.  thanks for your help and comments.
dbmechanic, why the penalty of B grade..wasn't this answered well?? what else were you expecting??
sorry wasimibm, i did not understand your question.  which reply are you referring to ?