Avatar of dbmechanic
dbmechanic
Flag 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.
Oracle Database

Avatar of undefined
Last Comment
dbmechanic

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wasim Akram Shaik

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..!!
dbmechanic

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Wasim Akram Shaik

dbmechanic, why the penalty of B grade..wasn't this answered well?? what else were you expecting??
dbmechanic

ASKER
sorry wasimibm, i did not understand your question.  which reply are you referring to ?