table statistics

dbmechanic
dbmechanic used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
which version is your oracle database?

is the query being called from web session and the query being called from non-web session(oracle sql developer, toad, sql*plus) same??

have you performed gather statistics on your schema objects?

--web sessions are not using the correct index, where as the non-web sessions are using the correct index??

how do you know that they are not using the indexes??

Author

Commented:
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
johnsoneSenior Oracle DBA
Commented:
Can you post a sample query from the web session and the non-web session?

When I usually saw this, the web session was using bind variables and the non-web session was not.  This can definitely generate the kinds of plans that you are describing.  With bind variables, the optimizer has less information to make its decision with.

Also, flushing the shared pool can get rid of older plans.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2013.htm#sthref4641

However, be careful using that in a production environment.  Active plans cannot be flushed and everything will be hard parsed until cache is built back up.  You can see some performance hits.  Of course, if you bounce the database that would certainly clear out cache.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Author

Commented:
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??

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial