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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..!!
ASKER
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??
ASKER
sorry wasimibm, i did not understand your question. which reply are you referring to ?
ASKER
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